I have been extensively testing a new app that we will use for scheduling work orders in production. I have been trying a couple of options to host the database, but sharepoint is my prefered option as it integrates well with other systems we use. The database will hold about 4,000 records, possibly more over time, but only about 20 - 50 will ever be returned to users at any one time.
The issue I am having is it doesn't seem to support filter delegation to the server at all. I have tried a number of different ways, including filtering columns against static text, as well as against variables and other controls (doprdowns, textinputs, etc.)
Some of the options I have tried are as follows:
Filter(ProductionSchedule, TextSearchBox1.Text in ItemSerial)
Filter(ProductionSchedule, PodSerial = TextSearchBox1.Text)
Both the options above only return 1 of 3 records.
If I search by ID (see below), it will only return items up to number 256.
Filter(ProductionSchedule, ID = Value( TextSearchBox1.Text))
Filter(ProductionSchedule, ID = 12)
Even unfiltered it will not return more than 256 records, although the documentation seems to say that it will return 500.
I have carefully reviewed the requirements here and I don't believe I am doing anything in the known limitations that is preventing it. Also the authoring environment isn’t flagging any part of the formula that is preventing delegation.
I have also tried doing the same filter operations against a CDM database and it works perfectly, so I don't think it is an issue with the formulas.
Is this a known limitation? Is there anything I can do to get around it?
This is indeed a known limitation. The Sharepoint connector (middle tier) does not yet provide the necessary back-end delegation capabilities, however this support is on our radar and will come in a future release.
In the mean time, would it be feasible for you to use CDM instead?
Yes we are using CDM short term, however it is difficult to maintain the database. Sometimes when we reschedule production there could be thousands of records change. We're working with the data using the excel connector, but it takes a long time abd I'm worried about the risk of overwriting data if users forget to refresh the data.
Is there any way to access CDM directly or is the only way through excel? MS Flow isn't an option as we need to perform a lookup based on the title field and cannot update from the ID.
Learn how to create your own user groups today!
Check out the new Power Platform Community Connections gallery!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.