Formula Queries: Show previous Record if Two Data Ranges Overlap

Question

I need you help as I lost my mind already on this testing :)I want to build Formula Queries which will provide me number of previous record if its date overlap with current data range. All possible scenarios below for test below. Also add Excel file which present my needs.Now I was preparing tests for 510 in Excel and come up with such formulasIn Excel I created formulas to support this check:After this I tried to make it to look like Formula Query connected with OR.
Excel Formulas are marked with Orange numbers 1, 2 and 3 and corresponds with below formulas connected later on with OR:1) ({63.GTE.'" & [AltBOM Request - Starting Date] & "'}AND{63.LTE.'" & [AltBOM Request - End Date] & "'})2) ({64.GTE.'" & [AltBOM Request - Starting Date] & "'}AND{64.LTE.'" & [AltBOM Request - End Date] & "'})3) ({64.GTE.'" & [AltBOM Request - End Date] & "'}AND{63.LTE.'" & [AltBOM Request - End Date] & "'})But after placing it in it does not show anything to my record 510, and also for 509 is showing record 392 which is out of range.
What is wrong with my Formula Queries?------------------------------Adam Krzyzanek------------------------------

MarkShnierYou · Answer

Detecting overlapping appointments is a classic problem typically almost impossible to solve with previous native Quickbase but which should be very well suited using Formula Queries.

I do not have time right now to actually do a test but your formula looks very long to me. I think you just need to look for where the start date of the record you are on is before than the end date of the other records and the end date of the record you are on it is after the start date of the other records.

Can you give that concept a try and post your solution if it works?

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

Sign up

Login to the community