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
