cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
grahamjtr
Level: Powered On

Renumbering records upon deletion or insertion

Hi,

I'm making an app which will be connected to a database to store details of fabrication process steps.

I am simulating the app with a collection as the data source for now, as suggested in:

https://powerusers.microsoft.com/t5/General-Discussion/Patch-a-collection-of-items-to-SharePoint-Lis...

 

My problem is very similar but I can't make it work all the time:

I have a collection (TestTable) with 2 columns: fabrication step number, and the type of fabrication process:

 

ClearCollect(TestTable,{Step: 1, Type: "Lithography"}, {Step: 2, Type: "RIE"},{Step: 3, Type: "PECVD"}, {Step: 4, Type: "Sputtering"}, {Step: 5, Type: "Lift off"});

 

These records are displayed in a gallery (Gallery1)

 

After selecting one of the steps from this gallery, I have 3 buttons:

  1. Add a new fabrication step at the end.
  2. Delete the selected step, then renumber all subsequent steps so all fabrication step numbers are consecutive: e.g. starting with 1,2,3,4,5, delete step 2, then decrement step numbers 3,4,5 to 2,3,4, so I end up with 1,2,3,4.
    OR
  3. Insert a new fabrication step before the selected one. This also involves renumbering: starting with 1,2,3,4,5, I select step 2, then increment 2,3,4,5 to 3,4,5,6, then add a new record with step number 2.

I am using a second collection (EditTable) to temporarily store the records in TestTable I need to update.

 

The 'Add New' button is working:

 

Patch(TestTable,Defaults(TestTable),{Step: CountRows(TestTable)+1, Type: "New process"})

 

The 'Delete selected step' button also seems to be working:

 

ClearCollect(EditTable,Filter(Gallery1.AllItems, Value(Step) > Value(Gallery1.Selected.Step)));
Remove(TestTable,LookUp(TestTable,Step=Gallery1.Selected.Step));
ForAll(RenameColumns(EditTable,"Step","modstep"),
Patch(TestTable,First(Filter(TestTable,Step=modstep)),{Step: modstep-1}))

 

 

However, the 'Insert before selected' button only works if I select the last record:

 

ClearCollect(EditTable,Filter(TestTable, Value(Step) >= Value(Gallery1.Selected.Step)));
ForAll(RenameColumns(EditTable,"Step","modStep"),
Patch(TestTable,LookUp(TestTable,Step=modStep),{Step: modStep+1}));
Patch(TestTable,Defaults(TestTable),{Step: Gallery1.Selected.Step, Type: "New process"})

 

If I select anything other than the last record, a new record is inserted in the correct place, but the selected record jumps to the end of the list, instead of all subsequent records being 'shifted down':

Starting with 1-A, 2-B, 3-C, 4-D, 5-E, selecting step number 5, inserting a new step before results in: 1-A, 2-B, 3-C, 4-D, 5-new, 6-E, as expected.

But, selecting step number 2, inserting a new step before this results in: 1-A, 2-new, 3-C, 4-D, 5-E, 6-B.

I would like this to result in: 1-A, 2-new, 3-B, 4-C, 5-D, 5-E.

 

Hopefully it's caused by something trivial, but I've stared at this too long and my head hurts Smiley Surprised

Any help debugging this would be very welcome!

Thanks,

Graham

1 ACCEPTED SOLUTION

Accepted Solutions
KLatch
Level: Powered On

Re: Renumbering records upon deletion or insertion

I got it working in a slightly different way:

Add Row:

Collect(TestTable,{Step: Max(TestTable,Step)+1, Type: TextInput1.Text})


Delete Row:

Patch(TestTable,Filter(TestTable,Step>Gallery3.Selected.Step),RenameColumns(DropColumns(AddColumns(Filter(TestTable,Step>Gallery3.Selected.Step),"tempStep",Step-1),"Step"),"tempStep","Step"));
Remove(TestTable,Gallery3.Selected)

This patches the entire TestTable collection at once for items where the Step is greater than the step that you have selected for deletion and reduces the step number. Once that update is done, it removes the selected row. I prefer to patch like this because in my experience performing one large patch is faster than iterating through the rows with ForAll and doing lots of indivdual patches.

 

Insert Row:

Patch(TestTable,Filter(TestTable,Step>Gallery3.Selected.Step),RenameColumns(DropColumns(AddColumns(Filter(TestTable,Step>Gallery3.Selected.Step),"tempStep",Step+1),"Step"),"tempStep","Step"));
Patch(TestTable,Defaults(TestTable),{Step:Gallery3.Selected.Step,Type:TextInput1.Text});
Patch(TestTable,Gallery3.Selected,{Step:Gallery3.Selected.Step+1})

Much the same as delete, this patches all items greater than the one selected at once, increasing the step by 1. It then adds the new step (referencing the selected step number to define what the step number will be) and then finally increases the step of the selected item.

 

Hope this helps.

 

 

 

3 REPLIES 3
KLatch
Level: Powered On

Re: Renumbering records upon deletion or insertion

I got it working in a slightly different way:

Add Row:

Collect(TestTable,{Step: Max(TestTable,Step)+1, Type: TextInput1.Text})


Delete Row:

Patch(TestTable,Filter(TestTable,Step>Gallery3.Selected.Step),RenameColumns(DropColumns(AddColumns(Filter(TestTable,Step>Gallery3.Selected.Step),"tempStep",Step-1),"Step"),"tempStep","Step"));
Remove(TestTable,Gallery3.Selected)

This patches the entire TestTable collection at once for items where the Step is greater than the step that you have selected for deletion and reduces the step number. Once that update is done, it removes the selected row. I prefer to patch like this because in my experience performing one large patch is faster than iterating through the rows with ForAll and doing lots of indivdual patches.

 

Insert Row:

Patch(TestTable,Filter(TestTable,Step>Gallery3.Selected.Step),RenameColumns(DropColumns(AddColumns(Filter(TestTable,Step>Gallery3.Selected.Step),"tempStep",Step+1),"Step"),"tempStep","Step"));
Patch(TestTable,Defaults(TestTable),{Step:Gallery3.Selected.Step,Type:TextInput1.Text});
Patch(TestTable,Gallery3.Selected,{Step:Gallery3.Selected.Step+1})

Much the same as delete, this patches all items greater than the one selected at once, increasing the step by 1. It then adds the new step (referencing the selected step number to define what the step number will be) and then finally increases the step of the selected item.

 

Hope this helps.

 

 

 

grahamjtr
Level: Powered On

Re: Renumbering records upon deletion or insertion

Great, that works! Thanks so much

 

I had to make a small change: for the Delete button, I had to put the Remove function first, then the patch. Otherwise it was deleting the wrong record.

Highlighted
grahamjtr
Level: Powered On

Re: Renumbering records upon deletion or insertion

Hi,

I'm back working on this after a few weeks on another project.

I think your solution works but it is buggy.

 

When it patches the entire TestTable collection at once for items where the Step is greater than the step that you have selected, I think it gets confused and patches items that it has just renumbered. It needs to be done iteratively to prevent this from happening.

It's like change steps 3,4,5 to 4,5,6, but once it has changed the original 3 to a 4, then instead of changing the original 4 to 5 it tries to change the new 4 instead of the original. If that makes sense?

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 214 members 4,828 guests
Please welcome our newest community members: