Forum Discussion

AdamKrzyzanek's avatar
AdamKrzyzanek
Qrew Captain
3 years ago

Formula Queries: Show previous Record if Two Data Ranges Overlap

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 formulas

In 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
------------------------------
APIs and custom code
formula queries
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    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
    ------------------------------
    • AdamKrzyzanek's avatar
      AdamKrzyzanek
      Qrew Captain
      3 years ago
      @Mark Shnier (YQC) and @Don Larson thank you both for reply. I tested it and indeed it works in 99 % of a time.

      In my previous formulas it was not working for particular dates and it is also not working with your suggested formula.​

      When I dig further I found out that problem lays in date format. For my App I have Date format set as DD-MM-YYYY​​. And my assumption is that formula queries are using MM-DD-YYYY. 

      It started to work when I first use "ToText" on my date fields to Convert date to QB standard format from DDMMYYYY to MMDDYYYY.
      In Formula Queries I used converted date field as you suggested, and it works :)

      ------------------------------
      Adam Krzyzanek
      ------------------------------