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

Browse all records in a table to update the value of a single column after deleting a single record

Hello PowerUsers !!

I created a feature that allows me to add multiple records in one table, but one record at a time, and at each new record, a 3rd column will contain an incremented number, the name of the column is RecordNo.

I've also added a button that delete a single selected record.

The problem is in the function code of this button, I am looking for a solution to browse all the records that follow the deleted record to update the RecordNo value.




Here is a scenario:

 

Step 0- initial screen

 

 

 

1.png

 

 

 

Step 1- The user entered Tail No.1: 111, Tail No 2: 222
Result in the table:
RecordNo: 1, Tail No.1: 111, Tail No 2: 222


2.png

 

 

Step 2- The user entered Tail No 1: 333, Tail No 2: 444
Result in the table:
RecordNo: 1, Tail No.1: 111, Tail No 2: 222
RecordNo: 2, Tail No 1: 333, Tail No 2: 444

3.png

 

 



Step 3- The user entered Tail No.1: 555, Tail No 2: 666
Result in the table:
RecordNo: 1, Tail No.1: 111, Tail No 2: 222
RecordNo: 2, Tail No 1: 333, Tail No 2: 444
RecordNo: 3, Tail No 1: 555, Tail No 2: 666

 

 

 

 

4.png

 

 

 


Step 4- The user selects the 2nd recording then clicks on the button "Remove selected row".

5.png

 

 

 

 



Step 5 - 


After deleting, the current result in the table:
RecordNo: 1, Tail No.1: 111, Tail No 2: 222
RecordNo: 3, Tail No 1: 555, Tail No 2: 666

 

While the desired result is:

RecordNo: 1, Tail No.1: 111, Tail No 2: 222
RecordNo: 2, Tail No 1: 555, Tail No 2: 666

 

 

6.png

 

 

 

 





Here is the source code : 

The control name is DataTable1.
This table contains the data of the collection TableX.

Button Next, who is in a screen that precedes this screen :

Collect(TableX,Table({ RecordNo:"",Tail1:"",Tail2:""}));

RemoveIf(TableX,RecordNo="",Tail1="",Tail2="");  // to delete the empty record

Navigate(Screen2,ScreenTransition.Fade)



Button ADD, OnSelect  :

Set(varRecordNo,Value(Last(TableX).RecordNo)+1);

Collect( TableX,
{
RecordNo: Text(varRecordNo),
Tail1: TextInputTailOne.Text,
Tail2: TextInputTailTwo.Text
}
);




Button Remove selected row :

RemoveIf
( 
  TableX, 
  RecordNo = DataTable1.Selected.RecordNo , 
  Tail1 = DataTable1.Selected.Tail1 , 
  Tail2 = DataTable1.Selected.Tail2 
)

/* 
The source code to update RecordNo column must be here ... 

I tried the functions PATCH and FORALL, but no success ...

*/




I tried PATCH and FORALL, but no success ... any help please ?

Thx !

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
zdridos
Level: Powered On

Re: Browse all records in a table to update the value of a single column after deleting a single rec

I found it !!

Here is the solution :


Button Remove selected row , OnSelect:

Set(varRecordNoSelected, Value(DataTable1.Selected.RecordNo));

RemoveIf
( 
  TableX, 
  RecordNo = DataTable1.Selected.RecordNo , 
  Tail1 = DataTable1.Selected.Tail1 , 
  Tail2 = DataTable1.Selected.Tail2 
)

ForAll(     
    RenameColumns(TableX, "RecordNo", "OrderRow"),
    If( 
        Value(LookUp(TableX, RecordNo=OrderRow).RecordNo) > varRecordNoSelected, 
        Patch( 
              TableX,
              LookUp(TableX, RecordNo=OrderRow),
              {RecordNo:LookUp(TableX, RecordNo=OrderRow).RecordNo-1}        
        )

    )
)


I hope it could help someone in the future !

Good luck ! Smiley Happy

View solution in original post

3 REPLIES 3
zdridos
Level: Powered On

Re: Browse all records in a table to update the value of a single column after deleting a single rec

I found it !!

Here is the solution :


Button Remove selected row , OnSelect:

Set(varRecordNoSelected, Value(DataTable1.Selected.RecordNo));

RemoveIf
( 
  TableX, 
  RecordNo = DataTable1.Selected.RecordNo , 
  Tail1 = DataTable1.Selected.Tail1 , 
  Tail2 = DataTable1.Selected.Tail2 
)

ForAll(     
    RenameColumns(TableX, "RecordNo", "OrderRow"),
    If( 
        Value(LookUp(TableX, RecordNo=OrderRow).RecordNo) > varRecordNoSelected, 
        Patch( 
              TableX,
              LookUp(TableX, RecordNo=OrderRow),
              {RecordNo:LookUp(TableX, RecordNo=OrderRow).RecordNo-1}        
        )

    )
)


I hope it could help someone in the future !

Good luck ! Smiley Happy

View solution in original post

Mike8
Level 10

Re: Browse all records in a table to update the value of a single column after deleting a single rec

Hello,

You can also try something like this..

 //Set the variable to the selected RecordNo

Set(varRecordNoSelected, Value(DataTable1.Selected.RecordNo));  
//Remove the selected record
Remove(TableX,LookUp(TableX,RecordNo=varRecordNoSelected)); 
//Update the values of the RecordNo only for the values that are greater than the selected RecordNo
UpdateIf(TableX,Value(RecordNo)>Value(varRecordNoSelected),{RecordNo:Text(Value(RecordNo)-1)});

zdridos
Level: Powered On

Re: Browse all records in a table to update the value of a single column after deleting a single rec

Thanks  @Mike8 , it's a very short code and it's handy for readability !

But it doesn't work, you need to modify this line :

Remove(TableX,LookUp(TableX,RecordNo=varRecordNoSelected)); 

by this line :

Remove(TableX,LookUp(TableX,Value(RecordNo)=varRecordNoSelected)); 

Thanks again !

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 333 members 5,860 guests
Please welcome our newest community members: