cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Importing From Large Datasource Into Collection For Offline Use

I am trying to import a large datasource (over 75k records) from a SharePoint List into a Collection. We need to do this for offline capabilities.  While that sounds like alot of data - it really isn't - it's equivalent to a small audio book.

 

  1. I know PowerApps only allows the import of a maximum of 2000 records through various delegation methods.]
  2. I do not have access to a cloud SQL databse.
  3. I cannot install the PowerApps gateway in our environemnt (government) to use with an on-prem SQL database.
  4. I found a great article on how to bring in chunks of records from a SQL databse here: https://blog.coeo.com/davidmorrison/2017/06/21/powerapps-500-record-limit-delegation-and-how-to-work...

Everything works fine with the 'code' detailed in the link from bullet 4 until I get to the point of actually bringing in the records from my SharePoint list.  The code I am about to post esentailly takes the records in 500 (can be eaily modified for the new 2000 record limit) record chucnks and adds these records to a collection. This will pass through the 500 record chunking a predetermined number of times based on the size of the source data.  The variables established in this code have been setup previously (see link):

 

ForAll(colNumbersTable, Collect(colMyBigTable1DataRaw, Filter('[datasource]', (datasourceID - 1) > ctxMinRecID + ((Number - 1) * 500) && datasourceID <= ctxMinRecID + (Number * 500)));

 

colNumbersTable: a collection with a sinlge column (named Number) containing numbers from 1 to x number of iterations (deteremined by the number of records in datasource / 500)

ctxMinRecID: the lowest record ID from the datasource

 

Probelms:

  1. Sharepoint/Powerapps integration does not allow for mathematical functions to be performed on the ID column, in this caase: (datasourceID - 1)
  2. Performing the ID >= value && ID <  value with OnSelect and a ForAll function produces the littel blue dot with the message: Part of this formula cannot be evlauted remotely due to service limiations. Highlighted operation is not supporeted by column 'ID'.
    1. I have gotten the Filter(datasource, ID >= variablemin && ID < variablemax) to work on a table. The blue dot still shows up but data is pulled into my table.
    2. I was able to have the same ForAll loop that builds the number of iterations table create a variablemin and varaiblemax column
  3. Probem Code: ForAll(colNumbersTable, Collect(colAssetDataRaw, Filter(Base_List, ID >= ImpRecMin && ID < ImpRecMax))

NEED:

  1. To have the app pull in multipe chunks of records from the SharePoint datasource to end up with a collection that mirrors the dataspource.
2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Importing From Large Datasource Into Collection For Offline Use

Hi @mikearmour,

 

Could you please share a bit more about the ImpRecMin and the ImpRecMax within your Problem code?

Why do you perform mathematical function (datasourceID - 1) within the ID column of SharePoint list?

 

For your first question, if the mathematical functions are not suppoered to be performed on the ID column of your SharePoint list, please take a try with the following formula:

 

datasourceID> ctxMinRecID + ((Number - 1) * 500) + 1

In addition, I think it is not necessary to perform mathematical function (datasourceID - 1) within the ID column of SharePoint list, please modify your formula as below:

ForAll(colNumbersTable, Collect(colMyBigTable1DataRaw, Filter('[datasource]', datasourceID >= ctxMinRecID + ((Number - 1) * 500) && datasourceID < ctxMinRecID + (Number * 500)));

For your second question, I think this issue is related to the delegable operators the SharePoint data source supports. The ">=" delegabe operator and the "<" delegable operator are not supported in SharePoint data source currently, the SharePoint data source only supports "=", "And (&&)", "Or (||)" and "StartWith" delegable predicates.

 

More details about the delegable predicates the SharePoint data source supports, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-list

 

 

For your third question, please take a reference to the formula that I provided above for your first question.

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Importing From Large Datasource Into Collection For Offline Use

Kris - 

 

Please take a look at the link I put in my original post as it explains alot of this code.  I am not the person who origianlly wrote the code, but borrowed what I could.

 

To that end - the answer to your second wustion is - this code was originall written for a SQL database as the datasource - not SharePoint.  I have since tested the original code against CDS as a satasource and could not perfomr the mathematical functions on the PrimaryID column either.  It may be something that can only be done when useing a SQL database as the datasource.

 

To answer your first wuestion - in a nutshell - what the code does is:

  1. Creates a numbers table that dicteates the number of data passes the code should do to pull in records in 2000 record chuncks.  For example - if I wanted to bring in 10,000 records - then I want to do 5 passes of 2000 records.  The numbers table will then create a 5 row table with:
    1. the first column being the pass number (1, 2, 3, 4, 5)
    2. the second column is ImpRecMin - which is the low end record for the chunk of records to be imported.
    3. the thrid column is ImpRecMax - which is the upper record limiit for the records to be imported.
    4. For Example for pass 1, ImpRecMin is 1, ImpRecMax is 2000; for pass 2, impRecMin is 2001, ImpRecMax is 4000, etc. etc
  2. The code then uses a ForAll to read the data source and add the chunk of records to the collection.

I have given up on using SharePoint as the datasource for this app becuase of all the service limitations.  I personally find it silly that you cannot specify a chunk of records to be pulled in bu using a ID >= min && ID < max.

 

I have been able to get the code to work using the CDS as my data source.  I sucessfully imported over 75K records into my app on my mobile device in around 1 min and 43 sec.

 

If anyone is interested in seeing the final code - please let me know and I will post.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (7,489)