IvanWeiss
Qrew Captain
12 months 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
------------------------------

### 4 Replies

• 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@gmail.com
------------------------------
• 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
------------------------------
• 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]) & "'}";

------------------------------