Answered

Joining two tables

(0) Share

Report

Posted on by kgiles123

224

Here's my failed formula:
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 && Provider = 'Name (Title)'), 'Name (Title)')

I'm trying to join these two tables based on 'Provider Inclusion By State', 'Provider' and "Provider_Resource_Guide, Name (Title)'
Basically if AR Start and AR End and Skilling match the values in Provider_Resource_Guide, I want to return Name (Title) if it matches a Provider that is also eligible w/in that state in the Provider Inclusion By State list. Hope I'm making sense. Thank you for any help!

Categories:

General Questions

I have the same question (0)

All responses (5)

Answers (1)

Rusk 1,369 Super User 2024 Season 1 on at

Like (0)

Report

Re: Joining two tables

Hi @kgiles123

I'm sorry but I didn't quite understand your question. You mentioned that you wanted to join two tables, but your formula looks like its to filter a Gallery.

Can you provide a screenshot or example of your data and what it is you're trying to return to the user?

Was this reply helpful? Yes No
kgiles123 224 on at

Like (0)

Report

Re: Joining two tables

Thanks for looking at this Rusk! I'm still fairly new to PowerApps so I'm probably explaining it all wrong.
1. I have two SharePoint Tables and a series of cascading lists in a PowerApp. I'm hoping to filter down to the provider name in the last PowerApp List.
2. Here's the Provider Inclusion By State SP List. Sorry I can't show our Provider names:
3. Here's our Provider_Resource_Guide SP List. Provider names covered again:

4. Lastly here is our PowerApp:

If the qualifiers of Age Range (AR Start & AR End) are met as well as skilling, we would like to match the Provider names returned to what was selected for State and return the qualifying Provider names from the Provider Inclusion By State list. Hope this makes more sense. Thank you!

Was this reply helpful? Yes No
Verified answer

Rusk 1,369 Super User 2024 Season 1 on at

Like (1)

Report
Re: Joining two tables

Hi @kgiles123

That info helped a lot! There's a several ways to accomplish this. Here's one idea:

1) Create a collection to combine your tables.

You can include this type of formula in your app OnStart property or the screen OnVisible property. In this example, I've used a button. Be sure to replace my collection names / columns with your actual ones:

ClearCollect(colNewTable, AddColumns(colProviderState, "Care", LookUp(colProviderResource, Name = colProviderState[@Name]).Care, "AR Start", LookUp(colProviderResource, Name = colProviderState[@Name]).'AR Start', "AR End", LookUp(colProviderResource, Name = colProviderState[@Name]).'AR End' ) )

The result is one combined table, which I believe is what you were looking for. In this example, the first 2 tables are the different sources, and the 3rd is the combined:

2) Use your formula to filter the desired results

Concat(Filter(colNewTable, Dropdown1.Selected.Age >= 'AR Start' && Dropdown1.Selected.Age <= 'AR End' && State = Dropdown2.Selected.State && Care = Dropdown3.Selected.Care), Name & Char(13))

You'll need to modify the ".Age" portion of the formula to work with your column names. That's just how my example was set up. Just type "." after the "Dropdown1" to see what valid options exist for your data

Was this reply helpful? Yes No
kgiles123 224 on at

Like (0)

Report

Re: Joining two tables

Hi Rusk! Thank you for your response! I'm a little new to this so please forgive my ignorance. Can I trigger this with an OnSelect fx in my Skilling list? Am I adding a Data Table to my page?

Was this reply helpful? Yes No
Rusk 1,369 Super User 2024 Season 1 on at

Like (0)

Report
Re: Joining two tables

Hi @kgiles123

No you don't need to add a table to your page, my screenshot with the tables was just to display what is happening when you use these formulas. The tables do not need to be visible. Yes, you could in theory trigger this with an OnSelect, but then it will run every time it's clicked. You could instead do this joining just once by placing the formula in the OnStart property of your app:

ClearCollect(colNewTable, AddColumns(colProviderState, "Care", LookUp(colProviderResource, Name = colProviderState[@Name]).Care, "AR Start", LookUp(colProviderResource, Name = colProviderState[@Name]).'AR Start', "AR End", LookUp(colProviderResource, Name = colProviderState[@Name]).'AR End' ) )

For your "Name" text box, that's where you'd use this filter formula:

Concat(Filter(colNewTable, Dropdown1.Selected.Age >= 'AR Start' && Dropdown1.Selected.Age <= 'AR End' && State = Dropdown2.Selected.State && Care = Dropdown3.Selected.Care), Name & Char(13))

Was this reply helpful? Yes No