Delete Mutiple rows via webhook and API

I have Table A and Table B. Table B holds a reference Column to table A. When I delete a row in Table A I want to create an Action that then deletes all rows in Table B that referenced the deleted row in Table A.

below is how I filled out the workflow

Target Data: Table A
When this happens: Deletes_Only

If this is True:

Reaction: Webhook
Name: Action 1
Preset: Custom
URL: https://api.appsheet.com/api/v2/apps/*******/tables/Table B/Action

HTTP verb: Delete
HTTP Content: JSON

Body Template:

The goal here is to SELECT all of the rows from Table B where the value of the [Properties] column is equal to the value of ID from the row deleted in Table A.
{
“Action”: “Delete”,
“Properties”: {
“Locale”: “en-US”,
“Location”: “47.623098, -122.330184”,
“Timezone”: “Pacific Standard Time”
},
“Rows”: [
<<Start: Select(Table B[ID], [Properties] = [_THISROW].[ID])>>

{
  "ID": "<<[ID]>>"
}

<>
]
}

Below is the result in the Audit history. It seems to be returning the correct IDs but it is not deleting them from the table.

Row:
[
“19”,
“12vlwZir”,
“Will Test”,
“Wills Test”,
“”,
“”,
“”,
“”,
“”,
“”,
“”,
“Er93uGK5”,
“”,
“”,
“”,
“”,
“”,
“”,
“ZIUnmByV , 7MGTEfIY , QV1Bg624”,
“”
]
Properties:
{
“TableName”: “Properties”,
“tzOffset”: “240”,
“settings”: “{”_RowNumber":“0”,"_EMAIL":"","_NAME":"","_LOCATION":"",“Options Heading”:"",“Option 1”:"",“Option 2”:"",“Country Option”:"",“Language Option”:"",“Option 5”:"",“Option 6”:"",“Option 7”:"",“Option 8”:"",“Option 9”:"","_THISUSER":“onlyvalue”}",
“apiLevel”: “1”,
“isPreview”: “undefined”,
“checkCache”: “false”,
“locale”: “en-US”,
“location”: “null”,
“appTemplateVersion”: “1.000755”,
“localVersion”: “”,
“timestamp”: “”,
“requestStartTime”: “2019-07-16T23:36:36.153Z”,
“lastSyncTime”: “”,
“appStartTime”: “”,
“dataStamp”: “”,
“clientId”: “c5da2c8d-4e37-4bb8-b5e8-db46567c8949”,
“build”: “undefined”,
“requestId”: “95293962”,
“RowSize”: 67,
“AppTemplateName”: “******”,
“Operation”: “Delete row”,
“RecordType”: “Start”
}

×
Audit Log Details
Row:
[
“19”,
“12vlwZir”,
“Will Test”,
“Wills Test”,
“”,
“”,
“”,
“”,
“”,
“”,
“”,
“Er93uGK5”,
“”,
“”,
“”,
“”,
“”,
“”,
“ZIUnmByV , 7MGTEfIY , QV1Bg624”,
“”
]
Properties:
{
“TableName”: “Properties”,
“tzOffset”: “240”,
“settings”: “{”_RowNumber":“0”,"_EMAIL":"","_NAME":"","_LOCATION":"",“Options Heading”:"",“Option 1”:"",“Option 2”:"",“Country Option”:"",“Language Option”:"",“Option 5”:"",“Option 6”:"",“Option 7”:"",“Option 8”:"",“Option 9”:"","_THISUSER":“onlyvalue”}",
“apiLevel”: “1”,
“isPreview”: “undefined”,
“checkCache”: “false”,
“locale”: “en-US”,
“location”: “null”,
“appTemplateVersion”: “1.000755”,
“localVersion”: “”,
“timestamp”: “”,
“requestStartTime”: “2019-07-16T23:36:36.153Z”,
“lastSyncTime”: “”,
“appStartTime”: “”,
“dataStamp”: “”,
“clientId”: “c5da2c8d-4e37-4bb8-b5e8-db46567c8949”,
“build”: “undefined”,
“requestId”: “95293962”,
“RowSize”: 67,
“AppTemplateName”: “77009382-fb60-4c59-b103-5a2a5c9b8849”,
“Operation”: “Delete row”,
“RecordType”: “Start”
}

Based on the information you included from the Audit History, it appears that you are reporting a problem with the application “RI_phase1-742623”. Can you confirm that?

I looked at rule “DeletePropertyPipelineOrphans” on the theory that this was the webhook rule that was failing. The JSON template looks correct.

When I searched the Audit History, I could not find any instance in which that rule had been invoked in the past 5 days.

I can investigate further if you provide:

  1. Your account id
  2. The app name
  3. The workflow rule name
  4. The timestamp from the Audit History where the workflow rule was invoked.

Are you aware that if you set the “Is part of” property on Reference in table B, AppSheet will automatically delete the child records in table B when the parent record in table A is deleted? That would be easier than deleting the children using a webhook that calls the REST API.

That seems to work and it is a lot easier. I did not realize that was a function. Thanks.

1 Like

One more issue is that when rows are deleted from sheets it clears the rows it does not however delete the actual rows.

That is intentional because some customers use row number as a key (even thought doing that is strongly discouraged). If we deleted the actual row, the row numbers of the following rows would change and cause the “row number keys” to change.

2 Likes

Ok,

From a CS perspective perhaps you guys should force an ID column. That is much better practice and in my specific case I have very “active/ busy” tables so deletions leave massive row gaps which is not a problem programmatically but the tables start to look strange.

Either way, keep up the good work.

-Will

Hi Will,

Point well taken.

I forgot to mention that you can manually “garbage collect” the empty rows provided you do that while the app is idle. Since you are not using row number as a key it is safe to simple delete the empty rows…

Hi, could you elaborate on how to do so ?

If you are not using the row number as a key you can manually “garbage collect” the empty rows by simply deleting the empty rows from the worksheet. You must only do this while the app is idle.

Simple open the worksheet in Google Sheets or Excel and delete all of the empty rows. In Excel you must then save the updated worksheet.

Once you have done this, your application users should perform a sync to retrieve the updated worksheet contents.

1 Like