Re: Bot to delete all records except the most rece...

StephenSaid · 10-24-2023 07:40 AM

I need a bot to delete all the records in the table except for the last 10 records.

I guess I need a query in the condition below using TOP and ORDER.

How can I achieve this, please?

StephenSaid

I've got an Event type Adds only with a this condition:

[_THISROW].[_RowNumber] < ( MAX( SELECT(ENQUIRIES[_RowNumber], TRUE) ) - 10 )

The Process is running a data action - Delete.

The bot is not triggering when I add a new record.

If I run a test I get the following:

The audit shows that the bot is triggering with Condition: false.

Not sure what's wrong.

Suvrutt_Gurjar

@StephenSaid wrote:

I need a bot to delete all the records in the table except for the last 10 records

I believe you mean the latest or the most 10 recent records by the above statement.

If so , I believe your bot could look like below

The bot step could look like below

The HTTP request body could be something like below

{
"Action": "Delete",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": [ <<Start:(Enquiries[Key column], [_ROWNUMBER],TRUE) - TOP(ORDERBY((Enquiries[Key Column], [_ROWNUMBER],TRUE),10))>>
{ "Key Column" : "<<[Key Column]>>",

}
<< END >>

]
}

Please change highlighted parameters per your locale. Please test well per your requirement. This is delete operation. So there may not be any reversal of deleted records depending on backend database you are using. Please have suitable data backup or test data / test app for testing.

Also there does not seem to be a date column in the table as well as any person specific column or such columns to further qualify the expression to select enquiries by a person or such qualifier. So the <<START:>>expression will simply select any enquiries older than latest 10 by row number for deletion. Please do modify expression suitably.

StephenSaid

@Suvrutt_Gurjar

Thanks for your answer. This is exactly what I am trying to achieve. Simply keep the most recent 10 records created from any user.

This solution seems to need an ApplicationAccessKey. I enabled this and after doing so I get no errors, but the records still do not get deleted although the bot is firing properly when I create a new record.

Also monitoring the bot, seems be filtering out the right records which needs to be deleted.

The listed key columns are the records I need to be deleted.

{
"$type": "Nirvana.Data.TaskResultWebhook, V2API",
"Headers": {
"$type": "System.Collections.Generic.Dictionary`2[[System.String, mscorlib],[System.String, mscorlib]], mscorlib",
"applicationAccessKey": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
"_webhookActionDepth": "0"
},
"Payload": "{\"Action\": \"Delete\",\"Properties\": {\"Locale\": \"en-US\",\"Location\": \"47.623098, -122.330184\",\"Timezone\": \"Pacific Standard Time\"},\"Rows\": [{ \"Key Column\" : \"Wwe0rRfTPFIdNXHIAUHTST\"},{ \"Key Column\" : \"uZlwafLTTNotx3ly3WpUun\"},{ \"Key Column\" : \"Vq1RMEfenUYqQhv4EoKCFu\"},{ \"Key Column\" : \"fcrEy6QeUPjw9uYgMwQ3s0\"},{ \"Key Column\" : \"DDg9RPr1wiqP3zC8jDR1Fj\"},{ \"Key Column\" : \"qB9JT6f5b5HUDv6ZULUhAp\"},{ \"Key Column\" : \"uQrhEmhq0WDNF8WOMCD0s3\"},{ \"Key Column\" : \"SUUhJKQZKhk6a4pP2znL4e\"},{ \"Key Column\" : \"2f52Z1gFTOaY09WOye94Uh\"},{ \"Key Column\" : \"rbhNLZDW0oZPxOUq3Lfzb6\"},{ \"Key Column\" : \"utfUcejiBUPZdqqIelIWvk\"},{ \"Key Column\" : \"fur1f1i2TuUGAqi9pVgpsn\"},{ \"Key Column\" : \"0lYGwo4k9ngioQAjDIDeMD\"}] }",
"Url": "https://api.appsheet.com/api/v2/apps/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/tables/Enquiries/Action",
"Verb": "Post",
"ContentType": "JSON",
"MimeType": "application/json",
"TimeoutSeconds": 180,
"MaxRetryCount": 3,
"AsyncExec": false,
"AppErrors": {
"$type": "Jeenee.DataTypes.AppErrors, Jeenee.DataTypes",
"RecordInfo": false,
"RecordWarning": true,
"AnnotateErrors": false,
"Errors": []
},
"TaskType": "Webhook",
"TaskName": "Delete old Enquiries Task - 1"
}

This is how I have the process set up:

I must be missing something else.

Suvrutt_Gurjar

One error I see in the screenshot you have shared in the webhook body is in the statement

"Key Column" : "<<[ROW ID]>>"

It should be

"ROW ID" : "<<[ROW ID]>>"

Essentially "Key column" is just a placeholder name for the actual key column name. It needs to be replaced at all instances.

StephenSaid

@Suvrutt_Gurjar

I fixed that and it worked. Thanks

I took your suggestion and added useremail and timestamp to the table.

I revised the HTTP request as following but this is not working properly.

{
"Action": "Delete",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": [ 
<<Start:
(Enquiries[ROW ID], [UserEmail] = USEREMAIL(), TRUE) - 
TOP( ORDERBY(Enquiries[ROW ID], [TimeStamp], TRUE), 10 )
>>
{ "ROW ID" : "<<[ROW ID]>>"}
<<END>>
] 
}

I wish to keep the last 10 records of each user and delete the rest.

Is there any documentation I can read about this HTTP request for AppSheet. I'd love to learn more.

Thanks

StephenSaid

So this works. But testing with a different email on the preview pane does not.

Thanks for your assistasnce.

{
"Action": "Delete",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time",
"RunAsUserEmail": "<<USEREMAIL()>>"
},
"Rows": [ 
<<Start: 
SELECT(Enquiries[ROW ID], [UserEmail] = USEREMAIL()) - 
TOP(ORDERBY(Enquiries[ROW ID], [TimeStamp], TRUE), 10)
>>
{ "ROW ID" : "<<[ROW ID]>>"}
<<END>>
] 
}

Bot to delete all records except the most recent 10 records