User Access on Sharepoint from 2 sets of data

[Anonymous]

Hi,

I have a PowerApp linked to 2 SPs. The first is a User Access list (SPUsers) and the second is Customer Info (CustomerInfo). Like follows:

Email_Address	Client Grouping
JohnDoe@gmail.com	500
ClarkKent@gmail.com	600
PeterParker@gmail.com	700
JohnDoe@gmail.com	550
JohnDoe@gmail.com	870

Customer	Customer ID	Client Grouping
Gotham Trading	100	500
Metropolis Art	101	600
Keystone Pharmaceuticals	102	550
New York Stations	103	870

The app needs to load only the client groupings per email address. I have got it to work with only one client grouping for example "John Doe" to work with Client Grouping "500". But I need the App to work such that John Doe can also view the other Client Groupings allocated to him i.e. "550" and "870".

I want to do it OnStart of the application. This is what I currently have:

Set(varUser, User().Email);Set(varUser_ClientGrouping,LookUp(SPUsers, EAddress = varUser).Client_Grouping);ClearCollect(ClientData,Filter(CustomerInfo, Client_Grouping = varUser_ClientGrouping))

How do I accomplish this such that the other client groupings for John Doe be read?

Thanks.

[mdevaney]

@Anonymous 

I believe the issue is your LOOKUP function only retrieves a single 'Client Grouping' value.  We need to get all the 'Client Grouping' values for the User instead.  We can do this this by using the FILTER function and storing them in a single column Collection.

Set(varUser, User().Email);
ClearCollect(colUCGrouping, Filter(SPUsers, EAddress = varUser).Client_Grouping);

Then we can create another Collection called 'Client Data' to store only those clients whose grouping ID appears in our single-column Collection called colUCGrouping.

ClearCollect(ClientData,Filter(CustomerInfo, Client_Grouping in colUCGrouping));

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

Hi @mdevaney 

This is what I want to do and the solution you provided works! However, The only problem is I'm getting a delegation warning because of the in operator within the filter function. Unfortunately, I do have a large dataset to work with. Is there any other way I can do this?

Many thanks!

[mdevaney]

@Anonymous 

To avoid a delegation warning you would need to change Grouping to a LOOKUP column in the Clients table.  Then you would add an additional column based off that LOOKUP to show the UserEmail.  Then you could directly FILTER on the UserEmail to show the Users's customers.

One thing I've learned about PowerApps is many times the best way to tame delegation is to change the data structure rather than find a solution with formulas 🙂

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Anonymous]

@mdevaney 

Ok so on SharePoint I need to do the following:

1. For Client table, Add a new column for GROUPING and make that type lookup?
2. For User table, do I also need to make the GROUPING column a lookup?

For this part "Then you would add an additional column based off that LOOKUP to show the UserEmail" where do I add the additional column to? Confused as the Clients table does not contain email.

[mdevaney]

@Anonymous 
When you add the lookup column there is an option that says:

"Add a column to show each of these additional fields"

This is where you would choose UserEmail from the list of available options.  Let me know if you need a screenshot.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up." 

[Anonymous]

@mdevaney 

I did the above. In the CustomerInfo SP list. I created a lookup column called "GroupingLink".  It created the column which has no values in it - if I edit that column it gives me the choices of the different emails in the SPUsers list. I then added filter using the following:

ClearCollect(TableNew,Filter(CustomerInfo,GroupingLink.Value = varUser))

But now no data is being populated in the Collection. Not sure what I'm doing wrong 😞 

Did I not do the lookup column correct in SP?

[Anonymous]

@mdevaney 

When I create the lookup column the additional column is a multi-select with all the emails listed in the SPUser List and is not filtering it based on the GROUPING.

[mdevaney]

@Anonymous 
I'll respond to your post eventually.  It will just take some time to create a mini-tutorial.

[mdevaney]

@Anonymous 
I tested my idea and it did not work.  It raised a delegation warning.  Sorry, I do not have any other ideas on how to get around the delegation requirement.

@WarrenBelz
If you have any ideas here I'm open to suggestions.  I don't see the path forward here

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."