List is an underused function in FileMaker Pro. I say it’s underused only because I never used it before

Recently I was working on an email engine that would notify the teachers at Fenixworks about who was enrolled for a specific class. The students are all in a related table that’s inside of my registration database, but for the life of me I couldn’t find a way to build a calculation that would include just the students from a specific workshop for the instructor email.

I tried about ten different approaches, it seems like there’s always at least that many ways to try something in FileMaker. I had already searched FileMaker’s help and had come up empty handed, so I turned to the internets, a la Google.

I find often I can type a plain english question into Google and find a worthy response. In this case, that’s exactly what I did.

I asked Google “How do I list related records in an email calculation for filemaker pro?” I turned up a few pages that didn’t help, but then I ran across one that listed exactly what I needed.

There’s actually a “List” function. You can use it to return a list of related records, all in one field. Then you can use that field just like any other field. It has the added benefits that it works to show all the contents of a repeating field in one place, or you can even use it to show a bunch of fields as if they were all concatenated into a list.

Sweet.

So for my needs, I was able to just use this one command in my calculation field:

list(regsitrations::student)

and that pulled all of the students related through the registrations table for the current record into one place where I could then work them into my email calculation field.

I immediately turned around and used it in another place to list all of the upcoming classes for my daily Craigslist posting. First, I set up a relationship that relies on both the ID of the class and dates greater than today’s date. Then I was able to use the list function like this:

list(futureSchedule::craigslistBlurb)

Now I’m using the list function so much, I don’t have any idea how I ever got along without it. I think you may find it useful too.

No Comments »

Sometimes in a FileMaker Pro database, a need arises to relate only to records within a specific range of criteria. Take for example a calorie/weight database. Imagine if you only want to display the weight figures for the past 7 days.

You set up the relationship to the weight table so that it has two criteria instead of just one. In the “Edit Relationship” dialog, you can click on “Add” to add a second connection.

So, in this specific example, assuming that you are displaying all data from a table called “Dates” with a relationship to another table called “Current Weight”. You could use the Date field in the “Current Weight” table (called “wDate”) to relate to two different date fields in the Date table.

One of the dates in the date table (”dToday”) is just a simple date field. The other date in the date table (”dTodayRange”) is a calculation field. Its calculation looks like this:

dDate - 7

Double click on the box in the relationship line to open the “Edit Relationship” dialog, and then get creative with ≥ and ≤ instead of just using an =.

By setting the relationship to be less-than-or-equal to the current date, and greater-than-or-equal to the date from 7 days ago, you’ve just set up a range that will always have the 7 most recent dates in it. The end result can live in a portal that always shows the most recent data, no matter what the date is.

Comments Off

I’ve been using FileMaker Pro since I got my first Mac 20 years ago to print mailing labels. It seems like a natural extension since I already use it to keep up with addresses and attendance records and phone numbers. Sometimes those mailing labels are for things as complex as mailers to a customer base or as simple as pre-printed labels to stick on postcards when I travel (that’s a trick I learned from mom! I carry labels instead of an address book when I travel, and when all the labels are gone, then I have sent postcards to everyone I planned for.)

Anyway, one gotcha when you set up a new layout in FileMaker Pro to print labels is that often the last row of labels on each sheet doesn’t print. It’s an easy fix, but it’s not one that you would necessarily think of.

Here’s how:

1. First make sure you are in Layout Mode by pressing Command-L on the Mac or Control-L on Windows.
2. Choose the layout with labels that you want to fix from the Layout menu in the Status area on the left side of the window
3. From the Layouts menu at the top of the screen, choose Part Setup
4. Double click on “Body” in the window that shows up
5. Finally, in the “Part Definition” window that is now on your screen, check the boxes for:
   • Allow Part to break across page boundaries
   • Discard remainder of part before new page

   When they are all checked, it should look similar to the picture below

6. Click on OK

That’s it. Now your labels will print on the bottom row of the sheet and you won’t be wasting any more space!

1 Comment »