Time to Stop Using SharePoint Lookups in PowerApps

SharePoint lookup columns cause a lot of complexity in PowerApps. Another issue is you need someone with the knowledge to correctly add choices to your SharePoint List to Update.
Instead let your users update your lookups in your PowerApp. Simple!
No Complex Equations.
1. Create Your SharePoint List
2. Create Your Lookup List
3. Use Only Single Line Text Fields
4. Create the relationship in PowerApps!

[@axioworks]

What are the advantages of this solution, versus giving the users access to the Projects lists and it's lookups directly in SharePoint?

[@andrewhess123]

1. #1 Sadly not everyone is as efficient with SharePoint as they should be.. I'm sure many of us are, but a lot of my customers just are not. 2. In order to update a PowerApp you have to go 2 places to update, PowerApps & SharePoint? I'd rather have 1 place to update a PowerApp, in PowerApps. 3. Using LookUp columns in PowerApps adds complexity. Compare the example below to just patching a single line text to a list: Patch( 'Site Inspections', LookUp('Site Inspections', ID=1), { BranchLocationLookUp: { Id: 2, Value: "Minneapolis, MN" } } )

[@andrewhess123]

I'm sure there are very smart developers out there, but this is a low-code/no code tool. When I have new "makers" come to me with complex lookups, I try to simplify it for them, and this example I keep going back to. I really think you could design an admin page with all your choice fields (that change more than usual) and keep the users out of the database ("SharePoint" in this use case) is a smart alternative. It comes down to preference, but now you have options :)

[@andrewhess123]

Hi AxioWorks, I just thought of the biggest benefit. In a LookUp field if a customer/client/user deletes a choice option, by default the data will be erased completely. You can turn on Restrict Delete and move the data out of the LookUp option.... but with this option using a Single Line Text, if you delete a choice out of the Lookup the data will not be erased. This is literally the best option due the countless times I've seen customers and clients delete a choice out of a column, and the data disappear and we need to rollback a List.

[@wendymeyer1250]

I LOVE this method!!! Been battling all day trying to Patch my records to a relational SharePoint data source, and am absolutely ecstatic to have found this video. Thank you Andrew! 🙌You explain so well.

[@andrewhess123]

Thanks so much Wendy! Hope to create many more videos that are just as helpful!

[@michelforro]

An advantage on this approach instead of the classic lookup is that you can use it to do a 'cross site collection' lookup, like you can have a site with master choices to be reused in more apps

[@andrewhess123]

Hi michelcarlo, very good point! Thank you for sharing.

[@michelforro]

@@andrewhess123 No worries :)

[@andreiasola9162]

Hi Andrew, thanks for sharing your knowledge! If we need to select multiple values, instead of just one, is it possible to adapt this solution?

[@andrewhess123]

Hi andriea, Should be possible, you just need to figure out how you want to write your multi select values to your datasource. It is possible to write them as multi choice fields or as plain text.

[@alizaz1993]

This is great Andrew! I am trying to implement this in what I'm currently working on however I can't get it to auto-fill a field based on a pick from one of these columns. Have you had any success with this?

[@andrewhess123]

Hey Spike, love the name! Yes I have had success getting it to autofill based on a selection. It's all about getting the filter to work right.

[@Bekesam]

Thank you for posting this solution!! This is great for Choice type columns but what about for one -to-many relationship that both have long lists, thus seemingly requiring a lookup column? I agree 100% percent on the reasons to move away from the lookup columns as it does create extra complication and syntax knowledge that maybe is not as intuitive as a regular database.

[@andrewhess123]

Hi Bekesam, the solution is to do the LookUp on the Power Apps side instead of the SharePoint site, you can see @10:43 where the list contains the items in the dropdown column. Technically you are doing LookUps but not doing a LookUp column which can lead to delegation issues.

[@andrewhess123]

Thank you for watching Bekesam! Let me know if that's what you were asking?

[@gregcalvert4645]

Great!!! I can now create a admin sharepoint list and only give them access to that screen to do the update?

[@andrewhess123]

Exactly! Yes that would be the next step. Create an admin screen and all the users can update their choices there. You could even build permissions around the admin screen so only certain people can update the fields

[@mikesutton1950]

This worked great for me until I deleted one of the items from the dropdown field list. After I did this, the field would not populate the default value if it wasn't available in the dropdown, even though you could see the value in the backend sharepoint list. I could never find a work-around for this. So, this feature works great as long as I keep the dropdown values available at all times. I wish I could find a work-around because I would like to delete certain available items from the dropdown over time.

[@PsYc87]

This was a great video. What if you have relationship between your lists? would lookup be the best way still?

[@andrewhess123]

If there is a relationship, I do not use SharePoint lookup columns. You can just filter your gallery or data on the Power Apps side using the secondary list. For any analytics I go straight to Power BI and create the relationships there.

[@PsYc87]

@@andrewhess123 thanks Andrew

[@filipwinski4219]

Hello, Great Video! This idea might be developed by using filters in the lookup field (in my case it is a very common scenario).

[@andrewhess123]

Totally agree Filip, thanks for that idea

[@thomasgoerlach5766]

Let's assume the user has made an entry to the project based on the choices, but later he is changing the name of a choice (e.g. from green to light green). How to implement that the old entries will be updated accordingly in an automated way.

[@andrewhess123]

Hi thomas, this would be tricky whether you use an official lookup column or the way we do lookup columns in this video. To do this 100% correctly, you most likely would need to use a custom Power Automate to do this. That looped through everything and matched. Other more simple solutions could be in the Power App or Power BI if you use that, to create an "or" statement and just say if(green or light green) then it's green. Another option if the row count is low is to create a filter and manually update each one. You have to be very careful when doing this so you don't lose the old data.

[@thomasgoerlach5766]

@@andrewhess123 Hi Andrew, thanks a lot. Is it correct that the advantage using LookUp columns is to get the changes automatically updated?

[@andrewhess123]

@@thomasgoerlach5766 One of the biggest advantages is cross-site/multi-site lookup. Also instead of having people update SharePoint you have users update the Power App instead. Also just writing the code is more simple than trying to use official Lookups. Also Lookups have delegation issues, shown here: ideas.powerapps.com/d365community/idea/7f557668-00d2-43ed-93ae-97aaa7b1867e