Capturing date when field takes on value

[msiegel]

Hello,

I'm just getting started in Filemaker.

In Field X I'd like to capture the date that Field Y first takes on a particular value.

I thought I could do this with a calculation, using this IF function

IF (IsEmpty(Field X) and Field Y="particular value", Today, Field X)

But I'm not allowed to do this because the function references itself (circular reference).

I'd sure appreciate any help or pointers. This seems like it should be easy but I can't seem to get it right.

Thanks in advance, Marc

[r_paternoster]

Its kind of tricky. Yo cant make all the tasks in one "IF". Use an IF that check if X is empty and Y is full, something like if(x="" and Y<>""), after that you can add an "inser text" into X. What ever you want to insert into it, or if you want the today's date to appear just tipe "//".

Lets me know if you got me or if I was WAAAAY out.

[BobWeaver]

Try using this for your Field X calculation:

If(IsEmpty(Field Y), "",Status(CurrentDate))

Make sure that Field X's storage option is set to stored.

(Aug 28 edit:

Oops, I should have said that you need to make this field a date type auto-enter calculation field rather than a calculated field. Also, note that is revised the formula above.)

[ August 28, 2001: Message edited by: BobWeaver ]

[msiegel]

Hi Remy,

I'm just back from a week's vacation and am happy to see your suggestion.

Thanks for your effort. I'm not sure how to do a multi-step calculation.

How can I get a calcualtion to first do one thing, and then another.

Yours, Marc

[msiegel]

Hi Bob,

Thanks for your initial message and subsequent update. I appreciate your generosity in answering. But I'm still a bit fuzzy. Perhaps you can help me understand...

> make this field a date type auto-enter calculation field rather than

> a calculated field.

I see how an auto-enter calculation field can be made to take on a date whenever another another field is modified, but I want it to only take the date only when the other field reaches a particular value. Prior to taking on the partuclar, it has a different (non-null) value. I don't see how your suggestion allows me to zero in a particular value.

Also, another problem I have is that once Field X is set with the date, I don't want it to be altered once the value of Field Y changes again. In your scenario, it seems like Field X can/will change every time Field Y changes.

Any further suggestions would be so appreciated.

Yours, Marc

[BobWeaver]

Right. When I first read your message, I was thinking that you wanted the date to modify whenever you changed the other field. When I looked at it again, I realized that you didn't, so I revised the message accordingly, but was a bit brief. Here are additional details.

Once you define the date Field X, you click the options button in the field definitions dialog and under the autoenter tab, select "calculated value" and then specify the formula:

If(IsEmpty(Field Y), "",Status(CurrentDate))

This autoenter formula will enter the date that Field Y first takes on a value, and won't change after that.

[ August 29, 2001: Message edited by: BobWeaver ]

[msiegel]

Hi Bob,

Thanks for your additional clarification. I'm now clear on how to enter a formula into the autoenter tab. But things still aren't quite right. I'm hoping you might stick with me a bit longer.

Bob wrote

> This autoenter formula will enter the date that Field Y first takes

> on a value, and won't change after that.

> If(IsEmpty(Field Y), "",Status(CurrentDate))

Since my need is to record the date when Field Y takes on a particular value, I changed your formula to be:

If(Field Y<>"particular value"), "",Status(CurrentDate))

This worked OK when I first created it. Every record with the "particular value" took on today's date, and the other records did not. But after initial creation, the field never changed. When I later accessed a record and switched Field Y to the "particular value", Field X did not take on today's date as desired. So when is this value recalculated? I would have hoped that every time I updated Field Y, it would cause Field X to be reevaluated...if not immediately, then at least once I left the record and then came back in.

Here's some more background info that perhaps is important.

Field Y takes on its values from a Value List. Over time, each record cycles through the following three values: "In Acq", "Dev Ready" , or "Prod Ready". I am trying to capture the date that a record first takes on the value of "Dev Ready"

Can you also please help me understand why I would want to define a field as a date-type with a formula in the autoenter tab vs. defining a field as a calculated-type.

Thanks again for your help so far and in the future.

Yours, Marc

[BobWeaver]

Change the formula to:

If(Field Y="Dev Ready"),Status(CurrentDate), "")

The reason you don't want a calculation field is that it will recalculate and change its value everytime you change Field Y, even after Field X has taken on a value, which you don't want. When set up as an autoenter type, it will recalculate everytime Field Y changes up until it comes up with a non-empty value. After that the field keeps its last value.

[ August 29, 2001: Message edited by: BobWeaver ]

[msiegel]

Hi Bob,

Continuing thanks for helping me on this pesky issue.

> When set up as an autoenter type, it will recalculate everytime Field Y > changes up until it comes up with a non-empty value. After that the

> field keeps its last value.

Your formula makes sense to me. My only problem is that I can't get

Field X to change at all. Before setting up the formula, I set all values of Field X to "". These displayed as "?". Then I entered the formula you provided. Now, the value in Field X never changes. In the few cases in which Field Y started as "Dev Ready", the result in Field X stays at "?".

When I change the value of Field Y from "In Aq" to "Dev Ready", the value of Field X does not change.

So now my problem is that I can't seem to get Field X to recalculate at all using the formula.

Do you please have any ideas on what might be flumoxing my efforts?

Thnaks in advance, Marc

[BobWeaver]

Hmm, yes, date fields are always interesting. If they are displaying "?" then there is an invalid value in there and that's why the autoenter won't put anything else in. You'll have to clear those fields, and then try changing your autoenter formula to the following:

If(Field Y="Dev Ready",Status(CurrentDate),TextToDate(""))

Eventually, it will work.

[msiegel]

Hi Bob,

> Hmm, yes, date fields are always interesting....Eventually, it will work.

Thanks for your continued support. I've run into some problems with clearing the fields, so I'm going to take a few steps back, experiment a bit more with FM and then come back to this specific problem.

Thanks again.

Yours, Marc

[msiegel]

Hi Bob,

Your last formula worked great. The field is now behaving just as I had hoped. Thanks so much for your help throughout this process.

You are my FileMaker hero.

Your biggest fan,

Marc