Dropdown on a form from two linked Sharepoint lists

[Anonymous]

Hi

This has been driving me mad, and I’m pretty sure it’s an easy fix.  I am working off two tables, one has a list of payments and the other has a list of payment status’ (table name PP_Status). The ID number of the payment status is a field on the payments list. The two are linked on SharePoint (and started out life on MS Access).

I have a drop down menu on a form that I want to update the payments table, which should contain detail from the PP_Status table. The only changes I have made are to the default settings are

Items on the drop down is set to - ShowColumns(PP_Status,"Title","PP_Status_ID"). This is showing the correct info on the drop down, but in the actual box it still shows the ID number

Update on the data card is set to Datacardvalue1.Selected.PP_Status_ID

I am getting a red line in the data card update section, and I get the error message’ An entry is required or has an invalid value. Please correct and try again’. Does anyone have any idea what I am doing wrong? I have also tried playing around with the patch function, with no success.

Thanks

[Drrickryp]

Hi @Anonymous,

Is the Payment Status ID a lookup type  in your Sharepoint list?

[Anonymous]

Hi. Yes the Payment Status ID is a lookup on the sharepoint list.

For your info on the payment list the field is called Payment_Status_ID and on the linked table the field (which is the unique ID) is called PP_Status_ID, so there is no confusion

[Drrickryp · Accepted solution]

@Anonymous,

This is probably what is causing some confusion. I personally don't like using the Sharepoint lookup column designation in Powerapps, because it is cumbersome to add to them or edit them.  (although some of the other advisors can give you information on how to get around the nuances).   My suggestion is to convert that column back to a number, its easier to work with. 

You can set the Items property of your Dropdown control to a table or even a filtered table.  What you are actually seeing displayed in the Dropdown is one column of an entire record from that table. You can check this out by placing the focus on the dropdown control, looking in the right hand column of the screen.  Under Advanced, half way down, you will see a label "value" with a dropbox to the the right. Selecting the dropbox, you will see all of the columns in the Items table of your dropbox. You choose the one being displayed in the control.  However, all of the columns can be referenced by Dropdown1.Selected.ColumnName.  There is no need to use the Showcolumns() function as you will only see the column in the value setting anyway.  Before modifying the data card, unlock it and hide the textbox inside by setting its Visibility property to false. Than add your Dropdown control to the datacard, you will need to reference the Dropdown property in the Update property of the card containing the dnew dropdown control. (It looks like you are still referencing the textinput box  ie. Datacardvalue1). You should be referencing Dropdown1.Selected.PP_Status_ID.  This will work even though the Dropdown control shows a text value.  I hope this helps to  get you on the right track.  

[Anonymous]

Hi. Thanks for getting back to me on this.  I will let you know how I get on

[v-xida-msft]

Hi @Anonymous,

Could you please share a screenshot about your app's configuration?

Could you please show more details about the error message within your app?

I suppoe that the Drop down control within your Edit form is relaetd to the Payment_Status_ID column in your Payments list, is it true?

If the Drop down control within your Edit form is relaetd to the Payment_Status_ID column in your Payments list, I think there is something wrong with the formula that you provided within the Update property of the Data card (which contains the Drop down).

The Update property of the Data card (which contains the Drop down) is required to provide a record value, bu the formula that you provided returned a text value.

Please take a try to modify the Update property of the Data card (which contains the Drop down) as below:

{
 '@odata.type' : "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
  Id: LookUp(PP_Status,PP_Status_ID=Datacardvalue1.Selected.PP_Status_ID,ID),
  Value: Datacardvalue1.Selected.PP_Status_ID
}

Please also check if my response within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Can-t-save-data-in-Combo-Box-filled-with-ClearCollect/m-p/158493#M53417

More details about setting a value for a LookUp column of a SP list, please check the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/Lookup-Field-Default-Value-Error/td-p/79481

Best regards,

Kris

[Anonymous]

Hi @Drrickryp. Your solutions appears to work, so thank you. It is not unlike the solution I saw on a youtube video recently that I also couldn't get to work! I do need to spend a bit of time checking how it impacts on the integrity of the data, given I have taken out the lookup column. Can you provide any insight into that?

@v-xida-msft thanks for replying, however I think your solution is a bit too complicated. Not least because you appear to reference Microsoft Azure, which I don't think I am using. 

Also apologies for the delay in replying, the last two days have been a bit hectic.

All the best

Pete

[Drrickryp]

Thanks Pete!  If you felt that my answer worked for your problem please mark it solved.  Good luck with your app and glad I could help.  I believe that Lookup and choice columns are really designed for use in Sharepoint and not for Powerapps.  I come from a background in MS Access and was warned against using Lookups from the primary table and instead to create a second table make it's ID field the foreign key in the first table and establish a relationship between the tables.  I have found that converting the Sharepoint Choice and Lookup columns to single line text and number types and creating a second list with the Choice or Lookup Items works better for me.  Moreover, I have not found any loss of data by doing this. If you use the ID of the second list as the foreign key, you can always use a Lookup(list2,ID=foreignkeyID,fieldname) in a gallery displaying the items in the first list. Using a second "lookup" list will also make it easier to add items to the list from Powerapps.  I then import these "Lookup" lists into Powerapps and use them to populate Dropdowns , Radio controls and Comboboxes and put these controls in the Edit Forms  Another option is to create a collection on the fly using Distinct(Datasource, ColumnName) from the original datasource and use that as the Dropdown Items property.  This will serve the same purpose as a second list but won't have any items in it that aren't already in the original datasource.  You can then use the dropdown to filter a DataTable or Gallery using the Filter(Datasource,Dropdown.Selected.Value).   I hope that explanation is intelligible. 

[Anonymous]

Thanks a lot
Your solution helped me a lot when I had a couple of days without solving it.

I also came from using MS Access and Sharepoint and have long used search fields to relate SP lists.
This is a paradigm that I brought now that I am starting to develop in PoweApps.

Again thank you very much, greetings!

[Drrickryp]

Hi Pete

Your question stimulated me to write a series on database principles and PowerApps starting here. https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An... Glad to see you are still with us!