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