I have a field that is a date format. When the form is created and that date field is null the saved form stores it as text. This makes it impossible to search on. Is there a way to fix this? Thanks.
Subject: SOLVED Null date field stored as text
I was just dealing with this problem in an R7 application. The solution is to use this as your input translation formula:
@If(@Text(@ThisValue) = “” ; @Unavailable ; @ThisValue)
Seems to be working for me so far.
Subject: Null date field stored as text
That is how Notes works – there is no such thing as “null”. Notes is not tabular, there are no data storage definitions. You are not using a relational database. NotesItems are typed entirely by their content, on a document-by-docvument, item-by-item basis. That, of course, does not mean that you “can’t search on” a date value in a particular field, or that you can’t look for “” in a field that should normally contain a date.
Subject: RE: Null date field stored as text
Thanks I realize this but our last version was 6.0.3 and stored empty dates as dates therefore making the search capability easy, as well as coding for web searching. When I search for a date value on the text field it brings back 0 documents when there are some with that particular date.
Subject: RE: Null date field stored as text
No version of Notes has ever stored an empty field value as anything but an empty string (or a variant containing an empty string). Exactly what do you mean by “search”? Are you using a view column, or are you actually searching?
Subject: RE: Null date field stored as text
I am searching. When there are empty date fields all you get is “contains” or “does not contain”. If the date fields have no empty values you can select “is on”, “is before” etc. In the previous version (6.0.3) even if there were empty values it would always come up with the date searching capability “is on” etc.
Subject: RE: Null date field stored as text
If that’s reproducible, it’s worth taking to Lotus as a support issue. As I said, the empty dates have always been strings, so that part of the equation hasn’t changed at all. It may be an UNK table issue (Notes’ internal UNique Keys table, which is more-or-less a field name map), or it may be simply that the first boatload of documents defined in a particular database all had empty (string) values in the datae field, and Notes “learned” what the value should have been. Oh, if the same fieldname has/had more than one definition in Designer, that can make a difference as well.