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.
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
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:
For your third question, please take a reference to the formula that I provided above for your first question.
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:
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.
I know this is an old thread, but I would very much like to see the code, that worked on CDS.
I'm trying to pull in 50.000 records, and I get an error on the last step, where the records are actually supposed to be loaded from CDS to my collection.
Here's my code:
It's the ForAll() in step 4. that fails (see attached image for error message)
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.
DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.