cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

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

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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
jup
Level: Powered On

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

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

jup
Level: Powered On

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

 
Highlighted
Super User
Super User

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

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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
ashaikh
Level: Powered On

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

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 99 members 5,064 guests
Please welcome our newest community members: