Do you mind telling me why you choose to store date/time values as text in Notes, when Notes has a perfectly good date/time datatype? It's a Really Bad Idea to store dates in text fields, because it makes them unusable as dates (e.g. you can't sort by them). Plus, different computers have different settings for how they display dates, and you may get some users who interpret 03/12/2004 as 3 December, and others who think it means 12 March 2004, because they're used to seeing their dates in that order. I've written extensively about this in the ND6 forum and elsewhere.
If you must do this, you can use the data transformation formulas on the Notes connection document to maintain an ephemeral date/time field in the Notes document. This is a field which you never store, but just convert to text and store the text instead. For instance -- I just tried this -- I defined a Notes form with two text fields (Enrolled and ID), and then added a Computed for Display Date field and a CFD number field (DateEnrolledTemp, IDTemp), both with formula “”. I created a Direct Transfer, mapping the relational date and number fields to the CFD fields. In the Notes conection document, I entered formulas as follows:
 Formula to Execute During Select: | SELECT @All; FIELD DateEnrolledTemp := @TextToTime(Enrolled); FIELD idTemp := @TextToNumber(ID) |
Formula to Execute Prior to Insert: | SELECT @All; FIELD Enrolled := @Text(DateEnrolledTemp); FIELD ID := @Text(IDtemp); FIELD DateEnrolledTemp := @Unavailable; FIELD IDTemp := @Unavailable; |
Formula to Execute Prior to Update: | SELECT @All; FIELD Enrolled := @Text(DateEnrolledTemp); FIELD ID := @Text(IDtemp); FIELD DateEnrolledTemp := @Unavailable; FIELD IDTemp := @Unavailable; |
The first formula isn't used during the DT operation -- that would be if you're copying from Notes to RDB. Notice we're removing the CFD fields so that they don't clutter up the Notes database -- I guess you could also do this by enforcing field flags but I have't tried it.
Return to top
. . . . . . RE: Question about datatype mismatc... (Andre Guirard 14.Apr.04) 
Print this page
