Pop: Goes the…um…Variable
We start our return to semi-regular writing with a small-but-awesome custom function: Pop. If you grew up in the American mid-west like me you might think I’m talking about [soda|soda pop|coke|soft drinks]. But I’m actually talking about stacks. In the computer science world, pop means to take something off the top of a list, and it is exceptionally handy to make it easy in FileMaker scripting.
Looping Through Lists
Imagine you have a list of values. (I know, what a weird concept in a database).
Isabel
Sophia
Mamie
Geoff
Jesse
And you want to do something with these items in a script. Typically, for example, you might want to make a new record for each item in the list. You probably already know that FileMaker has several functions designed to make working with return-separated lists easier. For instance, suppose the list above was in a variable called $names. Here are some functions you might use, and their results:
GetValue($names, 3) => returns "Mamie"
LeftValues($names, 2) => returns "Isabel¶Sophia¶"
MiddleValues($names, 3, 2) => returns "Mamie¶Geoff¶"
RightValues($names, 2) => returns "Geoff¶Jesse¶"
Writing our script using these functions isn’t particularly difficult. Here’s the form we normally use:
# assume $names has a return-separated list of names
# ...
Loop
Exit Loop If [ValueCount($names) = 0]
Set Variable [$name, GetValue($names, 1)]
New Record/Request
Set Field [The Name Field; $name]
# set other fields, do more with $name, or whatever*
Set Variable [$names, MiddleValues($names, 2, 999999)]
End
Note: You might wonder whey I have the
Exit Loop Ifstep right after the start of my loop. I do this a lot. It means my loop won’t run even once if the $names variable happens to be empty, which saves me anIfstep around the loop.
Pop: For the Lazy Programmer In Your Life
We write scripts like this a lot. We pull the first item out of the variable, use it or store it, and then re-set the variable to remove that first item again. Although it is minor, this is non-ideal for a few reasons:
The code is kind of strange. What does this code do? Why all those magic nines?
Any time you write the same couple of lines over and over again, your inner-time-saver-alert should start buzzing.
Call me lazy, but I’m always happy to save a few clicks and/or keystrokes.
And so, we wrote Pop. Here it is:
Pop(variableName) :=
Evaluate(
"let([
top = getValue( " & variableName & " ; 1 );
" & variableName & " = middlevalues( " & variableName & " ; 2 ; 999999999999999 )];
top)"
)
In a nutshell, this function takes the name of a variable as its parameter. It returns the first value in the variable and removes that value from the variable all in one step.
Note: Pay attention to the previous sentence. The parameter you pass to
Popis the name of a variable, not the variable itself. You want this:Pop("$variable"), not this:Pop($variable).
For the curious, I’ll explain how it works in a moment. But first let’s see it in action. We can now re-write our loop like this:
Loop
Exit Loop If [ValueCount($names) = 0]
Set Variable [$name, Pop("$names")]
New Record/Request
Set Field [The Name Field; $name]
# set other fields, do more with $name, or whatever
End
Instead of using GetValue to get the first item in the list, and then removing it later, we do it all in one step using Pop. In fact, since Pop is a function instead of a script step, we can use it right inside other expressions, so we can simplify our loop even more:
Loop
Exit Loop If [ValueCount($names) = 0]
New Record/Request
Set Field [The Name Field; Pop("$names")]
# *set other fields, do more with $name, or whatever*
End
This time, we get the value, remove it from the variable, and put it in the field all in one step. As a function, Pop can be used in other places too, like inside larger expressions:
Substitute(
$something;
[Pop("$to_remove"); ""];
[Pop("$to_remove"); ""];
[Pop("$to_remove"); ""]
)
Or in recursive custom functions:
If (ValueCount($whatever) = 0; ""; Pop("$whatever") & AmazingFunction
It is often really handy to be able to combine fetching and removing into one tiny expression.
How It Works
The Pop function uses an oft-overlooked FileMaker power-function called Evaluate. This is another one of those meta-features in FileMaker. Evaluate lets you process calculations inside your calculations.
When you write a calculation, you combine functions, values, fields, and operators to produce some useful result. Normally that result is some new data to display or use in your scripts. As you no-doubt know, FileMaker’s calculation engine provides some very powerful tools to process input and produce interesting results.
When you use Evaluate, the result of one calculation can be a calculation formula itself. Here’s a trivial example:
Evaluate("1 + 2")
In this case, the parameter we’re passing to Evaluate is the text value “1 + 2″. Since this is a valid FileMaker calculation, Evaluate will, well, evaluate it, and the final result of the formula will be 3.
Of course that example is silly. If I wanted to add 1 and 2 in a calculation, I’d just write it like this, and save a lot of trouble:
1 + 2
But since this is a calculation, you can get as complicated as you want:
Evaluate(Choose($something, "Average", "Min", "Max") & "(1, 2, 3)")
That equally-ridiculous formula will return either an average, minimum, or maximum of the values 1, 2, and 3 depending on the value of $something.
But why?! Why would I ever do that?
Well, you wouldn’t. But using those techniques, you can do all kinds of other cool things, like, oh, for instance, write Pop. All Pop does is generate a new calculation formula that looks like this:
Let(
[top = GetValue($my_variable, 1);
$my_variable = MiddleValues($my_variable, 2, 99999999)];
top
)
In other words, it makes a calculation that removes the first item from a variable and returns it. Since we don’t know the name of the variable ahead of time, we have to work the variable name into the calculation using a calculation. (I know, meta…). Once we’ve built this little formula, we use Evaluate to run it for us.
Easy as pie (wrapped in cake, wrapped in an enigma).
Why Would I Ever Use This
If you’re like me, right now you’re thinking this is a lot of work for a very little payoff. It saved a line or two in my loop. But I encourage you to try it out the next time you need to process lists of values. Like all custom functions, it is easy to abstract the complicated part away in your database and never worry about it again, calling on it here and there with an easy name. And that, truth be told, is the core power of programming.
Here, at the End of All Things
Let me start by saying: “Ouch.” It has been far, far, far too long since our last article. Someone much wiser than me once said something like this: “My only comfort is that I tried. My only regret is that I failed.” I’ll leave it at that.
In short order, I’ll be posting our first new article in over 18 months. But first I thought I’d share a little news. For those who are here for great FileMaker info, feel free to skip ahead. (And let’s be honest, that’s the only reason any of you are here.)
As many of you have noticed, my name is not on the cover of FileMaker Pro 11: The Missing Manual. Since I know a lot of people will ask, here’s the scoop:
I love O’Reilly and we had (have?) a fantastic relationship. I still remember the first O’Reilly book I ever read (The Camel Book), and the awe I felt at its immense quality. It was absolutely an honor to work with a company whose product I respect so much.
It should go without saying, but I love my former co-author, Susan Prosser, who has been a professional associate and personal friend for years. I loved working on the book. The process was hectic, and aggravating, and fun, and wonderful in a million ways.
But writing a book is a labor of love. If you add up all the late nights and killer deadlines and huge work, and divide by the modest revenues, it’s a tough sell. I always considered it worth it because (here comes the honesty) it helped my reputation, helped me sell my services, and was a bit of a thrill. But the calculus has shifted a little. I don’t know how much the book does those things anymore. So when it came time to fire up Microsoft Word (barf) and start in on a revision for 11, I realized I couldn’t justify the stress and life-disruption this time around. This was a personal decision on my part.
In the beginning, I wrote the never-publised FileMaker Pro 7, The Missing Manual over months of long nights. Susan later saved my bacon by revising my manuscript for FileMaker 8 so it could see the light of day. I then had the opportunity to work on revisions for 9 and 10 with Susan. In all, we wrote or revised approaching 3,000 pages, over a thousand screen shots and figures, and a dozen screen casts.
After all that, I would be remiss if I didn’t say this: Thank you.
Thank you to Vince Menanno who convinced me to pursue the project in the first place. Thank you to Tim O’Reilly who blew my mind when he responded personally to the inquiry placed on the O’Reilly web side. Thank you to Susan Prosser who literally saved the book from failure, and made later revisions a joy. Thank you to Stuart Gripman of Crooked Arm Consulting, who ably picked up the reins for FileMaker 11. Thank you to my first editor, David Pogue, who’s early feedback perfectly balanced advice and encouragement. Thank you to Nan Barber, my editor, who made the book much better than it otherwise could have been. Thank you to my family, who surrendered a husband and father to the computer for a few months every couple of years. And thank you to everyone who read the book, especially those of you who left positive feedback on Amazon.com : ).
It is a little sad to watch a big new FileMaker release roll out and not be working on a book. In some ways I miss the crunch, and especially the deep-dive into the minutia of a new release.
Ok, I’m done with all that now. Back to regularly scheduled (Ha!) programming.
Geoff
Six Fried Rice Methodology Part 2 – Anchor Buoy and Data Structures
In the last post about the Six Fried Rice methodology I went over the concept of data separation and why we use it. That is essentially the starting point for how we structure the files of our system. One file contains all of the UI components and scripts for the system while a second file contains all of the actual data. Just doing that one basic separation provides several benefits that generally make life easier, but how we structure the data within the Base File itself is equally if not more important than the data separation.
To begin with I’m going to go over how we initially setup our tables. The first thing we do after we create a table is add a number field named ID that is unmodifiable and set to auto-enter a serial number. This may seem obvious to a lot of people, but as an absolute beginner I never even thought about this approach. For simplicity’s sake we never call our primary ID field anything but ID. If we ever need to store another table’s ID for relationship purposes, we use the convention ID_ORDER or ID_PURCHASE_ORDER_LINE. Naming things according to a set standard of prefixes makes organizing and locating our fields a little bit easier and saves us from a lot of hunting through long lists of names. Typically we apply this to all fields that can logically be grouped together. For example, it we have two date fields and two purchase order fields in a table, here is what our field list would look like:
- DATE_ESTIMATED_SHIP
- DATE_PROMISED
- ID
- ID_PO
- PO_VENDOR_NAME
- PO_CREATED_BY
Standards like this help us keep our fields consistent, and should I ever need to work on a table that Jesse has created I will know exactly what I’m looking at. Logically grouping your fields and giving them a standard naming convention will make navigating your data structures considerably easier.
Come to think of it, most of what our conventions are about is legibility. The simpler and more standardized you can make your systems the easier it is to come back to them at a future date and work on them after the particulars of the system have left the forefront of you mind. With that in mind here is how we approach the Anchor Buoy relationship graph setup.
Anchor Buoy is another of those concepts that like Data Separation require a little bit of additional setup in the beginning, but pays off big when you start using it in conjunction with context independent scripting and our layout design style. The basic idea is to create your Table Occurrences in several tree structures, with the root of the tree being the context that you are actually working in. Here is an example of a relatively simple database relationship structure to help get the concept across. I’ll break down the individual components and why they are setup the way they are after the image.
First off, there are the root tables. In this graph they would be INV__INVOICES and CUS__CUSTOMERS. These table occurrences form the root of their respective contexts and give a clear starting point to the relationships that follow. Let’s take a look at the Invoices tree to delve into how we setup these relationships and why we structure our data around them.
Any time anything happens to an invoice and any information needs to be pulled onto an invoice, the place we start is the INV__INVOICES table. As you can see, all of the basic information we would need for this invoice is available through some relatively simple relationships. If I need to get to the customers billing or shipping address, I can go directly through the customer table to the associated address. This setup allows me to easily and clearly identify the relationships that my data relies upon. For basic information, there is nothing more complicated than that. I would recommend labeling any table occurrences that are based on anything but the basic ID relationship. For example, when I am going to the customer’s billing and shipping addresses I have two separate occurrences of the address table using the same basic naming convention with the only difference being that I append the #shipping or #billing label to the end of the name. And just as a note, because this was the first mistake I made when trying to use Anchor Buoy for the first time, never link a layout to anything but one of the root tables. Using one of the buoy tables that is further down stream than the root can create all types of context issues when scripting and setting up calculations. I can’t stress how many headaches you can avoid by having only one context to that you work in!
Now, calculations. Let’s say I wanted to store the order total on the order record itself, and I wanted to pull the total directly from the order lines associated with the order. Using the base table system, the context I would start in is ORD__ORDERS. From there, I would pull the sum of the order lines from the ord_ORDER_LINES table occurrence. This one calculation will now be the only place where the order gets it’s total price from. So, when I want to display the order total on the invoice itself I would just grab that calculated total from the inv_ORDERS occurrence. Basically the goal is to always set your data structure up so that your base tables do all the work of gathering information and creating contexts that will allow you to easily traverse your data when you are scripting and designing your layouts.
The end goal of using Anchor Buoy and Data Separation is to lay the ground work for a development environment that is simple to understand, powerful to work within and flexible enough to handle most any scenario that a customer can come up with. Once you have this base in place, using our scripting methods and layout design has the potential to greatly reduce the complexity of development. The next post will focus mainly on setting up the basic operating layouts we use and how we script around them to provide fully encapsulated and single point functionality.
Using Global Variables for List Selections
A funny thing happened while I was stumped over the question of how to allow one of our clients to select and deselect multiple items in a list. Multiple people would be using the list at the same time. I was struck by the fact that I’ve been making something more complicated than it needs to be for as long as I’ve been working with Filemaker. After toying with various methods which included adding fields to the records in the list, join tables, global fields and horribly convoluted scripting it occurred to me that you can actually write a relatively simple custom function to handle it all. It will add or subtract a value from a list that is stored in a global variable and then simply do some conditional formatting to highlight the selected items in the list. No additional fields or scripting are needed, the global variable is session specific and once you are done you’ve got your list of data ready to go as is.
Here are all the components of this process and how they fit together:
AddOrRemoveFromList(list, value) – The custom function that will maintain the values in the list for you.
$$selectedItems – The global variable that stores the list I want to operate against.
Selected Item – The actual record that you are adding to the list.
First I’d like to explain the custom function. Basically, that function checks a list that you the value you pass in. If the function finds that value, it removes the value and returns the new list sans value. If it doesn’t find the value, it returns to you the list with the value appended to the end of it. Here is the what AddOrRemoveFromList(value, theList) contains:
let(
[newList = Substitute(¶ & theList & ¶, ¶ & value & ¶, ¶)];
if( PatternCount( ¶ & theList & ¶; ¶ & value & ¶) = 1 ; Middle(newList, 2, length(newList) – 2); theList & if(not isempty(theList); ¶) & value)
)
The reason for all those extra ¶’s is for encapsulation and searching. When you think about what a list actually is, it turns into this: apple¶orange¶pear¶applejuice. If you want to search that list for apple right now you would be editing both the values apple and applejuice. To get around that we put an extra ¶ before the first entry in the list and after the last entry so our list now looks like ¶apple¶orange¶pear¶applejuice¶. So, if we now search for ¶apple¶ we would only be working with the actual apple value. To get the correct list format returned after that you just need to use the middle function to strip the first and last ¶ from the list.
So, we now have a custom function that will handle adding and removing values from a list. The only thing we have to do to make a selection is to say where we want to store the list. Using a global variable has the advantage of being easily accessible and being completely separated from your data structure, so you don’t need any additional fields or records to keep track of what the user wants selected. So, if you choose to use a global variable, the only step you need to take is to set the variable $$selectedItems using the custom function. Here’s what that step looks like:
If you are unformiliar with our parameter passing system, please take a look at this. So, we now have the selected items being added to the desired list automatically. So, let’s let the user see what they’ve selected. Using conditional formatting we’ll just apply that same list checking we used in the custom function. The formula should be:
PatternCount( ¶ & $$selectedItems & ¶; ¶ & item & ¶) = 1
“item” in the above example should be whatever you passed into the list. No
Dynamic Finds in FileMaker 10
In an earlier article we explored an isolation technique that was specifically designed to work on ID’s and that’s it. There is a lot more that can be done with the new Set Field By Name step and in this article we’ll take a look at a slightly more complex but much more versatile search method. This technique will rely on the use of our dictionary functions, so taking a look at this article is a definite prerequisite.
The Basics
First off lets go over the basic concept. Essentially, the idea is that with Set Field By Name step, we can now pass in the field we want to search as well as the value to search for. So, instead of having to script a bunch of hard coded Set Fields we can call one find script, pass in a bunch of find criteria and have that script set the fields we want dynamically. With this new system, you can have one script perform just about any find you need.
Before our script will work, we need a system to pass the name of the search field as well as the value to search. Using our parameter passing technique it’s actually pretty simple to send the information. To pass in a specific field name we can use the GetFieldName() in conjunction with #(). Here’s the example#(getfieldname(yourfieldnamehere); criteria). So, let’s say you have the NAME field in a table called CUSTOMER and you want to find Sarah. Here’s what I need to pass into the script:
#( GetFieldName( CUSTOMER::NAME ) ; "Sarah" )
At this point we’ve determined what we want to search for and where we want to search. Now we need to write a script that will take this information and generate our search. In concept it’s pretty simply. Just loop through any parameters and simply set the specified fields to the value provided. Now, since we are using theSet Field By Name script step, be sure that the field name you pass in is the fully qualified field name and that you have the right context before you call the find script. So, if you are on a layout on the CUSTOMER table and you want to search the ID field be sure that the field name you pass in is CUSTOMER::ID. If you pass in ORDERS::ID, you’ll never find anything!
Here’s the actual script:
The first order of business is to take all of the parameters that were passed into the script and stick them into a variable so we can operate on them more easily. What you end up with in the $dict variable is the full dictionary of passed in parameters. Now, we enter find mode, enter a loop and iterate through the dictionary values to create our individual criteria.
The loop is a little complicated. It uses a couple different of our custom functions in our previous article. We use DictFirst to grab the name of the parameter on the top of the list, then DictGet to get the value that corresponds with the name we’ve found. Those two values are then used to Set Field By Name. Finally we need to remove that entry from the dictionary. Once we’ve emptied all the entries out of the dictionary, we exit the loop.
Note: If you pass in the same field multiple times the final value passed in will be the one ultimately used. You’re basically just overriding the value every time you pass in the same field.
To perform the find, we use our tried and true Safe Find script and then return the error to make sure the calling script knows what happened.
This system has the potential to save us a tremendous amount of time. Basically, you can construct your finds on the fly by calculating the names of the search fields and the according search value. This find script is relatively simple and we’ll be fleshing it out to allow for multiple requests and omits, but this is a good first step.
Note: The find script itself does not navigate to any layouts. We’ve designed it around the context independent scripting principles that we here at Six Fried Rice love so much. Before calling the find script, make sure you are in the correct context for the find you are trying to perform.
find-database