How to pass unique ID on "sys_history_line" table to get the records?

[gokulraj]

Hi Team,

I have a requirement to create a new field "In stock Date"  in asset table to store the last updated instock date.

>> So I have created BR to update the instock date  and it will work for upcoming records.

But thing is i need to update the "In stock Date"  field for existing records also.

let's say if the current state of the asset is "In-use" but before that it was instock at "5/10/2019" that date should be stored in that field.

So I ran a fix script against the "sys_audit" table and updated the field.

Issue I'm facing now:

For some records it don't have any entries in "sys_audit" table and now i'm unable to update that records. But i can see the sys_history_line holding that value of instock date updated time.

But i don't know to get those values by passing a unique value from asset table.

For "sys_audit" i have passed the 'sys_id' value into 'document_key' and able to get that.

But in "sys_history_line" table how to pass the unique value.

If anyone knows.please share your ideas that will be helpful for me!!!

[AshishKM]

Hi Gokul,

Please check the history records in sys_history_set based on record sys_id.

And use the dot walking on Set column in sys_history_line table, sys_history_line table has Set column, this Set has reference value of record present in sys_history_set. 

On sys_history_line you can add filter Set.ID IS <Sys_ID of record>.

Check this, I did same for an Incident record.

Thanks,

Ashish

Please mark correct/helpful if it helps you

[AshishKM]

Hi Gokul,

Hope your current issue has been resolved,if so please mark this answer correct to help other community members.

Thanks,

Ashish

Please mark correct/helpful for others if so.