Need help with a dropdown/combo box

[DiegoJ]

Hi guys,

I'm currently building a powerapp which is meant to run stored procedures in order to either update or insert data into the table.
The issue I am having concerns two tables, one being the fact (Branch) and the other one being the dimension (BranchElement).

Branch table has a BranchClusterId which connected to BranchElement by a BranchElementId. Since these are numbers, they don't say much and are a bit useless to display in the drop down. BranchElement contains an ElementText column which is a description of sorts for the ID. Branch is also the only table that I update/insert, and the form also uses the Branch table as its data source.

My questions are:

1. Since my form uses the Branch table, how would I go about displaying the ElementText on the combo box without breaking the automatic insertion of the selected gallery item on the form?
2. Is it possible that the drop down selection item (ElementText) can return the ID associated with that text? This is vital as this is how it's stored in the Branch table (BranchClusterID).

Currently I've been trying by doing:
ShowColumns(
AddColumns(
'[dbo].[Branch]',
"ElementText",
LookUp('[dbo].[BranchElement]', BranchElementId='[dbo].[Branch]'[@BranchClusterId], ElementText)),
"BranchClusterId", "ElementText")
However, I don't know how to get further than this.

I know the post is not well structured, so if there are any questions or any additional information I can give out to help please let me know.

Thank you very much.

[gabibalaban]

I'm not sure if I understood your issue...but let me try:

I begin with the following presumptions:

         - between BranchClusterId and BranchElementId is a relationship of type: many to one;

         - your app is built on a Sql Database Template App.

In the DetailScreen:

Inside the datacard which is used to show the data for BranchClusterId, you'll need to put a new Label control with Text property set as:

   LookUp('[dbo].[BranchElement]',BranchElementId=BranchClusterId).BranchElement;

You can send the Visible property for Datacardvalue to false (not showing anymore the BranchClusterId value ).

Also in the EditScreen you have to do the same operation without of course hidding the BranchClusterId.

Please be aware that if you want to modify a datacard, you have to unlock change to properties in Advanced tab of right panel.

Hope it helps you !

[DiegoJ]

Thanks for the reply.

I don't know if I'm doing something wrong but this doesn't solve my issue.
Your assumption are correct.

I know I didn't explain it properly so I'll try to clarify.

As the BranchClusterID (Branch table) and BranchElementID (BranchElement table) are numbers, they do not say much about what they are. ElementText on the other hand contains a description on which location the branch is in, e.g. France, Spain, etc.

In order to make it user friendly, I want a drop down that contains the ElementText so that users can see that instead of ID's. The big problem with that though, is the fact that the database table I am updating/inserting (Branch) uses BranchClusterID which would mean that inserting ElementText would of course break the procedure.

I am currently using forms in order to insert/update, as seen below:


The insert branch button just runs a microsoft flow which runs a stored procedure that inserts the data into the database table Branch.

So in essence, I need a drop down that displays ElementText, but returns the BranchElementID of the selected ElementText. Is this possible within PowerApps?

[v-xida-msft · Accepted solution]

Hi @DiegoJ ,

Could you please share a bit more about your scenario?

Do you want to configure the BranchClusterId ComboBox to act as a ElementText selection list?

Based on the needs that you mentioned, I think PowerApps could achieve your needs. I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the BranchClusterId ComboBox in your Edit form to following:

ShowColumns('[dbo].[BranchElement]', "BranchElementId", "ElementText")

then configure the "ElementText" column as display column value within the BranchClusterId ComboBox.

Set the DefaultSelectedItems property of the BranchClusterId ComboBox to following:

{
  ElementText: LookUp('[dbo].[BranchElement]', BranchElementId = ThisItem.BranchClusterId, ElementText)
}

Set the Update property of the BranchClusterId field data card in your Edit form to following:

BranchClusterIdComboBox.Selected.BranchElementId

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[DiegoJ]

Thank you for this solution, it worked perfectly.

I do have on additional question, is it possible still to return the value of the selected item from the gallery as the default in the drop down?

Currently as you said, the DefaultSelectedItems function has:

{
ElementText: LookUp(
'[dbo].[BranchElement]',
BranchElementId = ThisItem.BranchClusterId,
ElementText
)
}

However, when I try to change ThisItem to refer to my gallery, I get an error. All my text input fields use Parent.Default to get data from the gallery, how could I do this for the ComboBox? I've read that the normal fix is to put Parent.Default in the DefaultSelectedItems, but from what I've seen this doesn't work because the Combo box is using element text, so it's expecting a value similar to ElementText but instead gets the BranchClusterId which is an int. Correct me if I'm wrong, error is below:

I found another thread that solves this issue by putting this into their DefaultSelectedItems (https://powerusers.microsoft.com/t5/Building-Power-Apps/Issues-with-quot-Default-quot-in-Combobox/td...😞

Filter('Table', Id = Gallery.Selected.Id).ColumnBoundToCombobox

However, I suspect that this would break the rest of the combo box.