Check and see if Catalogs::PS:Press is a field of type date. If it's text, this calculation will return a negative number and that's an invalid date--which gives you the question mark. The same result will be returned if this field is of type date, but is empty for a given record.
PS. if Catalogs::PS:Press is of type date, you do not need the GetasDate function.
Not knowing all the other parts of your tables/relationships I did a quick db with just those two fields
Both fields must be date fields.
Enter the define field s dialogue (command-shift-D); highlight the LCD field. If it is not a date field you will have to change it. Click on 'options'
and chose the auto-enter button/tab/window.
At the bottom is 'Calculated value' and a 'Do not replace existing value (if any)' check box. Check both. Click on the 'Specify' button.
The formula I used was: If (IsEmpty ( LCD Date ); PS Press Date - 56;LCD Date)
What this will do is when a record is created it will automatically enter a date based on an empty LCD date. This date can now be edited if you wish. If there is no date in the PS:Press then I too get a ?. So what comes first the chicken or the egg? Is a user required to make a PS:Press entry, or is a date auto entered in that field on record creation? If so then no more ?
Thanks to both. This has been fixed. Both were date fields so it was my formula (I wasn't telling it what to do if the LCD was NOT empty).
Martien Eh! - the ? is understandable and ok for records that do not yet have a press date
"the ? is understandable and ok for records that do not yet have a press date"
This can bite you. If in future, you search for * to find all records with a date, any records with ? will be found as well. Conversely, searching for empty dates using = will NOT find these records.
The calculation should not produce a invalid value if there is not a true date in the field. Invalid entries of any kind should be corrected. Also, if you share data with external sources, such as MYSQL and you have invalid values it can crash them.
That's a good point. I'll revise the calculation and let you know if I have any questions. Thanks!
Sorry this reply is late, but I'm hoping someone can help with this script to avoid the ? answer. The problem is:
If Date Field A is empty = Answer field should be blank.
If Date Field B is empty = Answer field should be blank.
But if Date Field A has a date = Answer field should be that date - 56 days.
If ( date A AND date B ; Date A - 56 )
You got me on the right track. I was thinking too hard.
Should be If (date A AND date B; ""; Date A - 56)
That will still give you a ? if Date B has a date and Date A is empty.
Luckily if A is empty, B will be empty. They both will either be populated or be empty. Thanks!