Skip to main content
Question

Formula Query - Data Reliant on Current User

  • Feb 22, 2022 11:39 PM UTC
  • 0 replies
  • 174 views

Forum|alt.badge.img+6
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($UserNameQB, ToText([Record ID#])), true,
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
------------------------------
This topic has been closed for replies.

Forum|alt.badge.img+19
  • Qrew Champion
  • Feb 23, 2022 12:56 PM UTC
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
------------------------------