Build relation between SharePoint lists. should we use SharePoint Lookup fields or store the ID inside a number fields

[johnjohn123]

I have built those SharePoint lists for our Mini CRM project:-

1) Departments

2) Projects

3) Tasks

4) Assets

The relation between those lists is as follow:-

- Each Project is linked to a single Department

- Each Task is linked to a single Project

- Each Asset is linked to a single Project

now i have 2 approaches to manage the relation between those lists:-

1) Using SharePoint Lookup field type.

2) Store the IDs of the related list in a Number field.

Now each appraoch have its Pros and Cons from my point of view, as follow:-

1) Using SharePoint Lookup field type.

-Pros

  -Ease creating and updating the items inside Power Apps. as Power Apps will automatically build the combo-boxes for us. for example when we add a Form for the Project list >> Power Apps will automatically create the Department combobox for for us

  - Work well when using the SharePoint UI to manage list.

-Cons

  - Power Apps formulas do not like lookup SharePoint fields, the formula will become complex when we want to get items using the Lookup field type

  - not delegate if we want to sort them.. but using the Number field type will not allow us to benefit from the sort as sorting using Number (which represents ID) will be meaningless

2) Store the IDs of the related list in a Number field.

-Pros

  - easily write formulas to get the items based on the Number field

  - Delegate on all the operations..

-Cons

  - we have to manage the Comboboxes ourselves inside the Forms. for example if we add a form for the Project list, then we will need to manage the Department combox box when adding and editing the projects.

  - even if we can sort based on number columns, i do not see it as a benefit.... as for example sort the Project using the Department ID will be meaningless,,, as we usually sort using Department name.

  - will not be very user friendly if we use the SharePoint built-in UI to manage the lists.

now i am really confused... to be honest i prefer to use the SharePoint Lookups field type which seems to be more standard and straightforward way to build relations between sharepoint lsits.. but many documentations inside the internet suggest to stay away from using SharePoint Lookups field type and to store the ID inside a number field instead... so can i get any advice on this.. as i am staring a new project and i got stucked on this point..

Thanks in advance for any help.

[WarrenBelz]

@johnjohn123 ,

To answer your long post with a short reply - store the ID in a number field in the related list - I have been doing this for over 5 years and it works very well from the user perspective even on large lists/libraries on mobile devices. I never have (and probably never will) use Lookup fields as the "relationship" is really only for the SharePoint native interface - as a data source for Power apps, SharePoint is not a relational data base and will not accept Delegation on relational queries. There are other complexities/limitations on these fields that can be easily avoided with some thought into the data structure required. 

As well, if you simply want the "looked up" field for a drop-down, you can do this by referencing the second list directly and then writing back to a Text field.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[johnjohn123]

@WarrenBelz Thanks for the reply. but based on Microsoft documentation we can filter using complex date types @ https://learn.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-op... but we can not sort...

But if we store the ID of the related list item (for example store the Department ID inside the Project list),, then what will be the benefit of the ability to sort the Projects using the Department ID .. it will not be beneficial..  am i correct? so what other limitation we will get other than the non-delegation on sorting lookup field types?

[WarrenBelz]

@johnjohn123 ,

Yes, you can filter the "primary" value using .Value, but you asked for advice and I am simply saying I believe they serve no useful purpose in Power Apps. I have not found anything they do that cannot be done with other structures/functions in Power Apps while at the same time avoiding the limitations (two examples are Sort and using StartsWith) and the code gymnastics sometimes necessary to write to them. I develop exclusively in SharePoint and in the last five years across multiple customers including two large systems with over 100 lists, I have never found the need for them.

I also have a blog on data structure which may be of use to you.