Need

When managing timeslots data, it is essential to ensure that they do not overlap.

Without proper validation, users may unintentionally create overlapping time ranges (for example, 08:00-12:00 and 11:00-15:00 on the same day). This can lead to inconsistent scheduling, reporting inaccuracies, and operational conflicts.

The objective is therefore to enforce a rule at the data model level that prevents overlapping time slots, ensuring data integrity and reliable time management. 


Summarized Solution

To prevent overlapping time slots, we will implement a validation rule using a SemQL query.

This rule will check, before saving a record, whether another record exists for the same data and will verify whether the time intervals intersect.

If an overlap is detected, the validation rule blocks the record creation or update, ensuring that only non-overlapping time slots can be stored in the system.

This approach guarantees consistent scheduling while keeping the validation logic centralized in the data model.


Detailed Solution

Let's take an example. Here, we have an entity Workhours and our goal is to build a validation rule that validates if timeslots with the same Workhours and Days are not overlapping.


App Builder configuration:

  • Go to the app builder.
  • In your entity, add a validation rule:

  • Set the validation condition using below SemQL query:

any Day.WorkHourses has (FID_WorkHoursType= ParentRecord.FID_WorkHoursType
and FID_Building= ParentRecord.FID_Building
and (to_timestamp(ParentRecord.StartTime,'HH24:MI')< to_timestamp(EndTime,'HH24:MI')
and to_timestamp(ParentRecord.EndTime,'HH24:MI')> to_timestamp(StartTime,'HH24:MI')
)
)
  • Save and deploy the model.


Application tests:

  • Go to the application.
  • Here is the data set we are starting with:

  • Let's create new Work Hours:

  • Here we see our validation in action > end time must be greater than the start time for same work hours and same day:

  • Here is the desired output where we can see the timeslots are different for same day and same work hours type no timeslot is overlapping :