1

I have a custom object "Object1" with validation dates ValidStartDate and ValidEndDate.

Is it possible with a Flow to create a validation that would prevent a new record of Object1 to be created if its Validation period is overlaping with already created ones?

For example, I have a record with Start date 1.01.2024, end date 1.02.2024. I must not be able to create a record with Start date between 1.01.2024 and 1.02.2024. the Strt date of a new record should be avaliable starting from 2.02.2024.

I tried to create a record triggered flow and with GetRecords to get all the records of Object1, then in a decision outcome try to campare the ValidStart of the new record and the ValidEnd from GetRecords, but it's not giving me the option to compare them, or I am not understanding how the GetRecord is working. I would then shoot an error, if there is an overlap. So, is it possible at all to do such a check with a flow, or is it possible only with a trigger?

Improve this question
2
  • Date range overlap is one of those problems that sounds easy, but is actually fairly difficult. I don't think you'd need a full-blown trigger to do this, but doing this in apex and providing an @AuraEnabled method so you can use it in your flow is going to ease the pain a bit (e.g. it's fairly simple to find the min start date and max end date in your records going through your flow, then use those to limit the number of existing records you'll query, making it more bulk-friendly).
    – Derek F
    Commented Jun 25 at 16:04
  • @DerekF This is actually easier to solve in a Record-Triggered Flow than Apex, because flows handle the bulkification for you.
    – sfdcfox ♦
    Commented Jun 25 at 16:57

1 Answer 1

Reset to default
3

Don't query the entire database to find a value. That's going to run into governor limits. Instead, in your Get Records element, write your filter as follows:

  1. End Date greater than $Record.Start Date AND

  2. Start Date less than $Record.End Date AND

  3. Id not equal to $Record.Id

A record that ends after the start date and starts before the end date overlaps.

I know it sounds too easy, but it really is. Let's make a truth table:

S1 E1 S2 E2 E2 > S1? S2 < E1? Overlap? Note
1/1/2024 1/31/2024 1/1/2023 1/31/2023 No Yes No Too early
1/1/2024 1/31/2024 1/1/2025 1/31/2025 Yes No No Too late
1/1/2024 1/31/2024 12/15/2023 1/15/2024 Yes Yes Yes Overlaps start
1/1/2024 1/31/2024 1/15/2024 2/15/2024 Yes Yes Yes Overlaps end
1/1/2024 1/31/2024 12/15/2023 2/15/2024 Yes Yes Yes Encloses range
1/1/2024 1/31/2024 1/10/2024 1/20/2024 Yes Yes Yes Enclosed by range

S = Start, E = End

As you can see, there are six possible scenarios, and in all cases, both date conditions must be satisfied in order for the date to be considered overlapping.

Improve this answer
3
  • Thanks, but I'm still a little confused. How then I compare the get results with the triggering record to validate if the record can be created? My main struggle is with the validation itself. After the Get element I placed a decision, where I wanted to place 2 outcomes: if the the start date of the triggering record is in the range of any of existing records, then I throw the error, end if not - nothing happenes. And the problem is that in the outcome condition If I place the Get element in resource I can't place the start date in the value and vice versa. How do I throw the validation error?
    – SlavaBychenko CT
    Commented Jun 25 at 20:55
  • @SlavaBychenkoCT If you set the filter as I described, the Get Records element already contains the overlapping record, if one was found. Your Decision can be as simple as {!Get_Overlapped_Record.Id} Is Null $GlobalConstant.false. To show an error, use the Custom Error element.
    – sfdcfox ♦
    Commented Jun 26 at 0:19
  • Awsome, thanks! it works perfetly!
    – SlavaBychenko CT
    Commented Jun 26 at 7:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .