Clean database

[Bob-B]

I want to use automation to clean a database as soon as a new record is being created.

I have an automation task that creates a record with a session ID, a timestamp (NOW()) and two attributes from another table. This works.

As soon as a new record is being created I want the robot to:

1) Check if one or more records with the same session ID's is available

2) Delete all but the newest

To retrieve the session ID I used context(device), works fine for now.

Can someone help me how to set up this robot.

I created a bot, triggered by event "add or update" on the session table

But I can't manage to create an action that checks for duplicates (say column userID: [userID]=Context(device)) and the remove all rows with same userID except the latest

So something like select row where userID=context(device) and delete everything but the row with the most recent timestamp.

[Bob-B · Accepted solution]

I made a grouped action which filters based on the same device ID, then delete everything, after that create a new session record

[Steve]

FILTER(
  "table",
  AND(
    ([userID] = CONTEXT("Device")),
    ([timestamp] < [_THISROW].[timestamp])
  )
)