I want to create a gallery with a lookup (join) on 2 tables.
My 1st szenario works fine:
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):
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?
Solved! Go to Solution.
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
Check out the News & Announcements to learn more.
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Check out our new Discover Your Career Path blog post series and get all the details.