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 ...
formula query
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)