Avoid Date overlap when creating a record

Hi all,

Im encountering an issue and hopefully someone can help me with this.

When trying to create a record there must be a check if there is any overlap between periods in time.

For example 

We have a record with VAT 19% Startdate 24-02-2020 Enddate 28-02-2020

I need to avoid that a new record for VAT 19% is created that has any overlap with the record above mentioned.

I created the following aggregate

In this case if I try to create a 19% record with Startdate 23 and enddate 29, despite the overlap the record is created

Thanks in advance! :)

Hi António,

Have you tried adding parenthesis between your two conditions? Usually, making a boolean condition with ANDs mixed with ORs won't produce the results one expects.

Try replacing your second filter with something like this:

(FirstCondition AND SecondCondition) OR (ThirdCondition AND FourthCondition)

Afonso Carvalho wrote:

Hi António,

Have you tried adding parenthesis between your two conditions? Usually, making a boolean condition with ANDs mixed with ORs won't produce the results one expects.

Try replacing your second filter with something like this:

(FirstCondition AND SecondCondition) OR (ThirdCondition AND FourthCondition)

Thanks Afonso for your reply!

Unfortunately the behavior is still the same :(



I think it's the condition itself - I tried writing it out with your date examples, and assuming VariableForm is the new Record, this is what gets evaluated:

VariableForm.Startdate = 23

VariableForm.Enddate = 29

Variable.Startdate = 24

Variable.EndDate = 28

(23 >= 24 AND 23 <= 29)
OR
(29 >= 24 AND 29 <= 28)

Which evaluates to false: is this what your code depends on? What do you do with the results of the Aggregate?

Hi Antonio,


The check should be as per the below pseudo code:

(Both start date and end date of new vat  < old vat start date)

OR

(Both start date and end date of new vat  > old vat start date)


Regards,

Saugat

Hi Antonio,

there are only 4 ways in which there can be overlap, looking at it graphically, the red one being the period currently being added / updated :



From this picture it is then easy to see, that the overlap can be detected in one of 2 conditions : either the start date of another period is between start and end date of currently edited period (the 2 blue examples) or the start date of the currently edited period is between start and end date of another period (the 2 green examples.

So your condition to find an overlapping record could be something like :


Form.Record.Variable.Startdate S <= Variable.Startdate S

and Form.Record.Variable.Enddate E >= Variable.Startdate S

or

Variable.Startdate S <= Form.Record.Variable.Startdate S

and Variable.Startdate E >= Form.Record.Variable.Startdate S


If you are planning on allowing updates, be sure to also exclude the currently edited period from your query,

So

Variable.Id <> Form.Record.Variable.Id


good luck,

Dorine

Dorine Boudry wrote:

Hi Antonio,

there are only 4 ways in which there can be overlap, looking at it graphically, the red one being the period currently being added / updated :



From this picture it is then easy to see, that the overlap can be detected in one of 2 conditions : either the start date of another period is between start and end date of currently edited period (the 2 blue examples) or the start date of the currently edited period is between start and end date of another period (the 2 green examples.

So your condition to find an overlapping record could be something like :


Form.Record.Variable.Startdate S <= Variable.Startdate S

and Form.Record.Variable.Enddate E >= Variable.Startdate S

or

Variable.Startdate S <= Form.Record.Variable.Startdate S

and Variable.Startdate E >= Form.Record.Variable.Startdate S


If you are planning on allowing updates, be sure to also exclude the currently edited period from your query,

So

Variable.Id <> Form.Record.Variable.Id


good luck,

Dorine


Or, to have an even shorter filter


Variable.Startdate <= Form.Record.Variable.Enddate and 

Variable.Enddate >= Form.Record.Variable.Startdate and

Variable.Variable = Form.Record.Variable.Variable and

Variable.Id <> Form.Record.Variable.Id