Distinct Choices

[cmegow]

I am trying to only show distinct choices in a look up column in power apps.  The formula looks like this:

Distinct(Choices([@'FPC-Invoices'].School),"School")

Despite there being no error in my formula, the form does not return any choices.

Help is be appreciated.

[RandyHayes · Accepted solution]

@cmegow 

Please consider the following formula:

Distinct(Choices([@'FPC-Invoices'].School), Value)

Choices returns a single column of values with a Value column.  It is that which you will need to use then for the Distinct function.

I hope this is helpful for you. 

[cmegow]

@RandyHayes Thanks for your quick reply.  I changed the formula as suggested and the result is a drown box with blank values.  I believe I can achieve my desired filtering using cascading drop downs tied to text fields instead of look up columns.  However, the control does not allow the user to begin typing in the School name with the drop down control like the default look up control.  With over 180 schools in my list, the task of locating a single school is too cumbersome without the type ahead feature.  Suggestions for adding the type ahead feature to a drop down box control?

[RandyHayes]

@cmegow 

Yes, the DropDown control does not provide lookup and typing/searching (although you can simulate it with another textinput).  But if that is something you need, you should explore the ComboBox.  However, the formula I provided should give you what you wanted for the previous requirement if I had understood all of your message correctly.

Also, an FYI on DropDowns - they flake out sometimes and show nothing when you change their items property.  Trick is to ctrl-X (cut) them and then ctrl-Z (undo) to put them back...they magically then work.

[cmegow]

The formula now matches your suggestion.  I also tried your copy and paste trick to no avail.  Still getting the blank drop down box.  Suggestions?  Thank you for your time to help.

[RandyHayes]

@cmegow 

Actually, the fact that you see a long blank list is indication that it is actually getting values - it's just not displaying the correct one.

First, please clarify - this is a DropDown control and not a ComboBox?  That image looks like it's a ComboBox.

Also, Please confirm the Lookup field settings in SharePoint - can you provide a snapshot of the column settings for that Lookup column in the SharePoint settings?

[cmegow]

@RandyHayes   Thank for all the help.  I ended up swapping the control to a drop down box from a combo box and that fixed it.  Thank you so much for your time and effort to help me through this issue. 

[RandyHayes]

@cmegow 

Yes, the ComboBox needs to have some additional settings made.  I had assumed you had a dropdown to start with.

Glad you got it going.

[cmegow]

@RandyHayes out of curiosity, what other things would need to be done to make the combo box work?  I love an opportunity to learn!  And, I honestly like the combo box control better than the dropdown box control.

[RandyHayes]

@cmegow 

So, the ComboBox has a lot more moving parts to it and more functionality, but with it comes more know-how.

Firstly - the DropDown Items property can be a table of any number of columns.  If there is only one column then that is the only "Value" that can be displayed (as seen in the Property Pane on the right of the designer).  If the Table in Items has multiple columns, then the "Value" property will only have one column.

Example:

Item Property : Table({Disp:"This", Value:1}, {Disp:"That", Value:2}, {Disp:"The other Thing", Value:3})

What is Displayed in the DropDown is what is Selected in the "Value" property:

Also note that the Default property of the DropDown is a TEXT property that is used to match the Column selected in the "Value" property to show as default.  So, If the "Value" was set to the Disp column and we wanted "That" to be default, then the DropDown Default would be "That".  If the "Value" property was set to "Value", and we wanted "That" (Value 2) to be default, then our Default property would be "2"  (note: not numeric 2).

When you refer to the DropDown to get it's value, you can refer to the Dropdown with Dropdown.Selected.xxx where xxx is any column in the Item records that you want to get.  So, if we had the "Value" property set to "Disp" and "That" was selected, then the following formulas would give these results:

   Dropdown.Selected.Disp would return "That"

   DropDown.Selected.Value would return 2

That's the basics for DropDowns.

For ComboBoxes there are some other factors.

First, ComboBoxes can have multiple Layouts - Single Line, Double Line and Person Layout

This allows you to display up to two lines of information in the DropDown and a picture if you choose Person Layout (but you need a valid person record signature).  You choose which columns you want to display in each line of the Layout through the Fields - Edit link in the property pane.  You can only display Text columns (although oddly enough, as in our example, if you choose 2 line and don't have enough text columns in your items (as we only have one), it will actually display the numeric column even though you can't select it to show - I might add that ComboBoxes are flaky).

For the ComboBox, there is a Default property but it does nothing.  To "default" items, you need to use the DefaultSelectedItems.  It gets tricky here as you must have a Table of rows that have values that match EXACTLY to the Items in order to get them to default as selected.

ComboBoxes are also either multi- or single-select.  They are also searchable - but be aware that when trying to search datasources with a ComboBox, you can quickly run into delegation issues.

In general, there are a lot of little quirks to ComboBoxes, but once you work with them enough, you get to know them.

I hope this is clear and helpful for you.