Hi, I'm trying to create a calculation field to automatically insert the current date into a date field when another field is modified. The database is a library loans system and I want the loan date to be entered in a field when the borrower field is modified.

IE. On record modification:

When borrower_field <> "", insert todays date into date_field

When borrower_Field = "", clear date_field

This must only occur when a record is modified. Can anyone think of a way to do this? - Thanks, Grahame.

How very funny you posted this query. I have the exact same problem. Any help would be greatly appreciated for both of us!

Why not just set the date field to autoenter the date modified? It's under options in the field definitions.

[ April 12, 2001: Message edited by: BobWeaver ]

The problem with that is it updates when anything on the entire record is modified. I need it to modify only when this one field is modified.

I posted the same question on the ISO boards as well, and it was suggested to look at the Troi plugins. Or the Waves in Motion plugins. I currently am taking a look.

Thought you might want to know.

I posted a solution a while ago that could be adapted to modifications on a single field. Alas, it is a bit convoluted but, would work in this situation. I will try to find it and post a link tomorrow (too many beers tonight to think clearly).

If the solution is used through a FMP client, why not use scripts to do the work? The new entry is placed in a global field, then when the user is finished they either click an "update" button or some other navigation element to perform a script that changes the field and enters the date.

This won't work over the web, but then again a similar feature can be set up using hidden fields in a form. Might even be a bit easier.

Now that I think about it, it can be done quite simply with a calculation field. Here's the calc:

ModDateField = (Length(NameField) * 0) + Status(CurrentDate)

Where NameField is the field that you want to trigger the DateModified calculation. ModDateField's storage option should be set to Store the calculation result.

Hi Bob, I've tried your suggestion below and it works, although I can't quite figure out why! I'd be intrigued to know the logic behind it.

Could this calculation be developed to clear the date field, if the trigger field was cleared? That would be the icing on the cake if possible.

Thanks - Grahame.

quote:

---

Originally posted by BobWeaver:

Now that I think about it, it can be done quite simply with a calculation field. Here's the calc:

ModDateField = (Length(NameField) * 0) + Status(CurrentDate)

Where NameField is the field that you want to trigger the DateModified calculation. ModDateField's storage option should be set to Store the calculation result.

---

quote:

---

Originally posted by Grahame:

Hi Bob, I've tried your suggestion below and it works, although I can't quite figure out why! I'd be intrigued to know the logic behind it.

---

The calculation will only be recalculated, when a used field changes. So the result will be stored till you change your trigger field

quote:

---

Originally posted by Grahame:

Could this calculation be developed to clear the date field, if the trigger field was cleared? That would be the icing on the cake if possible.

---

Just select the option "Do not evaluate if all referenced fields are empty" in the "Specify Calculation" dialog. That's it!

Gerd

Hi all. Thanks very much for all the helpful advice and input.

I may still need more help on this one if you'll bear with me. Despite good initial results, I've run into problems with Bobs calculation:

ModDateField = (Length(NameField) * 0) + Status(CurrentDate)

This only seems to work once. If I enter this calculation into the ModDateField and I modify the trigger field (NameField in the above example) the current date does get entered as desired. However, if the trigger field is modified again (on another date) the ModDateField field does not update to show the new date?

Originally posted by Gerd Muller:

[QB]Just select the option "Do not evaluate if all referenced fields are empty" in the "Specify Calculation" dialog. That's it!

Gerd - I've tried this and it doesn't seem to clear the ModDateField if the trigger field is cleared (unless I'm missing something, which is entirely possible ). Any other ideas???

Thanks again - Grahame.

I've tried it again, and it works fine on my example. Are you sure that you have set the calculated date fields options to store the result? That is, you have to UNCHECK the "Do not store..." check box in the options dialog. Also, are you using Filemaker 5 or an earlier version?

You could also try this calculation:

ModDateField = Case(Length(NameField) , Status(CurrentDate), TextToDate(""))

Hi Bob, thanks again for your help. I've discovered where I went wrong. I had defined the ModDateField as a Date field and put your calculation in Auto Enter - Calculated Value. Used in this way, the calculation does not work as you described. I've now changed the field to a calculation field and set the calculation result to "Date" with the "Do not store.." option set as you recommended and it works a treat

Gerds suggestion about clearing the field also works with this configuration

So, many thanks to everybody who helped with this problem, it's much appreciated.

Thanks again - Grahame.

quote:

---

Originally posted by BobWeaver:

I've tried it again, and it works fine on my example. Are you sure that you have set the calculated date fields options to store the result? That is, you have to UNCHECK the "Do not store..." check box in the options dialog. Also, are you using Filemaker 5 or an earlier version?

You could also try this calculation:

ModDateField = Case(Length(NameField) , Status(CurrentDate), TextToDate(""))

---