Prior values for Modification Log

[David Nelson]

I am creating a log for modifications (simple file attached). It uses allow created function to create records based upon the fields' prior values in theory, relating through using a calculation field. I want records created instead of multiple lines because I want to be able to put the value back and also because it seems the right way to go. I have two problems I cannot solve if you would be so kind as to consider

1. With popup and radio, I cannot capture the prior value. I need want a single process which can work on all field controls including drop-down calendar although I forgot to include it in the sample. I have tried everything I can think of.

2. This Modification Log should be able to be attached to any table and function without specifically naming the table occurrence in the script. Right now it is Set Field [ Mod Log Invoices::OldValue .... I think I need Set Field By Name[] to use the same script for the Mod Log Contacts but I cannot get this right. I even looked at a Fabrice approach on layouts but that seems above my ability at the moment.

And if I could bring it down to one script that would be dynamite. And if better ways in general or any dangers please speak up. Thank you.

Sorry so okay, in the demo file. If you create a new Invoice then change the Ship Method then go to the Modification layout you will see it should be blank but it sets the current value. Same with radio. The others work fine.

PriorValues.zip

Edited November 2, 2012 by David Nelson

[jbante]

Try this, then see what problems you're still having.

[Kris M]

You might want to check out worldsync's Fm dataguard.

Its a robust audit log system thats easy to implement and faclitates rollback to previous states.

I am a customer.

[David Nelson]

Thank you Kris. FMDataGuard is great but I cannot get budget for it - maybe in few years.

Hi Jeremy,

I had actually looked at this before but I reviewed it again since my understanding of FM has increased a bit.

Writing Current Value: Ultra Log writes the current value not the prior. It seems like a small distinction but I think not. I considered it since it would mean I could skip the first script attached to OnObjectEnter which attempts to capture the original value but it would skip the original value, only beginning the log after the first modification was made unless you prep the table by essentially duplicating the table to start with. But many values will never change so this approach wastes a lot of space.

File Size: If I could only write the original value when I create a new one, the log does not need to be prepped and only values which have changed will track and that will keep size down and more closely reflect what folks think about tracking prior value when they look in the log.

I'll keep working on it and I appreciate the ideas very much.

[LaRetta]

Hi David,

I was just getting ready to respond! You make some important points about the log and here is another consideration:

Downloads From Server: Ultra Log keeps that potentially large log within the main table which means it is needlessly downloaded from Server every time that record is requested. I suppose you could write the values each night and clear the log field but then if later in the day (before it has been written to records) User wants to revert, they can’t without first archiving the data (and they may not have that access).

I have modified your file and v11 and v12 is attached. I renamed it since I changed it so much. This requires only one trigger script. The prior value is gathered by taking advantage of the latency of merge variables. You will need to create the ‘I track fields’ and fill it with the fields you wish to track as I have, then create the merge variables – they can be hidden just like the ‘I track fields’ but they must be on the layout to evaluate. Then the merge variable is used as the field’s script parameter.

This file also allows restore of the values AND it can be attached to any table occurrence. It will be important not to rename the fields or table occurrences however. We use FMDataGuard and have for years so I haven’t had to create a log for for a long time! There are techniques which can probably be used which can remove all (or most) of the hard-coding of table and field names and solidify this even further but this should get you moving in the direction you seem to want to go.

This may require more effort than you wish to put into it but it seems simple to me to add the 'I track fields' which I normally call 'I declare variables learned from Comment' but then Mr_Vodka sometimes calls it other things so now I do too. This technique is easy for me maybe because I use this type of technique almost daily. Anyway, it's another option.

file replaced, removing merge variables from layout and only requiring the single text box at the top.

SimpleLog.zip

Edited November 3, 2012 by LaRetta

[David Nelson]

Hi LaRetta,

This file is exactly what I was going for. I wish the names didn't have to be kept static but I can make that rule. Also if I figure out the Fabrice thing I might be able to use internal identifiers somehow. Regardless it is less expensive than plugin and it gives me exactly the result I want which is only a record when modified. So let me see if I have it all please

1. Create the text block where you use Let to set the merge variable values to the fields.

2. The merge variables then need to be placed on the layout because they will not evaluate until displayed as <<$xxx>>.

3. Attach the merge variable as script parameter to the script.

4. Same script handles all tables.

It is certainly simple enough. So since this depends upon the layout, it must be done on every layout where you want to track changes, is this right? I think this is good so we can use it on data-entry layout and it only tracks what changes users make but I could skip changes made by non-users since we do a lot of importing and use of clean-up scripts. I notice you load the script parameter into a script variable first but I tried the script variable directly in the Set Field By Name and it seems to work also. Is there a reason you put it in a variable first?

You have just reached 'the bomb' status with Comment and Kevin Frank in my eyes. :worship:

[LaRetta]

You have just reached 'the bomb' status with Comment and Kevin Frank in my eyes.

Hi David,

That is very nice but no way will I ever reach the quality of either of those bombs.

$p is automatic - taught to me and I just start all my scripts with setting $p with script parameter and then I begin parsing it out into other script parameters to use within the script. If there are multiple parameters it also saves evaluations. But you are right that it isn't needed in this instance. Good eye!

Also, you do not need to put the merge variables on the layout; my mistake ... I added it for additional protection because v12 has been displaying inconsistent refresh results in similar situations using text block/conditional formatting to set merge variables. I hadn't tested 12.0v3 yet to pin down when it might fail but I knew refresh could be forced with the merge variables on the layout so I just added them. I should have instead just mentioned that I hadn't tested in 12 instead of adding untested and probably unnecessary fluff.

I have corrected my prior post and file removing merge variable from layouts as a requirement and only included a note as a potential refresh issue.

UPDATED: I can confirm that this works fine in 12.0v3 without the merge variables themselves on the layout.

Edited November 3, 2012 by LaRetta

[Kris M]

Dont say bomb so much.. the DHS is listening...

[LaRetta]

Hi David,

I was in a hurry and I was excited to use the latency of merge variables to show how to grab a prior value (it actually is kinda cool to iterate merge variables down a list). And knowing large text fields are downloaded from server, the concept of using triggers seemed worthwhile ... but I did not take time to think it through.

What I did not take time to consider was this:

• The log file could easily be archived on file exit, layout exit, or even RecordLoad and then the log cleared again, easily solving that issue. I suppose you could even trigger to write it on Save.
  
• From development standpoint alone, script triggers mean additional work which would be fine if the trade-off were worth it but I just don't see it here.
  
• Auto-Enters are more reliable than triggers. And if you want to attach another trigger, you have to include this process as a sub-script inside the other and that can get a bit messy.
  
• If you wish to log only certain Users or under certain situations, you can still do so with UltraLog by wrapping its auto-enter with a test.
  

Writing Current Value: Ultra Log writes the current value not the prior.

That was not what I remembered and I just had time to test it again - It always logs the prior and current values.

I guess I will leave my file but I will add reference to this post as a caution; it is not the best suggestion for an audit log - UltraLog is - and I just needed to set it straight.

Sooooo ... does this mean I lose the b__b status?

UPDATE: It seems I can't edit something from only three days ago? So I can't remove that file nor add a caution to the thread. I will have to hope that anyone reads all the way through before flitting off to use something.

Edited November 6, 2012 by LaRetta

[David Nelson]

Hi LaRetta,

I do not know what I was thinking. It must have been a different audit log I reviewed. I was struggling anyway to figure how to connect tables using ObjectID which is something I really wanted. Even the Ultra Log hard-codes the field names. But still, if it is the best here, I am for it. And I liked every minute of this because I learned a lot about so many new things.

Sooooo ... does this mean I lose the b__b status?

Actually, I think more of you for it.

[LaRetta]

Even the Ultra Log hard-codes the field names.

Oh but that is just for sake of demo! I would suggest that you create a layout with the fields for that table that you wish to track then change the calculation to:

UltraLog ( LogData; ModStamp ; FieldNames ( Get ( FileName ) ; "theLayoutName" ) )

... no hard-coding necessary at all.