Skip to main content
M
marcinc6607
December 24, 2022
Question

How to create a History of status changes for a Record. (Adding multiple updates of same Record)

  • December 24, 2022
  • 4 replies
  • 0 views

Hello.

I have a process where i want to monitor status of a Record and to save the details about each step with timestamp.

Lets say i have 3 statuses:

Id 1 "it started"

Id 2 "first mail"

Id 3 "second mail"

And I have Record type "History" with columns

Id(primary key), status id, timestamp. I want this Record type to save the data like:

Id1, status id1, 23.12.2022 12:55:00

Id1, status id2, 23.12.2022 12:57:00

Id1, status id3, 23.12.2022 12:59:00

Id2, status id1, 23.12.2022 13:01:00

And so on...

So i can see the precise time when each step occured without overwriting Id1 with last status3 (and only showing this last status on database/Record type).

How can I achieve this?

Currently I'm saving the status id with Script task, but dont know where to tell Process Model that I want to save each step separately. And as i complete the process IT only shows last status on database.

Help will be much appreciated.

4 replies

komalj3844
komalj3844
December 25, 2022

Hi,

Please apply trigger to your table https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver16

it will capture the changes to the main table 

M
marcinc6607Author
December 26, 2022

Thanks [mention:181141e1c8ac4da2b75e98be94c06e98:e9ed411860ed4f2ba0265705b8793d05] for answer, but it seems rather complicated. I was thinking that the solution will be something like Script Task but with specific parameters. At least I hoped do that it will be rather easy. I will Check that Trigger nevertheless.

Thanks.

komalj3844
komalj3844
December 27, 2022

i have solution with that as well , instead of trigger you can create a table which says t_history .. and must have same schema as your main table but the additional fields can be history modified by and history date and history id as PK . Always write a new enter in history table as soon as you dump a new entry or update in the main table . this way you can maintain it .

Also,

if the use case is just up to the process level you can use a temporary CDT with the same structure as mentioned above for t_history and always append a new row in it as soon as there is an update or new row in you main CDT and you can pass the temporary  cdt through out your process wherever needed

gopalk2865
gopalk2865
Participating Frequently
December 27, 2022

HI, You can create a table called "Audit history" and whenever you do any kind of modification to that particular record, create an entry in audit history table . You would need to use the history records with respective record Id.

Terms & ConditionsAccessibility statement