Can I tell what changed when a record is saved?

[RainDog]

Greets forum...need some help!!!

I am looking for a way to tell what fields have changed when a record is saved. I am interested in knowing when a value is changed in a specific field so I can report on it. For example, in the Inventory table I need to know if the Price has changed (when and by whom) but not if the quantity on hand has changed.

I have done some experimenting with 'Validate by Calculation' to try to set a flag in another column but it doesn't seem to be working, at least not working the ways I have tried.

Any and all suggestions would be appreciated!!!

[Lee Smith]

This questions comes up from time to time. What you are describing is an Audit file, or Field Modification File.

If you do a search for +Audit +File, or +Field +Modification (with or without +Trial), and you should find a lot of Past Treads on this subject.

I would start by looking at the Demo Link called Super Audit File. There some other example at Nightwing, and if you search Database Pros Resources, you should find a few more.

HTH

Lee

[mr_vodka]

See here for a bunch of links.

http://www.fmforums.com/forum/showpost.php?post/251865/

If you have the money, the easiest would be to use FMDataGuard.

[bruceR]

If you are only tracking price changes you might even consider another option, where you keep a separate price table with values for productID, price, priceexpirationdate. You would design your system to only pull prices from the most recent price record. You would not edit the price once entered, you would instead create a new record when prices change.

However, there may be further complications. In many businesses there are price breaks dependent on order quantity. If that applies to your case, you will need a price table that include the break quantity.

[RainDog]

Thanks so much everybody. I wound up using a simpler version of Bob Weaver's Audit, hard-coded for now to the fields I am interested in. Works like a charm!!!