Re: yyyymmdd date format import

On May 12, 2:22 pm, d-42 <db.pors...@xxxxxxxxx> wrote:
On May 12, 9:57 am, d.kinn...@xxxxxxxxxxxxx wrote:

I am in the process of moving a database from Access to Filemaker. One
field is giving me some problems working out the best way to handle
it. The date fields have the format of yyyymmdd with "99999999"
representing "forever" or "does not expire".

I can import the dates into the new data base with a calculation to
change to the fm date format BUT how can I handle the "forever" dates?
OR would it be best to define all of these as numbers with some kind
of calculation for input validation?

'Forever' is not a valid date, and 'magic numbers' that mean something
special are generally bad design. The 'correct' way to handle it is
with 2 fields. An number field acting like a boolean (true/false) for
'does not expire'.

Alternatively you can define a text field, and have it store 'forever'
or a date as text, but it doesn't really get you anywhere useful,
because you will have to apply conversions to work with the date; e.g.
to perform finds, etc.

If you want 'forever' or a date to appear in a single 'place' on
layouts, you just define an unstored text calc... if(doesnotexpire =
1,"forever", expirydatefield)

-cheers,
Dave

The steps I would take are

1. Import the date information from access to a TEXT field
2. Use a calculation to transfer all of the valid dates to a date
field (for non-expiring, either leave the date field blank or put
12/31/2099. NOTE: if you intend to do finds based on the expiration
date, and you want non-expiring records to be included, make sure you
do the latter)
3. Set a boolean like Dave suggested for the non-expiring

Cheers,
Kevin
.

Relevant Pages

  • Re: Some word files cant open
    ... > If you wait "forever" it will eventually give up. ... > File>Page Setup and change Format For... ... Thanks for the advice. ... It seems the word resets to the default printer after the failure to format. ...
    (microsoft.public.mac.office.word)
  • Cant open doc
    ... multitude of graphics. ... forever to save, and then last night it wouldn't save at all. ... Is there another format I ...
    (microsoft.public.word.formatting.longdocs)
  • Re: problem with DSL and dial up modem after Regedit
    ... > You know if anyone knows how to format then they know all is gone - ... > lost - forever. ... that is not necessarily true. ...
    (microsoft.public.windowsxp.newusers)
  • Re: yyyymmdd date format import
    ... field is giving me some problems working out the best way to handle ... I can import the dates into the new data base with a calculation to ... change to the fm date format BUT how can I handle the "forever" dates? ...
    (comp.databases.filemaker)