Workflow to delete all records in a Table

[Timothy_Bray]

I’m having trouble getting a Workflow Webhook to delete ALL the records in a table at ounce. It seems to fire just fine, but the table data does not get deleted. I read a bunch of eh guidance but still am struggling with it. Any help would be appreciated.

[Steve]

Please detail what you’ve attempted.

[Timothy_Bray]

Thanks Steve.

Update to my previous post: It seem to fire sometimes, but not others. (see audit history) In either case it does not delete the data in my table.

I set up an action to trigger this workflow intended to delete all the rows in a table. I am leaving the body empty so it uses the default template.

[Steve]

What is the error?

[Timothy_Bray]

Properties:
{
“RestAPIVersion”: 2,
“TableName”: “Fish Data Input From App”,
“AppTemplateVersion”: “1.000126”,
“AppTemplateName”: “2af8237d-c0fc-41af-901b-e29126bf49ea”,
“Operation”: “REST API invoke”,
“RecordType”: “Stop”,
“ResultSuccess”: false,
“ResultException”: “System.NullReferenceException: Object reference not set to an instance of an object.\r\n at Nirvana.source.api.PublicAPIV2Controller.RestApi_Action_Internal(Context context, LogOperation logOperation, String appId, String applicationAccessKey, String tableName, String actionName, JObject data) in d:\a\1\s\Nirvana\Controllers\API\PublicAPIV2Controller.cs:line 409”,
“StatusCode”: “InternalServerError”,

[Steve]

Hmmm… Unfortunately, I have virtually no experience with webhooks, and neither the error nor the screenshots provide any information that leads me to any helpful advice. What I can offer is an alternative approach using actions alone:

1. For the table from which the rows are to be deleted (Fish Data Input from App?), create an action called (e.g.) Delete without confirmation:

   • Do this: Data: delete this row

   • Needs confirmation: OFF

2. For your trigger table (Workflow Trigger?), create an action called (e.g.) Delete All Fish Data.

   • Do this: Data: execute an action on a set of rows

   • Referenced Table: (table from which the rows are to be deleted (Fish Data Input from App?))

   • Referenced Rows:

     ORDERBY(
       FILTER("Fish Data Input from App", TRUE),
       [_ROWNUMBER],
         TRUE
     )
     

     Replace Fish Data Input from App with the correct name of the table from which the rows are to be deleted.

   • Referenced Action: Delete without confirmation

Use this Delete All Fish Data action instead of your webhook.

[Timothy_Bray]

Thank you. This works to well to delete the data line by line.

The challenge this approach creates is very long sync times if there is a large amount of data to delete. I created something similar in an earlier version and it took about 45 minutes to delete 2400 rows of data.

I was hoping to select all rows and delete them all once. Perhaps there is Reference Rows expression to select them all.

[Bellave_Jayaram]

I don’t think this is a viable solution in the short and medium term if you are going to need to do this a lot (meaning several times a day). IMHO, you are better off writing a script that is triggered by the webhook to delete all the rows.

[Steve]

I’ll defer to the expert.

[MultiTech]

Timothy_Bray:

I was hoping to select all rows and delete them all once. Perhaps there is Reference Rows expression to select them all.

If you trigger the delete action (from Steve’s suggestion above) using a Change Data action in a workflow - the edit to the table is made in one edit, meaning they get mass deleted at once. The downside to deleting with a workflow is that you have to wait for the workflow to be triggered and for the app to sync afterwards.

But if this is a “set it and forget it thing” then it might be a solution.

[Timothy_Bray]

Thank you for your help. I’ll continue to investigate how to implement a webhook.

[Phil]

Hi Timothy,

The error “System.NullReferenceException” is indicating that there is a bug in AppSheet.
I suspect that the bug may be triggered by something wonky in your webhook, but you should never see a Null Ref Exception regardless of what your webhook does.

Please submit a bug report and supply the following information:

1. Your account id
2. The application name
3. The table name
4. The workflow rule or report name
5. A complete description of the problem
6. The exact steps to reproduce the problem
7. Please go to https://www.appsheet.com/account/account#_tab_acctConfig and check the option that enables support access.

[Timothy_Bray]

Thank you MultiTech_Visions! That is exactly what I was looking for and it works perfectly!

Thank you to all that helped.

[Tin_Nguyen]

I have a similar question. I have a table that I create quotes in deck view. Can I create and action button to delete all quotes?

[Steve]

Tin_Nguyen:

Can I create and action button to delete all quotes?

Yep.

[Tin_Nguyen]

I can create action button to delete one row. How would I delete all the rows? I don’t see an option for that.

[Steve]

Use an action of type Data: Execute an action on a set of rows to identify all rows and invoke a separate action that performs a Data: delete this row action for each row.

[Tin_Nguyen]

I got that far. I guess I don’t know what the expression is to select all the current rows. Obviously I don’t want to delete the header row in the table.

[Steve]

FILTER("table-name", TRUE) will produce a list of all rows in the table named table-name.

Don’t worry about the header row–it’s not considered a data row and will never be impacted by an action.

[Hussein_Osseily]

Thanks for sharing, this helped me even after a couple of years.

Thanks

[Tin_Nguyen]

It worked! Thanks again.

[Office_Geek]

Use AppScript to write make a delete all function & trigger via AppSheet.

AppScript Code here:

function deleteRowsFromRowTwo() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var numRows = sheet.getLastRow() - 1; // get the number of rows starting from row 2

  sheet.deleteRows(2, numRows); // delete the rows starting from row 2

}