In its simplest form, the Show Custom Dialog script step just delivers a message to the user. Figure 15-3 shows how to configure a box like this, and how the final result looks.

Figure 15-3. If you simply need to tell users something, give your custom dialog box only a title, message, and one button. In this picture, you can see the resulting dialog box below the options set in the script step. The title appears along the top of the dialog box, the message inside, and the button at the bottom. The first button (called Default Button in the Show Custom Dialog Options window) is the one that users can “click” by simply pressing Enter or Return—or, if they insist, by clicking with the mouse.

You can generate the title and message from a calculation to make it more dynamic. For instance, if someone wants to see all open invoices for a particular client, but there aren’t any, you can show a calculated custom message that says, “Lucy Pevensie’s account is paid in full,” instead of something equally true, but less helpful, like “No records were found.”

Using a custom dialog box like this in a script is simple. Just use the Show Custom Dialog step wherever you want in your script. When the dialog box pops up, your script waits for the user to click the button, and then continues with the next step.

To ask the user a simple question (one with either two or three possible responses), just add more buttons to the dialog box by filling in the Button 2 and Button 3 boxes in the Show Custom Dialog Options window. You can see this process in action in Figure 15-4.

Figure 15-4. This custom dialog box asks a question and offers two user choices (Cancel and Post). Notice that Cancel is the default button, acting as a safety device, since people often reflexively hit the Return or Enter key when they see a dialog box. By making Cancel the default button, you can prevent potentially destructive processes when someone fails to read instructions.

When a dialog box like this appears, your script waits for a click of one of its buttons, then continues. Although FileMaker knows whether button 1, 2, or 3 got clicked, you have to add logic to your script to tell the program what to do in each case. For that, you use the Get ( LastMessageChoice ) function and If statements based on its results. (The function returns 1 for the default button, 2 for Button 2, and 3 for Button 3.) The script in Figure 15-5 uses Get ( LastMessageChoice ) to take some steps only when someone clicks a custom dialog box’s default button.

Figure 15-5. This script uses the Show Custom Dialog script step to ask a question (the same question shown in Figure 15-4). If your user clicks Post, the script creates a new record and sets the Payments::Amount field appropriately. To make this decision, it uses an If step with the condition Get ( LastMessageChoice ) = 2.

If you need user input that goes beyond two or three simple choices, you can venture into the second tab—Input Fields—in the Show Custom Dialog script step (Figure 15-6). The Show Custom Dialog Options window’s Input Fields tab lets you add fields to your custom dialog box. To add a field to a custom dialog box, turn on one of the “Show input field” checkboxes in the Input Fields tab of the Show Custom Dialog Options window. When you do, FileMaker shows a Specify Field dialog box, in which you can pick the field to use. (If you want to change the field later, click the Specify button).

You can also give the field a label (like Start Date and End Date in Figure 15-6). The label can be the result of a calculation. Finally, if you turn on “Use password character” for an input field, the field works like a typical Password box: It shows * or • instead of the letters you type, so someone watching over your shoulder can’t see what you’re entering.

The fields you add to a custom dialog box have some limitations, including the ones listed below. (For other alternatives, see the box on Section 15.3.)

The key to each of these techniques is the Perform Script script step. It lets one script run another script—a subscript, in programming lingo. When you add the Perform Script step to your script, you get only one option: a Specify button. Figure 15-7 shows how to use the Specify Script dialog box to tell your script which subscript script you want to run and what file to find it in.

Figure 15-7. The Specify Script Options dialog box is where you tell the Perform Script step what script to run. You can pick any file you have a reference to from the File pop-up menu (or choose Add File Reference to add a reference to another file). The script list shows every script in the selected file. Just select the script you want and click OK.

When one script runs another, it waits for the subscript to finish before continuing. Imagine you have a script called Find Unpaid Invoices and you want to make a new script to print statements for all your customers. The Print Statements script might start off by performing the Find Unpaid Invoices script. It would then go to the appropriate layout, sort the records, and print them. The script might look like the one shown in Figure 15-8, which performs the Find Unpaid Invoices script, goes to the Statement layout, and then sorts and prints the invoices.

Figure 15-8. This script needs to find all the unpaid invoices before it can print statements. Luckily, you already have a script that finds unpaid invoices, so the first line in this script is the Perform Script step. That way, FileMaker first runs the Find Unpaid Invoices script, and waits for it to finish. By the time the second step (Go to Layout) runs, the find is finished and the found set includes the correct records.

When you perform a script in another file, FileMaker uses that file’s front most window (which is behind the user’s window). If it doesn’t have any windows, FileMaker automatically creates one. In either case, you’re in a unique scripting situation: The window you’re working with in your script isn’t the one in front. Here is where the Current Window option in the Select Window script step comes in handy.

Normally a script ends when its last step runs. For example, the script in Figure 15-8 ends as soon as the Print step finishes. But you can force a script to end early if you want to. FileMaker actually has two script steps that end a script prematurely: Exit Script and Halt Script. These two steps do exactly the same thing if you run a script yourself directly. But if the current script was run by another script—via the Perform Script step—then they do different things. The Exit Script step tells FileMaker to stop the current script and continue the script that ran it. Halt Script, on the other hand, causes all script execution to stop immediately, no matter how the current script was started. (To learn more about these ominously powerful steps and when to use them, see the box on Section 15.3.3.)

When a script or button runs a script, the Specify Script window shows a box labeled “Optional script parameter.” As you can see in Figure 15-9, you can use script parameters to influence a script’s behavior when you run it. For example, you might make a script that can sort the records in four different ways. The script parameter you specify when you run the script tells it which sort order to use.

Your script accesses its parameter using the Get ( ScriptParameter ) function. This function returns whatever value you specified in the “Optional script parameter” box in the button or Perform Script step.

Figure 15-9. When specifying a script to run, you get the chance to supply an optional script parameter. This can be any value you type in the box, or you can click Edit and enter any FileMaker calculation. When the script runs, FileMaker evaluates this calculation and remembers the result. The script can ask for the result at any time, and use it anywhere it can use a calculation.

Suppose you wanted buttons to sort your Customer records by Name, City and State, or Zip Code. You might think each button needs its own script, but using script parameters, you can get the job done with just one script. Each button passes a different parameter to the script, which takes the appropriate action. Before you can create the buttons, you need to create the script they’ll run:

Figure 15-11. This is how your finished sort script should look. It has three branches (one for each of the possible sort orders). Now you just need to add buttons that run this script.

Now it’s time to add buttons to the Customers layout to make it easy for your users to run the script. You need three buttons, one for each sort in the script you just wrote.

Time to work on the second button. To do so, you can save time by starting from a copy of the one you already have.

To add the third button, follow steps 1 through 3 again. This time, set the parameter to “Zip Code” and name the button Sort by Zip Code.

These three buttons each call the same script, but the script parameters attached to each button allow the script to do double, even triple duty. To make this script even more flexible, you could keep adding Else If and Sort steps, and then make a button with a script parameter that matches each new test. See the box below for a way to use script parameters to extend the flexibility of the Notes Separator script you wrote back in Chapter 14.

The Set Error Capture script step lets you turn on error capture. That way, instead of displaying potentially confusing error messages to your database’s users, FileMaker keeps track of error information (captures it) so you can pull it into your script and handle it there. Although error capturing is a great feature, it’s not part of FileMaker’s normal behavior. You have to activate it by adding the Set Error Capture step to your script, and choosing the On option. At any time in the script, you can turn it back off again by using the step a second time and switching the option off.

If a script turns error capture on, and then uses the Perform Script step to run another script, the second script also runs with error capture on. In other words, the error capture setting sticks around as long as scripts call other scripts. But as soon as script execution stops for good, FileMaker turns off error capture. Understanding this behavior helps you determine when you need an Error Capture script step and when it would just be redundant. Figure 15-13 shows a script that turns error capture on before performing a find, then turns it back off when it’s done.

Figure 15-13. This script performs a find, but first it turns error capture on. If the find fails, the user doesn’t see an error. The script can then check for no found records (in the If step) and show the user a more customized error.

As discussed in the box on Section 15.4.2, you could just turn error capture on so that your script ignores any and all errors—but that’s not good script writing. The best way to use Set Error Capture is hand-in-hand with the Get ( LastError ) function, described next, to achieve error-free results.

When error capture is on, FileMaker doesn’t just ignore the errors. Rather, it remembers which error occurred and gives you the chance to ask about it if you’re interested. The script in Figure 15-13, for example, isn’t interested. It doesn’t ask if an error occurred at all. Instead, it just checks to see if the find worked by counting the records in the found set.

But sometimes you can use such error information within your script, much like any other value, to trigger other script steps. To check an error, use the Get ( ErrorLastError ) function to find out what happened. This function returns an error code, which is always a number. If there was no error in the previous step, Get ( LastError ) returns a zero. In other words, it doesn’t return the number of the last error that occurred. Instead, it returns the error number for the last step that ran. You wouldn’t, therefore, put a comment step before the step that checks the last error, since the comment step itself always sets the last error back to zero. The same goes for End If and even Set Error Capture [Off].

In FileMaker, just about everything that could possibly go wrong has its own error number. This feature gives you a lot of flexibility, but it also makes it a real pain to figure out which errors you should check for. A complete list of error numbers is found in Appendix B. Luckily, most of these errors are pretty obscure, and chances are you’ll never have to worry about them. Here’s a list of the more common error numbers you might actually be interested in:

One more script step has ramifications when dealing with errors: Allow User Abort. This step lets you turn off a user’s ability to cancel the script. Allow User Abort only has two options: on and off. Its normal state is to be turned on, unless you specifically turn it off with the script step. Like Set Error Capture, when you turn user abort off or back on within a script, the setting carries through any subscripts called by the main script. Allow User Abort always turns back on again when the script finishes running.

If you turn user abort off, but leave error capture on, the Cancel button in error messages is removed, so the user is forced to continue the script. Turning off user abort also prevents the user from pressing Escape (Windows) or ⌘-period (Mac OS X) to cancel a running script. Finally, if the script pauses, the user doesn’t get a Cancel button in the status area. Instead, the only choice is to continue.

Planning a big script is usually an iterative process. You start by outlining the steps the script will take in very general terms. You can then go in and fill in more and more detail with each pass. When you’re done adding detail, you know exactly what steps your script will use. Using the invoicing script as an example, you’ll see how FileMaker gives you the tools to plan and execute your new script.

You can do this planning on paper, or in a word processor, or any other tool you choose. But one good place you might not think of is ScriptMaker itself. Since the planning process involves a series of steps, and since it naturally produces the finished script when it’s done, ScriptMaker is an ideal candidate. Just start with comments explaining each general step. You can then replace a comment line with some real script steps, and perhaps more comments. When you’re done, the script is written. As an added bonus, you never get lost because you always have a comment to tell you exactly what you still need to add, and exactly where it should go.

For this script, begin your planning by creating the script in Figure 15-14.

Many people forget one of the most critical steps in scriptwriting—planning for exceptions. The old saw, “A stitch in time saves nine,” truly applies. Spend a few minutes now, thinking ahead to what might go wrong and planning how to prevent problems. These minutes can save you hours of troubleshooting and repair work on your data later.

Look at what your script is supposed to do and try to think of reasonable exceptions—situations where your script might not be able to do its job. Thinking of exceptions is important for two reasons:

Figure 15-14. This is the first rough-cut of your script. Of course, since it’s all comments, it doesn’t actually do anything yet. Instead, it just maps out, at the most general level, what the script is going to do. You’ll add more and more details as you go. Since these comments are just placeholders for real steps and comments yet to come, each one starts with the word TODO. You’re welcome to use any marker you want, but it helps if you can easily tell these placeholder comments apart from the real comments you’ll add later.

For example, the Invoice creation script could run into two potential problems. The problems and fixes, if you haven’t already guessed, are as follows:

After you’ve made these changes, the first three TODO comments now suggest that you find some records, then make a decision based on whether any records were found (Figure 15-16, top). In order to do this, you need to store the counts somewhere. In other words, you need a new tool called a script variable, which you’ll learn about later in this chapter.

However, one of the remaining TODO comments is a bit broad. How are you going to “Add the line items?” Since you need to create line items from two different tables—expenses and timeslips—you handle them separately. First, you process expenses, and then you process timeslips. To “process” the expenses, you loop through the unbilled Expense records and add a line item for each one. Ditto for the timeslip records. Edit your script to match Figure 15-16, bottom.

Figure 15-16. Top: The script now has If and Else sections for the two possible outcomes: If there are no unbilled expenses or timeslips, it shows the user a custom dialog box that says, “An invoice was not created because this job has no unbilled activity.” If the job does have unbilled activity, on the other hand, it creates the invoice, adds the line items, and shows the user the invoice. Of course, you still need to implement those steps. Bottom: The script now explains in even more detail what it needs to do. You still have to add the necessary steps to find unbilled timeslips and expenses, to create a new invoice attached to the current job, and to add line items for the found expenses and timeslips. Finally, you’ll go to the Invoices layout to show the new invoice to the user.

Now that you’ve tested for exceptions in your script, you’ve come to a fork in the road. You could write a script containing all the necessary steps, but it would be long and hard to follow. For example, the End If steps at the end of the script would be a long way from their If and Else counterparts, making it hard to figure out where they belong. Alternatively, you might decide to break this script into pieces. Either way, you should make this decision before going any further.

You might consider a few other things: If you have several small scripts, you can run any one of them individually. This method gives you the chance to try out parts of the script to see if they work properly. Also, since you can pass parameters to scripts when you run them, using subscripts to do some jobs often saves you the trouble of adding another script variable. But in the end, either approach is perfectly valid. Some people really like short simple scripts, even if it means more of them and more opening and closing of the Edit Script window. Others find this multiple-script song and dance far worse than a script that needs a little scrolling.

For this example, you’ll be creating subscripts. Figure 15-17 shows a repeat of your script-in-progress with places for subscripts clearly identified. Each of these scripts are relatively short and easy to understand, but you’ll have five scripts in all. (See the box on Section 15.5.4 for some tips for breaking up long scripts into subscripts.)

Now that you know what subscripts you need, you can create them. And you already know how to do that because a subscript is just another script. To help you keep track of what you’re doing, you should create all the subscripts you’ll need first—but that doesn’t mean writing them all yet. You just need to create scripts in the Define Scripts window and make some placeholder comments to remind you what they should do.

Figure 15-17. Each marked step (or group of steps) becomes a sub-script in the finished script. The first script finds the unbilled expenses and timeslips; the second creates the new invoice and attaches it to the job; the third loops through all the expenses and adds the necessary line items; the fourth adds line items for all the timeslips. The Create Invoice for Job script performs each of these four subscripts at the right time to pull everything together.

Start by adding a new script called “-find unbilled activity.” This script name starts with a dash, and is in all lowercase. These clues help to remind you later that this script is a subscript used by another script, and not one you should expect to work all by itself. You can see the “-find unbilled activity” script in its planning stage form in Figure 15-18.

Figure 15-18. You’ll create the “- find unbilled activity” subscript first. Since a subscript is just another script, approach it like you did the Create Invoice for Jobscript: Start with a plan. You can come back later and implement the actual script steps.

The “- process unbilled expenses” and “- process unbilled timeslips” scripts are almost exactly the same. The only difference is that each script starts by going to a different layout, and sets a different Line Item ID field. It’s more efficient to combine them, and use a script parameter to decide between the two sets of behavior. Figure 15-19 shows the new “- process unbilled items” script, which works equally well for expenses and timeslips. Create the two subscripts shown in Figure 15-19 now.

Figure 15-19. Top: This script does the job of both “- process unbilled timeslips” and “- process unbilled expenses.” It checks the script parameter to decide which type of item to work on, as you can see in the If steps. Bottom: The “-create invoice record” subscript needs to set its context by switching to the invoices layout, where it will create a new record. Then it keeps context straight by switching back to the layout it came from.

With the three final subscripts defined, you’re ready to revise the original Create Invoice for Job script. Now, you can delete the TODO comments that served as placeholders for your subscript and replace them with the new subscripts, even though they’re made up of placeholder comments themselves.

The last TODO comment doesn’t need a subscript. It can just use the Go to Layout script step. In Figure 15-20, you can see the result.

Figure 15-20. This is the finished Create Invoice for Job script. You haven’t finished writing the subscripts yet, but you can already see how easy this script is to follow: Just by reading the subscript names and parameters, you get a very good idea what this script is doing.

You already know that you can set a script parameter for a button as it runs a script. You can also set a script parameter when you run a script with a Perform Script step. Take a look at Figure 15-21 to see an example of how that can be helpful: The “-find unbilled activity” script needs to find all the activity for the current job. Since the Create Invoice for Job script is performing this script from the Jobs layout, it can set the Job ID in a script parameter.

Here’s how to set a script parameter on a Perform Script step:

The Create Invoice for Jobs script calls all the proper subscripts and sends script parameters to the subscripts where it’s appropriate. Now it’s time to finish writing your subscripts.

You’ve created a series of subscripts that have placeholder comments to remind you what process the script will perform. Just as you did with the main Create Invoice for Job script, you’ll finish each subscript and turn them into working scripts by replacing those comments with real script steps.

The “- find unbilled activity” subscript

Now that a script parameter is passing the Job ID to the "- find unbilled activity” script, you can write steps to find the right expense and timeslip records. The first TODO item is a cinch: Just use the Go to Layout script step to switch to the appropriate layout. Next, you need to find expenses for the current job that don’t have a line item ID. This request is one of those find requests that you can’t enter directly in the Perform Find script step because you need to use the script parameter, whose value you don’t know until the script runs. Instead, you have to use Enter Find Mode, Set Field, and Perform Find together to build and perform the right find request:

1. 1. In ScriptMaker, double-click the “-find unbilled activity” script to edit it. Add the Go to Layout script step to the script.

   If necessary, drag the Go to Layout step to the top of the script. If a script step is selected, any new step you create lands just below it. But there’s no way to make a new step appear right at the top of a script that already has steps in it.

2. From the Go to Layout Script Step Options area’s Specify pop-up menu, choose Expenses. Select the first TODO comment, click Clear, and then click Go to Layout again.

   FileMaker removes the step from the list. You select Go to Layout so the next script step you create lands below Go to Layout and becomes the second script step.

3. Add the Enter Find Mode script step to the script. Turn off the Pause checkbox and turn on the “Specify find requests” option.

   The Specify Find Requests dialog box pops up. One part of the find request you’ll be using is the same every time: You need to find expenses with nothing in the Line Item ID field. You’ll add this to the find request here, and then insert the Job ID in the next script step.

4. If any requests are showing in the list, click Delete until they’re all gone. Click the New button.

   The Edit Find Request window appears.

5. From the “Find records when” pop-up menu, choose Expenses, and then click the Line Item ID field.

   The selected field is now highlighted.

6. Click the Insert Symbol button and choose “== Field Content Match” from the resulting menu. Click the Add button. Then click OK, and then OK to get back to your script.

   FileMaker puts “==” in the Criteria box. If you prefer, you can just type “==.” Two equal signs, used alone, tell FileMaker you want records where the Line Item ID field matches nothing. These are all your expenses that haven’t been billed.

7. Add the Set Field script step to the script, and then turn on the “Specify target field” checkbox. Select the Expenses::Job ID field and click OK.

   The step should appear after the Enter Find Mode step. If it doesn’t, move it there now. Make sure you get the right Job ID field: You need to pick the Expenses table from the pop-up menu first.

8. Click the Specify button to the right of “Calculated result.” In the calculation box, enter “==” & Get ( ScriptParameter ). Click OK.

   This calculation puts the Job ID (from the script parameter) into the field, with “==” before it, telling FileMaker you want to find records that match this ID exactly. Together with the find request above, the script finds unbilled activity for the current Job.

9. Add the Set Error Capture script step to the script and make sure its “On” option is selected.

   You’re about to perform a find, and you don’t want the user to see an error message if there are no unbilled expenses.

10. Add the Perform Find script step to the script.

    The script step belongs below the Set Error Capture step. Make sure you don’t select Perform Find/Replace by accident.

11. Add another copy of the Set Error Capture step to the script, this time with the “Off” option selected.

    You can select the existing Set Error Capture step, then click the duplicate button, then drag the new step into place and set the option to “Off.” Once the Perform Find step is finished, you want FileMaker to stop capturing error messages.

Your script should now look like the one in Figure 15-21.

Figure 15-21. The “- find unbilled activity” script is half done. The first six steps find the unbilled expense records.

Since it isn’t exactly obvious what this script’s doing, it’s a good idea to add a comment to the top explaining it in plain English. So far, your script finds unbilled items in preparation for creating an invoice. But you need to count the number of records the script has found and store that value. That’s where script variables come in.

Global fields are great for storing a value that’s not tied to a specific record in a table. Script variables are similar—you need to store a value for your script to use, usually when you’re testing a condition. Use the Set Script Variable script step to set a variable. There are three options available for this script step:

Adding a Set Variable script steps is easy. Open your “- find unbilled activity” script in ScriptMaker:

Your script finds all the unbilled expenses in your database and remembers how many records it’s found. Now you have to repeat the process for timeslips. First, though, add a comment to the top of the script that says, “Find and count all unbilled expense records.” In the next section, you’ll see why this comment makes sense.

Copying and editing existing script steps

Since the timeslips half of the script is almost a duplication of what you’ve done in the past two tutorials, you could repeat all those steps above and you’d be done. But it’s a lot faster to duplicate those steps, then make a few changes so that your duplicated steps operate on the timeslips table, not expenses. Here’s how:

1. Click the first line in the script (the comment you just added). With the Shift key held down, click the last Set Field step, and then click Duplicate.

   FileMaker creates an exact copy of the selected steps. They all wind up below the first set.

2. Double-click the second comment step in the script (the first line of the new set of duplicated steps). Change the word expense in the comment to timeslip. Click OK when you’re done.

   The comment shows that this section finds and counts unbilled timeslips. It’s also an easy way to spot where the second half of your script starts.

3. Select the next step—Go to Layout—and, from Script Step Options area’s popup menu, choose Timeslips.

   This time you want to work with Timeslips records, so you need to go to the Timeslips layout.

4. Double-click the next step: Enter Find Mode. Double-click the find request in the list. Select the only criterion in the list. From the “Find records when” popup menu, choose Timeslips and in the field list, select Line Item ID. Finally, click Change.

   You’re changing the find request so that it searches for empty Line Item IDs in the Timeslips table instead of in Expenses. The line in the criteria list changes to show Timeslips::Line Item ID instead of Expenses::Line Item ID.

   THINK LIKE A PROGRAMMER: Testing Multiple Conditions Redux

   Back in the box on Section 13.4.2, you learned how to write a script that tested multiple conditions. Now that you’re familiar with setting variables, it’s time to learn how to take a more advanced approach to the same problem. Instead of calling three different Custom Dialog boxes that are each hard coded, you can set your message in a local variable, then call a single Custom Dialog box that changes based on which condition tested as true.

   Why go to all that trouble? It’s certainly not because FileMaker gives you a limited number of Show Custom Dialog boxes and you have to ration their use.

   Here’s how it looks:

   	If [ Get ( CurrentTime ) > Time ( 4; 0 ; 0 ) and Get ( CurrentTime ) < Time
   	( 12 ; 0 ; 0 )
    ]
   			 Set Variable [ $customMessage ; Value:"Good morning!" ]
   	Else If [ Get ( CurrentTime ) > Time ( 12 ; 0 ; 0 ) and Get ( CurrentTime )
   	< Time ( 18 ; 
   0 ;
   	0 ) ]
   			 Set Variable [ $customMessage ; Value:"Good afternoon!" ]
   	Else
   			 Set Variable [ $customMessage ; Value:"Go to bed" ]
   	End If
   	Show Custom Dialog [ Title: "Greetings" ; Message: $customMessage ; Buttons: "Thanks" ]

   The rational behind this little exercise is to give you a taste of the software engineer’s approach to FileMaker’s variables. By thinking in terms of storing data that doesn’t need to last inside a local variable (which itself has a very short shelf-life), instead of as an option in a dialog box, you’re well on the way to solving more advanced problems as they arise. And if you’ve come from another programming background, you’ll be glad to see that FileMaker handles variable storage like some of the big toys in the programming world.

5. Click OK, and then OK again.

   These two clicks close the Edit Find Request and Specify Find Requests windows, respectively. You’re back in the script.

6. Double-click the next Set Field step and change the targeted field to Timeslips:: Job ID instead of Expenses::Job ID .

   The Set Error Capture and Perform Find steps don’t need to be changed at all, so you skip ahead to the last step.

7. Select the next Set Variable step (the last non-comment step), and click the Specify button. Change the value calculation to read: $$Unbilled Items + Get ( FoundCount ). Then click OK.

   This time you don’t want to replace the value in the script variable, so this calculation simply adds your new count to it.

8. Select the remaining TODO calculation steps and clear them.

   You can do this in one shot by clicking the first one, Shift-clicking the last, and the clicking the Clear button.

9. Add a Go to Layout step to the end of the script and set it to go to “original layout.”

   Once the script is done finding things, it needs to return to the layout it started on so the script that ran this one won’t be surprised by a layout change. It’s usually best when a sub-script puts things back the way they were when it started. If you want, you can also add a comment before this step to separate it from the previous block of steps.

Whew! Finally, the “- find unbilled activity” script is finished. It should look just like the one in Figure 15-23. When you’re done looking, click the OK button.

Figure 15-23. This is the completed “- find unbilled activity” script. It finds and counts the expenses, and then the timeslips. This really serves two purposes in the final process: The $$unbilled items variable helps the Create Invoice for Job script decide if it should create an invoice or not, and FileMaker uses the found sets later to create line items.

The “- process unbilled items” script

Last up is the "- process unbilled items” script. It’s time to look through its TODO steps and see what’s in store. The first two—“Switch to timeslips layout” and “Switch to expenses layout”—are easy. Just replace them with appropriately configured Go to Layout steps. Figure 15-25. shows how it should look. The last TODO step is just as easy. You should replace it with a Go to Layout step, with the “original layout” option selected.

Figure 15-24. The finished “- create invoice record” script is a simple one: just four steps.

Figure 15-25. Once you’ve written the first three subscripts, the whole script looks like this. It checks the script parameter and goes to the right layout accordingly. When the script is finished with its real work, it goes back to the original layout as well. Now you need to deal with the real meat of the script: looping and creating line items.

Retrofitting a prior subscript.

The next item left in the TODO list is “Create a line item record.” This one requires some thinking about what your script knows and when it knows it. Adding a record to the Line Items table is easy:

• Switch to a layout attached to the Line Items table.

• Create a record with a New Record/Request step.

But these two steps aren’t enough. You need to make sure the new line item record is related to the invoice created by the “- create invoice record” step. To do that, you need to put the right Invoice ID in the Line Items::Invoice ID field.

So where can you find the Invoice ID? It’s back there on the invoice record, but when you’re on the Line Items layout you can’t see the current invoice record because you don’t have the right context.

Here’s one approach. You could:

• Hop back to the Invoice table.

• Perform a find to make sure the current Invoice record didn’t change while you were gone.

• Grab the Invoice ID (wait a minute—how did you find the right record if you didn’t know what it was?).

• Then hop back to Line Items.

• Find the new record you just made (hope it’s the right new record).

• Shove the Invoice ID into the new record.

It’s possible to do all that hopping around reliably, but it’d be a mess. It’s much easier to plan ahead and bring the proper Invoice ID with you when you first get to Line Items. This scenario happens often in scripting. You think you have a script completed, but in order to finish a different script, you’ve to come back to it and add a step or two. So, open the “- create invoice record” script” and add a Set Variable step so the Invoice ID is ready and waiting for you when you need it:

1. Select the Set Field script step in the “-create invoice record” script. Add a Set Variable script step just below the Set Field step. Click the Specify button.

   The value you’re setting needs to persist past the end of this subscript, so you create a global variable, using the $$ prefix in the variable’s name.

2. Type $$Invoice ID in the Name field. Type Invoices::Invoice ID in the Value field, and then click OK until you’re back in your database.

   You can use the Specify button to pick the field from the list, but if you do, make sure you get the Invoice ID field from the Invoices table.

Now, as soon as your script creates a new invoice, it stores the Invoice ID in a script variable for later use.

Getting data from a script variable.

The "- process unbilled items” script now has what it needs to add line items to the invoice. You can go back to it and add steps that create a Line Item and relate it to an Invoice now:

1. Select the “TODO: Create a line item record” comment step, and then add the Go to Layout script step to the script.

   The new step appears after the highlighted comment.

2. From the Specify pop-up menu, choose Line Items.

   This layout is associated with the table you want to add records to.

   In order, add a New Record/Request step and a Set Field step to the script. For Set Field, turn on the “Specify target field” checkbox.

   The Specify Field window makes another visit.

3. From the pop-up menu above the field list, choose Line Items. Then select the Invoice ID field, and click OK.

   The Set Field step shows it will set the Line Items::Invoice ID field.

4. Click the Specify button to the right of “Calculated result.” In the Specify Calculation window, set the calculation to $$InvoiceID, and then click OK.

   This puts the invoice ID from the script variable into the new line item’s Invoice ID field.

5. Remove the “TODO: Create a line item record” comment step.

   You’re done with it, so clear it away.

Now the "- process unbilled items” script can create a Line Item that’s related to the appropriate Invoice.

Making a script more efficient.

When you’re writing complex scripts, you often find that your game plan changes. The steps you outlined in the TODO comments made sense when you wrote them, but now that you’re implementing the final script, you see things need to be changed. Take a look at your next TODO comment.

After it creates a Line Item, your script needs to return to the layout it came from. You might be tempted to use Go to Layout with the “original layout” setting, but that won’t do it, because the “original layout” is the layout that was current when the script first started. You need to get back to the layout that the If test switched to at the beginning of this script.

You might decide to repeat the same logic you used at the top of the script: Switch layouts based on the script parameter. That method certainly works, but think about what happens when your script runs, using “timeslips” as its parameter:

• Switch to the Timeslips layout.

• Switch to the Line Items layout.

• Add a line item.

• Switch to the Timeslips layout.

You’ve discovered that it doesn’t make sense to switch layouts at the top of the script after all. You can let the If test at the bottom handle the switching all by itself. Go ahead and remove those first five script steps (from If to End If). The script now looks like the one in Figure 15-26.

Pulling data through a relationship.

The next TODO comment says you need to “Set the description, quantity, and price appropriately.” That was good enough for a rough sketch, but now it’s time to fill in the detail. You have to think about where that data’s coming from and where it needs to go:

• A new timeslip line item. The line item description should match the timeslip description, and its start date and time. The quantity should be the number of hours worked, and the price should be your hourly rate (for now, pretend it is $20).

• A new expense line item. The description comes from the expense description. Add “Expense:” to the front, just to make it clear this is an expense. The price should be the amount of the expense, and the quantity should always be 1.

Figure 15-26. When you remove the If steps and put the steps needed to add a line item in place, the script looks like this. A comment has been added to help clarify what’s going on in the first few steps. On the bright side, you’re almost done.

Clearly, then, you need access to two different tables and you may need to do a lot of layout hopping to get back and forth. You already have an If set that lets you update the right Line Item ID field, and there’s no reason you can’t update the line item record from inside this If block. To figure out the best way to move your data around, take a look at Figure 15-27. It shows the relevant portion of the relationship graph for your database.

Figure 15-27. Remember this relationship graph? The Timeslips and Expenses table occurrences each have a direct relationship to the Line Items table by way of two special table occurrences (see Section 8.2.2.2). This relationship connects a timeslip or expense with the line item it was billed on, and the key field is LineItem ID. By putting the right key value in that field, you create a relationship that lets your script modify records right from the Line Items layout, without switching to Expenses or Timeslips.

If you were doing this process manually, it’d make perfect sense to switch layouts, so you could actually see the record you’re modifying. But a script doesn’t need to see a layout to edit data because it’s using a field reference to make sure the data goes into the right field. Think of it this way: If your script’s context has a relationship to the table you need to edit, then you can use the relationship to pull the data through without switching layouts. That’s just what you do to finish this writing the "- process unbilled items” script:

1. Select the Set Field script step in the script.

   You need to grab the Line Item ID before you start the If test.

2. Add a Set Variable script step. Click the Specify button. Type $LineItemID in the Name field and Line Items::Line Item ID in the value field.

   You want to store the Line Item ID in a variable while you’re here, so you can set in your Timeslips or Expenses record later on in the script. Since you won’t need the value after the subscript has run, you can use a local variable (the kind with a single $ prefix). Now the script creates a new blank line item, attaches it to the right invoice, and puts the line item ID in a global field. Your next job is to get this line item ID into the expense or timeslip record.

3. Select the “TODO: Record the Line Item ID in the timeslip record” comment step.

   You’ll tackle this one first.

4. Add a Go to Layout step, and set it to go to the Timeslips layout.

   You can’t work on a timeslip if you’re not on the Timeslips layout.

5. Add another Set Field step to the script.

   Set the options for the Set Field step so it targets the Timeslips::Line Item ID field and uses the calculation, $LineItemID.

6. Click the Duplicate button. Drag that second copy of the Set Field step down and position it under the last TODO comment line.

   It’s now in the right place, but it sets the wrong field. Time to fix that. (You don’t need to change the calculation because in both cases you’re grabbing the value from the same global field.)

7. Click the Specify button to the right of “Specify target field” and set it to target the Expenses::Line Item ID field.

   If your field names match exactly, FileMaker keeps the Line Item ID field highlighted for you. If it doesn’t, select it yourself.

   Tip

   This saves you a little work, and a lot of hassle remembering which field you’re supposed to be setting. So while you might have thought it was a little confusing to have fields with the same names in different tables, you get a payoff in increased efficiency when you’re rummaging around in dialog boxes.

8. Add another Go to Layout step, this time targeting the Expenses layout, and put it above the Set Field step you just added.

   Again, you need to make sure you’re on the right layout.

9. Delete the two TODO steps that start with “Record the Line Item ID.”

   Isn’t it nice to knock things off your to-do Figure 15-28 shows how your script should look now.

Figure 15-28. Nobody said big scripts were easy. Now that you’ve replaced the TODO comments inside your If step with Set Fields that create a relationship to the Timeslips or Expenses table, you can work on setting the Description, Quantity, and Price fields in the line item.

You’ve now created a Line Item record that’s related to another record in either Timeslips or Expenses (remember, that depended on your script parameter). Next, you need to pull three fields worth of data from the related record, so you need three set field steps. You need one set for Timeslips and one for Expenses, so you’ll add six Set Field steps.

The only hard part—and it isn’t that hard—is figuring out what to put in each field. Here goes:

1. Select the Set Field step that targets the Timeslips::Line Item ID field (it’s the one just after the If step).

   This step makes the link to the line item. So your new Set Field steps go after it.

2. Add a Set Field step to the script. Turn on the “Specify target field” checkbox.

   The Specify Field window arrives on cue.

   Note

   You’re about to set the Description field on the line item record. You’re going to do this by accessing the line item related to the current timeslip. To accomplish it, you need to pick the Timeslip Line Items table occurrence, not Line Items itself. The relationship graph (as shown in Figure 15-28) tells you this.

3. From the pop-up menu above the field list, choose Timeslip Line Items.

   The fields from the Line Items table show in the list. (Remember, this is just another occurrence of the same Line Items table.)

   POWER USER’S CLINIC: Referential Integrity

   By now, it’s ingrained in your developer’s brain that relationships work because there’s a match between key fields in the related tables. But what if you absolutely, positively have to change the value in a key field? You know it’ll wreak havoc with your related records, because as soon as you change the value in the “one” side of the relationship, all the “to-many” records are no longer related to their parent records (or to any other record). In other words, they’re orphaned.

   If you changed the value in key fields manually, it’d be fairly easy to figure out how to keep this from happening. You use the existing relationship to find the child records, change their keys, and only then, go back to the parent record and change its key. The record family is reunited and everybody’s happy.

   Here’s a script that handles that gruntwork for you:

   	Allow User Abort [ Off ]
   	Go to Layout [ "Customers" (Customers) ]
   	Set Variable [ $newID; Value:Customers::NewCustomerID ]
   	Go to Related Record [Show only related records ; From table: "Jobs"; Using layout:
   	"Jobs"
   	(Jobs) ]
   	Loop
   			 Set Field [ Jobs::Customer ID; $newID ]
   			 Go to Record/Request/Page [ Next; Exit after last ]
   	End Loop
   	Go to Layout [ "Customers" (Customers) ]
   	Set Field [ Customers::Customer ID; $newID ]

   There’s still some brainwork that this script doesn’t handle, like making sure that your NewCustomerID value is unique before you try to use it. If you’re changing your key field value, it’s probably not a surrogate key, so you’ll have to know how your business policy creates and insures unique key values, and then apply that logic to your script. And you need to ensure that each related record is unlocked and available for your script to change. (See the box on Section 18.2.1.1 for the scoop on record locking.) Only you can decide whether changing a key field is something you want to hand over to your users or if you’ll manage this task yourself. Whichever way you go, this skeleton script will get you started.

4. Select Description and click OK.

   You’re now targeting the right field. As explained before, you want to put the timeslip date, time, and description in it.

5. Set the calculation for this step to Timeslips::Date Time & “: " & Timeslips:: Description .

   Don’t worry if the box cuts off a bit of the end. Click the Duplicate button when you’re done.

6. For this next copy of the Set Field step, click Specify (to the right of “Specify target field”), and choose the Price Each field.

   You want to set the price to $20.

7. Set the calculation for this step to, simply, 20. Click Duplicate again.

8. For this third Set Field step, target the Timeslip Line Items::Quantity field.

   Since you duplicated the previous step, the Timeslip Line Items table occurrence is already selected; you just need to pick the field.

   Note

   Now you need to calculate the number of hours worked. The Timeslips::Duration field is a time field, so it holds the number of seconds worked. To convert seconds to hours, just divide by 60 twice (once gets you to minutes, the second gets you to hours). You should also round the result to two decimal places so you don’t get ugly or truncated numbers on your invoice. The final calculation looks like this:

   	Round ( Timeslips::Duration / 60 / 60 ; 2 )

9. Set the calculation for this step to Round ( Timeslips::Duration / 60 / 60 ; 2 ) .

   The three Set Field steps are in place for timeslip records. Now you need to add similar steps to the expenses half of the script.

10. Select the Set Field step that is below the Else step in your script, and add another Set Field step to the script.

    This is the same sort of maneuver you took in step 5.

11. Set the step options to target the Expense Line Items::Description field and use this calculation: “Expense: " & Expenses::Description.

    Easy enough. On to number two.

12. Duplicate the script step, set it to target the Price Each field, and use this calculation: Expenses::Amount .

    One more to go.

13. Duplicate the step again. This time target the Quantity field and set the calculation to, simply, 1 .

    Expenses always have a quantity of one.

Creating a looping script.

Your script works great now—if you have only one Line Item to process. But you may have a dozen unbilled items that need to go on your invoice. So you’ll create a loop that repeats for all your Line Items. The toughest thing about creating a loop is getting the steps in the right places:

1. Add a Loop step to the script.

   That’s right: Both Loop and End Loop appear in the script. Your goal is to wrap the Loop and End Loop steps around everything that should happen over and over. That means everything except the Go to Layout step at the end.

2. Drag just the Loop step to the very top, so that it’s first step. Drag the End Loop step down so that it’s positioned between End If and the last Go to Layout step.

   When you’re done, you can remove the “TODO: Loop through each record” comment line.

3. Add a Go to Record/Request/Page step just before the End Loop step. In the Specify pop-up list, choose Next. Click the “Exit after last” option.

   You don’t have to use an Exit Loop If step in this script, because you have a finite set of records to flip through.

If you know ahead of time that you’ll be processing a set of records, you can always start writing your script with a loop. But for testing a script, it’s often easier to figure out what’s happening if you don’t add the loop until you know the core of the script works. Figure 15-29 shows the final script.

Figure 15-29. Here is the finished “- process unbilled items” script. This version has a couple more comments, but otherwise it should look just like yours.

To test your script, first go to a job record that has unbilled timeslips and expenses (or create a new one if necessary). Once you’re on the job record, run your Create Invoice for Job script. In a flash, you should see a new invoice, properly assigned to the customer and containing line items for each unbilled item. You might be a little miffed that it took you hours to build the script, and FileMaker runs the whole thing in seconds. Cheer up, though; think how much time it will save you in the future.

Tip

If the script doesn’t work, you have a few options. First, you can look below to see the complete text of each script and make sure yours match. But if you have FileMaker Advanced, you should also read about the Debug Scripts and Data Viewer tools in Chapter 19. These gems can make hunting down script problems a breeze.

To help you hunt down problems, here are the four scripts you just created, with all options fully specified (you can also get them at www.missingmanuals.com).

	If [ Get ( LayoutName ) = "Jobs" ]
	Perform Script [ Script: "- find unbilled activity";
	Parameter: Jobs::Job ID ]
	If [ $$unbilled Items = 0 ]
	Show Custom Dialog [ Title: "Create Invoice"; Message: "This job has no
	unbilled activity, so no invoice was created."; Buttons: "OK" ]
Else
	Perform Script [ Script: "- create invoice record"; Parameter: Jobs::Job ID ]
	Perform Script [ Script: "- process unbilled items"; Parameter: "timeslips" ]
	Perform Script [ Script: "- process unbilled items"; Parameter: "expenses" ]
	Go to Layout [ "Invoices" ]
	End If
	End If

	#Find and count all unbilled expense records
	Go to Layout [ "Expenses" (Expenses) ]
	Enter Find Mode [ Specified Find Requests: Find Records; Criteria: Expenses::
	Line Item ID: "==" ] [ Restore ]
	Set Field [ Expenses::Job ID; "==" & Get ( ScriptParameter ) ]
	Set Error Capture [ On ]
	Perform Find [ ]
	Set Error Capture [ Off ]
	Set Variable [ $$Unbilled Items; Value:Get ( FoundCount ) ]
	#Find and count all unbilled timeslip records
	Go to Layout [ "Timeslips" (Timeslips) ]
	Enter Find Mode [ Specified Find Requests: Find Records; Criteria: Timeslips:
	:Line Item ID: "==" ] [ Restore ]
	Set Field [ Timeslips::Job ID; "==" & Get ( ScriptParameter ) ]
	Set Error Capture [ On ]
	Perform Find [ ]
	Set Error Capture [ Off ]
	Set Variable [ $$Unbilled Items; $$Unbilled Items + Get ( FoundCount ) ]
	#Return to the original layout
	Go to Layout [ original layout ]

	Go to Layout [ "Invoices" (Invoices) ]
	New Record/Request
	Set Field [ Invoices::Job ID; Get ( ScriptParameter ) ]
	Set Variable [ $$invoice ID; Invoices::Invoice ID ]
	Go to Layout [ original layout ]

	Loop
	#Add a new empty line item
	Go to Layout [ "Line Items" (Line Items) ]
	New Record/Request
	Set Field [ Line Items::Invoice ID; $$Invoice ID ]
	Set Variable [ $LineItemID; Value:Line Items::Line Item ID ]
	If [ Get ( ScriptParameter ) = "timeslips" ]
	#Process a timeslip record
	Go to Layout [ "Timeslips" (Timeslips) ]
	Set Field [ Timeslips::Line Item ID; $LineItemID ]
	Set Field [ Timeslip Line Items::Description; Timeslips::Date Time & ": " &
	Timeslips::Description ]
	Set Field [ Timeslip Line Items::Price Each; 20 ]
	Set Field [ Timeslip Line Items::Quantity; Round ( Timeslips::Duration / 60 /
	60 ; 2 ) ]
	Else
	#Process an expense record
	Go to Layout [ "Expenses" (Expenses) ]
	Set Field [ Expenses::Line Item ID; $LineItemID ]
	Set Field [ Expense Line Items::Description; "Expense: " & Expenses::
	Description ]
	Set Field [ Expense Line Items::Price Each; Expenses::Amount ]
	Set Field [ Expense Line Items::Quantity; 1 ]
	End If
	Go to Record/Request/Page [ Next; Exit after last ]
	End Loop
	Go to Layout [original layout]