Another method for making a collection from Sharepoint and SQL larger than 2,000 items. Bye-bye delegation!

[martinav]

Maybe I'm slow and just now finding this out, but I thought I would share it with you.  I have been working with some data sets in SQL and lists in Sharepoint that exceed 2,000 items.  There is one method I summarized a while back that provided a method to get up to 4,000 items.  Via flow, I have found a way to collect many more items.  I have sucessfully retrieved a SQL table of over 20,000 rows with this method.  This also works with sharepoint, but it is much slower and has some other hangups.  I'll try to outline them here.

The basic method is to create a JSON array from the data, and the pass that back to Powerapps via a Respond Action.  This is how it works:

Sample flow, "GetSQLData"

1. The entire flow is only three steps:

2. You go into the "Settings"  in the Get Rows action... and turn on Pagination, and set a max number in the Limit field.

3. The trick is the "JSON Schema".  You have to run the flow with steps one and two only first.  Limit the output to a single row (Set "Top Count" in advanced options to 1.  No quotes).  Copy (highlight, then Cntrl-V) the output, but ONLY what is in square brackets, including the brackets.

4. Open the Response action.  Select "Use sample payload to generate schema", and paste what you copied in 3.  Pick done, and the system will generate the schema.

5.  In powerapps, use your command in OnStart, or OnVisible:

ClearCollect(TableData,GetSQLData.Run())

6. After this, you should have all of the table data available in your collection.  No delegation issues and very fast response.  (Dont forget to remove the limit in "Top Count" if you did that!)

Sharepoint modification:

Sharepoint is just a little different animal...  You have to remap the tables, since there is something strange in the JSON output of the sharepoint that powerapps just does not like.  So, add the Select action.  You will have to map each column.  KEEP IN MIND, the pitfall note below.  If you change anything in the select columns, you will have to make a new scheme.  Your load file will come from the Select output when you do a test run, instead of the sharepoint output.  You will also have to remove the data source, and re-add the flow in powerapps to reset it.

Accellerating retrieval of thousands of items!

You can increase the speed of retrieval by a factor of how many "lanes" you create in parallel.  You basically divide up the indexes into as many ranges as you create "lanes".  In this example, I split the index into four ranges, and put them in parallel actions.  Yes, this nearly cuts retrieval time to 1/4 the speed of a single range!

Update data after initial load:

You may wish to update your data instead of loading the entire dataset again.  This method can take your existing dataset, look for updates from your sources, then update the dataset in your current collections.  While the initial load of my data can be nearly 60 seconds, a quick update check and update takes only 6-8 seconds.  This allows for your data to not go stale.  You can also use timers to refresh data at regular intervals.  

// Data Refresh Routine v1.0.0 3/26/2019

UpdateContext({LoadDataSpinner:true,Circle1_Color:Red,Circle2_Color:Red,Circle3_Color:Red});

Concurrent(
   Clear(UpdatedFromMDL),Clear(UpdatedAcct),Clear(UpdatedAcctProd),Clear(UpdatedAcctData),Clear(UpdatedMDL),
   UpdateContext({TimerReset:false, Circle1_Color:Green})
);

Concurrent(
   Refresh('Engineering Change Notice'), Refresh('Engineering Change Request'), Refresh(Drawing_Vault),
   If(CountRows(ClearCollect(UpdatedMDL,Filter(SortByColumns(Master_Drawing_List,"Modified",Descending),Modified>LastUpdate)))>0,UpdateContext({No_MDL_Updates:false}),UpdateContext({No_MDL_Updates:true})),

   If(CountRows(ClearCollect(UpdatedAcct,Filter(SortByColumns('[dbo].[tbINVTransactionDetail]',"PostedTimestamp",Descending),DateTimeValue(Text(PostedTimestamp))>DateAdd(LastUpdate,-DateDiff(Now(), DateAdd(Now(),TimeZoneOffset(),Minutes),Hours),Hours))))>0,UpdateContext({No_Acct_Updates:false}),UpdateContext({No_Acct_Updates:true})),

   If(CountRows(ClearCollect(UpdatedAcctProd,Filter(SortByColumns('[dbo].[VIEW_POWERAPPS_BASIC_ACCTIVATE_DATA]',"UpdatedDate",Descending),DateTimeValue(Text(UpdatedDate))>DateAdd(LastUpdate,-DateDiff(Now(), DateAdd(Now(),TimeZoneOffset(),Minutes),Hours),Hours))))>0,UpdateContext({No_AcctProd_Updates:false}),UpdateContext({No_AcctProd_Updates:true}))
);

Collect(UpdatedAcct,UpdatedAcctProd);

UpdateContext({Circle2_Color:Green});

Concurrent(
   ClearCollect(UpdatePartNumbers,UpdatedMDL.MD_PartNumber,RenameColumns(UpdatedAcct,"ProductID","MD_PartNumber").MD_PartNumber,RenameColumns(UpdatedAcctProd,"ProductID","MD_PartNumber").MD_PartNumber),

   ForAll(UpdatedAcct.ProductID,Collect(UpdatedAcctData,LookUp(RenameColumns('[dbo].[VIEW_POWERAPPS_BASIC_ACCTIVATE_DATA]',"ProductID","Acct_PartNumber"),Acct_PartNumber=ProductID)))
);

UpdateContext({Circle3_Color:Green});

ForAll(Distinct(UpdatePartNumbers,MD_PartNumber).Result,Collect(UpdatedFromMDL,LookUp(Master_Drawing_List,MD_PartNumber=Result)));

ForAll(RenameColumns(UpdatedFromMDL,"MD_PartNumber","MD_PartNumber2"),

   Patch(MDL_All,LookUp(MDL_All,MD_PartNumber=MD_PartNumber2),
      {
          MD_Project: MD_Project, 
          MD_LatestRevNumber: MD_LatestRevNumber, 
          MD_EngrgRelDate: MD_EngrgRelDate, 
          Title: Title, 
          MD_PDM_Link: MD_PDM_Link, 
          MD_LinkToDrawing: MD_LinkToDrawing, 
          MD_IsActive: MD_IsActive, 
          MD_Data_Flagged: MD_Data_Flagged,  
          MD_Latest_SW_DocumentID: MD_Latest_SW_DocumentID, 
          LastCost:LookUp(UpdatedAcctData,Acct_PartNumber=MD_PartNumber2,LastCost), 
          OnHand: LookUp(UpdatedAcctData,Acct_PartNumber=MD_PartNumber2,OnHand), 
          Location_Acct: LookUp(UpdatedAcctData,Acct_PartNumber=MD_PartNumber2,Location), 
          Discontinued_Acct: LookUp(UpdatedAcctData,Acct_PartNumber=MD_PartNumber2,Discontinued), 
          Available: LookUp(UpdatedAcctData,Acct_PartNumber=MD_PartNumber2,Available)
       }
    )
);
    
Set(LastUpdate,Now()-.002);

UpdateContext({TotalDwgCount:CountRows(MDL_All),LoadDataSpinner:false});

ClearCollect(StockQuote,GetKIQStockQuote.Run());

UpdateContext({LastStockUpdate:Now()})

**PITFALLS!!** (PLEASE read to reduce frustrations...)

1. Changing anything with the SQL table, or the flow REQUIRES you to disconnect the flow from powerapps (View->DataSources->select flow from list), and reconnect it (Action->Flows->select flow from list).  Sometimes, even when you do that its not enough!  I had more than one occasion I had to do a "Save As" on the flow to a new name, and add it back fresh into powerapps.  If all you get is a "Value" data vale in your collection, then something has gone awry.  I spent hours trying to find what the problem was with this.
2. The JSON Schema... the "Use Sample payload to generate schema" is NOT perfect!!!  If you get a red "Register error" when you try to add your flow to powerapps, check your schema.  There can be two problems that I have found.
   1. The "type" for a column is empty...  If you see "{}" after your column name, this is a problem.  You need to add it in manually.  See two examples...
      
      Copy what is between the curly brackets from another column definition, and paste it in.  Be sure the type is correct.  Allowable types are "integer", "string", "object","boolean".  Date columns are converted to a string.  So you have to use DateTimeValue() to convert the text to an object if you need it in that format.
   2. Sometimes, the schema generator interprets the type wrong.  Again, if you get the "Register Error" when you add it, then its likely that a "integer" was interpreted as a "string".  Again, you will need to update the schema manually if needed.

Flow Execution time for 22,000 rows was a total of 29 seconds.  Ten columns of data.  I did a test run with a basic table object, and a counter.  Powerapps shows the table in just about that amount of time.  Not bad if you are in a situation where you can pre-load all of your data.  It eliminates delegation issues.

[timl]

Hi @martinav 

Thanks for the excellent write-up! Just to confirm, what were the other hang-ups with SharePoint, besides performance?

Something interesting to try would be to create 2 Flows, each one selecting 50% of data you want to retrieve. You could then call the 2 Flows in parallel with the Concurrent function, and see if results in any performance improvement. 

[martinav]

Yes, that would likely speed it up a bit.  That is a similar method of a previous method:

https://powerusers.microsoft.com/t5/General-Discussion/Collections-maxes-out-at-2000-when-populating...

Something is happening with sharepoint.  I am trying to figure it out now.  I must be columns of a certain type, but everything looks great in flow, but on the powerapps side, it doesnt seem like the array is coming through correctly.  I have one now that is only happing a single value instead of a table.  I will update this post when I learn more.

[martinav · Accepted solution]

I updated the original post with a sharepoint modification...

[martinav]

Added to "PITFALLS"...  

[martinav]

Added accelleration method.

[martinav]

Added a method to update data, as opposed to doing a full data retrieve.  Data refresh routine only takes 5-8 seconds.

[Anonymous]

Thanks for this awesome thread @martinav and for all your hard work in the community!

@Anonymous 

[martinav]

@Anonymous ,

THis forum was instrumental in my being able to develop an entire change management system for our company.  It would NOT have been possible without this forum, and the wonderful contributors.  The least I could do is post some of my accomplishments for others to enjoy.  

Regards!

[Anonymous]

I have 2700 records in SharePoint List and this give me TimeOut Error, any suggestions ?