cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TGrounds Resolver II
Resolver II

How to patch a SharePoint secondary Lookup Column?

I have a SharePoint List that has 3 fields that are lookup values from other SharePoint lists.  For each of these fields I was given the option to bring in other fields from the other SharePoint lists.  I used the following method:

 

PowerAppsErrorSharePointSecondaryLookup.png

 

As a Result I have the following in my SharePoint list: 

  • Column:  App_ID - This is a lookup from APP list - along with this I select to bring in App_Name
  • Column:  Function_Code - This is a lookup from FUNCTION list - along with this I selected to bring in Func_Description
  • Column: Feature_Code - This is a lookup from FEATURE list - along with this I selected to bring in Feat_Description

So, my SharePoint list has 6 'Lookup' fields in it - the first 3 are directly manipulated - the last 3 are set based on the values selected in the first ones.

  

I am having an issue with Patching the list when it comes to these second 3 fields.  Out of frustration I removed the additional fields from other SharePoint lists and I'm able to Patch those lists without issues.  This table, I don't have that option - I'm using both the code and description in table listings.

 

Here is and example of the method I have tried to use - I have no errors in the function and when I select it (It's attached to a save Icon) I don't get any errors either, I just don't get a new record in the SharePoint listing.  Here are the functions that I have used for both the primary lookup and the secondary lookup :

 

Patch(D365SuccessCriteria,Defaults(D365SuccessCriteria),
{App_ID:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:LookUp(D365Apps,AddAppCode.Selected.Value in Title,ID),Value:AddAppCode.Selected.Value}},
{App_ID_x003a_App_Name:{'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",Id:LookUp(D365Apps,AddAppCode.Selected.Value in Title,ID),Value:LookUp(D365Apps,AddAppCode.Selected.Value in Title,App_Name)}})

Is there a different method I should be using on the secondary fields?  I can't find anything in any of the documentation.

 

I'm able to add records to this SharePoint list when I use the FORM method, but I have 3 dropdown values that I must default and I'm unable to set defaults on a 'New Form' command so the Form method isn't an option.

 

Thanks!

Tom.

2 REPLIES 2
Super User
Super User

Re: How to patch a SharePoint secondary Lookup Column?

For what its worth.  Unless the user needs to actually work in the sharepoint list (which I belive Powerapps takes them away from) I never create lookups in the actual SP List.  This gets rid of the problem straight away as you are now only working with a basic text field

 

I create sub list for all dropdowns

 

Then in powerapps I put dropdown controls on the forms in teh data cards and base them on the smaller list.  I put teh actual datacard field as hidden with its default value to equal the dropdown selected value

 

I make the default of teh dropdown to equal Parent.Default

 

So if you are doing an edit form the dropdown will have the default value from the textbox if there was one.  If you are creating a new item the user simply selects from the list which will populate the hidden box 

Highlighted
TGrounds Resolver II
Resolver II

Re: How to patch a SharePoint secondary Lookup Column?

Thanks for your reply and I agree working with SharePoint lists and especially dropdowns has been frustrating - but I look at it as a way to provide feedback about what is working and what isn't working so that the product continues to evolve and get better.

 

I'm leveraging SharePoint for several reasons for this particular application:

  • Document printing for customers - there are elements of the app that need to make it into status decks, etc. and I haven't seen any print capability from PowerApps.
  • Content approval before distribution - The PowerApp provides the ability to collect data from the CSM community centrally so that the collective community benefits.  However, there must be governance over that - so by allowing information to come into SharePoint and be reviewed and approved before it's available to the rest of the users is important
  • Lookups - It's my old habits coming out - I have always subscribed to normalized data even in SharePoint sites.  I create sub-lists for all dropdowns as you indicated but leverage them via the lookups.  This gives the community the ability to use SharePoint or the App depending on the situation.  Plus it allows for cascading changes if needed.

Defaults of the field are less about a default for the SharePoint listing, but rather defaults set based on the context from within the PowerApp.  If a user has navigated to an App in the gallery, and Function within the sub-gallery and decides to add Success Criteria, they shouldn't have to select the App and Function from the dropdown - the app already knows those two values based on their navigation.  This is the challenge I'm having with using the Forms - I'm able to get a default from the parent (especially on edits of existing records) but when creating new records it's been an issue.  Each of the SharePoint lists have a code and a description.  In the dropdowns I have only been able to figure out how to display a single value in the dropdown and not the code and description - so to have the users remember codes is a problem - they should just default.  It's about ease of use for the user and not having them have to remember things because of limitations of the technology.

 

This entire journey has been great for me to be able to dig in and learn PowerApps building an App that my team is now using for our day-to-day work with our customers (Growth Mindset ;)).  Given that this is intended for end users as a tool, unless they are working with IT, they are not going to have the ability to create SQL databases, etc.  They will be connecting to SharePoint lists and Excel and other readily available tools.  So for me to be able to understand what works and what doesn't or what requires a specific approach is helpful.  Also, where I can raise issues that the Product Group incorporates in future releases makes me feel like I'm contributing to the evoloution of PowerApps.

 

I agree with you, working with basic text fields would have been so much easier but I always love a challenge.

 

Tom.

 

 

 

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Power Platform 2020 release wave 1 plan

Power Platform 2020 release wave 1 plan

Features releasing from April 2020 through September 2020

Top Solution Authors
Top Kudoed Authors
Users online (8,545)