How can I save a previously selected field choice to another field and have it update to the previous selection each time the original field is changed?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have three fields in a table: Status (multiple choice), Previous Status (text), and Status Changed (checkbox). When the Status field changes and is saved, I want it to record that Status in the Previous Status field and check the box Status Changed. This works on my form rules except for the fact that when I change the Status again I want the Previous Status to always reflect what the Status was just before it changed again. This is so I can track what the most recent Previous Status was before the Status changed. For example, in Status initially on save I have "Active" and in Previous Status I have "Active." Then I change the Status to "On Hold" I want Previous Status to still show "Active." Once the first change on the Status field changes the Status Changed checkbox will always be checked). Does this make sense about what I am trying to do?

Any thoughts? Is this possible?

Thanks!

Photo of Dawn

Dawn

  • 40 Points

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
My suggestion is to Change the field for Status to a field called Status Update.  Then set that field to "Log Changes".

Then have a new field called [Current Status] with a formula of
Trim(Right([Status Update], "]"))

That way you will have a log of all your changes.

If you really need to  also have a separate field for the Previous status, there is a way to make a more complicated formula to extract that from the Update Status field.
Photo of Dawn

Dawn

  • 40 Points
Mark, looks like we have to go more complicated. We do need to have a separate field for the Previous Status so we can log the answer that was created just before the Current Status was updated. This way we will always be able to see what the most recent status was before the change to the Current Status. Let me know what you think. Thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Ok. I will post that formula later today. ie, to parse out the 2nd most recent Status.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Here is a formula to parse out the 2nd most recent update to an Append only (log Changes) field where the new entries are added to the end of the field.



Trim(Right(NotRight([Approval Status (Log Changes)],"["),"]"))