FileMaker Solution: entering partial dates

6 September 2018

At uLearnIT, we love solving your problems. Here is the latest one solved for a client using our mentoring services.

Request

In our Equipment table there is a field for DOM (date of manufacture). It is defined as a text field since the data is just a month and a year such as 03/10 or 11/15. We would prefer the service techs to enter the data as written. We don’t want them rejecting the process because it’s uncomfortable.

But then we need to perform simple calculations on the DOM. For example, if the DOM is more than 5 years past, a “Level 4” service is due.

Solution

Part 1: Data Entry

It would be great to allow users to enter a month and year as the DOM in a number of ways. So let’s allow them to enter say March 2015 as any of 315, 0315, 3/15 or 03/15. And then reset the data on field exit to the standard 03/15.

This is done with a field option which is an auto-entered calculation. Importantly, the option Do not replace existing value of field (if any) was unchecked – so that the calculation is evaluated when leaving the field.

Case ( 
 Length ( Self ) = 3; 
   "0" & Left (Self; 1) & "/" & Right (Self; 2);
 PatternCount (Self ; "/") and Length (Self) = 5;
   Self;
 PatternCount (Self ; "/") and Length (Self) = 4;
   "0" & Self;
 Length ( Self ) = 4;
   Left (Self; 2) & "/" & Right (Self; 2);
 "Error"
)

In this Case statement, there are four tests:

if the length of the data is 3 characters, then add a leading zero and insert a slash character – so 315 becomes 03/15
if there is a slash and the length is five characters, then leave as is – so 03/15 would be left as is (returns Self)
if there is a slash and the length is four characters, then add a leading zero – so 3/15 becomes 03/15
if the length is four characters, then insert a slash in the middle – so 0315 becomes 03/15

And if all those tests fail, it returns the result “Error”. That may happen if you enter 35 or 032015.

Exceptions could be handled in other ways but it was not needed here.

Part 2: Calculate a proper date

Now that we have some standard data to work with, we can easily convert that into a proper date with a calculation. The decision was made to standardise on the first of the month.

So we set up a calculation field (DOM as date) using the expression:

Let ([
    raw = DOM;         // the field entered as MM/YY
    themonth = GetAsNumber (Left (raw ; 2));
    theyear = GetAsNumber (Right (raw ; 2));
    theyear = theyear + 2000 - If(theyear >50; 100);
                      // convert to four digit
    thedate = Date (themonth ; 1 ; theyear)
                      // date of first of month
];
    thedate
)

The process in the Let statement is:

The field (DOM) is set into a variable called raw.
Then raw is processed to get the month and the year – first and last two characters respectively.
Then the month and year are used to get the date of the first day of the month.
The year is converted into four digits.
The Let function returns the proper date (calculation result type is date)

QED

So that is the simple solution that lets techs enter the date quickly and easily, and then use it to move forward into other calculations to work out service requirements and more.

Do you need uLearnIT to solve a problem?

We can do the same for you. Just let us know what you are struggling with and we will provide a cost effective solution. We have saved our customers hours of Googling answers and frustration trying things that never worked for them.

We can do the same for you.