Simple screen with a table and delete button. what is the best way to delete a row from that table based on the date column(Oldest Date) in the table?
Hello @KaiT-2f69, welcome to the community! You can delete a row in a table based on the oldest date in a given column by using the combined power of builder automations and the FINDROW function.
For example: =FINDROW(Parts,"Parts[Date]=MIN(Parts[Date])")
And here it is in action:
Hope this helps!
3 Likes
Thank you. This is exactly what I need.
HI @aj -
Is there any way to add an automation to delete the top 10 rows in a Table? I am trying to create an archive of sorts, but I only want to keep an archived record of the last 500 records. So I have set up an automation to run if =COUNT(Archive[Date Archived])>500. Now I am trying to figure out how to specify which rows to delete (the top 10 rows of the table in this case).
@Jared, if you click Run Options within the Delete row action block (see screenshot), you can specify multiple rows to delete using a filter. In your case, you will likely want to add an ID or Number column to your Archive table. Each time you add a new record to Archive, put the value =MAX(Archive[ID]) + 1 in the ID column of the new record. Then your filter in the Delete row action block would be =FILTER(Archive, "Archive[ID] < MIN(Archive[ID]) + 10"). Place that formula in the textbox labeled Run the step for these rows.
If you don't want to add an ID column to your table, you could filter the 10 oldest records using your Date Archived column, but the formula would be more complex. It would be something like: =FILTER(Archive, "COUNT(FILTER(Archive, %)) < 10", "Archive[Date Archived] < THISROW()[Date Archived]"). No guarantees that last formula is correct. It's a little complicated with the nested FILTER's, and I didn't test it.
Hey @Isaiah,
Thanks for your insight on this. What I ended up doing was using a filter to delete the oldest batch of records by archive date once the archive reached a certain capacity. I'll post the function below.
=filter(archive,"archive[date archived]=MIN(Archive[Date Archived])")
Thanks!
1 Like
Nice, that's even simpler!