Skip to main content
Question

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

  • June 7, 2023
  • 0 replies
  • 50 views

Forum|alt.badge.img+11

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
------------------------------

MarkShnierYou
Forum|alt.badge.img+24

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
------------------------------