Matching multiple values from different tables in a drop-down

(0) Share

Report

Posted on by kgiles123

224

Matching multiple values from different tables in a drop-down

I have two SharePoint Lists: ‘Provider_Resource_Guide’ & ‘Provider Inclusion By State’

‘Provider Inclusion By State’ includes 'State (State0)', 'Name (Title)', & ‘Skilling’

‘Provider_Resource_Guide’ includes 'Name (Title)', ‘Skilling’, 'AR Start' & 'AR End'

The dropdown I would like to filter to is currently only filtered to 'AR Start' & 'AR End':

Distinct(Filter(Provider_Resource_Guide, 'AR Start' <= Value(TextInput1.Text) && 'AR End' >= Value(TextInput1.Text)),'Name (Title)')

In my form AR (age range) is represented by TextInput1, State is NameList1_8, and Skilling is NameList1_9
This is the only advanced filtering I need other than the match I need on ‘Skilling’

Here is my failed attempt:

Distinct(
Filter(
Provider_Resource_Guide,
'AR Start' <= Value(TextInput1.Text) && 'AR End' >= Value(TextInput1.Text) && Skilling in Lookup('Provider Inclusion By State', 'State (State0)', NameList1_8.Selected.Value, 'Skilling')
),
'Name (Title)'
)

Please and thank you!

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Ash13 576 on at

Like (1)

Report
Hi @kgiles123

I think you should be using Filter instead of LookUp - LookUp only returns the first row it finds that matches the criteria.

Try this:

Distinct( Filter( Provider_Resource_Guide, 'AR Start' <= Value(TextInput1.Text) && 'AR End' >= Value(TextInput1.Text) && Skilling in Filter('Provider Inclusion By State', 'State (State0)'= NameList1_8.Selected.Value).'Skilling' ), 'Name (Title)' )

Was this reply helpful? Yes No
kgiles123 224 on at

Like (0)

Report

This seems to partially work. When I enter a numeric value into ‘TextInput1’ then select the 'Provider Inclusion By State','State (State0)' from NameList1_8, the Skilling (matching item from both SP tables) selections appear in NameList1_9. The list box in question does not populate a filtered list of 'Name (Title)' items though. It just gives me everything. Thank you for all the effort!

Was this reply helpful? Yes No
Ash13 576 on at

Like (1)

Report

Would you be able to share what you have in the Items property for each of the filters/dropdowns/listboxes?

Was this reply helpful? Yes No
kgiles123 224 on at

Like (0)

Report
Sure and thanks again:
Age Restriction: TextInput1 = “”
State: NameList1_8 = Distinct(Sort('Provider Inclusion By State','State (State0)'),'State (State0)')
Skilling: NameList_9 = Distinct(Sort(Filter('Provider Inclusion By State', 'State (State0)' = NameList1_8.Selected.Value),Skilling),Skilling)
Name: NameList1_11 = Distinct(Filter(Provider_Resource_Guide, 'AR Start' <= Value(TextInput1.Text) && 'AR End' >= Value(TextInput1.Text) && Skilling in Filter('Provider Inclusion By State', 'State (State0)'= NameList1_8.Selected.Value).'Skilling'),'Name (Title)')

Was this reply helpful? Yes No
Verified answer

Ash13 576 on at

Like (1)

Report
Ok, does simply putting for NamesList1_11:
Distinct( Filter( Provider_Resource_Guide, 'AR Start' <= Value(TextInput1.Text) && 'AR End' >= Value(TextInput1.Text) && Skilling = NameList_9.Selected.Value ), 'Name (Title)' )
get you what you're after?

Was this reply helpful? Yes No
kgiles123 224 on at

Like (1)

Report

Wooooohoooo! This works! Thank you so much!

Was this reply helpful? Yes No
kgiles123 224 on at

Like (0)

Report

Sorry but one more item... I want to join 'Provider Inclusion By State','State (State0) so that ,'Name (Title) from Provider_Resource_Guide matches 'Provider Inclusion By State','Provider. Would you like me to start a new discussion? Thank you again for all your help Ash13!
Distinct(Filter(Provider_Resource_Guide, 'AR Start' <= Value(TextInput1.Text) && 'AR End' >= Value(TextInput1.Text) && Skilling = NameList1_9.Selected.Value && 'Provider Inclusion By State'.'State (State0)' = NameList1_8.Selected.Value && 'Name (Title)' = 'Provider Inclusion By State'.Provider), 'Name (Title)')

Was this reply helpful? Yes No