Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
2 years ago

Formula Query to create Line Chart filtered by the current logged in user

Hi all, I am working on a formula query to try to build a line graph that will show cumulative sales for the current user in the current year and previous two years.  I had this working without the by user filter.  Here is my formula…. Just to help explain fid 19 is a lookup field that is a user.  That is our sales person for the project.  fid 66 is a formula field and in it is User() to get the current logged in user.  For some reason this is pulling blank lines (all 0's) and not pulling in data.

//fid 7 = [Billing Amount]
//fid 11 = [QuickBooks Invoice Date]
//fid 18 = [Opportunity Type]
//fid 71 = [Current User Is Team Member (=1) - Lookup]
//fid 19 = [Project Solutions]
//fid 66 = [Current User]

var text QUERY = "{11.OAF.'" & FirstDayOfYear([QuickBooks Invoice Date]) & "'}AND{11.OBF.'" & LastDayOfMonth([QuickBooks Invoice Date]) & "'} AND {19.EX.66}";

SumValues(GetRecords($QUERY),7)



------------------------------
Ivan Weiss
------------------------------
  • Not tested but try this.  TV is a special operator intended to be used with user fields.

     AND {19.TV." & ToText([Current User]) & "}";



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • IvanWeiss's avatar
      IvanWeiss
      Qrew Captain

      Nope, that did not work either.  Would I swap out the ToText([Current User]) with the fid maybe?  I didnt try but I am not sure what the TV means



      ------------------------------
      Ivan Weiss
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        I just did a test to count children of a parent where the current user is the record owner and it worked.

        var text QUERY = 
          "{7.EX." & ToText([Record ID#]) & "}"
        & " AND " 
        & "{4.TV.'" & ToText(User()) & "'}";
        Size(
        GetRecords($Query, [_DBID_CHILD]))
        So maybe its those pesky single quotes that will fix it.
        AND {19.TV.'" & ToText([Current User]) & "'}";


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