Need help with a query formula for ranking dates.
I have a table of Training cycle start dates. Each record is a Training Start Date (FID 6 - Date Field) for a Training Team (FID 25 - Text lookup field from related Table) with a Start Date Status (FID 7 - Text). I have created a Formula Numeric field titled Rank.
I need to rank the upcoming Training Start Dates for the Traditional team only, and exclude any dates that are past or do not have a Status of "Available." The one coming up soonest on/after today ranked 1, next ranked 2, and so on.
We've tried several formulas without success.
var text QUERY = "{6.AF.'" & Today() &"';}AND{25.EX.'Traditional'}AND{7.EX.'Available'}";
If([Start Date Status]<>"Available", null, Size(GetRecords($QUERY))+1)
Attempt 1 above failed. Returned rank 11 for all available start dates, past and present.
var number position = Size(GetRecords("{6.AF.'"& Today() &"'}AND{25.EX.'Traditional'}AND{7.EX.'Available'}")), $position+1
Attempt 2 above failed. Returned rank 10 for all available start dates, past and present.
var text baseQ = "{25.EX.'Traditional'}AND{7.EX.'Available'}"; var text QAfter = $baseQ; var number position = Size(GetRecords("{6.AF.[Training Start Date]}AND{25.EX.'Available'}")); $position+1
Attempt 3 above failed. Returned rank 1 for all dates.