Validation for gaps in date ranges

Modeling
1

Hi all,

I have a repeatable list with some information which is valid for a certain period. This validity period is defined as a DateRange field in my model. I want to validate that validity periods are plausible - this should happen when the user is saving the form containing this repeatable group.

I am already validating that validity periods are not overlapping (using DateRangesOverlap).

But I also want to make sure that there are no gaps between these validity periods: i.e. if there is a data set for 01.01.2025-01.02.2025 and then the user adds a new one for 03.02.2025-01.03.2025 then the validation error should be triggered because you have no data defined for 02.02.2025. Is this somehow possible?

2

Hi @jana-dark-core

Yes, this is possible.

The first thing to mention is that this solution will only work with a Full Validation. This means you need to model a Full Validation on the Event button that is being clicked when the user saves the form.
This solution will not work with Partial Validation. This means that the user will never see a warning whilst interacting with the repeat.

I did it like this:

4NmzPGY5Mc
4NmzPGY5Mc1920Ă—1200 184 KB

Using the Validation Rule:

CurrentRepetition(DateRanges) != 1
And GroupFilled(DateRanges)
And NotExactlyOneFieldFilled(DateRanges*/DateRange Having 
        DifferenceInDays(
            EndOfDateRange(DateRanges/DateRange),
            StartOfDateRange($DateRanges/DateRange)) == 1)

You can check my model (2023.06-ext6)
DateRangesWithNoGapsOrOverlaps_DM.json (5.4 KB)

The computed Field “NumberOfGaps” is just there to aid understanding and is not required. This rule works without additional helper fields.

The “NoGaps” rule checks the following:

  • Is this row this first row of the repeat?
CurrentRepetition(DateRanges) != 1

This is important as the first row of the repeat will never match this condition.

  • Does this row exist?
GroupFilled(DateRanges)

The rule should only fire if the row in the repeat has been created

  • Is there exactly one Starting Date for any Date Range that comes one day after the End Date of this Date Range
NotExactlyOneFieldFilled(DateRanges*/DateRange Having 
        DifferenceInDays(
            EndOfDateRange(DateRanges/DateRange),
            StartOfDateRange($DateRanges/DateRange)) == 1)

This part of the rule can be seen in the “NumberOfGaps” Field.

This rule assumes that the first Date Range is the earliest Date Range in the repeat. This is enforced in a further rule “EntriesInOrder”.

You could model this without having to have the date ranges in order of enforcing the first date in the repeat is the earliest date but this requires computed helper Fields to extract the start and end dates into extra Date Fields.

fyi There are also examples of Rules with Dates in the e-Commerce workspace that comeswith the installer from 2024.06 that you can take a look at for inspiration. See Product_Common_DM and the “Pricing” repeat.

3

I just stumbled on another solution in my project’s documentation, working with dates “Start/End” rather than date ranges. But as it might help somebody:

model is

image
image286Ă—169 6.07 KB

rule is

image
image747Ă—211 28 KB

explanation:

  • all fields filled
  • end is not the highest
  • end + 1 is not included in any interval
4

Adapting the example shown by mrousseau to date range fields could look like this:

image
image1141Ă—513 58.6 KB

With result:
image
image1130Ă—403 37.4 KB

5

Oh @josef-silent-vector,

I love your idea to use

AtLeastOneFieldFilled (K*/F Having EndOfDateRange($K/F) < EndOfDateRange(K/F))

to check if any of the DateRanges that you entered end before the one that you are currently checking.

That’s much cooler than enforcing that the first date is the earliest and excluding the first row as I did:

6

Thank you all for your solution ideas! Validation of @josef-silent-vector worked like a charm :smile: