I have an app that records surgery details. There’s an Enum type column called [Surgery Status], that has 3 values - Done, Pending, Postponed.
If a particular patient’s surgery gets postponed, just tapping ‘Postponed’ in the [Surgery Status] column should record a value of 1 in a Number type column called [Postponed Times]. Now, once this postponed patient gets rescheduled for surgery, the person who maintains the app will change the [Surgery Status] to ‘Pending’, and still that should keep the value 1 in the [Postponed Times] column which was previously recorded when the surgery got postponed (because the patient was postponed once). Now let’s say, this patient’s surgery gets postponed again for the second time also, and now the person who maintains the app changes the [Surgery Status] again to ‘Postponed’. When that happens, I want that [Postponed Times] column to record the value 2, as now this patient’s surgery got postponed twice. I want this going on for as long as the patient’s surgery status changes from pending to done (let’s say if the surgery gets postponed 4 times, I want the [Postponed Times] column to record the value 4 automatically).
I wrote an expression to the [Postponed Times] Number type column’s app formula
IF([Surgery Status]=“Postponed”,[Postponed Times]+1,[Postponed Times]+0)
While it does work, only issue is, when ever the same patient’s data row is accessed via the edit button to change some other info of the patient, if the [Surgery Status] is kept at the “Postponed” value for the patient, this adds a +1 value to the [Postponed Times] column again which I don’t want to happen. Is there a way to mitigate this issue and just maintain an authentic counter for the number of times the surgery gets postponed for the patient?
Thanks a lot