Show related description in combobox instead of Id

[Anonymous]

I just started out with PowerApps, but I have extensive background with VBA, DAX, SQL and C#. Despite this, I can't get the most simple thing to work in PowerApps form tied to Sharepoint lists. I have a simple model with three tables, Product, Unit and Data. The data has lookup columns for the Product and Unit and a Quantity column. When I build a new app based on the Data table, I get the following simple view.

Now, the only thing I want to do is to show the descriptions from the tables Product and Unit instead of the id's (but still of course save the Id's as the data has no description columns), but I have spent two days without success. I'm sure this has  to be a trivial thing, but I just have not found a way. Can anyone help?

I thought I needed only to adjust the DisplayFields property, but the only Field for the ProductId combobox I have is "Value". I have managed to create another combobox from the Product table, and in this I can select Id and Description to the DisplayFields, but I can't tie this combobox to the form which edits the data table.

[EddieE]

@Anonymous 

What are the Items properties set as? If you add a new Edit From to your app using a new screen and your data source this will usually give you the example of the types of things you need to set up. The Items property is the most important in dropdowns / comboboxes.

Try setting the Items property of your Combo that points at the Product table to

Choices(yourProductListName.yourLookUpColumnName)

As a tip, LookUp columns inside PowerApps are quite difficult to work with. They do make working in SharePoint easier but in PowerApps they can throw many errors, if you start trying to do things that are a little off standard. They're not totally unworkable, just time consuming and frustrating.

I tend to design my models using Text, Numbers and Date field types and use PowerApp formulas to manage updates across various lists.

[Anonymous]

Hi, thanks for the reply. The items are set to Choices([@'03_Data'].ProductId) by default.This is already something I don't really understand, as how can it reference the products correctly even though the data does not contain all the products? To my logic this should  already reference the 01_Product table, but clearly there is some fundamental I don't understand.

Setting the Items property of that Combobox to Choices([@'01_Product'].Description) fails with the following error: "The function 'Choices' has some invalid arguments.

I also tried bringing the related column ProductId: Description to the data and changing it here, but this is not something I would like to do, and this also did not use the Id as the selected value, but the Description.

Would this then be somehow simpler without the lookup column? I thought it was "mandatory" as it would act as a "foreign key" relationship.

I really thought this would be the most basic need ever, so I'm stunned that I can't get this to work, and can't seem to find good examples of!

[EddieE]

@Anonymous 

Re: This is already something I don't really understand, as how can it reference the products correctly even though the data does not contain all the products?

The Choices() function inside PowerApps acts similar to the LookUp column functionality in SharePoint. The LookUp column pulls data (as a table) from Products into a dropdown, usually, then this table is then collected - in it's entirety - by the function. Hence you are able to see all of the available choices, even when the data list hasn't yet used them all.

Re: Would this then be somehow simpler without the lookup column?

All PowerApps are simpler without LookUp columns IMO 🙂

I'm not really sure where or what you are stuck on now, is it still the original question? Good sources can be found on YouTube. Both Shane Young and @RezaDorrani have good videos that explain the use of Complex column types in PowerApps, here

- Shane Young

- Reza Dorrani

Let me know if you need anymore assistance, I'm happy to help.

[Anonymous]

Yeah, I tried this without the lookup column, and succeeded immediately by adding a dropdown tied to products and setting the ProductId text field (instead of combobox) default value to Dropdown.Selected.Id. So I guess I'll ditch the lookup columns.

I would still like to know if it can be done with lookup columns and which is the "correct" way.

[EddieE · Accepted solution]

@Anonymous 

I think I know what you are wanting to do. It sounds like that when you setup the LookUp column in SharePoint you selected 'ID' from the 'In this column' Dropdown. This is why the ID is showing inside your app.

There's no real need to do it this unless you want the ID showing in your Data table for some reason? If you change this to Description (instead of ID) in the column settings then refresh the data source inside your app you'll see the Descriptions instead of the ID. To refresh the data source do this in PowerApps

If this is all you need then you can continue to use the LookUp columns in your app, however, prepare to lose a lot of hair haha

Let me know if this helps?

[Anonymous]

Yes, as I have designed several SQL Server databases, I tried to adhere here to the same principles that the data has no redundancy, and has only the related Id (or a unique code as natural key) as foreign key, but perhaps this approach is not the best here. Of course, if I take the lookup column to the product Description, the solution is automatic. Thank you for the ideas.

[EddieE]

@Anonymous 

Yes, you are correct, the traditional approach to database design doesn't work with SharePoint because it's not a database. The relationships that allow data to flow between tables in a traditional database design need to be physically coded when working with PowerApps and SharePoint as the data source. Data redundancy is difficult to avoid and/or manage in this system.

Lookup columns in SP provide a relationship, of sorts, but as you've seen the relationship is only to the specified column in the table being looked-up and not that entire table.

If you want to avoid these issues then the solution is to use a database as your data source, such as, SQL, DataVerse or others. These, however, come at an extra licensing cost, which is why so many use the PowerApps + SP combo.