Delete Mutiple rows via webhook and API

FatLou · 07-16-2019 05:45 PM

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”
}

Phil

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:

Your account id
The app name
The workflow rule name
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.

FatLou

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

FatLou

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

Phil

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.

FatLou

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

Phil

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…

David_Philippe

Hi, could you elaborate on how to do so ?

Phil

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.