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.
