Unanswered

Creating a join on 2 SQL Tables in the gallery

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I'm fairly new to PowerApps, getting to grips with it and liking it the more I learn. However I'm a bit stuck on a requirement I've been given and hope someone can help me out.

We have 2 tables in separate DB's in SQL. One table contains our customers profile (CustomerProfiles) and the other table contains our customers with all the roles of employees (RolesForCustomers) for each of these customers, such as Field Sales, BDM etc. The two tables share a field called PartyNumber that is the customers unique ID.

CustomerProfile (my main table)

- PartyNumber

- Customer Name

- Customer Address etc

RolesForCustomers (Secondary table)

- PartyNumber

- BDM

- Credit Controller etc

On the Browse Screen I would like to present the currently logged in user with a link to the CustomersProfile record, but I only want to display customers where the currently logged in user exists in one of the roles in RolesForCustomers.

So far I've come up with this for the BrowseGallery

SortByColumns(Filter(AddColumns('CustomersProfile]', "BDM", LookUp('RolesForCustomers', PartyNumber = PartyNumber, BDM)),User().FullName = "BDM"), "CustomerName", If(SortDescending1, Descending, Ascending))

However, in the options for the BrowseGallery, it's showing me the RolesForCustomers datasource instead of the CustomersProfile datasource. So clearly I've got something wrong here.

As I understand it, the AddColumns function allows me to add a "calculated" column to the named datasource, The Lookup is then looking at the RolesForCustomers table to match the PartyNumber ID from the two tables and bring back the BDM column. and then compare the value of the currently logged in user against the BDM column and return all rows where they match.

In time I'd like to increase this to be adding other columns as well (Credit Controller, Field Sales Rep etc), but for now, just getting it working with the one column would be a win for me.

I've followed this blog so far, but clearly I'm not translating it quite to my requirements.

http://www.crmviking.com/2017/02/using-related-entities-in-powerapps.html

Thanks in advance for any help you can provide.

Categories:

Building Power Apps

I have the same question (0)

All responses (20)

Answers (0)

Meneghino 6,949 on at

Like (2)

Report
Copy link

Link copied!
Hi @Anonymous
In theory you are doing everything correctly, I think, and assuming that your SortByColumns etc. etc. expression is placed in the Items property of the gallery.
However, you may be up against performance issues. If your tables are large (and maybe even if they are not), then the LookUp may not work in retrieving the values for the calculated column. I have resolved this in the past by caching one or both of the tables in memory as a collection. Then your expression certainly works.
Also, never use User().FullName in a row expression, since it will send out a request for every row! (I know this is strange, but that is the way PA works at the moment) So you may want to save this in a text box or context variable or collection.
You may also want to try a ForAll expression to retrieve Roles for customers.

In any case, you could try this:
1) Cache the current user in memory like this:
ClearCollect(CurrentUser, User())
2) Then cache the RolesForCustomers in memory like this:
ClearCollect(MyRolesForCustomers, BDM = First(CurrentUser).FullName)
3) Then fetch the customers with an in expression, if this works without caching the customer table:
Filter(CustomersProfile, PartyNumber in MyRolesForCustomers.PartyNumber)
Since the column names are the same, you may need to use the @ for disambiguation.
For steps 1 and 2 you initially can use the OnSelect property of a button, which means that you can also inspect the contents of the collection in PowerApps studio. Then you can set them as the OnVisible property of the screen where you need this.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thanks @Meneghino for your reply. Encouraging I'm on the right path.

The data I'm dealing with is over 100k for both tables, is this manageable in cache, the other threads I've read about this are dealing with much smaller numbers. This is why I was looking to filter the items before returning any results as this would reduce the numbers to hundreds rather than thousands, but I get the impression that all the data must be pulled into PowerApps and then filtered down, correct?

Regarding the button, is the suggestion that I create a Homescreen or something with the button on and then this contains the first 2 points you've made for cacheing the gallery? Also, the 1st point you made, is cacheing the username, does this mean I don't need to worry about storing it in a textbox as mentioned earlier in your reply?

The 3rd point you make, does this replace the Filter in my Gallery Items formula?

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (1)

Report
Copy link

Link copied!

Hi @Anonymous, please see below:
The data I'm dealing with is over 100k for both tables, is this manageable in cache, the other threads I've read about this are dealing with much smaller numbers.

Yes, I have cached up to 5,000 rows in a few seconds, but more becomes a bit heavy. It depends on your back end and your internet connection speed.

This is why I was looking to filter the items before returning any results as this would reduce the numbers to hundreds rather than thousands, but I get the impression that all the data must be pulled into PowerApps and then filtered down, correct?

You can cache a filtered table (assuming that you can delegate your filter), therefore you can rely on SQL Server to filter and then pull it into power apps.

Regarding the button, is the suggestion that I create a Homescreen or something with the button on and then this contains the first 2 points you've made for cacheing the gallery?

Absolutely, that is what I do regularly. You can include dropdowns here to filter the data to be cached, for example only that relevant to the current user.

Also, the 1st point you made, is cacheing the username, does this mean I don't need to worry about storing it in a textbox as mentioned earlier in your reply?

Yes, absolutely correct. If you have your expression for the button, you can make that the first expression and then use the collection in the subsequent expressions, all separated by the semicolon

The 3rd point you make, does this replace the Filter in my Gallery Items formula?

This would be your formula for Gallery1.Items However, I tested this morning and even though the documentation says that the in operator is delegated for SQL Server, I couldn't get it to work. It could be me, or it could be that it does not work. In that case you would need to cache both tables. Or you find another combination that works.

I hope this helps.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi again @Meneghino,

Afraid I'm a little stuck. I've tried to strip it right back and build again as I don't seem to be 100% clear on what I'm doing here.

My formula for my gallery looks like this.
SortByColumns(AddColumns('[dbo].[DIM_CustomerCRMCLONE]', "AccountBDM",LookUp('[dbo].[Dim_CustomerAccounts]',Partynumber=PartyNumber,BDM)),"CustomerName",Ascending)

I've removed Search and Filter for now because that wasn't working. and I wanted to build things up stage by stage again.

At this stage, I'd expect my gallery to have the CustomerCRMCLONE table as it's main table, and to have added the BDM column from the CustomerAccounts table. So there's no filtering going on.

However, I'm getting no results returned. No error in the gallery, however the following looks wrong.

I would expect that to say DIM_CustomerCRMCLONE as that is the main datasource and CustomerAccounts is just being linked to it through one column.

On the Details page, I have an error on the Item property, presumably because the detail screen is looking for CustomerCRMCLONE and the datasource has changed to CustomerAccounts.

Help much appreciated with this. I feel that there's something obvious I'm missing and if I sort this, the rest should start to make more sense.

Thanks in advance.

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous
Are you using forms? (i.e. View and Edit forms) I ask this because I see a DataCard mentioned in your screenshot.

If so, I can't help you much as I strongly discourage people to use forms unless they do zero customisation.

If you need any customisation, IMHO you are much better off creating the single controls and writing back to your data source with patches

If not, then I will take a closer look at your post.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Only using forms as that was what I assumed I should use, but can use individual controls.

I don't think that's the root cause of my problem though it it?

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report
Copy link

Link copied!

No, absolutely not, agreed. Let me get bak to your original post then.

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report
Copy link

Link copied!

I would ignore the Content piece, see here for a simple example. Let's concentrate on the Items expression, I will look at it as soon as I have some time.

Was this reply helpful? Yes No
Meneghino 6,949 on at

Like (0)

Report
Copy link

Link copied!
Actually, I think some of your issues are linked to forms/data cards, so I will ignore the second part of your question for the moment.

Can we define the task for the moment as getting the following formula for the Items property of a custom gallery to show the data that you would like?

SortByColumns(
AddColumns(
'[dbo].[DIM_CustomerCRMCLONE]',
"AccountBDM",
LookUp('[dbo].[Dim_CustomerAccounts]',Partynumber=PartyNumber, BDM)
),
"CustomerName",Ascending)

If so, I can see a few issues to explore, and they are the same I think I mentioned in my previous posts:
A LookUp to a connected SQL Server data source (i.e. not a cached collection) does work as the function of an AddColumns. I have just experimented looking up to a table with 1,000 rows and it took 20 seconds to show any data in the gallery. I thus suspect that looking up to 100k rows may be very slow, hence why you are not getting any results.
Partynumber=Partynumber is ambiguous and may need to be disambiguated
I would create copies of the tables with 100 rows each and would experiment in getting the result you want. Then double the rows each time and see how performance changes. Then all should be clear.

Please let me know how you get on.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@Meneghino, thanks again for your time on this.

I seem to be making some progress. However, when I try to disambiguate the PartyNumber column in the lookup I get a warning.

Suggestion: Part of this lookup formula cannot be evaluated remotely due to service limitations. The local evaluation may produce suboptimal or partial results. I f possible, please try to simplify the formula. For more information, please see the Lookup function documentation.

This message appears under the disambiguation which is as follows.
LookUp('[dbo].[MemberShipDashBoardSTATIC]','[dbo].[MemberShipDashBoardSTATIC]'[@Partynumber]=PartyNumber, BDM)

I'm getting some results back now but the BDM field doesn't contain any results when it should.

Was this reply helpful? Yes No