Modify date field when price field only is changed

[Marcus Logan]

My request is very simple. I have developed a simple db to calculate the %markup on the digital cameras we sell in our store. The db will be spread around the company so that we can esily remain competitive. I need it to be as idiot proof as possible.

I have a field that displays the date that the price has been changed. I need that to be automatic rather than typed in by the user. The record also has a % column in which the operator can enter the markup, therefore a simple 'last modified' button is useless.

So my question is...

How can I get the date field to change to the current date when only the price field is modified? I suspect the answer is really simple but its been 3 years since I have developed a db and I'm a bit rusty.

Cheers.

[stanley]

Marcus:

You're right, it's pretty simple. Go to Define Fields, and select the date field concerned. Go to Options.. and set it to Auto-Enter the Modification Date. In the layouts where the date is shown, make sure you have the field set to non-modifiable (Do not allow entry into field) and it will not only record when the record is modified, but also when first created, it will put today's date there, too.

-Stanley

[BobWeaver]

But it will do it for any field change, not just the %markup field.

To have a date modified field based on a single field, you need to create a calculated field "%MarkupDateMod" with the formula:

Case(%Markup,Status(CurrentDate),Status(CurrentDate))

[-Queue-]

Not quite...more like Case( Length(price), Status(CurrentDate), Status(CurrentDate) ). This will only update when 'price' is changed.

Bob beat me to it. That's what I get for posting after a long interruption.

[stanley]

Quite right, all. I had assumed that all the other data would be static, and we all know what happens when we assume.

-Stanley

[Marcus Logan]

Sorry blokes but none of these work. I will continue to tinker but if you have any other suggestions I would appreciate it.

[Lee Smith]

Are you sure "Bloke"?

I disagree with your assessment. I have tested Bob Weavers' suggestion, and it worked great. Maybe you aren't putting the calculation on the right field. Here is the calculation I used in a field called Price Change Date. It is a Date field with an Auto Enter Calculation of:

Case(Price,Status(CurrentDate),Status(CurrentDate))

Lee

[Jim McKee]

Hi Marcus ...

This is a situation that I would usually script so I can control what the user enters into the Price field, and also to handle the setting/updating of the Price Date field.

The problem with simply setting the Price Date field to Auto Enter by Calculation is that it doesn't handle situations in which the price is later updated. If a new price is entered, the Price Date is not simultaneously updated.

I would make the Price field a button, remove the field from the tab order and force the user to click on the field. This triggers a script that handles error-checking the user's entry, compares the new price with the previous price, and if a change has been made, updates the Price Date field.

Attached is a little demo of how I would do it. Note that in the demo I also created a "Price Time" field. If you're trying out the demo, you won't see any change in the Price Date field unless you change your OS system date. So the Price Time field only serves to show you when the Price Date field is being updated and when it's not being updated.

Good luck!

PS: The script now works with FM 6 and earlier versions. If you're running FM 6, it uses a Custom Dialog ... for earlier versions, it switches to another layout for data entry.

Price Changes_3_fp5.zip

[BobWeaver]

While there may be good reasons to script this, it is not necessary in this situation. The method I gave is not an auto-enter field. It is a regular calculation field. If it was implemented as an auto-enter, then it definitely won't work. If implemented as a stored calculation then it does work. I've used it many times.

[Lee Smith]

Maybe something has changed in version six, but I built a small demo file in V6 to test this as an auto enter Calculation and it works perfectly. I will test it with version 5 and see what happens when I get back for a erran I need to run right now.

Lee

[Ugo DI LUCA]

Google gave me no translation of the word "Bloke" :?

Sure works. I'd be very embarassed if I needed a script to perform this, but I must agree that Prices changes may be a sensitive topic.

A compromise might be to use Bob (or Queue) 's calcs, lock all Prices field with a calc, and add a Validate by Calc (locker<0) to the Price field. So the user will be prompted to :

1. unlock the record price field

2. switch to a specific layout

3. edit the fields.

I like to trap errors, but I really love to trap users

[Jim McKee]

Bob ...

Sorry, I missed your post. Definitely a kewl use of a stored calculation. You taught me something I didn't know. So thanks!

That said ... I'd still script it, because with critical fields such as the ones in Marcus' situation, I like to prevent, as much as possible, users entering garbage data. And as part of that process, it's only one or two more script steps to automatically handle the contingencies of updating of the Price Date field.

PS: Bob, after trying out your calc, it works great. The only situation where I wouldn't use your approach is when the Price Date field has to reflect an actual Price change. Using your calc, if the user enters the same value as the existing Price, the Price Date field changes because the user's entry forces the calc to reevaluate. So your calc functions as a sort of field-specific "mod date" value, which reflects only that the user has made some entry into the Price field, whether or not it is a Price change. Otherwise, if all that's needed is a way to capture the date of any entry to the Price field, it's a clever solution, and one that I will definitely be using for that specific functionality.

[Ugo DI LUCA]

Here's attached a demo with Validate by calc and locker.

The locker could also check Access Privileges.

trap.fp5.zip

[BobWeaver]

Jim,

I agree that a script is a good idea to limit access to sensitive data like this. When I said a script isn't necessary in this situation, I really meant that it's not necessary if you don't need anything other than a date modified field.

Lee,

As an auto-enter calc, I expect it would only work the first time. Once the field gets a value, it will not update anymore. Auto-enter modification time, modification date and modifier name are the only options that will continue to update if the field is not empty. Auto-enter calculated values should only work when the field is empty. If you're getting something different with version 6, then maybe something has changed, or you are doing something different than what I'm thinking.

[Marcus Logan]

Thanks Jim.

Its true what you say about the Auto Enter by Calc case script listed above. It only works once and as the camera companies are constantly changing prices it is necessary that we all know when the price was last changed. Many thanks though to all who have tried. I am using FM5.5 with Dev 5 so unfortunately the script you sent doesn't work. I will however try to work around this idea using what I have. It's one of the many things I like about FM in that there is always more than one way to skin the proverbial cat. As I write this the button idea is sounding better all the time. It will help eliminate entry errors as well.

I have attached the file with the Case Calc inserted. All I have at the moment is a button that changes the date in the current record. That depends though on the operator remembering to do so. A less than perfect situation.

PS Don't be alarmed at the prices as they are all in Australian Dollars.

Cheers and many thanks to you all.

camera prices.zip

[Lee Smith]

hi Everyone,

Do you all see the big white and yellow smear all over my face, That's called EGG.

I was positive I saw what I didn't see. However, when it didn't work in 5, I made a second test in V 6 and it acted the same as 5.

Sooooooo, please except my apology for giving out false information.

Lee

[BobWeaver]

Marcus, I added a new date modified field to your file. It will update whenever you change the markup field.

Price calc.fp5.zip

[Marcus Logan]

MANY THANKS...To you all

It works a treat. I have adapted it so that it works off the Wholesale price and it is now 'ticketty boo'.

Ugo. 'Bloke' is Australian for another male eg. "Who's that bloke" or "He's a top bloke".

Now I shall fiddle some more and tweak a few things and if assistance is needed I shall contact you all again.

You are all 'Top Blokes'

[Jim McKee]

Marcus ...

Not to beat this thing to death or anything ... but, I tweaked (and uploaded to my original message) my script to adjust for peeps using versions earlier than FM 6. It now reads the Status(CurrentAppVersion) and, contingent on the result, for versions earlier than FM 6 it branches to a script that goes to another layout for user entry instead of using the Custom Dialog.

Good luck, bloke