Answered

Drop down of choices based on another drop down of choices in the same SP List

(0) Share

Report

Posted on by kdc02

Hi, I have two drop downs and they both have choices as its column type from the same Sharepoint list. Is it possible to have the choices of the 2nd drop down list based on the 1st drop down list aside from making a new sharepoint list just to show the relationships? For the existing drop down, I currently have the following as the code under the Items Property: Drop down 1: Choices('Sharepoint List Test'.'Column A') Drop down 2: Choices('Sharepoint List Test'.'Column B') Thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (2)

WarrenBelz 154,757 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @kdc02 ,

The issue here is how do you define the relationship between the various choice values ? You really need a reference list with the combinations of ColumnA to ColumnB (which is not what you want to do).

Was this reply helpful? Yes No
kdc02 51 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz
Does creating a new sharepoint list specifically for the relationships work even if they are choices in my main sharepoint list? Also, is the "lookup" column type in sharepoint list the way to define relationships in sharepoint list?

Thanks

Was this reply helpful? Yes No
Verified answer

AARON_C 2,235 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @kdc02,

If you still wish to not create a relationship between the two choices, you would have to hard code the Items property of your dropdown. This is an example of what it would look like.

If(DataCardValue2.Selected.Value = "Red", ["Apples","Strawberries","Watermelon"], DataCardValue2.Selected.Value = "Green", ["Grapes","Pear"], DataCardValue2.Selected.Value = "Orange/Yellow", ["Oranges","Mango","Rockmelon"] )

Please tick Accept as solution if the answer is useful.
Thanks,
@AARON_C

Was this reply helpful? Yes No
Verified answer

WarrenBelz 154,757 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @kdc02 ,

The whole issue is that there is no relationship defined between two lists of free-text choices, so how after choosing from the first one, do you determine which ones from the second list belong to it ? I use reference lists all the time - for instance you will have duplicates of field1 as many field2 items belong to it, so the Items of the first drop-down would be

Sort( Distinct( YourRefList, field1 ), Value )

and the Items of the second drop-down

Sort( Filter( YourRefList, field1 = dropdown1.Selected.Value ), field2 ).field2

I would recommend you turn the choices columns in your main list back to Text and simply write the drop-down values back to these3 fields. Choice columns also have restrictions - (the main one) you cannot sort by them in a Delegable manner nor can you use StartsWith() in a filter.

Lookup columns serve little purpose and sometimes restrictions/complexity you can do without - I never use them. SahrePoint is not a relational database and will not accept delegation on queries involving relationships at a database level.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
kdc02 51 on at

Like (0)

Report
Copy link

Link copied!
Hi @WarrenBelz
I tried making a reference list and it worked. Although when I try to patch the values, I get the error "Expecting a record value but of a different schema". My code for the patch are as follows:

Patch('Test',
Defaults('Test'),
{
'Field A':Dropdown_A.Selected,
Field B:Dropdown_B.Selected}
);

Thanks

Was this reply helpful? Yes No
WarrenBelz 154,757 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
@kdc02 ,

As below - if you are writing to Text fields

{ 'Field A': Dropdown_A.Selected.Value, 'Field B': Dropdown_B.Selected.'Field B' }

If you have left them at Choice fields

{ 'Field A': {Value: Dropdown_A.Selected.Value}, 'Field B': {Value: Dropdown_B.Selected.'Field B'} }

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No