Query based on record ID - Delegation Workaround

[RyanBarton]

There is a need to query based on record ID if you're creating a relational data model.

However, if you have thousands of records in a given table, your results are going to be incomplete due to the delegation limit. 

Is anyone interested in being able to query any number of records based on record ID to get around the delegation limit of 2000? I have a workaround. Its ridiculous, but it works. 

It will be a long post. Let me know if you're interested and ill write up the workaround using a simple example.

Microsoft,

Please allow us to have the option to turn this limitation off.

[RyanBarton]

You are correct. I am confusing delegation with record limit.

However... i am recieving an empty collection using the statement: Filter(Substations, ID > 2600)

Even with a delegation limit of 2000, empty. The "=" operator works fine.

Sigh... shouldn't have done this in SharePoint.

"Wouldn't you be doing that by an ID value?  That *should* be doable."

Yep. I am sort of creating my own type of ID as a text type. Then using this with the StartsWith() function to get around the constraints.

[RyanBarton]

Copy that. I'll write it up this weekend.

[RandyHayes]

@RyanBarton 

Yeah you'll have issues with the text types and those operators - only equals there (or other functions like StartsWith).

And yeah...hold off on my statement about ID>xxxx would return results.  I was told it would (despite the docs), but I am not seeing that perhaps the docs are correct.  I included @timl in this thread for some input on that.

[RyanBarton]

Heres an overview. I'll get extra weird in the actual post.

Lets say i have a table with 3 columns

1. ID (auto int)

2. str_ID (text)

3. then whatever you want

So essentially, i fire off a record using a FormA that has the following properties

DefaultMode = FormMode.New.

I then have a second hidden form, FormB that has the following properties.

DefaultMode = FormMode.Edit

Item = FormA.LastSubmit

the str_ID datacard has a Default = Text(Value(ThisItem.ID), "[$-en-US]000000000000")

So lets say i submit a record using

ResetForm(FormB); // make sure that FormB is clear... probably not needed
SubmitForm(FormA); // Submit the record we want
SubmitForm(FormB); // Append the previous record with the new str_ID value
NewForm(FormA); // reset FormA

Collect(equip100,Filter('Substation Equipment', StartsWith(str_ID, "000000000")));      // returns 0-999
Collect(equip1000,Filter('Substation Equipment', StartsWith(str_ID, "000000001")));    // returns 1000-1999
Collect(equip2000,Filter('Substation Equipment', StartsWith(str_ID, "000000002")));    // returns 2000-2999

[RandyHayes]

@RyanBarton 

Ah yes...you must take a look at the other posting.  Beside the ID "stringification", the multi-collect aspect is in there.  I did something similar with a situation regarding names and pulling in all of those.  Again, that is in the posting.

What's nice with that method is that you can wrap a good amount of that work into a Concurrent function.

I definitely get your concept of stringification though.  You might want to cut the extra form B and just put that in your submit Action. You could drop that form and overhead and just change your formula to this:

SubmitForm(FormA); // Submit the record we want
Patch(dataSource, FormA.LastSubmit, {str_ID: Text(FormA.LastSubmit.ID, "[$-en-US]000000000000")});
NewForm(FormA); // reset FormA

Might save you some steps.

Cool stuff though!!  Take a look at that posting too.  Always enjoy the conversation 🙂

[RyanBarton]

ahhh man thats way better! Thanks Randy!

[RyanBarton]

@RandyHayes,

Again, thanks for showing me how to get around that hidden form thing... I had about 10 of those on one screen to complete an automated configuration task... Looks like you can use the Patch method in conjunction with a variable as well... eliminating all forms entirely.

Set(Term1, Patch('Substation Terms', Defaults('Substation Terms'),
{'Position Description': "Term 1 test",
str_SubstationID: Substationstr_ID,
str_SubstationPositionID: NewPositionstr_ID,
'Component Type': LookUp(Choices('Substation Terms'.'Component Type'), Value = "TERM")
}));

Patch('Substation Terms', Term1, {str_ID: Text(Term1.ID, "[$-en-US]000000000000")});

[timl]

Hi @RandyHayes 

I think you've covered the gist of the delegation issue. With SharePoint, the predicates <, <=, =, <>, >, >= are delegable for numeric columns, but NOT delegable for the ID column(!).

This is obviously disappointing because it prevents us using these predicates to batch load more than 2000 records.

I agree with your advice of using the Concurrent function, which will help performance.

[RandyHayes]

@timl Ah, that was the part I must have missed...NOT for the ID column.  Thanks for clarifying.

[RyanBarton]

@timl ,

Thank you for the clarification!

@RandyHayes,

Thank you again for showing me how to patch a record without using the hidden form.. I had about 10 hidden forms working together to perform an automated configuration.

Now im using the patch function and a variable in conjunction with the concept you provided to update all 10 records. No more hidden forms!

Set(Term1, Patch('Substation Terms', Defaults('Substation Terms'),
{'Position Description': "Term 1 test",
str_SubstationID: Substationstr_ID,
str_SubstationPositionID: NewPositionstr_ID,
'Component Type': LookUp(Choices('Substation Terms'.'Component Type'), Value = "TERM")
}));
Set(Term1_strID, Text(Term1.ID, "[$-en-US]000000000000"));
Patch('Substation Terms', Term1, {str_ID: Term1_strID});