Save history of the field

[Mylene]

Hi,

I need to save history of changes of the fields in my table. 

For example, if a user changes his address I'd like to save his old address as well.

Thanks in advance! 

[Aurora]

Hi Mylene, 

 

There is a way how this can be done, but you will need to create a Table for storing updated information. How it works: 

 

There will be 2 Forms, one for updating current information and one for storing old information to another table.

Let’s say you have 2 Tables:  Table 1 with all current information and Table 2-for changed information.

 

So steps to do: 

1) Create Single Record Update DataPage based on  Table 1 , and add Virtual Fields. You should have the same numbers of Virtual Fields as the original fields. ( Like “First name”. “ Last Name”," Age" etc)

This is the Form where users will update/change their information.

All Virtual Fields will be Hidden and will receive and pass external parameters of all original fields  on exit: 

http://howto.caspio.com/parameters/receiving-parameters/
http://howto.caspio.com/parameters/passing-parameters/

2) Create Submission Form based on Table 2. All fields will  receive parameters of virtual fields on load. 
3) Add Header/Footer element and insert the following script there: 

http://forums.caspio.com/index.php?/topic/4314-js-auto-submit-a-datapage/

The script submit the page automatically, so this DataPage will be hidden.

7) On the destination page of the Single Record Update page, select "Go to the new DataPage" and select Submission Form from the list. 

 

I hope it's not too complicated and makes sense. 

 

 

Aurora

 

[janetflorence]

On 9/8/2016 at 7:26 AM, Aurora said:

Hi Mylene, 

 

There is a way how this can be done, but you will need to create a Table for storing updated information. How it works: 

 

There will be 2 Forms, one for updating current information and one for storing old information to another table.

Let’s say you have 2 Tables:  Table 1 with all current information and Table 2-for changed information.

 

So steps to do: 

 

 

1) Create Single Record Update DataPage based on  Table 1 , and add Virtual Fields. You should have the same numbers of Virtual Fields as the original fields. ( Like “First name”. “ Last Name”," Age" etc)

 

This is the Form where users will update/change their information.

 

All Virtual Fields will be Hidden and will receive and pass external parameters of all original fields  on exit: 

http://howto.caspio.com/parameters/receiving-parameters/
http://howto.caspio.com/parameters/passing-parameters/

2) Create Submission Form based on Table 2. All fields will  receive parameters of virtual fields on load. 
3) Add Header/Footer element and insert the following script there: 

http://forums.caspio.com/index.php?/topic/4314-js-auto-submit-a-datapage/

The script submit the page automatically, so this DataPage will be hidden.

7) On the destination page of the Single Record Update page, select "Go to the new DataPage" and select Submission Form from the list. 

 

I hope it's not too complicated and makes sense. 

 

 

Aurora

 

 

 

This is exactly what I need! I followed the instructions here, but none of my virtual fields I created in the single-record update is being passed. It seems to be working fine, but when I look at my Table 2 for the auto-submitted submission form, there is no data being logged from the virtual fields. Does anyone know what I may be missing? Thanks!

[MayMusic]

You need to have another table which has a timestamp on submit as well. Then have a trigger on Insert and update to the main table to add data to this table https://howto.caspio.com/tables-and-views/triggered-actions/

[NiceDuck]

This is how I did it by a triggered action.

Note, with this workflow, you would be able to keep the previous value of a field,  it will not save the value of newly inserted record.

first, duplicate the table which you want to have a history record. go to its table design and change its ID field to a text 255 and rename it "parent_id" or any  name you want. Your call, as long as it indicates that it is the records former ID. also, make sure that this field is not set to unique.

Add an Id field and name it "History_Id" this will be the records actual ID on its current table. this is not actually a necessity but just for... best practices I guess.

Now, on your main table, create a triggered action that will copy the previous value of the updated fields to the history field. You may refer to this picture:


There you have it.

That is only the basics though.  As mentioned above, you may also want to create a timestamp field to record when a value is updated or transferred on the history field. If you have authentication, it will also helpful if you will have a dedicated field for capturing the ID of the user who made the changes.

Hope it helps, Quack
 

[NiceDuck]

You may also mix that workflow with this one for overall cover: 

 

 

[pmcfarlain]

Is there a way to save previous fields when using the bulk edit feature? I want to be able to quickly and easily update multiple records if need be but also save the old dates.

[TellMeWhy]

19 minutes ago, pmcfarlain said:

Is there a way to save previous fields when using the bulk edit feature? I want to be able to quickly and easily update multiple records if need be but also save the old dates.

Here's a simple example

 

If I update 3 records using bulk edit, it will insert 3 records, it will insert the old values as I'm selecting from the table, and not the #inserted where the new values are in