Validate Time and Date fields

[Ryan Lindeman]

We have a web application that interacts with FileMaker Pro via ODBC. When our web application retrieves the time and date fields it processes these before displaying them to the user. In the past, my web application, had issues with missing "seconds" in the time field. I want to create a validation calc for the time field in question. This validation would force the users to enter :00 for the seconds on every time. The problem is I can't seem to get FileMaker to care what the user enters.

 

Here is what I think is going on:

1) User enters 05:20 in the time field

2) My validation script looks for 2 ":"'s in the text entered.

3) I think filemaker converts the 05:20 into seconds before running the calculation script.

4) The value "05:20" is still saved into the database as the value for this field.

5) My web application does a SELECT timefield FROM table; call.

6) It gets the value "05:20" from the ODBC Query and has to add the ":00" to the end for the seconds.

 

My question is: How can I get FileMaker Pro to validate Time fields? How do I force users to always enter the full time values?

 

Next Problem:

This is a similar issue to the time field but not as important. I need to force users to use "/" as a delimiter for dates instead of typeing "-" as the delimiter. What can I do to force users to always use "/" as a delimiter?

 

Ryan Lindeman

[bikergeek]

Why force the user to enter something when you can have a script add it automatically?

 

Use a temporary text field as a container for the time entered, and then populate the time field with a calculation that strips any data more than two positions after the first ":" plus ":00"

 

Set Field [time; Left(temp;Position(temp;":";1;1)+2) & ":00"]

 

For date delimitiers, you can solicit year month and day in separate field of a layout used specifically for data entry and a calculation to add in the delimiters, or use a temporary field and a dialog and the calculation:

 

Set Field [date; Substitute(temp;"-";"/")]

[Leader]

I recognize this is lecturing, but I absolutely agree with Bike– the more I read, the more my tension level rose. I use Washington Mutual for my on-line banking, and I HATE their dates which have to be keyed in complete with leading zeros, slashes, and crap like that.

 

One of the reasons I like FM is that it's very user friendly, and we should pass that on to our users. Don't make them do anything they don't have to do. Why not let them them enter slashes, dashes, dots, or even blanks?

 

Based on the assumption that seconds truly are crucial, why not format your time stamp with 6 data entry fields and then assemble the data in the way you perfer? That would solve most problems. In other words, you define 8 fields:

da 2 digit numeric validate

mo 2 digit numeric validate

yr 4 digit numeric validate

hr 2 digit numeric validate

mn 2 digit numeric validate

sc 2 digit numeric validate

fulldate calc text = da&'/'&mo&'/'&yr

fulltime calc text = hr&':'&mn&':'&sc

 

There's a number of benefits here, especially the ability to have FM validate the data entry. You could, if you wish, create pop-up menus for some of the items.

 

Take it a step further, and you could add an extra field and have a pop-up menu of months spelled out or abbreviated. (Jan, Feb, etc.)

 

One of my soapboxes is that the US is the only country left that still formats dates as MM/DD/YY, whereas the rest of the world formats them as (business) DD.MM.YYYY or ISO YYYY-MM-DD. The latter is useful because it allows sorting, but it looks 'funny' to American eyes. Usually I user a format of DD-MMM-YYYY in which the month is abbreviated, making it understandable to almost everyone in Europe, North and South Americas.