Security Group Management

This is a very interesting challenge. I imagine it's a pretty large employee group probably thousands of employees if you have 175 different groups.  

Your key statement is this:

These security groups are rule based and are used to examine an employee's Company, Department, Job Title, and Office Location to determine their group membership.

I suggest having a set of tables which would provide the correct group based on various subsets and combinations of these for factors above. 

For example, let's take the simplest one, Company. You might have security groups that employees are eligible to be a member of simply for being a member of that Company. Perhaps that would let the Employee see company wide announcements.  The Key to that table could be company name and the Employee could have a field called [Link to Company] with a formula of just [Company], and you look up the security group based on Company.

Then probably repeat that three more times for the Rules based solely on being a member of a Department, Job Title, or Office Location.   So far we have 4 parent tables and have created 4 [Link to ...] fields. and we have looked up 4 security groups.

OK, now we deal with rules based on solely a combination of the Company and Department.  So we make a table called Company Departments and the Key field will be in the format Acme Company-Finance.   i.e. concatenation with a hyphen.

Create hyphenated formula field on the employees table called [Link to Company Department], and run a summary report on that field, and you will get all the unique combinations of Company and Departments and use the more button to copy these into the Company Department Table. Load up the security groups based on those 2 factor combinations.   Now, you make a relationship and look up the security group down to the employee based on their company and department. 

Now, I believe if there are four factors and you were choosing any two of them to control the rule then there are six possible combinations.  So in theory, you would have to do the above set up five more times. But before doing that, I suppose that you might have knowledge yourself about, whether in fact, you do have rules that are simply based on everyone of those six pair combinations.   But on the other hand, I would say what the heck, while you're building this may as well build out all six combinations, just in case they are needed in the future.

OK so now the employee record has the correct security group assigned based on any single factor of the four,  or any of the six combinations of two factors of the four. 

The next set of parent tables is going to be just four Tables because there are four ways to choose three of the four factors. Basically each one of the four omits one of the factors.

So again, you build those Link fields on the Employees table,  run summary reports and populate the tables with their respective key fields based on the existing data the current employee population and then of course you need to edit those combinations to select the security group and looks down to the employees table.

Lastly we need one more table, just in case you have the most granular of security group assignment based on all four factors. So again we make the link in the employees table, run the summary report, populate, the unique combinations and load that up with the correct security groups, and look up the security group to employee. 

I think that gives you an elegant structure without having what would be a horrible long series of if statements and logic statements, which will be really terrible to maintain. This approach provides full visibility, not hidden in formula fields and hence able to be updated by non admins of the app.  

As you load up the master tables I'm sure that some there are certain combinations that just don't drive a particular security group. I would suggest you still make an entry there because there's a difference between "none"  and a security group that's blank that you haven't assigned yet.

Once you have set this all up, you set up a Daily Subscription safety net report to catch any employees who have Security group look up fields that are blank, meaning that the parent record does not exist and needs to be added. That would mean that you have on-boarded some unique employee that requires an assessment as to which security group they belong to (or none), based on a previously un-anticipated combination of your four factors.

So the employee will end up in a total of of 

4 + 6 + 4 + 1 = 15 different Security Groups including some which will lookup to "none".

Some of those security groups could end up being duplicates.

If you string them together in a semi colon delimited list, this formula would give you the unique ones

var text RawList = List(" ; ",

[Security Group 1], [Security Group 2], etc ... [Security Group 15]);

var text A = Trim(Part($RawList,1,";"));

var text B = If(not Contains($A,Trim(Part($RawList,2,";"))), List("\n", $A, Trim(Part($RawList,2,";"))), $A);

var text C = If(not Contains($B,Trim(Part($RawList,3,";"))), List("\n", $B, Trim(Part($RawList,3,";"))), $B);

var text D = If(not Contains($C,Trim(Part($RawList,4,";"))), List("\n", $C, Trim(Part($RawList,4,";"))), $C);

var text E = If(not Contains($D,Trim(Part($RawList,5,";"))), List("\n", $D, Trim(Part($RawList,5,";"))), $D);

var text F = If(not Contains($E,Trim(Part($RawList,6,";"))), List("\n", $E, Trim(Part($RawList,6,";"))), $E);

var text G = If(not Contains($F,Trim(Part($RawList,7,";"))), List("\n", $F, Trim(Part($RawList,7,";"))), $F);

var text H = If(not Contains($G,Trim(Part($RawList,8,";"))), List("\n", $G, Trim(Part($RawList,8,";"))), $G);

var text I = If(not Contains($H,Trim(Part($RawList,9,";"))), List("\n", $H, Trim(Part($RawList,9,";"))), $H);

var text J = If(not Contains($I,Trim(Part($RawList,10,";"))), List("\n", $I, Trim(Part($RawList,10,";"))), $I);

var text K = If(not Contains($J,Trim(Part($RawList,11,";"))), List("\n", $J, Trim(Part($RawList,11,";"))), $J);

var text L = If(not Contains($K,Trim(Part($RawList,12,";"))), List("\n", $K, Trim(Part($RawList,12,";"))), $K);

var text M = If(not Contains($L,Trim(Part($RawList,13,";"))), List("\n", $L, Trim(Part($RawList,13,";"))), $L);

var text N = If(not Contains($M,Trim(Part($RawList,14,";"))), List("\n", $M, Trim(Part($RawList,14,";"))), $M);

var text O = If(not Contains($N,Trim(Part($RawList,15,";"))), List("\n", $N, Trim(Part($RawList,15,";"))), $N);

var text P = If(not Contains($O,Trim(Part($RawList,16,";"))), List("\n", $O, Trim(Part($RawList,16,";"))), $O);

var text Q = If(not Contains($P,Trim(Part($RawList,17,";"))), List("\n", $P, Trim(Part($RawList,17,";"))), $P);

var text R = If(not Contains($Q,Trim(Part($RawList,18,";"))), List("\n", $Q, Trim(Part($RawList,18, ";"))), $Q);

var text S = If(not Contains($R,Trim(Part($RawList,19,";"))), List("\n", $R, Trim(Part($RawList,19,";"))), $R);

var text T = If(not Contains($S,Trim(Part($RawList,20,";"))), List("\n", $S, Trim(Part($RawList,20,";"))), $S);

var text U = If(not Contains($T,Trim(Part($RawList,21,";"))), List("\n", $T, Trim(Part($RawList,21,";"))), $T);

var text V = If(not Contains($U,Trim(Part($RawList,22,";"))), List("\n", $U, Trim(Part($RawList,22,";"))), $U);

var text W = If(not Contains($V,Trim(Part($RawList,23,";"))), List("\n", $V, Trim(Part($RawList,23,";"))), $V);

var text X = If(not Contains($W,Trim(Part($RawList,24,";"))), List("\n", $W, Trim(Part($RawList,24,";"))), $W);

var text Y = If(not Contains($X,Trim(Part($RawList,25,";"))), List("\n", $X, Trim(Part($RawList,25,";"))), $X);

var text Z = If(not Contains($Y,Trim(Part($RawList,26,";"))), List("\n", $Y, Trim(Part($RawList,26,";"))), $Y);

$Z

Interesting take, I would need to spend some time to fully understand/follow your suggestion.

In the application there is already a table with each of the (17) companies, there is a table with the departments (there are 139 to account for the various departments within each company), and there is a table for office locations (of which we have 51). These are all lookup fields to the Employee table. This was done to ensure data entry consistency.

Does knowing there are these additional tables adjust your recommendation?

I feel confident on being able to determine the "easy" groups, those groups that just need to reference a single field selection (i.e. Company group, Main Department group, Office Location group, etc.). The ones that are more difficult are when the rule needs to evaluate based on multiple fields.

Example: we have 17 Companies, and of those 6 have an Accounting Department. So for our Accounting security groups we actually have 7, one for anyone in accounting, then six additional groups for each company-department combo. An employee would then be a member of the company group, the all accounting group, their specific company-department combo group, and their office location group.

Actually, I think I may have figured it out...In each of my lookup tables I'll add a field called security group. Then I can just bring that field in as an additional field in the table relationships. I do appreciate the offer to connect. If this line of thinking doesn't work out I might reach out again. :)

If you have the time, I would love to connect.