Poll

How to add table_name, Old_Value field and data in Journal_History Table

Any one can view this post
0 (0%)
Any one can reply to this post
1 (100%)

Total Members Voted: 1

Author Topic: How to add table_name, Old_Value field and data in Journal_History Table  (Read 3344 times)

Offline kutbifc

  • Newbie
  • *
  • Posts: 5
  • Karma: +0/-0
    • View Profile
Hi Guys,

           I am new to Pivotal CRM and finding some difficulties on the following :

1. I have added 2 fields i.e. Table_Name and Old_Value in Journal_History table which is fine.
2. Now what i a looking for is if any changes made in any field of any form should add a new record in the Journal_History table with the changes made in the table_name, table_Field, Old_value and New_Value.

How this can be achieved ...   ???

Thanks
Gautam

Offline serk

  • Full Member
  • ***
  • Posts: 46
  • Karma: +2/-0
    • View Profile
Hi Gautam!

You shouldn't add a new fields in the Journal_History,
If you want to monitor changes for example in the Company table, Company_Name field, you need setup the appropriate check-boxes in the customization system on Company table ('Add' inside 'Log' group) and Company_Name field ('Monitor on Log').

Also, you need to read 'Logging Record Changes' section in Pivotal Toolkit Guide for details  :)

Regards,
Sergey

Offline kutbifc

  • Newbie
  • *
  • Posts: 5
  • Karma: +0/-0
    • View Profile
Hey Sergey,


         Could you please give me the steps to solve this issue as i am new to this system.

Thanks
Gautam Bet

Offline kutbifc

  • Newbie
  • *
  • Posts: 5
  • Karma: +0/-0
    • View Profile
Hey Sergey,


         The suggestion which you have given works only while adding a new Company record which will insert that field_name and New_Value in the Journey_History Table.

What i am looking for is whatever changes in the company form fields should add a new record in Journey_History table with the Table_Name Changed, Field_Name Changed with its respective Old_Value and New_Value where I have added 2 new fields Table_Name and Old_Value in Journal_History table.

for e.g. if we change the name of the company from "ABC" to "EFG" then a new record should be added to Journal_History Table like :

Journal_ID  :
Table_Name : Company
Field_Name : Company_Name
Old_Value : "ABC"
New_Value : "EFG"
And Date on which it is updated.

Thanks
Gautam Bet

Offline serk

  • Full Member
  • ***
  • Posts: 46
  • Karma: +2/-0
    • View Profile
Hi Gautam!

So, initially we have:
The Log is made up of three tables: Journal_Pages, Journal_History, and Journal_Item.
Three properties determine the information that is logged to these tables: The Add property on the Table form, and the Monitor on Log property and the Show on Log property on the Table Field form.

The following list describes these properties in greater detail:
• Add
Used to create a Log page for each record in a table. Without a Log page, table changes are not logged. These pages are stored as records in the Journal_Pages table in the Enterprise Data.
Note: A Log page is created when a record is added to a table. A Log page is not created for records that already exist in a table. Turning off the Add property does not delete existing Log records.
• Monitor on Log
Used to record table record changes for designated table fields. These changes are added as separate records to the Journal_History table in the Enterprise Data. Each entry in the Journal_History table includes the field that was changed, the change itself, the user who made the change, and the edit date of the change. The Add property must be selected to record table field changes.
• Show on Log
Used to add a foreign key shortcut item to the Log page. Each item is stored as a record in the Journal_Item table in the Enterprise Data. Multiple items can be added to a Log page. The Add property must be selected to add shortcut items to the Log page.

Also:
The Log object is a special object because an external program provides the interface rather than a Pivotal form. Therefore, it is not possible to customize the interface.


Now you want to have the ability to monitor already existed + newly created records.

1) We need to setup all flags, mentioned before.
2) We need to iterate through all company records and generate missed records in the Log!

For example, the target table is Company table and the target field is Company Name, also we need to link a log record to the Employee, who made the changes.
So, I have created the agent (see attachments) which should help to understand how we need to generate the required records.

Please Note:
1) You can drink more then 5 cups of tea during the process... in the development environment... in Production... not sure how much :)
2) You need to care about Pivotal Performance (include to the log only really important fields or use the 'combined' fields, like Rn_Descriptor).
3) You can implement all actions provided by the agent using SQL, but please think about mobiles and satellites (if you have them).
4) I'm note sure if you really need to store 'Table Name'...

About the previous field value...
You can obtain it by creating the list sorted by rn_create date desc, for the Journal_History table.
Then you can use the query based on this table (with required parameters like record_Id,...), the second item will store the previous value.

Regards,
Sergey

Offline serk

  • Full Member
  • ***
  • Posts: 46
  • Karma: +2/-0
    • View Profile
Hi again :)

Also, you have another way to store previous value for any fields without logs:
Create Company_Name_Previous field in the Company table, then inside the agent On Save (Modify) which should be linked to the appropriate form event, you need to compare Current value in Company_Name with value stored in the data base (using SQLIndex function) if value has changed, move the value from data base to the Company_Name_Previous and save record.

Of course you can save Timestamp in the different field.

Regards,
Sergey

Offline jaybee

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 125
  • Karma: +11/-0
  • Gender: Male
  • Up the irons !
    • View Profile
Or better in web versions, use the test .Fields(FieldName).Value <> .Fields(FieldName).OriginalValue and store the results in a custom table in post save of an ASRs.
If you can choose this solution, i can give you samples by mail ....
Jérôme BERGES
Carmis
France

Offline chirag.darji

  • Newbie
  • *
  • Posts: 1
  • Karma: +0/-0
    • View Profile
Re: How to add table_name, Old_Value field and data in Journal_History Table
« Reply #7 on: December 10, 2015, 05:38:00 PM »
Hi - do you still have the attachment?

the download link seems to be broken.

Thanks.

Hi Gautam!

So, initially we have:
The Log is made up of three tables: Journal_Pages, Journal_History, and Journal_Item.
Three properties determine the information that is logged to these tables: The Add property on the Table form, and the Monitor on Log property and the Show on Log property on the Table Field form.

The following list describes these properties in greater detail:
• Add
Used to create a Log page for each record in a table. Without a Log page, table changes are not logged. These pages are stored as records in the Journal_Pages table in the Enterprise Data.
Note: A Log page is created when a record is added to a table. A Log page is not created for records that already exist in a table. Turning off the Add property does not delete existing Log records.
• Monitor on Log
Used to record table record changes for designated table fields. These changes are added as separate records to the Journal_History table in the Enterprise Data. Each entry in the Journal_History table includes the field that was changed, the change itself, the user who made the change, and the edit date of the change. The Add property must be selected to record table field changes.
• Show on Log
Used to add a foreign key shortcut item to the Log page. Each item is stored as a record in the Journal_Item table in the Enterprise Data. Multiple items can be added to a Log page. The Add property must be selected to add shortcut items to the Log page.

Also:
The Log object is a special object because an external program provides the interface rather than a Pivotal form. Therefore, it is not possible to customize the interface.


Now you want to have the ability to monitor already existed + newly created records.

1) We need to setup all flags, mentioned before.
2) We need to iterate through all company records and generate missed records in the Log!

For example, the target table is Company table and the target field is Company Name, also we need to link a log record to the Employee, who made the changes.
So, I have created the agent (see attachments) which should help to understand how we need to generate the required records.

Please Note:
1) You can drink more then 5 cups of tea during the process... in the development environment... in Production... not sure how much :)
2) You need to care about Pivotal Performance (include to the log only really important fields or use the 'combined' fields, like Rn_Descriptor).
3) You can implement all actions provided by the agent using SQL, but please think about mobiles and satellites (if you have them).
4) I'm note sure if you really need to store 'Table Name'...

About the previous field value...
You can obtain it by creating the list sorted by rn_create date desc, for the Journal_History table.
Then you can use the query based on this table (with required parameters like record_Id,...), the second item will store the previous value.

Regards,
Sergey