cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jup
Helper II
Helper II

Problem with SQL Query - Lookup funktion in Gallery (AddColumns)

Hi all!

 

my problem:

 

I want to create a gallery with a lookup (join) on 2 tables.

 

My 1st szenario works fine:

 

2.PNG1.PNG

 

As you can see: In the gallery the values from both tables are shown correctly ([TestUser].[name] -and- [TestCostCenter].[Caption].

I used "ThisItem.TestUserRecord.Caption" for the label.

----------------------------

BUT... my 2nd szenario doesn't show the value from the lookup-Table (it's blank and I dont know why):

3.PNG

Here I'll get the items from the table [StockItem] but the values from the table [StockType] is BLANK (???!!!).

I used "ThisItem.StockItemRecord.Caption" for the label.

-----------

The table structure in both szenarios are the same. (Primary Keys...)

The only difference is that in szenario 1 (working szenario) there are only a few records in the both tables.-In szenario 2 (not working - blank items) there are >1000000 items in the tables.

 

Is this a delegation-problem?

please help.

 

thanks

jup

14 REPLIES 14

Ah finally!  Sorry I didn't hit it on the head earlier.  It was clear in my mind, but had a million things going on around me today. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

okay.. if feel like an expert now 😉

 

SortByColumns(

    Search(

            AddColumns('[dbo].[StockItem]';
                    "StockItemCaption"; 
                    LookUp('[dbo].[StockType]'; '[dbo].[StockType]'[@IdStockType]='[dbo].[StockItem]'[@IdStockItem]).Caption)

    ; TextSearchBox1.Text; "StockItemCaption")

; "StockItemCaption"; If(SortDescending1; Descending; Ascending))

Works great... BUT:

My tables "StockItem" and "StockType" have much more than 2000 Items ("delegation").

In my formula I use a "AddColumns"-Function and I read that "AddColumns" is not supported by delegation feature.

And I also read that it should be handeled by using a "Collect" call on one table to cache the items (on app start) and then do a "Lookup" function.

 

Hä?!?!? No really!

Should I really store all Data from the table in a collect before?! Is there no other (cool & easy) way to handle sql join queries with more than 2000 records per table ?!?!

 

For now:

If I search or filter the data in my currect application I can only search/filter data in the current record set (2000 items) 😕 

 

Thanks in advance 🙂

Jup

 

Delegation delegation delegation...yes, always a factor.

You have to consider your current and future situation for the data your are collecting.  If you know it will never be an issue, then don't worry over it.  If you know it will be, then plan ahead.

In this case, you know it will be, and yes, AddColumns will be a thorn in the side for that.

 

So, you are correct that one of the workarounds is to collect the entire table on start or at some point in the process.

This introduces a new set of complexities that you have to accound for. 

One is that you are now dealing with your own copy of the data table.  The problems I run into is then dealing with change backs to the data source.  If you patch/update a true source data table, then, do you refresh your collection again and take the hit on that, or do you put the extra "code" in to also update the local collection.  

This opens up a whole new can of worms in many cases. 

And this falls in with the topic of "pre-shaping" your data.  You can do that in PowerApps, with the potential hits of performance and complexities of "code", or you can shape at the source.  In this case, perhaps consider a view on the database that would return the exact results that you want.

 

Although PowerApps is considered a no-code solution, it really still has many of the factors of software engineering to be considered.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hello @RandyHayes,

 

I have the same problem as described by the user here and teying to implement the solution you suggested still having same issue. This is the formula I am using -

 

AddColumns('[dbo].[note_attachment_time_entry]',
"tasknumber",
LookUp('[dbo].[wh_task]', '[dbo].[wh_task]'[@task_id] = '[dbo].[note_attachment_time_entry]'[@task_id]).task_number)

Here task_number is a column from wh_task

 

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,192)