topic Re: Setting a retention policy? in ArcGIS Survey123 Questions

Setting a retention policy?

RHammers — Thu, 13 Jun 2024 18:45:59 GMT

Hello! I would like to set up a system to track how long specific files (generally PDFs or images) have been uploaded, and when they hit 3 years, trigger a notification that the file should be deleted. Is there a best practice for this sort of thing? I know I can track when a survey was submitted, however part of my workflow involves editing surveys via embedding in a dashboard.

Any suggestions would be greatly appreciated!

Re: Setting a retention policy?

MobiusSnake — Thu, 13 Jun 2024 20:37:10 GMT

I don't believe this is easily possible. The REST API for querying attachments doesn't seem to provide any kind of timestamp indicating when the upload occurred. It's possible to extract EXIF data but that probably wouldn't work for you in every case, since it might not accurately reflect upload date, or it could be missing.

A very brute force approach would be something like this:

Create a feature service with a single table that has two columns, attachment_id and date_identified.
Run a nightly process that reads all of the attachments against a layer. If new attachments are found, insert a row into the table with the attachment's ID and the current date. If an old attachment has been deleted, remove it from the table.
As a follow-up to that process, run a query to see if any of the table's records have a date greater than three years old and trigger your logic based on that - auto-delete, notify by email, etc.

This doesn't really work for any records that have already been sitting around for years though, the three-year clock would start running when you make this live.

Edit to add - With a three-year clock you could even run this thing weekly or monthly and it'd probably be good enough.

Re: Setting a retention policy?

abureaux — Fri, 14 Jun 2024 15:02:24 GMT

This actually should be possible with the new Power Automate connectors. You can periodically pull data from a Feature Layer, look at it's Created Date, and then delete if that is >3 years old.

For the ParseJSON step, you can use this:

{ "data": [ { "attributes": { "last_edited_date": null, "created_date": null, "objectid": null } } ] }

"created_date" is saved in UNIX time, so needs to be converted:

addseconds('1970-1-1', Div(items('Apply_to_each_-_Loop_through_data')?['attributes']?['created_date'],1000) , 'yyyy-MM-dd')

The Date difference expression is also annoying, so here:

int(first(split(dateDifference(outputs('Compose_-_Create_Date'),variables('dateToday')),'.')))

Here is a quick and dirty example:

EDIT: In case it wasn't obvious, the "Outputs" used in the Condition are the outputs of "Compose - Date Difference". The output should be in days, so 3 years = 1095 days.

Re: Setting a retention policy?

MobiusSnake — Fri, 14 Jun 2024 15:24:28 GMT

It sounds like the attachment age may not necessarily align with the feature's editor tracking, since they're editing the survey after submission via a webform, and attachments don't have their own editor tracking.

Re: Setting a retention policy?

abureaux — Fri, 14 Jun 2024 15:29:13 GMT

I thought of this in my head, but didn't say it out loud.

While I am using "create date" in the flow, I also exposed "modified date" since that probably makes more sense to use. I would assume (aka, hope) Esri links the modified date to any changes to a feature layer (e.g., attachments). Should be easy enough to test for RHammers, I just don't have a data set that would work.

Re: Setting a retention policy?

MobiusSnake — Fri, 14 Jun 2024 16:55:25 GMT

Unfortunately modifying attachments doesn't set the modified date (I've run into this before trying to do change detection). I think attachment adds/deletes are treated like an edit to a related table rather than an edit on the table itself.

Re: Setting a retention policy?

abureaux — Fri, 14 Jun 2024 17:27:01 GMT

I just tried uploading an attachment and compared the "last_edited_date" value. They were identical before and after. And this timestamp should include up to the millisecond I believe. Well... that's disappointing.

Ya, attachments are painfully lacking of support for this type of operation. The easiest thing to do would be to delete the entire entry after 3 years from "last modified date". But that assumes you aren't uploading attachments at a later date... very frustrating.