Forum Discussion

ANGELARIVERA's avatar
ANGELARIVERA
Qrew Member
8 months ago

Formula with Exclusion

I'm trying to figure out how to work an exclusion in my formula.  I am trying to calculate the sum of all records cost for a certain director but only for trips where the member actually traveled.  If the member did not travel, a checkbox will be checked to exclude those records from the total cost.

This formula almost works but once I check a "Did not travel" box, it excludes all costs when I just want it to exclude the records that the "Did not travel" box were checked and calculate the sum of all others. 

If((([L3]="Caty")and([Did not Travel]=null)),(SumValues(GetRecords("{33.GT.01-01-2024 and LT.12-31-2024}"),55)),0)

formula query
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    8 months ago

    If I am understanding the ask correctly, the did not travel condition should be part of the Query. Also, I don't believe your query will work as written. I think it needs to be in this format:

    var text Query = "{15.EX.'" & [Field A] & "'} AND {6.CT.'AnyWordsHere'} AND {23.OBF.'" & [Date Field] & "'}";

    I believe each condition for the query needs to be contained in its own {} and each of the words 'and' needs to be capitalized 'AND'

    In your case, one of those conditions would be {6.EX.'False'} where 6 is the field ID for your Did Not Travel Checkbox (I forget if that is the correct query syntax for checkboxes. It may be 6.EX.'0', hopefully someone else can jump in and correct if needed).