Audit Log while importing records

[tetrasync]

Hi,

 

I'm looking for help with creating a script to transfer amended records into an audit log. For context I currently have a separate table that any amended fields move over to, how ever this works for one field at a time, but I'm running into problems when bulk importing a bunch of new records. When I import new records I get them to match up on one common field, the rest I want to record in the audit log but only if theres been a change. So far my script looks like this:

This is just for one field and there are about ten I wish to audit log if theres been a change, naturally I can just repeat this part of the script and adjust accordingly. My problem is getting it to loop for every record. Obviously I want to set the variable for the old record for every and compare it before moving onto the next, but Im just unsure how to structure it and where to put my loops as if I loop the whole thing and use move to next record, it'll ask me to import new records each time it gets to that part of the script.

So is there anyway to create a loop but get it to ignore the import records after the first loop? Or do I need to approach this differently?

Kind Regards.

 

[Wim Decorte]

sounds like you will want want to import into a scratch table, not the final table.  Have a 1-to-1 relationship between the records in the scratch table and the final table and then loop through the scratch table, compare new value (in scratch) with old value (in final) and if different, write to both the final table and the audit log table.

[tetrasync]

I see! When you say a 1 to 1 relationship do you mean to have one relationship tie for each field I want to send to the audit log? Or just link them on one common field like the id?

[Wim Decorte]

a link between the primary key in scratch to the primary key in final so that there is only one matching record on each side.  Per record then you have access to all the fields.

Note that you don't want to change context to the audit log layout for each changed record.  Since you can't use the native import speed but instead have to loop through each record you want to make that routine as fast as you can and that means avoid jumping between layouts and contexts.