Forum Discussion

AVulliet's avatar
AVulliet
Qrew Member
10 days ago

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.

formula query
  • Mez's avatar
    Mez
    Qrew Cadet
    9 days ago

    Could you solve this with a simple formula field using Days from Today? 

    If you want to continue with rankings, you will also need to consider the distance of self (current record) from all other Training Start Dates. Here is one way to solve. 

    Example: fid 6 = Start Date; also substitute in your values for Traditional and Available in place of Full

    var text query = "{6.AF.'" & Today() & "'}AND{6.BF.'" & [Start Date] & "'}AND{'" & [Type] & "'.EX.'Full'}";

    If(
        [Type] = "Full" and [Start Date] > Today(), Size(GetRecords($query) ) + 1,
        null
    )

    Also, it looks like you have a terminating character with your first formula - before the first "AND": &"';}AND