cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EJenn
Frequent Visitor

Renumbering Unique IDs when item is deleted in a Gallery

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!! 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

6 REPLIES 6
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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 :). 

EJenn_0-1663275667189.png

 

RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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. 

EJenn_0-1663335850444.png

 

RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

@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! 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (6,126)