Linking combo boxes

[Anonymous]

Hi all

I'm new to powerapps so please forgive me if this is an obvious question! I'm struggling to link 2 combo boxes so that one will display data based on what was entered into the last.

Both boxes get data from a table (see file). The first displays data from the first column (called Column1) and I'd like the second to show the record associated with said column (but obviously not the data from Column1) however I can't get it to work.

Here's what I've done - where have i gone wrong?

When you click on the combobox it displays everything in column B rather than searching through the row

EDIT: Looks like the picture isn't loading so here is what i typed: 

LookUp(Sheets_List, Column1 = "ComboBox2.SelectedItems")

Into both the search and display field boxes in combobox 3 - where combobox 2 is where the first selection from that table takes place

[dinusc]

Hello Colleen,

Please note that:

1. "SelectedItems" is an array of items

2. LookUp returns a record while ComboBox.Items expects a table.

Please try this:

Filter(Sheets_List, Column1 in ComboBox2.SelectedItems.Column1)

[Anonymous]

Hi - thanks for the reply.

No error is being thrown but the box always looks empty - do you know why this would be? Also does this formula go in the search fields or display fields box (sorry i'm very new to this!)

[wyotim]

Hello @Anonymous and welcome to PowerApps!

To check my understanding, are you trying to collect the row of items in columns 0 to 7 (Excel columns B through I) in a combo box based on what is selected from Column1 (Excel column A)? If so, I have an attempt at a solution, though it isn't too elegant.

One reason why you are getting the items in column B is that the combo box DisplayFields area defaults to the first item in an alphanumeric order, so the 0 column is being displayed. Second is that these functions tend to operate over columns of data and not rows. 

Now, there may be a way to operate on the row (and hopefully someone will speak up with that solution) but here is my solution. You would put this in the OnSelect section of the combo box where you are selecting the Column1 value:

Clear(RowListCollection);
Collect(RowListCollection, {ListItem: LookUp(Sheets_List, Column1 = ComboBox2.Selected.Column1, '0')});
Collect(RowListCollection, {ListItem: LookUp(Sheets_List, Column1 = ComboBox2.Selected.Column1, '1')});
Collect(RowListCollection, {ListItem: LookUp(Sheets_List, Column1 = ComboBox2.Selected.Column1, '2')});
Collect(RowListCollection, {ListItem: LookUp(Sheets_List, Column1 = ComboBox2.Selected.Column1, '3')});
Collect(RowListCollection, {ListItem: LookUp(Sheets_List, Column1 = ComboBox2.Selected.Column1, '4')});
Collect(RowListCollection, {ListItem: LookUp(Sheets_List, Column1 = ComboBox2.Selected.Column1, '5')});
Collect(RowListCollection, {ListItem: LookUp(Sheets_List, Column1 = ComboBox2.Selected.Column1, '6')});
Collect(RowListCollection, {ListItem: LookUp(Sheets_List, Column1 = ComboBox2.Selected.Column1, '7')})

To break down what it is doing, there is a collection being made to store the row values when a value is chosen for Column1 in ComboBox2. The first task is to clear the collection so only the values in the row for the selected item are used (rather than adding to values previously collected). The next 8 statements are collecting each of those values, one from each column in the row. The end result is a collection with one column titled ListItem that has the values of the row based on what is in Column1.

Now, this will leave a bunch of blank items in the collection when there are empty spaces in a selected column. To take care of this, in the Items section of ComboBox3 put:

Filter(RowListCollection, Not(IsBlank(ListItem)))

This connects the collection that was just made to the combo box and filters out any blanks so you will only have fields with data to select from.

If it were possible, a better approach would be to format your data into two columns, such as:

Column1          Column2

ROCKSTAR    ROCKSTAR KNEE

ROCKSTAR    ROCKSTAR ANKLE

...                     ...

This would greatly simplify the approach needed and perform a bit better than my solution. Plus, it makes it easier to have more than 8 items, if that ever comes up. It may not be possible, but I figured I would just comment on that in case it is.

Please let me know if this works out or if you encounter any issues or questions.

[dinusc]

The formula goes in the "Items" property of another control:

ComboBox1.Items=Filter(Sheets_List, Column1 in ComboBox2.SelectedItems.Column1)

[v-xida-msft · Accepted solution]

Hi @Anonymous,

Which column do you want to use within the second Combo box control?

Do you want to display all other column values associated with the selected Column1 value (from the first combo box control) within the second Combo box control?

I assume that you want to display all other column values associated with the selected Column1 value within the second Combo box control, I have made a test on my side, please take a try with the following workaround:

Set the Items property of the first Combo box control to following formula:

Table1.Column1

Note: The Table1 represents the Excel table on my side, on your side, you should type following formula:

Sheets_List.Column1

Set the OnChange property of the first Combo box control to following formula:

Clear(Collection1);
ForAll(
RenameColumns(ComboBox2.SelectedItems,"Column1","Iterator"),
Collect(Collection1,{ListItem:LookUp(Table1,Column1=Iterator,'0')});
Collect(Collection1,{ListItem:LookUp(Table1,Column1=Iterator,'1')});
Collect(Collection1,{ListItem:LookUp(Table1,Column1=Iterator,'2')});
Collect(Collection1,{ListItem:LookUp(Table1,Column1=Iterator,'3')});
Collect(Collection1,{ListItem:LookUp(Table1,Column1=Iterator,'4')});
Collect(Collection1,{ListItem:LookUp(Table1,Column1=Iterator,'5')});
Collect(Collection1,{ListItem:LookUp(Table1,Column1=Iterator,'6')});
Collect(Collection1,{ListItem:LookUp(Table1,Column1=Iterator,'7')})
)

Set the Items property of the second Combo box control to following formula:

Filter(Collection1,Not(IsBlank(ListItem)))

The GIF image as below:

Best regards,

Kris

[Anonymous]

Everyone - I'd just like to thank you all for your help and advice. This is a lovely community and I'm very happy you put in the time to help me with this project 🙂 

@v-xida-msft I've followed through with your solution and it seems to work however no text is being displayed. I've made sure to save and publish the work in case that would have made a difference but the same problem still occurs - I also deleted and re-added the boxes in case previous attempts hindered your solution. What have I done wrong?

 (I know this GIF shows it not published - but i tried it again after recording this clipping and the same result occured)

Thank you millions in advance,

Colleen 

[v-xida-msft]

Hi @Anonymous,

I have made a test on my side again, the formula that I provided works well and don't have the issue that you mentioned.

Please take a try to re-create your app and try the formula that I provided within the Combo box controls again, then check if the issue is solved.

In addition, you could also consider take a try to add a Drop down control instead of the second Combo box control within your app, then set the Items property of the Drop down control to following formula:

Filter(Collection1,Not(IsBlank(ListItem)))

Then check if the issue is solved on your side.

More detaisl about the Drop down control in PowerApps, please check the following article:

Drop down control

Best regards,

Kris

[Anonymous]

Thank you so much Kris,

You're right this worked perfectly (I've no idea what i did wrong initially!). I really appreciate the help.

Warmest regards,

Colleen

[wyotim]

@v-xida-msftWith this issue being solved, I wanted to follow up on something for my own learning.

Your solution was very close to mine aside from your ForAll loop handling of the multi-select in the combo box (awesome, by the way). I assumed that there was some way to iterate through rows of data that I just didn't know about and that my solution (and overly verbose description) was more of a brute force method. It would seem that there is not a way to iterate through rows in the same way that many functions natively do through columns.

My question: in PowerApps, is there a way to do/mimic basic data transformations, such as a rows-to-columns transposition as one would do in PowerQuery/Power BI? I ask because I have seen the column/row hybrid data structure that Colleen was working with multiple times in the Excel realm and I was trying to think of a way to handle the scenario of another column being added to that table without having to edit the PowerApps code. Any thoughts?