Old School Technique For Portals Based Upon Date Ranges

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts


Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here). 

A portal of data ranges can be a very nice addition to any FileMaker solution. Before the advent of FileMaker 7 and multiple predicate relationships, you had to do some nifty tricks to get portal ranged information. We also had to do some work around programming due to the lack of the pop up calendar that you find in FileMaker these days.


We cover this old school method because you may come across it from time to time when you inherit a database and the techniques used can be applied in other creative ways.

This example has a lot going on and it’s possible you might get bewhildered with some of it. Not to fret, I’ll take my time explaining each technique. This is an nice example of how you can integrate many different techniques into one robust interface.

What we have is a screen that allows you to set a start date and an end date via pop up menus for the month, day and year. When the start and end dates are set properly, you click the Show Results button. In the portal, you will see all the records that fall within that date range.

USING POP UP FIELDS
First lets talk about how we setup a date via a set of pop up menus. You may be thinking that we could just have a standard date field for the start and end dates. You could and it will work well unless ... the person doing the data entry has troubles entering in dates. The may enter in 12/10/2002 and think they have entered in October 12th and not December 10th.

So what we did was create three global number fields for the start date and three for the end date.

g_start_month ( global number )
g_start_day ( global number )
g_start_year ( global number )
g_end_month ( global number )
g_end_day ( global number )
g_end_year ( global number )

Next we need to build a calculation field to pull the three fields together. We start off by using the Date function, which as a syntax of Date (month, day, year). So we end up with ...

start_date ( calculation - date result )
Date(g_start_month, g_start_day, g_start_year)

end_date ( calculation - date result )
Date(g_end_month, g_end_day, g_end_year)

CREATING A DISPLAY FOR THE DATA ENTRY PERSON
You may want to show the user what their settings are before they click the Show Results button. So we created a calculation field to do just that. We have setup the display field to tell the data entry person if they left any fields blank. This is nice because the message changes as the user enters data in each field.

IsEmpty(g_start_month), "Start Month Empty",
IsEmpty(g_start_day), "Start Day Empty",
IsEmpty(g_start_year), "Start Month Year",
IsEmpty(g_end_month), "End Month Empty",
IsEmpty(g_end_day), "End Day Empty",
IsEmpty(g_end_year), "End Year Empty",

We also have a warning if the user accidently sets the start date to occur after the end date. We don’t want to try and build a range going in the opposite direction.

end_date ≤ start_date, "The start date needs to be less than the end date!",

Finally, we want to encourage the user to click the Show Results button.

"Is the start date of " & DateToText(start_date) & " and the end date of " & DateToText(end_date) & " correct? If so, click the show results button."

So putting the calculation all together, it looks something like this.

Case(
IsEmpty(g_start_month), "Start Month Empty",
IsEmpty(g_start_day), "Start Day Empty",
IsEmpty(g_start_year), "Start Month Year",
IsEmpty(g_end_month), "End Month Empty",
IsEmpty(g_end_day), "End Day Empty",
IsEmpty(g_end_year), "End Year Empty",
end_date ≤ start_date, "The start date needs to be less than the end date!",
"Is the start date of " & DateToText(start_date) & " and the end date of " & DateToText(end_date) & " correct? If so, click the show results button.")

So when all the fields are entered in correctly, we get something like this.

Is the start date of 1/1/2003 and the end date of 1/1/2004 correct? If so, click the show results button.

DEFINING A MULTIPLE LINE KEY FIELD
Now we need to build the parent key so that it can match to the range of possible dates. Are you familiar with a multiple line key field? A field can have multiple lines of key field data if each piece of data is separated by a carriage return. If one field has the following data ...

1/1/2003
1/2/2003
1/3/2003
1/4/2003
1/5/2003
1/6/2003
1/7/2003
1/8/2003
1/9/2003
1/10/2003
1/11/2003
1/12/2003
1/13/2003
1/14/2003
1/15/2003

it will match relationally to any record that has any of the matching dates! Each line acts like a duplicate key field!

BUILDING THE MULTIPLE LINE KEY
There are a number of ways to build this information. We are building it by clicking a button that runs a script. There are calculation methods but they are very complex and not very flexible. Another option is to use a plug-in. There are both freeware and commercial plug-ins that will do this for you. All you need to do is make sure all your users have the plug-in installed.

Here is the method we used by running a script.

If [end_date < start_date]
Show Message [ “ Oops ... the end date of your range is before the start date. I can't build a range this way!”]
Halt Script
Else
End If
Set Field [ g_range, DateToText(start_date)]
Set Field [ g_working, start_date]
Loop
Set Field [ g_range, g_range & "¶" & DateToText( (g_working + 1))]
Set Field [ g_working, g_working +1]
Exit Loop If [ g_working ≥ end_date ]
End Loop
Exit Record / Request

Let’s show that script again but add some comments along the way ...

If [end_date < start_date]
Show Message [ “ Oops ... the end date of your range is before the start date. I can't build a range this way!”]
Halt Script
Else
End If
** COMMENT - Here we are not allowing the script to execute if the start and end dates are not correct **
Set Field [ g_range, DateToText(start_date)]
Set Field [ g_working, start_date]
** COMMENT - Here we are setting out start off information. The g_range field is our parent key field. The g_working field is our counter. It allows us to exit the script when we have built all the data we need. **
Loop
Set Field [ g_range, g_range & "¶" & DateToText( (g_working + 1))]
** COMMENT - Here we are setting our key field equal to itself and adding the next day**
Set Field [ g_working, g_working +1]
** COMMENT - Here we are adding one day to our counter. **
Exit Loop If [ g_working ≥ end_date ]
** COMMENT - Here we are telling the script to exit the loop if we have reached the last date we need.
End Loop
Exit Record / Request
** COMMENT - Here we are exiting the record so the portal will refresh with the updated parent key information. **

An example file can be downloaded by clicking (here).
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.