Skip to main content

Is there a way to do Data lookups with a form of Matrix using values in a X-axis and Y-axis of a grid to get a value.

Here is my basic use case on an onboarding form. Each business unit has a distribution group based off the city. so City would be on one axis and business unit on another with the group names.

I just cant figure out how to work conditions on the. I was thinking of using a Sharepoint list but maybe something else would be better?

  New York London Paris Tokyo
Business Unit 1 DG-BU1-NY DG-BU1-Lon DG-BU1-Paris DG-BU1-Tokyo
Business Unit 2 DG-BU2-NY DG-BU2-Lon DG-BU2-Paris DG-BU2-Tokyo
Business Unit 3 DG-BU3-NY DG-BU3-Lon DG-BU3-Paris DG-BU3-Tokyo
Business Unit 4 DG-BU4-NY DG-BU4-Lon DG-BU4-Paris DG-BU4-Tokyo

 

Alternately I was thinking of using two columns with conditions and the third the value but I think this would get very large fast with70+ cities and at least 10 business units to include. 

BU City DG name
Business Unit 1 New York DG-BU1-NY
Business Unit 1 London DG-BU1-Lon
Business Unit 1 Paris DG-BU1-Paris
Business Unit 1 Tokyo DG-BU1-Tokyo
Business Unit 2 New York DG-BU2-NY
Business Unit 2 London DG-BU2-Lon
Business Unit 2 Paris DG-BU2-Paris
Business Unit 2 Tokyo DG-BU2-Tokyo
Business Unit 3 New York DG-BU3-NY
Business Unit 3 London DG-BU3-Lon
Business Unit 3 Paris DG-BU3-Paris
Business Unit 3 Tokyo DG-BU3-Tokyo
Business Unit 4 New York DG-BU4-NY
Business Unit 4 London DG-BU4-Lon
Business Unit 4 Paris DG-BU4-Paris
Business Unit 4 Tokyo DG-BU4-Tokyo

I am hoping for en elegant solution but I guess I will take brute force.

We also have plans for more complicated lookups with Role calculation  based on department, title and business unit.

It’s going to depend on several factors. 

  • Will the user completing the form need to pick the city or is this driven by some other method through the workflow?
  • Is the goal giving them options of cities to pick from based on business unit on the form? Then have the workflow identify distribution group based on business unit and city to send an email or task to those members returned?
  • I’m assuming your DG Name column is a person or group field in SharePoint with group names picked for those values? 
    • If so, your best bet is to simply nix the person or group field and use a text field to store the DG name in your list. Use the get members from ad group in designer to pull members as needed based on business unit selected. 

I assume you will send a task or an email to the identified group of users. If so, I think method #2 you provided is your best approach however change the DG Name to a text column like I mentioned above and add that AD action to get members. If you don’t have to loop through members, this approach is feasible.

 

Note: Just remember there are limitations for both email actions. I do not recall the one for exchange send an email action but send an email action provided by Nintex is 1,000. Unless something changed since April. Sharing this, in case it applies, not sure how big your DG groups are.


The user completing the form will be selecting both the City and Business Unit.

This is a New user Onboarding form. The goal is to identify the groups the new user will be assigned.
We wont be sending a message to the group just assigning the group to the new hire as part of the process.

Trying to automate as much as I can here. Assigning this group tends to be something people forget. Ideally we would do this with Dynamic groups in exchange but that would mean rebuilding several hundred groups that already exist.


Ok, so the following would work. 

 

  • Add multiple choice field on your form that provides the business units. 
  • Add data control lookup on your form with condition set to the business unit form control to only show the cities available for that business unit.

What do you mean assigning group to the user? Meaning, giving them the DG name for reference? Or adding that member to the AD group using the workflow based on what they complete on the form?

 

I think I’m almost there, just trying to make sure I follow what you need to do with the DG Name value. 


I guess my question is really, are these distribution groups setup in Azure Active Directory as dynamic groups or security groups? If so, you just need to add that person to them based on BU and City? I know dynamic groups would already pick them up if your rules were setup on that group for what you need. I assume they aren’t, is that what you mean by dynamic groups in exchange?


What do you mean assigning group to the user? Adding the user to the AD group. We already have an API connection setup to another tool that creates the basic AD user (we are not cloud only yet unfortunately). 

I guess my question is really, are these distribution groups setup in Azure Active Directory as dynamic groups or security groups? These are  old-fashion exchange distribution groups . Some are AAD and some are Local AD, depending on how old the group is. Unfortunately Exchange Dynamic distribution groups does not give City or Company as conditions. M365 groups do allow that but that would require removing all the existing groups and rebuilding as M365 groups. 
So my choices are lots of work creating groups or creating rules to assign the right group on creation. Doing the Dynamic groups  would probably be better long term but it is hundreds of groups. And getting the help to get it done runs into office politics.


Also in the future I want to expand on this idea to assign application roles based on multiple factors like job role, department, and Company.

We have an application that has a matrix that a person uses to calculate the permissions. If I can automate that, it will save them 30 minutes per user setup.


Hi Scottg,

As you have a connection setup to create the AAD accounts - you could use NWC to help migrate these by iterating through the exchange groups and their members - and creating the AAD Group or to generate the powershell to create these accounts.

 


Reply