Build the relation between SharePoint lists in a way that is aligned with Power apps recommendations

(0) Share

Report

Posted on by johnjohn123

3,514

We have those SharePoint 4 SharePoint lists:-

1) Asset

2) Medium

3) Asset Spare Parts. Each item inside this list will be related to an Asset + a Medium + a Payment Category

4) Payment Category

Now I got lost of what is the best way to build the relation between the Asset Spare Parts list and the other 3 lists???

First Approach, will be to create 3 SharePoint lookup fields inside the Asset Spare Parts list to link it to the other 3 lists. but i always read and get advices to avoid using SharePoint Lookup fields as they will not work well and will cause lot of issues inside Power Apps

Second Approach, Instead of creating 3 lookup fields, to just create 3 Number fields to store the Asset ID + Medium ID + Payment Category ID inside the Asset Spare Part list. but the issue will be when we want to show the list of Assets Spare Parts inside a gallery and we need to show the Asset Title + Payment Category Title + Medium Title. to do so, we need to write these formulas inside the Asset Spare Parts Gallery:-

Lookup(Medium, ID=ThisItem.MediumID).Title

Lookuo(PaymentCategory, ID= ThisItem.PaymentCategoryID).Title

Lookup(Asset,ID= ThisItem.AssetID).Title

This will work well, but we will face this major issue:- where most of our users have E1 & E3 licenses, and hence they can only make 6,000 requests per day inside the Power Platform. and using the above 3 formulas to get the info of the other lists, will cause the gallery to send 3 requests to SharePoint for each item when navigating through a gallery... which will not work for us , due to the 6,000 requests limit. and since we have around 40 SharePoint lists which are connected together..

Third approach. is that store store the IDs of the related 3 lists items (as in second approach) and also store the Titles of the related 3 lists items... i can do this when adding and update the Asset Spare Parts list's items.. but the issue will be on how we can keep the titles up-to-date, incase the title of the related Assets and/or the title of the related PaymentCategory and/or the title of the related Medium, get updated.. so we can run a sync job to sync the titles... but this approach will also have these 2 drawbacks:-

- the idea that updating a title will not get reflected inside the other lists till the sync job runs, might not be much appreciated by the client

- the overhead we will have to write the sync job,,, we have around 40 SharePoint lists which are connected together..

so to be honest i got lost on which approach to follow? using SharePoint lookup field seems to be the way to go,, but it is always adviced to stay away from using SharePoint lookup fields in Power apps... so is there other approach to consider which will not have any drawback and will be able to work with large lists, as in our case?

thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (16)

Answers (0)

Sort by

Michael E. Gernaey 53,968 Super User 2026 Season 2 on at

Like
a
(1)

Report
Copy link

Link copied!

@johnjohn123

I am not sure who is telling you not to use LookUps, but that is not accurate. People use them all the time and its a necessary way to build what you are doing. So I would do your first option with 3 LookUps. As long as you have a nice unique (preferrably ID) to filter on its easy.

There is however another option.
I do not know how large the Asset, Payment Category and Medium are, but you could make these single Choice columns. And put all the possible options in them and update them over time.
I still prefer the 3 lookups but this would work also.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @FLMike ,

I would be one of those people if you are referring to SharePoint (very different subject for Dataverse).

There is no function I know of (apart from a couple of left-field workarounds/hacks) that cannot be done easier and with less restrictions directly in Power Apps (simply refer to the other list and field in the drop-down) without at times the code gymnastics needed to deal with anything changed from the standard function and the Delegation issues with items such as sorting by them.

SharePoint is not a relational database and will not accept Delegation on any "link" created by one of these columns to any other field in the second data set.

I am happy to agree to disagree, but I have not used (or needed to use) them in five years on some quite complex apps and data sets in SharePoint.

Was this reply helpful? Yes No
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!

@FLMike Thanks for the reply,, i do not want to store "Asset, Payment Category and Medium" as columns, as they have their own business logic and approval to manage them.. so they need to have their own sharepoint lists.. so you are with using lookups for my case?

Was this reply helpful? Yes No
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz thanks for the reply.. so if you do not want to use SharePoint lookup field type then how i can handle the drawbacks for the Second and Third approaches? Thanks

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@johnjohn123 ,

I was referring to Lookup columns in SharePoint, not the Lookup function - I did not post with the intention of intervening here and will leave you with @FLMike

Was this reply helpful? Yes No
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz yes i know,, we are talking about SharePoint Lookup field type.. and my second approach and third approach do not use Lookups field type, but have their own drawbacks.. do you have any suggestions how to solve those drawback as per my original question ?

Was this reply helpful? Yes No
WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@johnjohn123 ,

As I noted - I will not be intervening here - just clarified the reason I do not use them.

Was this reply helpful? Yes No
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz ok fair enough .. thanks

Was this reply helpful? Yes No
Pstork1 69,657 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!

You've stated the trade-offs fairly well. The problem is that give the amount of data and the complexity of the data you are using the real solution is to use an actual relational database and not use SharePoint. SharePoint is not a relational database and although it is good for a lot of things the situation you describe isn't one of them. You need to use a real database if you want that many interrelated tables with that much data. There is no appropriate approach to do something of that scale in SharePoint. Its not what it was designed for and any way you do it there will be some issues that you will face.

Having said that I would probably opt for strategy #3 if I absolutely had to do it in SharePoint. But there will be issues and you'll have to do something with recurrence flows to update the synced data perodically.

Was this reply helpful? Yes No
johnjohn123 3,514 on at

Like
a
(0)

Report
Copy link

Link copied!

@Pstork1 so why you do not like SharePoint Lookup fields? although they will solve a lot of sync and performance issues? i always get advices not to use SharePoint lookups fields,, but never find a valid reason..

Was this reply helpful? Yes No