This article tackles a logical problem and demonstrates a technique that is useful in other “date-time” scheduling or equipment tracking situations.
The logical problem it attempts to solve is, “How do you keep track of things when they are not being used?” It is fairly easy to enter when things are used, but in many situations it is also useful to have an entry for when they are not. This is especially true if you want to later build calculations to analyze the usage of large numbers of entries, percentages, etc..
It does, however, bend one of the basic common sense rules of database construction, so it should be used only when necessary. That rule is to not create a structure where records are going to be primarily empty. A bunch of empty records is a waste of disk space, at the very least. In this case, however, only a small percentage would be empty, and their usefulness outweighs the wastage (IMHO).
There are a couple of techniques with portals. The sections are related, but independent, so “take what you need and leave the rest,” as a wise man once said.
Show Me the Limos
This particular problem involved a limousine rental service. Most of the limos would be used every day. But sometimes one would not. The owners wanted to be able to easily see which were not being used as well.
They had been using a spreadsheet to see where the gaps were. This was primitive but presented a quick visual picture. With databases the data is not tied to any particular place on the screen or document, and while this is ultimately a feature, it makes these kinds of “grid” views more difficult.
My idea was to simulate what the spreadsheet was doing. I just created a record for each possible entry. The main trick is the automation of this creation.
An entry would be a date, a shift (AM or PM), and a limo. There would also be a driver, but that would be entered manually; since there could only be at most one driver per each entry this doesn’t change the number of records needed.
The cool part of the trick (also IMHO) is how it checks to find out how many limos to create records for, so that they don’t have to be “hard-coded” into the script. It goes to the Limos file to get the current limos.
My regular readers will recognize this as the “Copy All Records” trick.
Basically it copies all the ID’s of the found set, on a layout with only that field, then pastes them into a text global field, so that each ID is on its own line.
This part of the larger script is in the Limos file; it’s called as an External script by our record creation script in the Limo|Drivers file.
Rather than use the global field with all the Limo ID’s directly in a relationship, we’ll use it to create records in the Limo|Drivers file. This will be kind of the opposite of what we just did to get them.
First we’ll pass the global field to Limo|Drivers. Then we’ll take the Limo ID’s out one at a time and create records. This will be done with a Looping script.
Because each ID is only one word, we can use the simple LeftWords(Limo IDs g, 1) function to get the first one. Then we can subtract that word by using:
Set Field [“Limo IDs g”, “RightWords(Limo IDs g, WordCount (Limo IDs g) – 1)”]
We’ll use each Limo ID to create 2 records for the date, one for AM and one for PM (you could remove the second part if you only needed 1 per date).
The Loop will stop when the Limo ID’s g field is empty.
Make Day Script
Freeze Window
Go to Layout [“List”]
Perform Script [Sub-scripts, External: “Limoss”]
Comment [“Copy Limo ID’s”]
Set Field [“Limo IDs g”, “Limos Constant::Limo IDs g”]
Exit Record/Request
If [“IsEmpty(Date g)”]
Set Field [“Date g”, “Status(CurrentDate)”]
Comment [“So you don’t have to enter the date”]
Exit Record/Request
End If
Loop
New Record/Request
Set Field [“Limo ID”, “LeftWords(Limo IDs g, 1)”]
Set Field [“Shift”, “”AM””]
Set Field [“Date”, “Date g”]
Exit Record/Request
New Record/Request
Paste from Last Record [Select, “Limo ID”]
Set Field [“Shift”, “”PM””]
Set Field [“Date”, “Date g”]
Set Field [“Limo IDs g”, “RightWords(Limo IDs g, WordCount(Limo IDs g) – 1)”]
Exit Loop If [“Limo IDs g = “””]
End Loop
Go to Layout [“Entry”]
Data Entry
Data entry will be done with global fields, mirroring the regular fields. Since we’ve already got a record for the Limo and the Date, all we need to do is find that record and enter the driver, who is the only variable.*
The entries in the global fields (Field-Formatted as drop-down lists) for the Limo, Date and Shift will be used to quickly locate its record.
All we need is a couple of concatenated (put together) calculation fields, one made with the regular fields, the other with the globals, and a relationship from the global one to the regular one (g suffix means global field).
Limo|Date|Shift Calculation Indexed, = Limo ID & ” ” & Date & ” ” & Shift
Limo|Date|Shift gc Calculation Unstored, = Limo ID g & ” ” & Date g & ” ” & Shift g
Limo|Date|Shift gc = ::Limo|Date|Shift
Remember, we already created all possible records for the day. We just need to get to the right one quickly.
Date entry is performed with a script, triggered by an Enter button. It can check first to see if the record has already been entered.
More importantly, it also must check to see if the records for that date have been created yet. If not, then it performs the Loop above to create them.
It will only run it once per each date. If you enter a date in the past or future, it will create those.
*If you don’t have a variable that needs manual entry, but are just checking off that something was done, then you could do it directly by clicking on the record, on a list or a portal row. Just set a checkbox to 1.
Portal for Completed Entries
Speaking of portals, I put a portal next to the data entry. It doesn’t show all the records, or even all of those for a certain date (which is what you’d want for the above checkbox). It’s filtered to show only records that have been filled.
This is done by creating a marking field, based on the variable, Driver ID.
Limo_Used c = not IsEmpty(Driver ID)
It’s Field-Formatted as Boolean, either 1 or nothing, so we get a 1 for entered records.
A Constant calculation field, =1, is related to it (I almost always have this field).
We can tack this onto the Date field and add a 1 to a Date global, then create a relationship between them to get a filtered portal (unstored always on the left side).
Used|Date Calculation Indexed, = Limo_Used c & Date
Used|Date gc Calculation Unstored, = 1 & Date g
Used|Date gc = ::Used|Date Limo|Datess
The portal gives the data entry person (in this case me) some visual feedback.
I also added a “highlight” background behind the current global choice, so that you could instantly see whether a Limo|Driver had worked both shifts.
Invisible Day Before and Day After Portals
There are two more “invisible” portals, at the upper left. They show the entries for the selected Limo|Driver for the day before and the day after.
They were created with concatenated calculation fields, adding 1 and subtracting 1 from the global date and the regular date, tying that to the Limo|Driver.
There are 2 rows in each portal, to show AM and PM.
Here are the fields. I think you can see how they’d go together in relationships. They are all text results.
Limo|Driver|Date Calculation Indexed, = Limo ID & ” ” & Driver ID & ” ” & Date
Limo|Driver|Date 1 Calculation = Limo ID & ” ” & Driver ID & ” ” & (Date + 1)
Limo|Driver|Date _1 Calculation = Limo ID & ” ” & Driver ID & ” ” & (Date – 1)
Limo|Driver|Date 1gc Calculation Unstored, = Limo ID g & ” ” & Driver ID g & ” ” & (Date g + 1)
Limo|Driver|Date _1gc Calculation Unstored, = Limo ID g & ” ” & Driver ID g & ” ” & (Date g – 1)
Seeing whether someone had worked the adjacent shift on the day before or after was useful to the Limo folks. But me, I just used it to remember who drove what limo when entering example records 🙂
You can also use an invisible portal like this in a scheduling database to show which record a possible new entry is conflicting with.
But that’s a little more complicated. My scheduling database is in perpetual “almost finished” limbo, so I can’t help you much there.
Also, if you manage to adapt this, or any of my examples, to a real-world use, please let me know what it is. My examples are “open-source,” so you can modify and use them; but I’d like to know, just for my own curiosity. There’s a lot I don’t know about the real world.
Disclaimer: As I said, this solution bends the rules of normalization. If you find that you are creating a bunch of empty records and few entries, then there are other ways to track used and unused items. They involve variations of Copy All Records, Go To Related Records, Find Omitted.
It’s difficult to know where to store the data obtained however, without knowing the specific setup.
Please don’t tell the Normalization Police.
(P.S. BTW, IMHO is “in my humble opinion.” Oh no, BTW is “by the way.” Better stop now.)
Fenton Jones
Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com
Leave a Reply
You must be logged in to post a comment.