Forum Discussion
Building relationship between SharePoint lists (Lookups or store the ID using number field)
I have 3 SharePoint custom lists:-
1) Direction
2) Street
3) Location
where 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 become of 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
- Hi John,
Which based column you used for sharepoint lookup column ? the ID or title?
In my side, i mostly using sharepoint relationship for customer needs and never get any issues with that.
Once the relationship is done from the ID as primary, of course i add columns i want to see in the parent list but when data changed from one of these column in relationship, it's updated as the relation is done from ID.
Rgds,
Valentinvlecerf so you mean you use Lookup fields? and not the appraoch of storing the ID of the parent list in a number field? and you are happy with it?
