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

[johnjohn123]

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

[johnjohn123]

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

[WarrenBelz]

@johnjohn123 ,

As I have come in on this, I will offer a couple of thoughts (and then exit gracefully)

A lookup column does not create a Delegable relationship to the other fields in those lists and even if it did it would still be the same query as your linked ID code.

Where did you get the 6,000 item limit from ? My understanding is that it 40,000 API calls and this is multiplied by the number of licenced users.

Also (see the Monitor below refreshing a gallery query) I believe getRows is two API calls (not a call per row)

@FLMike may have different information, but those are my thoughts.

[johnjohn123]

@WarrenBelz you will get 6,000 requests per 24 hours, inside power apps and power automate when users have Office 365 licenses like E3 , here is a link:-

https://learn.microsoft.com/en-us/power-platform/admin/api-request-limits-allocations

[WarrenBelz]

@johnjohn123 ,

If you are using pay-as-you-go (you did not mention that and to be honest I had never looked that one up) - you said where most of our users have E1 & E3 licenses which I thought was simply a licenced user (where they get 40,000 as I noted. although I am possibly incorrect on the accumulation), however whatever the case, you probably need to do a bit of monitor testing to see how many API calls are normal.

You also might note these comments in the document you linked

Power Platform Request limits have been updated and substantially increased in late 2021 to be at levels that are significantly higher than typical usage for most customers. With the updated limits, expectations are that very few users would exceed the documented limits. If you anticipate exceeding the non-licensed user limits, reach out to your reseller or Microsoft Account team to discuss a custom solution.

Any possible high usage enforcement won't happen until six months after Power Platform Request usage reporting has been generally available in the Power Platform Admin Center.

Microsoft reserves the right to enforce limits for overages. If a customer encounters high usage enforcement, they can expect some form of throttling. Customers can purchase additional capacity to avoid high usage enforcement or move their environment to pay-as-you-go and pay for actual usage above daily limits.

[Pstork1]

As @WarrenBelz mentioned, they don't work particularly well in Power Apps. Support for using them with Forms and dropdowns is difficult at best. They are designed for use inside SharePoint. When you try to use them in Power Apps they have limited support. This gets even worse if you set the Lookup for multi-select.  The primary issues are that its a complex data type so its hard to use it in a delegable filter and almost impossible to use in a Odata query. Its also not particulary well supported in Forms so you'll almost always have to use Patch() instead of SubmitForm() which will increase the complexity.

[johnjohn123]

@Pstork1 yes they are more complex compared to number or single line fields when we want to filter by them.. but not having Lookup fields will complex syncing and showing the data for the stored ID, as i mentioned in approach 2 & 3. drawbacks. so if we compare the complexity of using Lookup field and the complicity for not using them,, then using them will be simpler...

[Pstork1]

I don't tend to agree with that, but you have a right to your own opinion. Either way for the size and scale of the enterprise app you are describing SharePoint just isn't a good choice for the data source. You need to use a fully relational database and may need to move to professional development tools for an app like you describe. Power apps and SharePoint have a lot of uses, but they aren't an universal fix. There are some high end apps that will never work correctly in Power Apps and SharePoint. It sounds like this is one of them.