[SalesForce] Check if Event already exists in Salesforce via Flow

I have a form/flow that has three fields Start date/Time, End Date/Time and location. What I want is when the user selects the values for those 3 fields in the form and proceeds, I do a record lookup on all the existing events in Salesforce and a decision element. So if the dates chosen by the user conflicts with any of the existing event in Salesforce, it chooses one path, if no conflict was found, it takes another path in the flow.

For some reason, when I enter dates that conflicts with existing event, even then it does not take the path that it is supposed to choose when a conflict is detected, it always chooses the other path no matter what.

Here are the screenshots of the record lookup.
enter image description here

enter image description here

Screenshot of the isthereconflict variable.
enter image description here

Here is the screenshot of decision element.

enter image description here

Best Answer

The challenge here is that you actually have multiple possible cases to test. (Let's assume that in all cases the location matches.)

  1. The specified start and end times exactly match an existing event or are between the start and end times of an existing event (completely contained).
  2. The specified start time occurs before an existing event's start time, but specified end time is between the existing event's start and end times (overlaps the start).
  3. The specified start time occurs between the start and end times of an existing event, but the specified end time is after the existing event's end time (overlaps the end).
  4. The specified start time occurs before the start time of an existing event, and the specified end time occurs after the end time of the same existing event (exactly matches or overlaps the entire event).

You are only testing the 4th case (specified event starts before and ends after an existing event, or is an exact match since it's GTE/LTE), so any other cases are not being matched. (When I reproduced your criteria/decision, I received exact matches and my decision routed correctly.)

The problem with using Get Records to check this is that it doesn't have the ability to check for custom condition logic, such as (1 AND 2) or (3 AND 4) or (5 AND 6). It can only do all records or an AND on all conditions.

What you might be able to do instead is get all events, loop through them, and then move the decision logic (which does support advanced logic) into the loop. That's not very efficient though.

Slightly more efficient, you could run Get Records additional times with the different sets criteria if no matches were retrieved. So you could use a variable to indicate which criteria you had already checked (assigning a value each time you hit the Decision element) and then rerouting to a different Get Records each time no record was retrieved until finally all four queries had been made. Kind of awkward, and you might find that the overhead to make multiple queries results in a wait.

Other than that, it might be faster and more efficient to use Apex to write a SOQL query and create an invocable action that you can then add to your flow.