Delete data in another table through action and workflow

[edmund_chan]

I have been trying to edit and delete data from table 2 based on the same key column in table 1, and i have been reading resources on how to do it, including the url below, but I can’t seem to do it myself. I can use action and workflow to add a new row of data to another table, but i cannot edit or delete data. My 2 tables are setup to be able to add, edit and delete data. Any help is much appreciated

help.appsheet.com

Changing Data from a Workflow Rule or Scheduled Report

How to change data in your app on a schedule or when some event happens.

[Suvrutt_Gurjar]

Hi @edmund_chan,

I believe you may wish to mention more details about your requirement.

In the meantime, have you taken a look at the concept of reference actions?Please take a look at the below sample app including description of the app.

https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-...

[edmund_chan]

Hi @Suvrutt_Gurjar, thank you for looking into this question. From the screen print below, I can add record into sheet1, and through Action and Workflow, I can automatically add the same record to sheet2. However, when I try to remove records from sheet1, I don’t know how to auto-delete the same record in sheet 2. I have tried many ways, including using Action and Workflow. So any advice is much appreciated.

[Suvrutt_Gurjar]

Hi @edmund_chan,

Thank you. Could you please mention how the tables belonging to sheets 1 and sheet 2 are related? Do these tables have parent child relationship?

Also in your first post you have mentioned data -could you please mention whether you would like to delete only some column values or entire records the tables of the two sheets?

[edmund_chan]

Sheet2 is a duplicate of Sheet1, and the column “sn” is a common key between the two sheets. It’s a 1-1 parent child relationship. For data, I meant that I want to delete the same record in Sheet2 when I delete the same record in Sheet1. The record in Sheet2 could be in a different row from Sheet1, but the key is common.

Edmund Chan

[Suvrutt_Gurjar · Accepted solution]

You could try to set the IsPartOf setting of the reference column in the child table to on. Please take a look at the setting and the associated description mentioned with it.

As with any delete operation testing, I wouldrequest you to first try it on test data.

[edmund_chan]

Hi @Suvrutt_Gurjar and Appsheet community! yes for some tables i need to delete multiple child records, and i have set “is a part of” as well as using test data. However, though I see more than 4 or 8 child records from my inline view, but when i delete the parent record, it only deletes the first 2 child records. How can I get it to delete all child records?

[Suvrutt_Gurjar]

Hi @edmund_chan ,

You may want to elaborate what you mean by “using test data”. Could you please create new parents and associated child records after you have turned on this “IsPArtof” setting and test. You could test with these newly added records.

I tested with deleting two parent records in a test app, each having 4 child records, They all -parent as well as respective chidren- got properly deleted

If you still face issues, I believe you may wish to share the column settings of both the reference, key columns.

[edmund_chan]

I manage to delete multiple records in the child table, for the number of lines I need. Thank you for all the help In terms of the key for the child, does it have to be _RowNumber? When I change the key to other fields, it doesn’t seem to work.

[Suvrutt_Gurjar]

Hi @edmund_chan ,

Great.

If you are referring to the approach with “ISPartOf” setting, the key of the child need not be row number. Please take a look at the sample app, " order capture" referred below.

Here child table “Order Details” is tied to parent "Order"table through referencing and “IsPartOf” setting. An order delete results in delete of child records of order details as well.

The key of the child table “Order Details” is not row number. I believe you may explore that sample app a bit to get the idea.

https://www.appsheet.com/samples/An-app-for-managing-customers-products-and-orders?appGuidString=245...

[Steve]

edmund_chan:

In terms of the key for the child, does it have to be _RowNumber?

The key column should never be _ROWNUMBER.

See also:

help.appsheet.com

What is a Key?

Learn the purpose of keys and why they’re necessary.

[edmund_chan]

Thank you for the reminder that key column should never be _RowNumber. I have updated it.

[sunil_prajapati]

dear sir,

thanks a lot just I use this hint and it’s perfectly worked, but I need one more hint that when we update the parent table child table is not updating like delete works. how to update dynamically.

[Suvrutt_Gurjar]

sunil_prajapati:

but I need one more hint that when we update the parent table child table is not updating like delete works.

Please explore if the reference actions help.

https://www.appsheet.com/samples/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e73-...

[Steve]

From your description, it appears the user would initiate the delete from table 1 by clicking a delete button on a row in table 1. The table 1 action must delete the row in table 2 first, then delete the row in table 1. If you delete the row in table 1 first, the action sequence will stop immediately, preventing you from then deleting the row in table 2.

Delete this table 1 row

• For records of this table: table 1
• Do this: Data: delete this row
• Needs confirmation?: OFF

Delete this table 2 row

• For records of this table: table 2
• Do this: Data: delete this row
• Needs confirmation?: OFF

Delete this table 1 row in table 2

• For records of this table: table 1
• Do this: Data: execute an action on a set of rows
• Referenced Table: table 2
• Referenced Rows: LIST([_THISROW])
• Referenced Action: Delete this table 2 row

Delete this row in table 2 and here in table 1

• For records of this table: table 1
• Do this: Grouped: execute a sequence of actions
• Actions:
  • Delete this table 1 row in table 2
  • Delete this table 1 row

[edmund_chan]

Steve:

Delete this row in table 2 and here in table 1

Brilliant! why didn’t I think of deleting from Table 2 first then Table 1? Thank you so much Steve

[reggieneo]

Can this be extended to table 3? Say, I like to delete the grand child too?

[Steve]

It could, yes.

[Koichi_Tsuji]

Need a bit of trick.

[dbaum]

Thank you! Super helpful. The documentation and UI are less than clear regarding what Referenced Table/Rows/Action mean in a "Data: execute an action on a set of rows" action. Your example illustrates it well, and I've put it to good use.

[Goncalo_Marques]

Hello everyone! I try to delete a row on table “user_data” with a action button on table “baloicos”. So i try this on Referenced rows of an action:
FILTER(
“USER DATA”,
AND(
([_THISROW].[ID] = [ID_ROW_BALOICO]),
([Estado] = “Visitado”)
)
)

This is the action:

The errors:
“Action on a set of rows’ ‘’ for Referenced Table ‘USER DATA’ could not be found.”

Can anyone help me. 5 days around this and not solve the error.

Thank you all in advance!

[Marc_Dillon]

Try to change this to something else, then change it back to the delete Action that you want.

[Goncalo_Marques]

OMG!!! IT WORKS!!!

Thank you a lot my friend!

[MarcelaVega]

Hola! esta solución me ha servido perfectamente, lo único que me falta es poder eliminar el mensaje de confirmación que aparece al ejecutarse la acción de eliminacion de registros, ya que es una acción que se ejecuta junto con otras. ¿Se puede?

Gracias por su tiempo!

[Steve]

Create your own delete action with confirmation disabled, then use that delete action instead of the system-provided one.

[reggieneo]

Gents, I have done 3 layers of select and it just got an error of something’s like “too long time to respond “ .

So I just created a grandparent key column copy in grandchild and then referenced the grandparent from parent.
Then one final select statement from grand parent action to delete all Thru the grandchildren records.

Thanks