Answered

Delegation-related LOOKUP issue

(0) Share

Report

Posted on by Community Power Pla...

My PowerApps Canvas app pulls some data from a few SharePoint lists, but there are a few data sources that are on a SQL Azure table. I use a SQL table because one of the tables is a constant and growing collection of transactions (around 250,000 so far), and the other table is a table that I have to update daily with new data from our system (which on any given day could be from 1000-10,000 items). In particular, this second table consists of "orders in the field that are not delivered yet". For this latter table, I don't necessarily need to keep it in SQL, so if the solution ultimately is to move it to SharePoint, I'm open.

Anyway...

When I first wrote this thing, I used the following line:

 ClearCollect(MailingListExploded, AddColumns(ScanDataCollection,
 "CustomerName", LookUp(Spiderfood_RITMData, Number = Mid(Result, 12, 11), Name1),
 "CustomerEmail", LookUp(Spiderfood_RITMData, Number = Mid(Result, 12, 11), Email),
 "ManagerName", LookUp(Spiderfood_RITMData, Number = Mid(Result, 12, 11), Name2),
 "ManagerEmail", LookUp(Spiderfood_RITMData, Number = Mid(Result, 12, 11), Email1),
 "ItemListHTML", "<li><strong>" & Left(Result,10) & " - " & Mid(Result, 12, 11) & " - " & LookUp(Spiderfood_RITMData, Number = Mid(Result, 12, 11), Name) & "</li></strong>")
 ); // ClearCollect(MailingListExploded

ScanDataCollection was a list of physical items (The column header was "Result") being acted upon. (physically, a list of items a technician was touching at that moment, or that was passing by an RFID checkpoint).

Spiderfood_RITMData was my table in SQL that included, basically "all currently-open orders, plus details about each order". (One of the interesting things about Spiderfood_RITMData is that even though there may be 10,000 or more rows, for any given value of Number, there was only a single record with that value in Number.)

As you are no doubt aware, the above produced delegation issues. More importantly, as the quantity of records in Spiderfood_RITMData grew, we more and more often came up with blank results (at 3000 records, ~1/3 of these actions resulted in blanks).

I am led to believe from a few sources that this is because LookUp is non-delegable. Okay, sure.

So, I am thinking that there are two possible ways to go, here...

I could use some code that works here instead of LookUp that produces the same MailingListExploded collection, or
I could run a delegable function that pulls all records from Spiderfood_RITMData that are applicable in this instance (that dataset would never be more than, say, 250 per field action), and put that into a collection that is then referenced using LookUp, instead of the LookUp referencing the database directly.

I'm open to suggestions and really wanting to get this fixed, as it's affecting several areas of production.

Thank you kindly, for your attention!

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

With data tables that large I would consider doing the lookup via a SQL stored procedure to be called by a Power Automate flow. Then return the filtered and trimmed results back to Power Apps. Skip the Collection entirely.

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

Like (0)

Report

I should probably clarify that as far as I know, writing SQL stored procedures is probably way beyond my skill level today. And I've only once managed to make a PowerAutomate "script-block-whatever-it's-called" do what I wanted, and I'm not even sure how I did that. I'm not sure what collection I would "skip," but in case it helps, the collection I build above, MailingListExploded, undergoes a few more transformations before ultimately feeding an email householding routine (the householding routine alone took me almost two weeks to write). So this is not the information's final form, This is just where (at the moment), approximately 30% of the data is dropped.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... on at

Like (0)

Report
This is how I solved it so far, using a great hint from Matthew Devaney:

Because my database was an Azure SQL database, "=" was delegable.

What was causing mischief was the mid-stream string calculations.

So I pushed that out to its own line, adding "RawRITM" as a field to a intermediary Collection, and then performing my LookUp on that intermediary Collection.

Like this:

// Begin by building the Collections needed to feed the email tool. ClearCollect(ScanDataCollectionPlus, AddColumns(ScanDataCollection, "RawRITM", Mid(Result, 12, 11))); ClearCollect(MailingListExploded, AddColumns(ScanDataCollectionPlus, "CustomerName", LookUp(Spiderfood_RITMData, Number = RawRITM, Name1), "CustomerEmail", LookUp(Spiderfood_RITMData, Number = RawRITM, Email), "ManagerName", LookUp(Spiderfood_RITMData, Number = RawRITM, Name2), "ManagerEmail", LookUp(Spiderfood_RITMData, Number = RawRITM, Email1), "ItemListHTML", "<li><strong>" & Left(Result,10) & " - " & RawRITM & " - " & LookUp(Spiderfood_RITMData, Number = RawRITM, Name) & "</li></strong>") ); // ClearCollect(MailingListExploded // Trim away the Result column ClearCollect(MailingListExplodedTrimmed, DropColumns(MailingListExploded, "Result", "RawRITM"));

That seemed to remove all the delegation errors!

I'll keep monitoring it in production, but this is a bit exciting.

Was this reply helpful? Yes No
v-xiaochen-msft on at

Like (0)

Report

Hi @Anonymous ,

Glad to see you solved the problem.

You could mark yourself as a solution.

Best Regards,

Wearsky

Was this reply helpful? Yes No