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

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

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
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (10,217)