Discussions

 View Only
  • 1.  Overlapping dates

    Posted 09-05-2019 16:36
    I have an out of office table. I need to check that new records don't overlap with existing records. Any way to do this natively?

    The most recent post on this I could find was from 2017, and it pointed to an AJAX solution, not a native one. 




    ------------------------------
    Nathaniel Cahill
    ------------------------------


  • 2.  RE: Overlapping dates

    Posted 03-01-2022 11:25
    Hi Nathaniel, I have been looking to create an overlapping report based on date/time fields as well. 
    Is there a function in QB that can reproduce the same results of the SUMPRODUCT excel formula?

    Sadly, I have not found a solution yet...

    ------------------------------
    Francesco Spiga
    ------------------------------



  • 3.  RE: Overlapping dates

    Posted 03-01-2022 11:29
    This can likely be done with formula queries.

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



  • 4.  RE: Overlapping dates

    Posted 03-06-2022 15:09
    Edited by D S 03-06-2022 15:11

    Could you please tell what that formula query look like?

    I am also in somewhat similar situation. I have records with fields start date and end date. I want to add a custom data rule, to not allow creating records that overlap existing records

    thank you!



    ------------------------------
    D S
    ------------------------------



  • 5.  RE: Overlapping dates

    Posted 03-07-2022 11:01
    Edited by Matt Hardy 03-07-2022 11:20
    Can you explain what you mean by overlapping?
    • Matching start and end dates?
    • Matching start or end date?
    • Start date between an existing start and end date?
    • End date between an existing start and end date?

    Here is an example that can be used in a Formula - Checkbox field to indicate if the entered start and end dates both fall within an existing range.
    // Define Variables
    var date startDate = [Start Date];
    var number startDateFID = 13; // Field ID of the above field
    var date endDate = [End Date];
    var number endDateFID = 14; // Field ID of the above field
    
    // Define Query
    // Searches for existing records where the [Start Date] is BEFORE the current record [Start Date] and the existing [End Date] is AFTER the current [End Date]
    var text query = "{" & $startDateFID & ".BF.'" & $startDate & "'} AND {" & $endDateFID & ".AF.'" & $endDate & "'}";
    
    // Run the query and count the results. If the query returns a result, tick the checkbox
    If (Size(GetRecords($query)) > 0, true)​




    ------------------------------
    Matt Hardy
    ------------------------------



  • 6.  RE: Overlapping dates

    Posted 03-08-2022 08:27
    Thank you, Matt.

    What formula query would be for 

    • Start date between an existing start and end date?
    • End date between an existing start and end date?

    Can it be achieved through IR?


    ------------------------------
    DS
    ------------------------------



  • 7.  RE: Overlapping dates

    Posted 03-08-2022 08:39
    Change the query from AND to OR

    ------------------------------
    Matt Hardy
    ------------------------------