cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

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
Highlighted
Advocate II
Advocate II

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
Highlighted
Advocate II
Advocate II

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

Highlighted
Super User
Super User

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)});

Highlighted
Advocate II
Advocate II

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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (9,115)