Secret Santa

In my family, we decided to do Secret Santas this year. My wife and I have three daughters, and the two older daughters got married in the last year, so there are seven of us in the immediate family. We all agree that Christmas is really about something rather more important than shopping or gift-giving, and we agree that keeping it simple is a good idea.

So somebody said, How do we do this? Although we're all in Dallas, we weren't together on the day that this decision was made and weren't going to be all together until Christmas day, so writing names on paper and drawing them from a hat wasn't going to be practical. Without thinking, I said I could build a database that would make make the Secret Santa assignments and email them out anonymously.

My wife looked at me like I was crazy (which, as it happens, she knows me to be). The first concern she voiced had to do with security. She worried that I was going to know who all the Secret Santas were. I assured here that once I'd tossed the database together it would do its job without any input from me, and I promised that I wouldn't peek at the results.

Then she was worried that this sounded like a lot of work. Nah, I replied, it'll take me five minutes. Silly me.

Simple Santa

Well, more than five minutes later, after trying a couple of approaches that were too complicated conceptually, I came up an approach that worked and had the virtue of being intelligible, as well. The idea was simple. Sort all our names randomly, then assign each person to the next person as Secret Santa. Once the seven names were in the database, this would require two steps: the random sort, and a looping script to put record 1's ID into the "Giving To" field in record 2, etc.

My little FileMaker Pro database has one table. The important fields are

• ID (an autoentered serial number)
• Name
• Giver Email
• Giving To ID
• sorting number

Although there's only 1 data table, I did decide to create a simple self-join, so that the email that gets sent out could give each person the name of the person they're supposed to buy a gift for. The self-join looks like this:

So each person in the GIVING list has a value in the field "Giving to ID" and the related table, called "GETTING", translates that Giving To ID value into the name of the recipient.

Having defined the necessary fields and this little relationship, I created seven records and entered our names and email addresses.

The random sort

The random sort is easy. Now, using FileMaker's "Unsort" option in the Sort dialog is not random. That sorts by creation order. To sort a bunch of records randomly, you have to assign a random number to a sort field in each record, then sort on that field. The script steps for this are:

1. Show All Records
2. Go to Record/Request/Page [ First ]
3. Loop
4.       Set Field [ GIVING::sort number; Round ( Random * 1000 ; 0 ) ]
5.       Go to Record/Request/Page [ Next; Exit after last ] 
6. End Loop
7. Sort Records [ Keep records in sorted order; Specified Sort Order: GIVING::sort number; ascending ][ Restore; No dialog ]

  

The FileMaker Pro "Random" function returns a machine-random value between 0 and 1. I multiplied that value by 1000 just to convert it to whole numbers. While I was testing the idea, I wanted to see the random numbers in the sort field and I don't like looking at small fractions. But that multiplication was not necessary.

Assigning 1 to 2, 2 to 3, etc. (with a loop)

There are actually multiple ways to make the assignment. The one that occurred to me first was to use a loop. The loop requires a variable. I named the variable $idrecipient. 

1. Set Variable [ $idrecipient; Value:"" ] 
2. Allow User Abort [ On ]
3. #
4. Go to Record/Request/Page [ First ]
5. Loop
6.       Set Variable [ $idrecipient; Value:NAMES::ID ]
7.       Go to Record/Request/Page [ Next; Exit after last ]
8.       Set Field [ NAMES::Giving To ID; $idrecipient ] 
9. End Loop
10. Go to Record/Request/Page [ First ]
11. Set Field [ GIVING::Giving To ID; $idrecipient ]

Step 1 simply declares the variable. (Notice that I set the variable's value to null or empty at this point.) I always declare every variable used in a script at the start of the script, so I can keep track of 'em. 

Step 2 (Allow User Abort [On]) is important when you're writing loops. If you forget to define the loop in a way that allows it to exit properly (say, you forget the "Exit After Last" parameter in the Go to Next Record Step), then you'll need the ability to abort the script manually, by typing Cmd-period. There are very few things that we FileMaker developers can do to lock up the user's database. Writing a loop without a proper exit step is the main one. 

Steps 4–9 get almost all of the assignments made. The first record's ID is stored in the variable, then entered into the "Giving To" field of the second record. That second record's ID is stored in the variable, and entered into the Giving To field of the third record. And so on, until the script exits when it gets to the last record.

Now, the first record is a problem here. If each person is assigned to be the Secret Santa for the preceding person in the list, record 1 has no assignment, because no record precedes it. The solution is obvious: Assign the last record to the first. And that's what steps 10–11 do.

Assigning 1 to 2, 2 to 3, etc. (using GetNthRecord)

But the goals achieved by looping scripts can often be achieved in other ways, as well. Here's another way to accomplish the same end.

1. Replace Field Contents [ GIVING::Giving To ID; Replace with calculation: GetNthRecord ( GIVING::ID ; Get ( RecordNumber ) - 1 ) ]
2. Go to Record/Request/Page [ First ]
3. Set Field [ GIVING::Giving To ID; GetNthRecord ( GIVING::ID ; Get ( FoundCount ) ) ]

It took the looping script 8 steps to accomplish what's accomplished here in only 3. And I don't need a variable here, either. The trick is accomplished using the GetNthRecord function. I've set its parameters so that, for each record, it gets the ID value from the preceding record.

Once again, the first record poses a problem as it has no predecessor. Setting record 1's Giving To field to the ID of non-existent record 0 doesn't generate a serious error in FileMaker Pro, so in the approach above, I allowed the error to stand; then, when the replace step (step 1) was finished, I went to record 1 and set its Giving To field to the value of the last record in the found set.

This approach could actually be done with a single step — just step 1 above, but with the calculation formula modified as follows:

Let ( recnum = Get ( RecordNumber ) ; 

GetNthRecord (    NAMES::ID ;    Case ( recnum = 1 ; Get ( FoundCount ) ; recnum - 1 ) // case) // getnthrecord

) // let

The GetNthRecord function takes 2 parameters: first, the field whose value you want to retrieve from the Nth record; and second, the value of N. In my earlier, three-step version of this script, N was always equal to the current record number minus one. In this even more abbreviated version of the script, the parameter that gets the value of N checks to see if the current record is record number 1, and if it is, it gets the ID from the last record in the found set.

There are no prizes for brevity in scripting. Elegance in coding and scripting is supposed to be a virtue. It's not. Inelegance is a vice. But elegance in coding is potentially self-defeating. It's possible to be so elegant that you can't figure out what you've done. And as I said, figuring out what's wrong in a looping script can be much easier than figuring out why a more abstract, one-step process isn't working. Still, it's nice to know how to be brief, and sometimes it can make a difference.

Which one's better: loop? or replace field contents?

I write a lot of loop scripts. They're usually easy to conceptualize and they're always easier to debug than the alternatives, because with a looping script, things happen one step at a time, in order. They're also often safer. It's not just that looping scripts lacking exit steps can lock up the database on the user. It's also that, when I'm debugging a looping script, if there's a Set Field step that's making a mistake, I can see it in the very first record, and abort the script and fix the problem. Can't do that if I use the Replace Field Contents below. Finally, a looping script can handle record-locking issues more gracefully.

But for a little database like this, it doesn't make much difference.

Wrapping and Mailing

Ultimately, I put the random sort steps together with the assignment steps, to make a single script. And at the end, I tacked on a Send Mail step. Unfortunately, the Send Mail script step doesn't always work well with Google's SMTP servers. I could have solved the problem, but remember, this was supposed to take just 5 minutes. So I abandoned the project.

Besides, there was a another problem. As happens so often, the client (i.e. my family) hadn't informed me of a critically important spec. My wife didn't want members of the same residential unit to get one another. She didn't want me to get her, or her to get me, or the married couples to get one another, etc. Of course, I could have fixed that in another five minutes, by adding another field and modifying the scripts slightly.

But instead, we used a Secret Santa app on my oldest daughter's iPhone. This was definitely not as elegant or easy as my database would have been (had mine worked), because the phone had to be passed from person to person, and because, since we weren't all together (as I mentioned at the start), we had to ask my sister-in-law to stand in for one of us and then text the missing person the name of the "givee" they'd been assigned.

Still, it was a fun little problem.