0

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

  1. 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.

1 Answer 1

2

I've been working with SharePoint for 15 years. Whenever I had a chance to remove Lookup fields, I've done that. With InfoPath, JavaScript forms, and now with PowerApps, I've always avoided Lookup fields. Lookup fields are a pest and difficult to work with. Instead, I prefer to store the ID of the looked up value, but I also store the title or any other pertinent information of the looked up record, or I serve it up at run time of the PowerApp.

If you need the looked up data to be meaningful in a SharePoint list view, then you will need a bit of data duplication, but all the data can be held in simple text/number fields, instead of the complex lookup field objects that cause so much pain when addressing the lookup elements with code in PowerApps or Power Automate.

With PowerApps as the user interface, it is dead easy to store lookup lists into collections when the app starts. Filter the collection to contain only the data that pertains to the record. Then use the collections to do your sorting, serve up dropdowns, etc., especially if the lookups are about slow changing lists of departments, Project numbers, etc.

Using this approach, you can even store a combination of looked up fields into a single text field of the main record, like <project number> - <project title>, instead of using two fields.

The only thing that is not working in this approach is the dynamic nature of a lookup field. If the Title of a true lookup field record is changed, the related dependent records will reflect that immediately, because the SharePoint view uses a dynamic lookup. If the relationship is created with text/number fields stored in the dependent record, these need to be edited to receive that change. However, this has never presented as a real issue in my experience working with SharePoint data in customised forms, where I can build the dynamic lookup into the form.

Your mileage may vary, but this is my experience.

3
  • Thanks for the detailed reply.. i worked with lookup fields inside power apps and power automate, and they need more work to work with compared to Number or SingleLineOfText field types, but at the same time they are not that complex for me to stay away from them...but what let me to think twice before using Lookups is that whenever i ask this question i always get the answer to stay away from using Lookup fields.. but till now i am not sure why.. or atleast i could not find a convincing answer Oct 9, 2023 at 0:03
  • second point, you mentioned it is dead easy to store lookup lists into collections when the app starts. but what if the list of lookups contains more than 2,000 items.. then we cannot hold more than 2,000 items inside collections. am i correct? so this is a limitation Oct 9, 2023 at 0:05
  • for one, it is possible to store more than 2000 item in a collection. You just can't query more than 2000 items at a time. Second, if you have more than 2000 items, you may not want to use the whole list, but bring only those items into the collection that you need. You will never have 2000 items or more in a pick list, will you? So, what is the scenario where you would need more than 2000 items?
    – teylyn
    Oct 10, 2023 at 3:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.