Forum Discussion

DenisRochat's avatar
DenisRochat
Qrew Trainee
3 months ago

Size(GetRecords Formula Query

I am trying to populate a table named Metrics using formula queries. I populated several fields from the Opportunities table with no problem. See Attachment. I am trying to populate # QWT Leads (number of qualified water treatment leads) from the Leads TBL with no success using the following formula query:

var text QUERY = "{145.OAF. ' " & FirstDayOfMonth([Month (First Day)]) & " '}AND{145.OBF. ' " & LastDayOfMonth([Month (First Day)]) & " '}AND{80.CT. ' " & "Qualified" & " '}AND{81.CT. ' " & "Water Trmt" & " '}";

Size(GetRecords($QUERY, "brravks49"))

145 is a date field, 80 is the Lead Status and 81 is the lead type, all in the Leads table and "brravks49" is the Leads Table.

What am I doing wrong?



------------------------------
Denis Rochat
------------------------------

10 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 months ago

    What field type is this field?

    [Month (First Day)]



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • DenisRochat's avatar
      DenisRochat
      Qrew Trainee
      3 months ago

      Month (First Day)] is a Date field.

      Lead Status and Lead Type are Multiple Choice.



      ------------------------------
      Denis Rochat
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 months ago

        I can't really see a problem with your formula. So I'm sort of grasping at straws.  

        1. Is the phrase "Water Trmt" actually in the data?

        2. Is brravks49 the correct target table?

        3. Maybe try this

        var text QUERY = "{145.OAF. ' " & ToText(FirstDayOfMonth([Month (First Day)])) & " '}AND{145.OBF. ' " & ToText(LastDayOfMonth([Month (First Day)])) & " '}AND{80.CT. ' " & "Qualified" & " '}AND{81.CT. ' " & "Water Trmt" & " '}";



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------