Renumbering Unique IDs when item is deleted in a Gallery

[EJenn]

Hello! I'm hoping someone can help me with this!

About the App: I'm currently creating a Test Case Creator App, where users will be developing test cases. They are wanting each User Story to have Test cases associated with them that have appended numbering and then store that number on a SP List. 

When I'm creating a new record, I'm counting all the items in a Gallery and adding 1, then patching the new record to my SP List (eg:"US01-1", "US01-2", "US01-3", "US01-4", "US01-5", etc.) from a text field. 

Requirement/Want: I have a requirement where a user is wanting to delete test case(s) and renumber ALL affected test case number. I have a trash can for each Gallery item and when selected, I want to remove the record from my SPList then renumber all other items in the gallery and patch those new numbers back to my list. For instance, if I remove "US01-3", "US01-4" will then become "US01-3" and "US01-5" will become "US01-4".

In my main Gallery, I currently have the below code applied on my Items property, which, will renumber the items. However, they are wanting the value stored on the SP list and not just in the App. 

With(
{
ThisGallery: Filter([MYSPLIST],[REFRENCEID] = varRecord.[REFERENCEID])
},
ForAll(
Sequence(CountRows(ThisGallery)),
Patch(
Last(FirstN(ThisGallery,Value)),
{rowNum:varRecord.[REFERENCEID] & "-"& Value}
)))
 

Thank you!! 

[RandyHayes]

@EJenn 

Yes, so your formula is currently showing the rowNum based on the ReferenceID in your list and a sequence number.

If you remove a record in MYSPLIST, the formula you have will re-evaluate and all the records will update with new sequences.

So, if you delete the record for "US01-3", "US01-4" will then become "US01-3" and "US01-5" will become "US01-4".

Have you had problems with that?

As for storing the value...that you would need to store in the record.  And if you do that, then the above point gets a little more complicated (well...more slowdown) as you need to update then all the sequentially larger records to decrement their values.

I hope this is helpful for you.

[EJenn]

Hi @RandyHayes, thank you for responding. No, I haven't had a problem with the Sequencing in Powerapps, resequencing. I'm trying to figure out how to "bulk update" my other gallery items to their new appended Ids. So, in the screenshot below I 'deleted' the old "US02-3" and my gallery updated correctly (with the code I wrote above). I'm wanting to patch the "New ID" column for "Old ID" "US02-4" since I deleted "US02-3", it is now the 3rd Test Case and is the New ID US02-3. I think I have to use a combo of Collections and ForAll, but I'm not sure where to start. Let me know if I need to provide further detail :). 

[RandyHayes]

@EJenn 

No collections needed!  Avoid!

Your gallery has the data you need.  If you want to bulk update all the items based on the value you have displayed for the rowNum, then you just need to use that value.

To do the Bulk Update, you would just do the following formula as a behavioral formula on an OnSelect of something...like a button:

Patch(MYSPLIST,
    ForAll(yourGallery.AllItems,
        {ID: ID,
         rowNum: rowNum
        }
    )
)

This assumes that rowNum is not only the name of the column you created in your gallery, but also in your records.  If not, then adjust accordingly.

[EJenn]

Hi @RandyHayes, 

Probably user error on my part, but I'm running into an issue with it updating the Test Case Numbers on my SP list.

Here is my code on OnSelect for the trash can in  each gallery item 

UpdateContext({varTestRecord:galTestCases.Selected});
Remove(UAT_Test_Cases,varTestRecord);
Patch(UAT_Test_Cases,
  ForAll(galTestCases.AllItems,
  {'UAT Test Cases': 'UAT Test Cases', //this is the name of my required field//
   TestCaseNumber: rowNum //TestCaseNumber is my "USXX-X" column for the test case name//
  }
))

 On select of button, I want to remove the Test Case, then patch the updated rowNum for each of the remaining items in the gallery. 

[RandyHayes · Accepted solution]

@EJenn 

Okay...you mentioned "required field".  Is that to say that it is the Primary Key, or just a required field?  You indicated this was a SharePoint list...if so, then ID is the primary key!

Your formula should be:

Remove(UAT_Test_Cases, {ID: ThisItem.ID});
Patch(UAT_Test_Cases,
  ForAll(galTestCases.AllItems,
  {ID: ID,
   TestCaseNumber: rowNum //TestCaseNumber is my "USXX-X" column for the test case name//
  }
))

HOWEVER...this is going to cause some issue in that once you remove the record, the gallery is NOT going to re-evaluate and update until the entire formula is complete.  So the following Patch will have incorrect values.

Usually this would be done outside of the gallery in some sort of final "submit" action.

You'd have to get the gallery to re-evaluate...you can attempt to put a Reset(galTestCases) before you do the Patch...but not sure without a test to know if that would actually cause the re-evaluate properly.

[EJenn]

@RandyHayes Thank you! I was able to get it to work! The Reset(galTestCases) didn't end up working, but I ended up putting a timer and setting it to 'true' after the 'Remove' function then, 'OnTimerEnd' put my Patch statement in. Works like a charm!