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.
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 ?!?!
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 🙂
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.
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
Read the announcement for more information!
Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.
Features releasing from October 2019 through March 2020
Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications