Best way to update a sequential value in Sharepoint

(0) Share

Report

Posted on by DCHammer

635

I have a user group that wants to automatically renumber items upon request.

Here is the requirement and underlying data.

List has a PriorityRank column. This is a numerical value that orders items. The values are unique. This is controlled both in the SP list as well as in Control input validation in the app.

Upon demand, the users want to order the list by the PriorityRank column and then update the PriorityRank to increments of 10.

I have a button building the initial collection sorted by PriorityRank and also adding a RowNo value that I can use to calculate the new value.

Now the real question:

What is the best way to update the SP list?

I thought that if I reversed the order and worked through the collection that way, meaning updating the record with the largest value first, it would work. But it doesn't seem to.

Because there are a number of required fields, I included values for them since it's how you have to do it in a Flow so I assumed the same here.

Here is the Patch statement:

ForAll(
    colPriRankRevOrderModded,
    Patch(
        'DataSource',
        LookUp(
            'DataSource',
            ID = Value(ThisRecord.ID)
        ),
        {
            PriorityRank: ThisRecord.PriorityRank,
            Status: {Value: ThisRecord.Status.Value},
            'Due Date': ThisRecord.'Due Date',
            Description: ThisRecord.Description,
            Cynefin: {Value: ThisRecord.Cynefin.Value},
            Priority: {Value: ThisRecord.Priority.Value},
            Expectation: ThisRecord.Expectation
        }
    )
);

I know know that the collection has the full record for Status, Cynefin and Priority so my problem isn't there. The seven columns I'm patching are the only required fields in the SP list.

The one thing I'm aware that it could be is the Rich Text fields. Both Expectation and Description are Rich Text and I'm not aware if there is anything specific that needs to be done to get them to patch correctly.

The other thing could be that it doesn't do a formal commit between records and if that's the case, by the time it gets to the end of the loop, there are definitely going to be PriorityRank values in conflict. Which is really what I think it is.

So, any suggestions on how to force a 'commit' and the end of each loop pass?

Categories:

General Questions

All responses (7)

Answers (1)

Verified answer

DCHammer 635 on at

Like (0)

Report
Best way to update a sequential value in Sharepoint

Well Ronald I had two problems and I may have only had one if I solved them in the right order.

When I chose to use a completely out of range interim value for PriorityRank, I discovered by Patch had a pretty common problem.

Patch( 'DataSource', LookUp( 'DataSource', ID = Value(ThisRecord.ID)

That's against the rules. Specifically the ThisRecord portion.

The solution is to rename the ID column when creating the final collection as NewID. Then you change that section of that Path to:

Patch( 'SEAL - Salesforce - Priority Queue', LookUp( 'SEAL - Salesforce - Priority Queue', ID = Value(IDNew) ),

Given that the entire thing is working and repeatable, I'm not going to bother testing if I could have avoided the out of range logic.
ronaldwalcott 855 on at

Like (0)

Report

Best way to update a sequential value in Sharepoint

Understood.

I would use that approach since it's a one-off job and find the highest priority rank and renumber from the highest then run the process again and renumber with the increments of 10.

You would therefore generate two collections with the priority ranks for first one probably being 1000000, 1000001, 1000002 and the other collection 10, 20, 30
DCHammer 635 on at

Like (0)

Report
Best way to update a sequential value in Sharepoint

@ronaldwalcott The issue is that PriorityRank requires unique values. So if the Patch doesn't occur in the correct order, it fails, which is what is happening now.

Here, I'll just share everything so there is less confusion:

Block 1: This gets all of the records in the list that aren't Completed of Cancelled and adds a RowNo to sort by since later I'm going to modify the value that been used to do the sort. RowNo also becomes the index value later to calculate the new PriorityRank.

ClearCollect( colPriRankOrig, With( { wList: Sort( Filter( 'SEAL - Salesforce - Priority Queue', Status.Value <> "Completed" && Status.Value <> "Cancelled" ), 'Priority Rank', SortOrder.Ascending ) }, ForAll( Sequence(CountRows(wList)), Patch( Index( wList, Value ), {RowNo: Value} ) ) ) );

Then we sort that collection in reverse order. I did this because I assumed that ForAll would patch sequentially. And as long as the first record patched has the highest possible PriorityRank that will occur, even if it's the same as it is already, the patch should succeed because there is no conflict.

ClearCollect( colPriRankRevOrder, Sort( colPriRankOrig, RowNo, SortOrder.Descending ) );

Lastly, I modify the PriorityRank value and create a new ordered collection with just the three values I need for the Patch. NOTE: This is different from my OP because I realized I don't need to include required fields in the Patch, just what I'm going to change.

ClearCollect( colPriRankRevOrderModded, ForAll( colPriRankRevOrder, { PriorityRank: 10 * RowNo, RowNo: RowNo, ID: ID } ) );

Then the Patch, which just loops through the collection and Patches individual rows.

ForAll( colPriRankRevOrderModded, Patch( 'SEAL - Salesforce - Priority Queue', LookUp( 'SEAL - Salesforce - Priority Queue', ID = Value(ThisRecord.ID) ), { PriorityRank: ThisRecord.PriorityRank } ) );

I know the Patch itself works because I manually tested patching individual records from the 'top' down. ie. dataset has 21 records, current PriorityRank of item 21 is 200. Patch it to 210, then manually patch item 20 to 200 etc. Since I'm always raising the upper limit, my logic actually works and will work as soon as I can figure out how to control the Patch.

It seems like PowerApps runs through all 21 records and doesn't consider what it did at record 1 when it gets to record X later in the list and returns an error because one or more PriorityRank values end up in conflict with an existing value.

I'm considering modifying the colPriRankRevOrderModded and adding a temporary PriorityRank. I can loop through the records and set the temp values to start at 100000 which will never conflict with anything, and then make another pass through the collection to set them to increments of 10 starting at 10.

But, it seems like there should be a logic way to 'commit' inside the loop.

If I was doing this in PL/SQL, I'd stick an AUTONOMOUS COMMIT inside the loop so each record gets updated individually regardless of what might happen with the whole transaction.
ronaldwalcott 855 on at

Like (0)

Report

Best way to update a sequential value in Sharepoint

Sorry I am not understanding what you mean regarding try and modify records simultaneously and why you sorted descending. Maybe I am misunderstanding your requirements. What do your new PriorityRanks look like? My approach should let you set them in increments of 10. Where you may have previously had 1, 5, 7 you would now have 10, 20, 30.

If you use ForAll on the collection assuming ID is unique it would Patch the table with the new PriorityRank value. It doesn't matter what sequence ForAll executes in you are only updating the single related record.
DCHammer 635 on at

Like (0)

Report

Best way to update a sequential value in Sharepoint

@ronaldwalcott Yes, PriorityRank is the only column that needs to be modified. I only included the other fields in the Patch because I assumed that required fields needed to be supplied in a Patch like they do when updating an SP Item from Automate.

I want to sort by the original PriorityRank and then standardize the increments to 10.

Here is the usage:

Users create items and the app auto-sets the PriorityRank to Max(PriorityRank) +10 (There is a filter used within the Max so it only considers open items that I left out here.)

Then, during the work flow, items get rearranged and instead of manually adjusting every value, a user will change 90 to 65 so that it lands between 60 and 70. The problem is that this methodology eventually runs out of choices. The next thing that goes between 60 an 65 becomes 62 etc and then 61 and we're out of options.

So this logic will reset the PriorityRank values occasionally when necessary.

I am pretty much doing exactly what you are suggesting with the collections and that's how I end up with the colPriRankRevOrderModified collection.

This collection has all of the necessary records in SortOrder.Descending by PriorityRank with the modified PRs.

But the ForAll fails because it seems to try and modify records simultaneously.
ronaldwalcott 855 on at

Like (0)

Report

Best way to update a sequential value in Sharepoint

Sorry, can you confirm that the only column that you want to modify is PriorityRank.

What do you mean by increments of 10? Do you want to sort by priority then change the priorities to 10, 20, 30, 40, ... ?

Depending on your answer you could do something like create a sorted collection with the ID and priority rank (this column is only needed for testing)

SortByColumns(table, PriorityRank)

You would then need to add a new PriorityRank column which would need to be the incremented value

AddColumn(SortByColumns(table, PriorityRank), newPriorityRank, 0)

To do this you use something like

ClearCollect(

SortedIndexedPriority,

ForAll(

Sequence(CountRows(SortedPriority)),

With(

{

Index: Value,

Item: Last(

FirstN(SortedPriority, Value)

)

},

{

ItemField1: Item.Name,

NewPriorityRank: Index*10,

ID: Item.ID,

PriorityRank: Item.PriorityRank

}

)

)

);

You would then use this collection to update the original records
DCHammer 635 on at

Like (0)

Report

Best way to update a sequential value in Sharepoint

Should have read more. lol.

ForAll doesn't just do it all at once, it iterates through the collection completely randomly.

So, the answer appears to be NOT using ForAll since I can't control execution sequence and use another method.