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

Question

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

Answer 1

Using lookup fields in SharePoint provides a more dynamic way to manage relationships between lists, as SharePoint handles the linking and can automatically retrieve related information, such as the Street Title and Location Title. This can simplify data management and ensure that updates to the source lists are reflected in the related lists.

The choice between lookup fields and number fields largely depends on your specific needs for data integrity versus ease of use in formulas. If maintaining accurate relationships is critical, lookup fields may be the better choice despite the added complexity in formulas. For your reference: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/sharepoint-lookup-fields#how-to-link-lists-from-microsoft-lists-using-a-lookup-column-in-power-apps