Forum Discussion

LauraLinssen1's avatar
LauraLinssen1
Qrew Trainee
3 years ago

Formula Queries or some other way to do this.....

What am I trying to do..... I have Sales Reps that can have multiple incentive programs that are based on an Effective Date and an End Date. I have a different table that is calculating their Actuals vs. Objectives, however, I need the correct data for a month that coincides with the active incentive program record at that time. 

I was thinking of using a formula query to look at the records in the table for the one that matches the related person and where the date of the month in question falls in between the Effective Date and the End Date. But I don't just need the Record ID of what is found, or a listing of records. I want the value in the field that I am looking for. 

So long story short, I need help to say, hey for this value, which program value do I need to go with.

------------------------------
Laura Linssen
llinssen@jjkeller.com
------------------------------
formula queries

1 Reply

Sort By
  • AhuvaBrown's avatar
    AhuvaBrown
    Qrew Trainee
    3 years ago
    If you want to get a list of values in a field from all records that match your conditions, you can use GetFieldValues
    I'm not clear on which fields are on which table, but this is what comes to mind.

    Say the Incentive Program field id on the table you're searching is 6 and the Related Person field is 7

    var text query = "{7.EX.'[Related Person]'}AND{other conditions}"
    GetFieldValues(GetRecords($query, tableid), 6)

    You would put this in a multi-select text formula field, and it would return a list of incentive program values on the records that match the conditions.

    ------------------------------
    Ahuva Brown
    ------------------------------