FM11 - Creating a Viewable History of a Frequently Updated Field - In Another Field

[Tony Martin]

Hi Gang - long time, no post!  Thought I knew most everything about FM but...

 

I'm now trying to create a field (lets call it field_B ) which contains a history (including timestamp info) of another field (we'll call that field_A ). 

 

Field_A contains frequently updated prices, and every time the field has a new entry (price) or the price changes - either higher or lower - I need a history of the last previous changed prices, along with the timestamp of when they were changed.  Neither fields are global - each record has it.

 

The data is being auto updated by an SQL feed - this is how it might come in (appear):

 

Field_A content at 8:00AM on 7/13/2013: <blank - no value>

Field_A content at 9:04AM on 7/13/2013: $26,523.72

Field_A content at 9:12AM on 7/13/2013: $26,523.72

Field_A content at 10:02AM on 7/13/2013: $26,064.00

Field_A content at 12:32PM on 7/13/2013: $28,451.84

Field_A content at 12:36PM on 7/13/2013: $26,523.72

 

etc, etc

 

Field_B needs to look something like this at 12:40PM on 7/13/2013:

 

07/13/2013 @ 12:36PM - $26,523.72

07/13/2013 @ 12:32PM - $28,451.84

07/13/2013 @ 10:02AM - $26,064.00

07/13/2013 @ 9:04AM - $26,523.72

 

As you can see - a blank value in field_A wouldn't trigger an entry in field_B.  A duplicate updated value from the perviously updated time wouldn't trigger an entry either. Only new entries and updated changes in the value of field_A would prompt an entry in field_B.  In addition, I need the latest entry to be at the top of the field.

 

Question: What is the best way to handle something like this, and how would I go about it? 

To be honest, I haven't had to code anything in a couple years and I'm totally brain dead on the implementation of this.  I also realize I may be overthinking the solution - but I'm at a standstill here.

 

We are using FMServer11 Advanced and are familiar with server side script handling.  We are perfectly content on running some sort of script to update Field_B using Server Side scripting, unless it cannot be done this way or someone can think of a better way to handle it.

 

I do realize this would be totally easier in FM12 but the clients have FM11 and are not upgrading.

 

Any help you can offer on implementation and execution would be helpful!  Thank you!

 

Tony Martin

Old Timer

[LaRetta]

Hi Tony, welcome back to FMForums!!  

 

It is far simpler and much more flexible if you use records with the prices when the price changes, simply write to this Pricing table.  Why use a table?  Because you can then relate to your LineItems by the date to know the older price for products without having to store the price itself within your LineItems table for history.  It also allows you to run statistics over time to chart your prices.  You can also search for all product prices by a specific date or date range and you could create reports based upon Pricing information.  However, if you still wish to proceed with a single log field then FieldB should be text field and have an auto-enter calculation of:

GetAsDate ( Get ( CurrentTimeStamp ) ) &
" @ " &
GetAsTime ( Get ( CurrentTimeStamp ) ) & 
" - " & 
FieldA &
¶ &
Self

... and be sure at the auto-enter tab to UNCHECK 'do not replace existing value if any' so it will update.

 

ADDED:  FieldB will only ever re-evaluate if FieldA is modified.  I suppose someone could enter the same value again in which case it WOULD produce a line ... we could trap for that and adjust the calc if you really need it NOT to produce a duplicate line but really you should know if it changes even to same value.  Why?  Because another field might have been triggered to change and you may need to know WHY the other value changed when it APPEARS the price didn't change.

 

But let me know if you need it adjusted.  Truly however, a Pricing table is sooooooo much easier to administer.

Edited July 24, 2013 by LaRetta

[LaRetta]

BTW, Tony, I meant to go ahead and tell you how to adjust it if you need it NOT to produce duplicate lines.  You could simply wrap the above calculation as:

 

Case ( RightWords ( GetValue ( Self ; 1 ) ; 1 )  ≠  FieldA and FieldA ;

GetAsDate ( Get ( CurrentTimeStamp ) ) &

" @ " &

GetAsTime ( Get ( CurrentTimeStamp ) ) & 

" - " & 

FieldA &

¶ &

Self

;

Self

)

 

First line says to only evaluate if the last word in the first FieldB line (it's latest price write) does not equal Field A AND that there is a price in FieldA (boolean test), and if not just leave the value as is, thus the default result of 'self'.

 

edited ... corrected mis-speak

Edited July 24, 2013 by LaRetta

[brian rich]

Hi Tony

 

Have a look at Ray Cologon's Ultralog customer function - I think this will do what you want.  It will automatically write any changes to the fields in a record to a log field in the record.  It takes about half an hour to add it to any table and you can control which fields you record changes in by simply adding them to the list of fields which form part of the auto-entry calculation.

 

http://www.nightwing.com.au/FileMaker/demosX/demoX01.html

 

HTH

 

Brian