TL:DR - I need to apply a timestamp to a record and once applied for it not to be amended.
I am creating a sort of complaints management app and I want it to place a timestamp in the datasheet when a complaint handler confirms the complaint is closed. This is so the sheet will identify the closed complaints and order them by the date and time they were closed. In turn the sheet uses the autocrat add on to create a report with the data entered into the sheet.
I have figured out how to apply the timestamp using the following expression (this is a dummy sheet hence the strange headers):
IFS(AND(ISBLANK([Complaint Completed]),ISNOTBLANK([Date of a thing])),NOW())
However, if someone goes back into the record and saves it again, it removes the timestamp. To keep the timestamp I used this expression:
IF(ISNOTBLANK([Date of a thing]),NOW(),"")
The problem with this is it updates the timestamp whenever anyone makes any changes to the record (for example if someone needs to amend any of the details entered for any reason). In turn this will change the order of the sheet where the reports are generated. The links to the report stay where they are as this is not a dynamic list, however the record moves as this is a dynamic list (ordered by the timestamp) and as such the record does not sit on the line corresponding to the report.
I need to ensure that, once the timestamp is entered, it does not change when someone amends the record.
Alternatively, is there a way to limit the number of times a field can be edited?
Thanks in advance for any assistance