Dynamically update rows in same table

[David_H1]

Hello.

I would appreciate helping with solving this scenario:

I have a table with Sales order number.

And sometimes Credit Order has to be created for Sales Order. I do this by creating a new record - credit order - and from dropdown I select the Sales order, which needs correction and here I add additional info.

When saving this record I would like to dynamically update the original Sales Order, for which the correction was just made by creating this  Credit Order.

I would need the workflow to find in table this exact Sales order number and then update or add additional info, which was created in Credit order.

I hope it makes sense.

Thank you.

[Suvrutt_Gurjar]

Is the understanding correct that the general column arrangement is as below in the table

Column Key	Sales Order Number	Credit Order Number	Other columns	Update Column
1234abcd	S1234		This is sales order	Update this column when  Credit Order c=record is added
2345bcde	S1234	C4321	This is credit order with some correction

If so, in general you could use reference actions to find the previous record with the same sale order number and update a column or columns in that record with the desired information.

Please take a look at the sample app mentioned below for reference actions. The app's description describes the reference actions well.  Please do take a look at that under option Info --> Properties --> App documentation

One can invoke reference actions from parent to child, child to parent , one table to another and within the same table. Essentially the initiating action needs a list of rows to act on and the second action in the reference action pair changes a column or more in the list of records referenced by the first initiating action.

Reference Actions - AppSheet

[David_H1]

Hello Sir,

Yes, you are correct, thank you - this is exactly what I am trying to do. Now I am trying to replicate the reference action.

In my case, all is happening within same table. I have created a new virtual column List of Sale Orders  -> REF_ROWS("MAIN", "Sale Orders").

- I have created first action  to update column Update Column.  
- And then second action with referenced rows  List of Sale Orders  and selected newly created referenced action UPDATE INFO.

When executing second action nothing happens. Update Column is not being updated with new data.

I guess I have a problem with List of Sale Orders...

[Suvrutt_Gurjar]

For the initiating action of type "Execute an action on a set of rows" , you could have an expression something like

SELECT(Table Name[Table Key],AND([Sales Order Number]=[_THISROW].[Sales Order Number], [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]))

It is not clear of you are updating the Update Column, meaning are you updating it with some fixed text or with a column value from the Credit Number row. If it is later ( update from a value in the credit order row), you may need another SELECT() expression to update the column value back in the sales order number row.

Also the above expression assumes that there will be only one credit number extra row for a sales number record. Or else some more logic will need to be built in, if there can be multiple credit order rows for a single sales order. You could also make the SELECT() expression less expensive by using a slice etc. if you are using one.

You could have this reference action as event action on form save.  In the condition of initiating action, you could have an expression such as  ISNOTBLANK([Credit Number])  so that the action does not fire on all form saves.

[David_H1]

I have created SELECT() expression as you have suggested in initiating action of type "Execute an action on a set of rows"  in referenced rows.

I am updating Update Column with a column value from the Credit Number row, so I need another SELECT() expression to update the column value back in the sales order number row. Does this mean I update the existing SELECT expression that you have suggested with AND(SELECT,...)?

Thank you.

[Suvrutt_Gurjar]

---

@David_H1 wrote:

I am updating Update Column with a column value from the Credit Number row,

---

Your SELECT() expression to update the column value can be something like 

SELECT(Table Name[Column Name],AND([Sales Order Number]=[_THISROW].[Sales Order Number], [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]))

where [Column Name] is the column name containing column value in the Credit Number row with which you wish to update the Update Column

Again, please explore if you can create/ use a slice that limits the row numbers in the SELECT(). With increasing number of rows, SELECT() expressions start taking more time to compute.

[David_H1]

Ok, so my data looks like this. Table "Main":

Column Key	Sales Order	Credit Order	Correction	Notes
1111	S1234

When adding a new record with Credit Order new Row appears:

Column Key	Sales Order	Credit Order	Correction	Notes
1111	C5678		S1234	wrong VAT

This indicates that Sales order C5678 is a credit order for Sales order S1234.

I am trying now to dynamically update Sales Order 1234 via action, so it would look like this:

Column Key	Sales Order	Credit Order	Correction	Notes
1111	S1234	C5678		wrong VAT

I have created two actions.

1. First one "Update Sale Order with Credit order Info" where I set the values of some columns in this row:

Credit Order = [_THISROW].[Credit Order]
Notes = [_THISROW].[Notes]

2. Second action "Update SO with CO INFO" execute an action on set of rows and in Referenced Rows I have this formula:

SELECT(Main[Credit Order],AND([Sales Order]=[_THISROW].[Correction], [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]))

With Referenced 1st action (Update Sale Order with Credit order Info).

But no luck, nothing happens when executing 2nd action. There are no errors. And there is no indication that anything is happening...

[Suvrutt_Gurjar · Accepted solution]

Hi @David_H1 ,

Thank you for more details. A few things,

1. In the example of two rows in tabular format you have shared, the column key in both the records is 1111. I hope it is a typo. The key cannot be same for any two records. Also I hope column key is the key column of the table and not some other column,
2. If [column key] is the key column of the table, then the below mentioned expression for the referenced rows should NOT work.

   ---

   @David_H1 wrote:
   

   2. Second action "Update SO with CO INFO" execute an action on set of rows and in Referenced Rows I have this formula:

   SELECT(Main[Credit Order],AND([Sales Order]=[_THISROW].[Correction], [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]))

   ---

   This is so because the referenced row action (  execute an action on set of rows) expects a list of keys and not any other list. The expression should be something like 

   SELECT(Main[Column key],AND([Sales Order]=[_THISROW].[Correction], [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER])).   I had requested to create an expression with key column as below.

   ---

   @Suvrutt_Gurjar wrote:
   

   SELECT(Table Name[Table Key],AND([Sales Order Number]=[_THISROW].[Sales Order Number], [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]))

   ---

   
   

   3.  The below action expression will also not work.

3. ---

   @David_H1 wrote:
   

   1. First one "Update Sale Order with Credit order Info" where I set the values of some columns in this row:

   Credit Order = [_THISROW].[Credit Order]
   Notes = [_THISROW].[Notes]

   ---

   I had requested a SELECT() expression as below.

   ---

   @Suvrutt_Gurjar wrote:
   

   Your SELECT() expression to update the column value can be something like 

   SELECT(Table Name[Column Name],AND([Sales Order Number]=[_THISROW].[Sales Order Number], [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]))

   ---

   
   

   So your expression to change [Credit Order] can be 

   
   

   ANY( SELECT(Table Name[Credit Order],AND([Correction]=[_THISROW].[Sales Order], [_ROWNUMBER]<>[_THISROW].[_ROWNUMBER])))

   Edit: Added ANY() to he expression.
4. Finally it is not exactly clear , how you are initiating the referenced row action. Presume you are doing it as a "Form Save" event action.

Hope this helps.

[David_H1]

Hi Sir,

With your guidance I was able to finally make it work.  Thank you so much for your time and patience. 

You are my hero! :).