Delete data in another table through action and workflow

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 :slight_smile:

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-3d26-475c-a8f8-9911f5015920

1 Like

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.

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?

1 Like

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

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.

1 Like

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
3 Likes

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

2 Likes

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?

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.

2 Likes

I manage to delete multiple records in the child table, for the number of lines I need. Thank you for all the help :slight_smile: 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.

1 Like

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=245700e5-9061-4045-843f-7850b5eb439a

1 Like

The key column should never be _ROWNUMBER.

See also:

1 Like

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

1 Like