CurtisMiddleto1
3 years ago

# Formula Query - Data Reliant on Current User

Hi,

Below is a Report formula I built to ideally solve the following problem:
We have an application where users can submit records to be processed. We want to limit the number of records that can be viewed based on an organizational/managerial level. We have an Employees table with all the necessary data, but I run into the issue where QB can recognize me as the current user, but cannot associate it with my Employee record to the Financials table which records I should be able to see. I want it to be based on everyone below my tier 2 manager. So everyone who has that manager, I should be able to see their records. I attempted to build out the following formula, but QB told me it was too time intensive to run. Hoping someone has a tip on how I can refine my formula or a better solution to my problem.

Thank you!

Formula - Checkbox
//This will get the username for the current user's tier 2 manager in the employees table via a formula query
//This box will check if the current user's Manager 02 is related to any of the UserName QB fields in the accruals table.
//This is so administrative assistants can manage accruals for a department under their tier two. Currently this solution works for Environmental.

var TextList ManagerCurrentUser = GetFieldValues(GetRecords("{26.EX.'"&ToText([Current User])&"'}", [_DBID_EMPLOYEES]), 31);
//The above line looks at the employees table and gets a result of the manager 02 field for the current user.
var TextList UserNameQB = GetFieldValues(GetRecords("{48.EX.'"&\$ManagerCurrentUser&"'}"), 3);
var TextList ManagerOne = GetFieldValues(GetRecords("{53.EX.'"&\$ManagerCurrentUser&"'}"), 3);
var TextList ManagerTwo = GetFieldValues(GetRecords("{54.EX.'"&\$ManagerCurrentUser&"'}"), 3);
var TextList ManagerThree = GetFieldValues(GetRecords("{55.EX.'"&\$ManagerCurrentUser&"'}"), 3);
var TextList ManagerFour = GetFieldValues(GetRecords("{56.EX.'"&\$ManagerCurrentUser&"'}"), 3);
//The above 5 TextLists look to see if any of the "UserName QB" fields contain the manager name give from \$ManagerCurrentUser.
//If they do, they will pull a list of records where that is true.

//The formula below is taking the data above and checking the box if the current record is on any of those lists.
If(Contains(\$ManagerOne, ToText([Record ID#])), true,
If(Contains(\$ManagerTwo, ToText([Record ID#])), true,
If(Contains(\$ManagerThree, ToText([Record ID#])), true,
If(Contains(\$ManagerFour, ToText([Record ID#])), true, false)))))

------------------------------
Curtis Middleton
------------------------------

• DonLarson
Qrew Commander
Curtis,

I have this problem with many issues.   The solution I am using is to have a Pipeline fire when the record is created that will related the record to the Employees table.  I am comparing the Record Owner  with the User in the Employee table and limiting the Search Results to one record.

I am using this against business objects such as Tasks, Forecasts and Opportunity Submissions.   The Employee table has a lot of relationships but the follow on business logic becomes much simpler to execute.

The Field Queries are great but they are still very limited.

------------------------------
Don Larson
------------------------------