Power Apps - Building Power Apps

Building relationship between SharePoint lists (Lookups or store the ID using number field)

(1) Share

Report

Posted on by johnjohnPter

1,542 Super User 2024 Season 1

I have 3 SharePoint custom lists:-

1) Direction

2) Street

3) Location

now each Direction item should be linked to one street and one location.

There are 2 ways to build the relation between these 3 lists, as follow:-

1) StreetID & LocationID as lookup fields inside Direction list

2) StreetID & LocationID as number fields inside Direction list

From my point of view here are the pros and cons for each appraoch:-

1) Lookups Approach

Pros:-

- SharePoint will manage the relation. and SharePoint can dynamically get the other list info such as Street Title , Street Description inside Direction list

Cons:-

- Complicate the formulas for creating and updating Directions items inside Power Apps and Power Automate

2) Number field Approach

Pros:-

- Ease the formulas for creating and updating Directions items inside Power Apps and Power Automate

Cons:-

- I need to manage the relation by myself.

I tried the Number fields approach in a recent project, and i face those complexities:-

1) I have a gallery to show all the Directions items, and their Street Title & Location title and the ability to filter them:-

2) To do so i have added those fields inside the Directions list:-

- Direction Title

- Street Title

- Street ID

- Location Title

- Location ID

3) everything seems to work well. but when a user update the street title inside the street list or the Location title inside the location list, then the street title inside the direction list became out of date, and same applies to the Location title..

4) to fix this i needed to create a schedule work flow that runs daily, which loop through all the streets and all locations inside the streets and locations lists, For example i loop through all streets inside the streets list >> get all Directions items which have their streetID = the street ID inside the loop and their StreetTitle does not equal the Street Title inside the loop >> then update the StreetTitle inside the Direction list accordingly.. and i did the same for the Locations items..

so to be honest using number field ease Patching the Direction items,, but have over-complicate manually managing the relation ... so can anyone provide some advice? i might be missing some Pros & Cons ??

Thanks

All responses (13)

Answers (0)

WarrenBelz 146,702 Most Valuable Professional on at

Like (0)

Report

Building relationship between SharePoint lists (Lookups or store the ID using number field)

Not sure how to explain it any better - your specific set of needs make this a viable option. I have not seen the specific need before.
johnjohnPter 1,542 Super User 2024 Season 1 on at

Like (0)

Report

Building relationship between SharePoint lists (Lookups or store the ID using number field)

@WarrenBelz
i did not get your last answer? which test u are talking about ? thanks
WarrenBelz 146,702 Most Valuable Professional on at

Like (0)

Report

Building relationship between SharePoint lists (Lookups or store the ID using number field)

I am saying that this may be (albeit isolated to a specific set of circumstances) an application for a lookup column. It surprised me a bit, although it has been a while since I did any testing on these.
johnjohnPter 1,542 Super User 2024 Season 1 on at

Like (0)

Report

Building relationship between SharePoint lists (Lookups or store the ID using number field)

@WarrenBelz
sorry i did not get you last comment? can you advice more on it please?
WarrenBelz 146,702 Most Valuable Professional on at

Like (0)

Report

Building relationship between SharePoint lists (Lookups or store the ID using number field)

Strangely, testing here suggests the method you are using seems to accept Delegation with minimal API calls if the query is otherwise delegable (it may be a recent enhancement and it does not in other uses). Just be aware of the other limitations of these field types.
johnjohnPter 1,542 Super User 2024 Season 1 on at

Like (0)

Report

Building relationship between SharePoint lists (Lookups or store the ID using number field)

WarrenBelz

Ok thanks a lot for your help. i am not saying that we need to use LookUps... i tried both approaches on real projects,, and till now i am unable to determine which approach to use in the upcomming projects.. as each approach has its own pros and cons
WarrenBelz 146,702 Most Valuable Professional on at

Like (0)

Report

Building relationship between SharePoint lists (Lookups or store the ID using number field)

johnjohnPter

I guess I have provided an opinion - you will need to run with whatever is best for you .
johnjohnPter 1,542 Super User 2024 Season 1 on at

Like (0)

Report

Building relationship between SharePoint lists (Lookups or store the ID using number field)

@WarrenBelz

Thanks for the reply. but building the whole list using With() or as a collection will raise an issue as in power apps collections can only hold max of 2,000 items...
WarrenBelz 146,702 Most Valuable Professional on at

Like (0)

Report

Building relationship between SharePoint lists (Lookups or store the ID using number field)

I must note that in the seven years of app development, I have never had a customer run into an API call issue from added columns in a Gallery (or any other reason) and you are one of the few users I have seen raise it as a consideration to basic display queries.
As you are dealing with a Data Row Limit restricted query whichever way you go, it would be easier if the looked up list is under this limit to capture it using a With() statement at the start and then use AddColumns() for the additional fields. Another approach (again if the second list is under your DRL) is to make a collection from it and do the lookups to that.
Both involve zero API calls.
johnjohnPter 1,542 Super User 2024 Season 1 on at

Like (0)

Report

Building relationship between SharePoint lists (Lookups or store the ID using number field)

SaiRT14 Yes this what i have concluded from my last project where i used the Number filed, and i store the additional info such as StreetTitle & LocationTitle inside the Direction list to avoid dynamically referencing those values for each Direction item.. things will become overcomplicated if we change the Street Title inside the Street list for example.. but most of the highly ranked users on this forum or on YouTube always say to avoid using lookups... but i still feel that they worth using..