Increment problems due to SP list delegation issues

[Anonymous]

Hello brilliant peoples, I have an app and am struggling with something that seems like it should be so basic. In the top right corner of the app is a number that belongs to the workflow that the app kicks off. When someone starts the app this number should increment by one according to the last ID number from a sharepoint column.

Every solution I implement brings on delegation warnings, however, and then the incrementing breaks when I hit the record ceiling.

Originally I would clear collect and bring in the entire ID column and then pull the max value from the collection, but that broke when I hit the 500 record ceiling.

Then I tried using the Last and Max functions, but those don't work properly in a SP list.

Currently I'm using this formula but it's created circular logic that doesn't work correctly when the app starts:
OnVisible: 
ClearCollect(
wamincrement,
Filter(
WAM_WorkflowData,
WAMIncrementedID >= Value(WAMIDInfo.Text)-2
)
)

The problem is that WAMIDInfo is the box where the ID increments, and its default is set to:
Max(
wamincrement,
WAMIncrementedID
)
+1

Is there a function I can use for the WAMIDInfo box that would look at my SP list and pull the Last or Max number value from that column and then increment it by one? Seems so simple, but it's causing me a year's-worth of headaches.

I'm also fairly limited in my PowerApps skillset.

[iAm_ManCat]

Hello!

Have you tried sorting the list reversed and then using the First function?

like this:

Set(LastItemID,
               First(
                     Sort(WAM_WorkflowData,
                          ID,
                          SortOrder.Descending)
                     )
)

Cheers,

ManCat 

[Anonymous]

Hi ManCat! @iam_man_cat
Thank you so much for your info. I can't tell yet if this is working because I'm not able to see the result in my text field.

Using the formula you sent, I'm not setting the Default for the text box that should display this information as LastItemID but getting a red squiggly line. 

When I hover over it it's giving me this message: "The property expects Text values, but this rule produces incompatible Record values."

I've tried using the Value function to see if needs to convert a number into text, but I can't seem to get that to work.

Any additional thoughts?

Daniel

[iAm_ManCat · Accepted solution]

Whoops, yes you're right, I didn't include the .ID and it will default to a number as ID is a number - apologies I was at work when I wrote that and didn't have time to do the normal proofing/testing - if you're filling a text value then it will be:

Set(LastItemID,
               Text(
                    First(
                          Sort(WAM_WorkflowData,
                               ID,
                               SortOrder.Descending)
                          ).ID
                    )
)

Regarding your question on Value( 😞

we use the Value( ) function to get a number value from a Text item

we use the   Text( ) function to convert from Number/Date/etc to Text

Cheers,

@iAm_ManCat 

[Anonymous]

Incredible! 

This worked @iAm_ManCat 

I can't thank you enough.

It's working now, so I can't thank you enough and don't want to bother you any more, but if you're in the mood and can spare a couple of sentences, could you tell me why this worked vs. doing a ClearCollect or using Max or something else. Is it because Sort combined with First is something that is delegable on a SP list?

Again, thank you so much. 

I'm trying to mark this as the answer but it keeps throwing an error. I'll keep trying though in the hopes of other people being able to tap into your brilliance.

Daniel

[iAm_ManCat]

Hey @Anonymous 

Glad to explain:

The TL;DR - even when Sorted backwards, it loads let's say 25 entries, then we use First() to pull the first of those 25 entries.

So think about when you you are actually navigating a sharepoint list - as you're scrolling down it effectively loads the next X values once you reach the bottom, and then keeps doing that as you go along, so it's effectively only loading a small subset initially and then paging in new data X records at a time.

Now when we use Sort, that's a basic delegable function - the data source knows how to sort backwards and forwards and will do that for us before delegation is even an issue, especially considering what I said above, it will load that data in sections, from back to front as we've applied a Sort - this means that it only has to load X number of values (whatever it's limit is, can't remember the sharepoint default number of items cache) before it caches the next set of records in, and this then makes the first X values effectively {(LastID), (LastID - 1), (LastID - 2), (LastID - 3)... }

So we're still in delegable territory, and now we use the First() function, which pulls just one record (the first record within the currently-applied sorting and filtering), then we are able to access any of the properties by using .property IE .ID

In terms of your question around ClearCollect - think of a library.

Side-Note: I'm not particularly fond of doing ClearCollect within PowerApps, as I don't think it's a very efficient solution, and once you move to mobile-use then you want to keep the number of records pulled to an absolute minimum for performance reasons when pulling data over mobile connection - usually there is a better, more delegable way.

..back to the library:

So you want to find the last book in the library? Ok, so the librarian asks you to Clear out the shelves and Collect every single book in the library and load it into your truck, then they ask you to go through and check the ISBN number and look that up against a dewey-decimal ID number of every book until you find the one with the Maximum value.

You then tell them exactly where to shove it because surely they should have some sort of a Dewey-decimal system where books are Sorted before someone goes looking for one.

The librarian then says "Oh my, yes we do have that system but you never asked for that, if you start at that end of the library you'll find the Last items and we've divided it into smaller sections within shelves where you'll find the last 25 items per shelf."

..and that, is why I don't use ClearCollect unless absolutely necessary - it's bulky, it's unnecessary data unless you absolutely need local access to items, it increases loading times on mobile devices causing user frustrations, it doesn't scale, and your code has to assume how big your database will get as it can only collect 2000 items per collection currently (so if you have a DB with 6 thousand items, and you plan for 20k, but that gets reached within a month, then you have to re-write your code to collect those additional items and plan ahead for further expansions and it's just not scalable that way as you'll always end up writing for more expansions)

Ok, enough rambling, hopefully I've managed to explain a bit around why we are able to do this 🙂

Cheers,

ManCat

[Anonymous]

Wonderful stuff.

Thank you for lending your genius and expertise to a newbie. I've used ClearCollect a lot in my apps so I'm going to spend some time with your last reply to make sure I understand it on a fundamental level and then I might start to rethink my app design.

Have a great one @iAm_ManCat and thanks again for your help.

Daniel

[iAm_ManCat]

Ha thanks, although I definitely wouldn't call my myself an expert or genius!

I'd say that I know some things and I'm fairly good at seeing how things inter-work - I'm happy to explain the things that I do know so that others can gain from it 🙂

If you have any queries about delegation (especially with SharePoint), just pop open a new discussion and @ tag me in it 🙂

[Anonymous]

@iAm_ManCat 

Hello again my friend!

Well I'm having another issue and hoping you might have a solution:

When I used your solution in my test environment it worked like a charm, but when I tried it in my live environment I'm having an odd issue and I think it's related to the way you said that a SP list will load the first few records and none more.

Looking at my test environment, I don't have more than 100 records and so my ID is being incremented properly, but in live, which has 790 records to date, it's stopping at 99. So it's incrementing me to #100. I tried changing the Descending to Ascending in your initial code but that didn't fix it.

So now I'm wondering...is there a way to force Sort to load all of the records on the SP side?

Sorry for @ing you again but I'm not sure how to proceed.

Daniel

[Anonymous]

@iAm_ManCat 

NEVER MIND!

I figured the problem out. In my test environment I had converted my ID column to numbers, but in my live environment I forgot to do that, and so it wasn't working properly. Once I turned that ID column into a number-type column it's working fine.

Sorry for the bother! 🙂

Daniel