Forum Discussion

GeoffBarrenger's avatar
GeoffBarrenger
Qrew Captain
1 month ago

Check Date against a range of dates in another table

I'm trying to take a date of an event, and return the name of the Fiscal Period that date falls within, from another table!!

Here's the scenario.

A transaction happens in Table A.  The date of the event is 5/24/2026.

Normally, on a summary table, that transaction would all in the the May 2026 summaries of total sales.  Too simple!!

Our finance team has fiscal months, closing on the last weekend of the month!  Meaning this would actually roll into next Fiscal Month! To make matters worse, they do not refer to months, but Periods.

Below is a table of the FY and Period Labels.

What I'd like to do - is from my transactions list, is query the FY and Period Labels Table, and pull the FY and Period which this transaction falls within (Start and End Date) so that I can then build summaries of my transactions using FY and Period.

For example:

GetFieldValues(GetRecords( Date of Transaction is On or After the Start Date AND Date of Transaction is On or Before the End Date) Return the FY and Period Label.

Any ideas?

FY and Period LabelFiscal YearPeriodStart DateEnd Date
FY27 - P02FY27P024/26/265/23/26
FY27 - P03FY27P035/24/266/27/26
formula query

3 Replies

  • GeoffBarrenger's avatar
    GeoffBarrenger
    Qrew Captain
    1 month ago

    Hi all thanks for the replies,  figured it out with this formula where $setDate is the date of the event happening - and I bring in the "Fiscal Period Label"

    Your options were great, but unfortunately because it's not always a certain number of days, but a variable based on weekends...I needed to have a table with the Fiscal Periods listed.  Luckily our finance team was able to provide!

     

    FY and Period LabelFiscal YearPeriodStart DateEnd Date
    FY27 - P02FY27P024/26/265/23/26
    FY27 - P03FY27P035/24/266/27/26

     

    ToText(GetFieldValues(GetRecords("{9.OBF.'" & $setDate & "'} AND {10.OAF.'" & $setDate & "'}", [_DBID_Fiscal_Calendar]), 6))

  • MarcDouglas's avatar
    MarcDouglas
    Qrew Member
    1 month ago

    To be more explicit about the boundary conditions:

    var text qryFirstOfMonth = "{6.OAF.'" & Date($FYStartYear,10,1) & "'}";

    var text qryEndOfMonth = "{6.OBF.'" & ($endOfMonth + Days(1)) & "'}"; // Add one day and use "before" instead of "on or before"

    Or alternatively, ensure your End Date field in the Period table is set to the last second of the period (e.g., "5/23/2026 11:59:59 PM") rather than just the date.

  • hueyal's avatar
    hueyal
    Qrew Cadet
    1 month ago

    I have a similar query that you can probably review and adjust as needed.

    This queries another table where the date is between the fiscal year start and end.  I'm query the same date field (ID = 6).  In your formula, the qryFirstOfMonth would correspond to the FID of Start Date and qryEndOfMonth would correspond to the FID of End Date.  In line 8, you would replace the table name (_DBID_OTHER) and FID (27) to your table name and FID of the value you want returned.

    Based on your setup, you should only get one value back.

    var number FYStartYear = If(Month([Month])<10,Year([Month])-1,Year([Month]));

var text qryProvider = "{33.EX.'" & [Related Provider] & "'}";
var date endOfMonth = LastDayOfMonth([Month]);
var text qryFirstOfMonth = "{6.OAF.'" & Date($FYStartYear,10,1) & "'}";
var text qryEndOfMonth = "{6.OBF.'" & $endOfMonth & "'}";

var textlist detailRecords = GetFieldValues(GetRecords($qryProvider & "AND" & $qryfirstofmonth & "AND" & $qryEndOfMonth, [_DBID_OTHER]),27);

ToText($detailRecords)