Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.
I want to capture the date that a status field changed in a date field, and I don't want the date field to change unless the status field changed. How can I do this?
This can be accomplished by adding a new table to your application and using the snapshot property of a date field.
For the purpose of this example let's assume the name of the table containing the field that you want to date stamp is called "Call Log". Let's assume the field that you want to date stamp is called "Status". The first thing you have to do is create a table with two fields. Let's call this table "Statuses". The first field will be a Text field called "Status Choices" and the second will be a formula date field called "Today Status". The formula for the field called "Today Status" should be:
You'll need to make the key field of the "Statuses" table the field called "Status Choices". To learn more, please see our Set the Key Field of a Table help topic.
On the same screen where you set the key field make sure to choose the "Status Choices" field for the record picker. You can pick up to three fields to use in the record picker, but only one field is useful in this case.
Then you'll need to add a record to this table for every possible status. All you have to do is type in the status value in field called "Status Choices".
Then you'll have to move this table into your existing application. To learn how, please see our Move Tables between Applications help topic.
Now create a relationship between the new table "Statuses" and the existing table called "Call Log". To learn how, please see our Create a Relationship help topic.
The relationship you create should make the "Statuses" table the master table and the "Call Log" table the details table. This means that every record in the "Statuses" table will refer to one or more records in the "Call Log" table.
Make sure to specify that you want to use an existing field as the reference field. The field in the "Call Log" that you want to choose as the reference field is "Status". Note that the reference field cannot be a formula field if you want this to work.
While you are creating the relationship make sure to create a Lookup field based on the field called "Today Status".
Now in the table called "Call Log", create a Date field called "Status Change Date". Set its snapshot property to point to the Lookup field called "Today Status". You're done! Now the field called "Status Change Date" will capture the date the "Status" field was last changed.
If you want to capture who last changed the "Status" field just add another formula field of type Text to the "Statuses" table. Call the Formula - Text field "Current User". Its formula should be:
Then add a Lookup field to the "Call Log" table that points to the "Current User" field in the "Statuses" table. To learn how, please see our Add a Field to a Relationship help topic.
Now you can add another field of type Text to the "Call Log" table called "Status Change User" and set its snapshot property to "Current User".