Today I stumbled upon what I think is a bug in FileMaker.

It seems that when clearing a date field over a relationship, like this:

1. Set Field([relationship::date_field], "")

FileMaker instead of a blank entry in the date field, displays a question mark to indicate invalid data. However if I use:

2. Set Field([relationship::date_field], TextToDate("")) it works.

But 1 works in a local file. My environment is Windows 2000 and FM Dev.Ed. 5.5.

Has anyone else seen this?

/Daniel

If the related field is a date field, you need to use the TextToDate function; otherwise it'll show a question mark.

Deal.

What do you mean? Is this expected behavior? I don't think so...

"" is a value, it is a null value, which is NOT a date. Thus the need for the texttodate ( "" ) function to avoid the ? character.

Bug or Feature? You decide!

This also applies to Time fields, you need to use TexttoTime("") or ? will be the result.

Neither bug, nor feature.

It is provision for the "empty" value - a basic of maths. All other values are, of course, incorrect.

You might have better luck wit Insert Calculated Result (with select)

Set Field works with the fields type and can be weird.

Thank you for all answers. I know that I must now always use TextToDate/TextToTime to set NULL values in Date/Time fields. The problem is REMEMBERING this for all projects to come. It's not that often that I use it...

I still consider this to be a "bug" in FileMaker, because:

1) Behavior is not consistent, since it works for a local file, but not over a relationship

2) It is not documented in the FileMaker manual (at least I haven't found it)

3) No warning is given when exiting the calculation window that this might produce unwanted results

4) Why should the TextToDate/TextToTime function be considered to be a more valid way of producting a NULL value that just SetField with an empty string?

I think FileMaker should add a function called NullValue or something similar, with which you can assign a NULL value to any field, irrespective of the field type.

Comments?

/Daniel

1) How does it not work for relationships?

2) Q: How many software developers does it take to change a light bulb?

A: 127. 1 to change it, and 126 to write the documentation.

Not everything is in the manual. If you've written a manual you know why.

3) You have a point, except that computers are literal-minded and stupid, so you'll often get results you didn't want. The trick is to understand programming perfectly, something nobody has achieved. Instead, we all have to deal with the occasional misstep. It's an occupational hazard.

4) The "" returns what can best be described as "Null Text", and as such, needs to be converted to Time or Date -- that's the way it's written. Changing it to be just "NullValue" that can be Text, Time or Date would be nice, I agree, but until (and unless) it happens, I suggest re-reading #3. Meanwhile, I'd sooner buy Enron stock than bet on this change happening any time soon.

Cynically,

Dan

Have you tried Clear() ? I think this works on all field types.

The problem with "Clear" is that the field you're accessing must be in the current layout.

"Why should the TextToDate/TextToTime function be considered to be a more valid way of producting a NULL value that just SetField with an empty string?"

Because a string is data type TEXT: a string of text.

Dates and times are internally stored as numbers, not text strings: they only display as text according to the format of the field on the layout.

This is not a bug nor a problem, in fact it affords extreme flexibility. It just requires understanding of data types and the way FMP stores date and time.