can you post the calc?
Sometimes wrapping the result in GetAsDate() will allow 'empty' to show nothing rather than the dread ?
A couple of other thoughts - have you set the field not to evaluate if reference fields are empty (checkbox at the bottom of the calc dialog)? Turning this off might be the answer. Is the calculated field set to type date?
If none of that works, or using GetAsDate(), you could also use the Evaluate function in an if statement -
If ( Evaluate ( your calculation ) = "?" ) ; "" ; your calculation ).
Posting the calc you have used would help.
Yes, please post the calc. It should be easy enough to figure out with that.
In general you can do the something like the following to ensure calculations that fail to evaluate return nothing instead of ?
not EvaluationError (
$result = GetAsDate ( "13/32/2016" ) // Replace this with your calc ;
~result = $result;
$result = ""
If ( ~calcOk; ~result )
It's a bit of a hassle and involves three nested lets, but in some cases it's handy.
Lots of good suggestions.
Here's the calc for the second field.
Date ( Month(DATE_LETTER_RECEIVED)+1 ; Day(DATE_LETTER_RECEIVED) + 60; Year(DATE_LETTER_RECEIVED) ) -1
So, if I pick a valid date for the first field, this all works fine. But, if I delete the date from the first field, I get the "?". GetAsDate() didn't fix that unfortunately.
I have the auto-calc for the second date field defined like this:
Shouldn't your adjustments be inside the brackets for each item - e.g. Month ( DATE_LETTER_RECEIVED + 1 )?
The way you have it, the day could end up as greater than the days in the month.
In plain terms, what result are you trying to achieve from the calculation?
The calc works fine unless I pick a date in the previous date field and then remove it.
There seems to be something going on with the first date field.
This is an easy one, right?
If ( not IsEmpty ( DATE_LETTER_RECEIVED );
Month ( DATE_LETTER_RECEIVED ) +1;
Day ( DATE_LETTER_RECEIVED ) + 60;
Year ( DATE_LETTER_RECEIVED ) ) -1
Assuming that you are happy with the result of the calc when it's not returning ?, then adding the If wrapper is all you need to do.
You would think that the Date function would throw an error if the numbers were larger than the number of days in a month or months in a year, but in fact it just rolls over in that case, e.g.
Returns 2/1/2017. Kinda handy
That may well also do the trick.
Yes I just saw that rollover - a new one on me. Opens up some possibilities…
Your idea could possibly work, but the second field type in this case is not a calculation field (It's a plan date field) so there is no such option.
In any case, I "solved" the problem by just using a small script instead:
Script Logic (on first field exit trigger):
If the first field is empty, then set the second field to "". Otherwise, do the date math as before.
Thanks to all for all the great responses and ideas.
an Auto-enter by calculation (on a "plain" date field) would use the same calculation, such as suggested. no triggers needed.
"The way you have it, the day could end up as greater than the days in the month."
This is a non-issue. FileMaker date calculations will create a valid date.
use hide when = ? is also possible.