Answered

User Access on Sharepoint from 2 sets of data

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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.

Categories:

Building Power Apps

I have the same question (0)

All responses (16)

Answers (1)

Sort by

mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!
@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."

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@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."

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
@mdevaney

Ok so on SharePoint I need to do the following:
For Client table, Add a new column for GROUPING and make that type lookup?
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.

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@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."

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
@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?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!

@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."

Was this reply helpful? Yes No
WarrenBelz 155,675 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Thanks @mdevaney ,
Firstly, can I endorse your observation
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 with a giant tick.
I have also read the post and from painful experience can tell you that Lookup columns are the symptom of many problems and provide no solutions in PowerApps and cause completely unnecessary heartburn.
@Anonymous - to your problem - before I head anywhere else, please have a read of this post of mine and see if that model might solve your issue.
.

Was this reply helpful? Yes No