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)

Sort by

Ash13 576 on at

Like
a
(1)

Report
Copy link

Link copied!
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
a
(0)

Report
Copy link

Link copied!

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
a
(1)

Report
Copy link

Link copied!

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
a
(0)

Report
Copy link

Link copied!
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
a
(1)

Report
Copy link

Link copied!
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
a
(1)

Report
Copy link

Link copied!

Wooooohoooo! This works! Thank you so much!

Was this reply helpful? Yes No
kgiles123 224 on at

Like
a
(0)

Report
Copy link

Link copied!

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