Putting a Complex Script Together

Building a complicated script takes time and planning. Given the flexible nature of scripting, you could script a given process many different ways, and outside the artful application of your business's rules to your database, there is no one way. Your job is to find a good way.

In this section, you'll make a script that generates an invoice for a job. It gathers all the unbilled expenses and timeslips for the job and adds appropriate line items for them to the invoice. To make a script like this you need to cover all your bases:

Planning. Without writing the actual script, what will your general approach be? Can you outline all the things your script will do, and in the right order? This process usually evolves from very general to somewhat specific. The most specific version is the script itself, when you'll tell FileMaker exactly what to do.
Exceptions. What kinds of things can go wrong? How do you check for, and prevent, those problems?
Efficiency. Are you doing the same things several places in the script? Are there other reasons to break your script into multiple smaller scripts?

Tip: The rest of this chapter is one long excercise. It's best digested if you work straight through from here to the endwithout skipping parts or jumping around.

15.5.1. Planning the Script

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.

15.5.2. Considering Exceptions

Many people forget one of the most critical steps in scriptwritingplanning 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 exceptionssituations where your script might not be able to do its job. Thinking of exceptions is important for two reasons:

If your script always assumes ideal circumstances, it can wreak havoc if your assumptions are wrong when it runs. The last thing you need is a wild script running amok in your data, changing and deleting the wrong things.
If a script gets halfway through its job, and then discovers that it can't continue, you might be left with half-finished work. It's usually best to look for the problems up front so the script can simply refuse to run if it won't be able to finish. (For more detail, see the box on Section 15.5.3.)

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:

How does the script know which job to create an invoice for? This problem is easy to solve. You just make sure the user is on the Jobs layout when the script runs. Then the script runs on the current Job record. If another layout is selected, the script should just do nothing at all. Add an If script step and the Get ( LayoutName ) function. Since you don't want the script to do anything if the user's on a different layout, put all the other steps inside the If block.
What if the job has no timeslips or expenses that haven't been billed? You'd wind up with an invoice that has no line items, and you don't want to send that to your customer. You could go ahead and create the invoice, and then delete it if it's empty. But this approach uses up an invoice number, and it means your script has to go through all the work of creating an invoice only to throw it away when it's done. Your script should check first to be sure there's something to bill. Then, it can show the user an informative message and skip all the hard work when there's nothing to bill. Here's how to add the If test, as shown in Figure 15-15.

Figure 15-15. Now your script checks to see if the user is on the right layout before beginning its work.
Add an If step just below the TODO item that finds unbilled expenses. Put a commented calculation (Section 9.7) as your test for now, just to remind yourself what needs to be tested. You can put the real test in later.
Add feedback for the user by putting a custom dialog step in the true part of the If step. If you don't give the user feedback here, he may become confused, since nothing happens. The dialog box should tell your user why FileMaker doesn't create any invoice.
Add an Else step. Since you don't want to create an invoice if there aren't any billable items for the Job, put the last three TODO items in an Else step. The End If for this test should be just above the final End If in the script.

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 tablesexpenses and timeslipsyou 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.

15.5.3. Creating Subscripts

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.

DIAMOND IN THE ROUGH
The Problem with Problems

Although detecting problems up front is usually best, it isn't always possible. Sometimes you can't find out about problems until your script has run partway through.

Most database systems handle this problem with something called transactions, a chunk of work that's held in limbo until you tell the database to make it permanent. In a nutshell, you open a transaction, then you're free to do anything you want, but your changes don't get saved to the real data until you commit the record. If you decide you don't want the changes after all, you undo, the transaction.

FileMaker's uses this transaction concept under the hood to handle record changes, but unfortunately there's no easy way to tap in to the transaction system from a script. Here's why. When you first enter a recordusing the Open Record/Request script step, for instanceFileMaker begins a transaction for you. When you exit the recordCommit Record/RequestFileMaker commits the transaction, writing all changes to the database. If you revert the record insteadRevert Record/RequestFileMaker essentially rolls back the transaction, leaving the database untouched. Just remember that each transaction is linked to a record. For example, you can't begin a transaction, then make changes to five different customer records and eleven invoices, and then roll back all those changes.

But if you create, edit, or delete records in portal rows while you're still in the record, all your changes happen in one transaction. Do this exercise in the Customers file to explore how this works. Have two windows openone showing the Invoice layout and the other showing Line Items. Create a new Invoice record and add a few Line Items. Notice that FileMaker creates the new Line Item records when you add items to the Line Item portal on Invoice layout. Being very careful not to commit the record (that is, don't hit the Enter key or click anywhere outside the fields onto your layout), choose Records Revert Record. The parent Invoice record disappears, Knowing this, you can use the Open Record/Request script step on an Invoice record, then make changes to dozens of line items. Then if your script detects a problem, you can revert the Invoice record, and all your line item changes are tossed out as well. If you absolutely positively must have control over your transactions, arrange your scripts so they do everything through relationships from one single record.

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 firstbut 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.

Tip: Using naming conventions like this can really help keep your script list organized and easy to work with. Which convention you use, though, is up to you. In this book, all subscripts start with a dash and use all lowercase.

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.

UP TO SPEED
The Right Way to Create Subscripts

When you think about ways to break your script into smaller pieces, you should be thinking about tasks. It makes good sense to create a smaller script to do one of the tasks needed in the larger script. It doesn't make sense to simply take a long script and break it in two, so that the last step in the first script simply performs the second script. Breaking scripts up that way has all the disadvantages of multiple scripts (more windows to work in, more scripts to scroll through) and none of the advantages (neither script is particularly simple or self-contained, and neither can be run individually for testing purposes). Also, as you look for places to use subscripts, you would also be looking for opportunities for reuse. In other words, you should look for things the script has to do more than once in two different places. It almost always makes sense to use a subscript in this situation.

In almost every case, the right way to subdivide a script is to create one master script that starts and finishes the process.

The Create Invoice for Job script does just that. It starts by checking what layout you're on, and finishes by showing the invoice. Along the way, it relies on other simple scripts to get the job done.

This isn't to say that a subscript should never have subscripts of its own. In fact, subscripts often do. But you should structure the entire set of scripts so that the top-level script implements the highest-level logic and behavior of the entire script itself. Each subscript should in turn do some particular task from start to finish. If the task is particularly complex, then the subscript itself might implement only the highest level of logic, calling upon more subscripts to handle parts of the task. In this way, the scripts themselves provide helpful overviews of what's going on. When the Create Invoice for Job script is finished, you see that even though it's somewhat complex, its structure makes it easy to follow. The script almost reads like a book, describing exactly what it's doing.

15.5.4. Adding a Script Parameter to a Perform Script Step

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:

In ScriptMaker, double-click the Create Invoice for Job script to edit it. Select the second script step, Perform Script ["- find unbilled activity"].

The Script Step Options area shows a Specify button.
Click Specify. In the "Optional script parameter" box, enter Jobs::Job ID.

If you don't like typing field names, click Edit instead and add the field in the Specify Calculation window. Then close the Specify Calculation window by clicking OK.
Click OK until you're back in the database.

The first Perform Script step shows the proper parameter value.

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.

15.5.5. Finishing the 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.

15.5.5.1. 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. 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.
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.
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.
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.
From the "Find records when" pop-up menu, choose Expenses, and then click the Line Item ID field.

The selected field is now highlighted.
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.
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.
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.
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.
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.
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.

15.5.6. Script variables

Global fields are great for storing a value that's not tied to a specific record in a table. Script variables are similaryou 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:

Give your variable a name. Choose a short, descriptive name. All script variable names require a "$" prefix. If you forget to type the prefix, ScriptMaker adds it for you. But you should get in the habit of typing the prefix because it determines how long the value in the variable is available. A single $ means that the variable is "local" and lasts only while your script is running. If you set a local variable, then perform a subscript, the local variable isn't sent to that subscript. But if you need a global variable, (the value persists through subscriptsand even after all scripts have finished running), prefix the variable's name with "$$." Global variables are cleared when you close the file.
Assign a value. This is the value you want to store in the variable. Values can be text or the result of a calculation.
Set a repetition. Repetitions are optional. You can make a variable store multiple values, similar to the way a repeating field stores multiple values. Most of the time, though, you don't set a repetition.

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

Select the second Set Error Capture script step, then create a Set Variable script step. Click its Specify button (Figure 15-22).

You want to capture the number of records the script just found, so it makes sense to set the script variable as soon as possible after the find was performed.

Figure 15-22. The "Set Variable" Options dialog box lets you define variables that you can use in functions throughout your scripts. The two dollar signs at the beginning of "$$unbilled Items" in the name field indicate a global variableone whose value persists until it's reset or until the file is closed.

In the Name field, type $$unbilled Items.

You need to check this value after the current script is finished running, so use the "$$" prefix to make the variable available globally.

Tip: Don't make global variables without thinking about whether you really need the value to persist after the script is run. Although FileMaker purges variables when you close the file, it's bad housekeeping to clutter up your database by leaving unneeded values laying around in global variables.
Click the Specify button beside the value field. In the View list, choose "Get Functions." Choose Get (FoundCount) from the list, and then click OK until you're back in your database.

If you prefer not to rummage through the Specify Calculation dialog box, you can type Get (FoundCount) in the Value field. You're telling ScriptMaker to grab the found count from the find and hold it in a variable so you can test that value later on.

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.

Tip: Script variables can be mysterious because unlike the Set Field script step, you can't really see the value that's set in them. But if you have FileMaker Pro Advanced, there's a great tool for monitoring variables. See Section 19.1.1.4 to see how the Data Viewer can make your life a whole lot easier.

15.5.6.1. 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:

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.
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.
Select the next stepGo to Layoutand, 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.

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.

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.
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.
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.
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.
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.

15.5.6.2. The "- create invoice record" subscript

The "- create invoice record" script needs to switch to the Invoices layout, create a new record, attach it to the job (by filling in its Job ID field) and then switch back to the original layout. Just like the last script, you send it the right Job ID in its script parameter, so your first job is to edit the Create Invoice for Job script, and get it to send the Job ID when it performs the "- create invoice record" script.

Now you're ready to polish off the "- create invoice record" script itself:

Double-click the "-create invoice record" script in ScriptMaker to edit it. Select the first line: "TODO: Switch to invoices layout."

The next step you add appears after this comment.
Add the Go to Layout script step to the script. From the Specify pop-up menu, choose Invoices.

You can't add an invoice record from the Jobs layout, so you're switching to a layout attached to the Invoices table first.
Select the second comment step: "TODO: Create invoice record and set Job ID field." Add the New Record/Request step to the script.

The step appears after the second comment.
Add the Set Field step to the script. Turn on the "Specify target field" checkbox. From the pop-up menu at the top of the dialog box, choose Invoices. Then select the Job ID field in the list and click OK.

You want to be sure you're setting the right-hand Job ID field. When you make this choice, the fields from the Invoices table appear in the list.
Click the Specify button to the right of "Calculated Result." Set the calculation to Get ( ScriptParameter ) and click OK.

You're matching the new Invoice to the Job number that was set as the script's parameter way back when the script began running.
Add the Go to Layout step to the script.

Subscripts should always return the database to its previous state, so the main script doesn't get confused about its context.
Clear the TODO comment steps from the script and click OK.

Your finished script creates a new Invoice record and sets the proper Job ID in it.

You can see the finished "- create invoice record" subscript in Figure 15-24. Now that your script creates a new invoice, you're ready to write the subscript that creates its line items.

15.5.6.3. 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.

15.5.6.3.1. 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 minutehow 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:

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.
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.

15.5.6.3.2. 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:

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.
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.
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.
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.
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.

15.5.6.3.3. 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.

15.5.6.3.4. 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:

Select the Set Field script step in the script.

You need to grab the Line Item ID before you start the If test.
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.
Select the "TODO: Record the Line Item ID in the timeslip record" comment step.

You'll tackle this one first.
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.
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.
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.)
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.
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.
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 partand it isn't that hardis figuring out what to put in each field. Here goes:

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.
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.

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.

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.
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.
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.
Set the calculation for this step to, simply, 20. Click Duplicate again.
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:
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.
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.
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.
Duplicate the script step, set it to target the Price Each field, and use this calculation: Expenses::Amount.

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

Expenses always have a quantity of one.

15.5.6.3.5. Creating a looping script.

Your script works great nowif 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:

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.
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.
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.

15.5.7. The Finished Scripts

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).

15.5.7.1. Create Invoice for Job

	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

15.5.7.2. -find unbilled activity

	#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 ]

15.5.7.3. - create invoice record

	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 ]

15.5.7.4. - process unbilled items

	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]

Congratulations! You've just made it through a long, hard slog. Perhaps it seemed like you didn't know where you where going, but that's often the case when you're writing scripts. In this chapter, you learned a system for breaking down a complicated task into manageable pieces. When you're writing your first few complex scripts, you'll probably want to follow the same procedure fairly closely. But as you gain experience, you'll find that planning, finding exceptions, and subscripting will start to feel natural. You'll start envisioning scripts of increasing complexity and making them your own way, without following a rigid plan.

Part I: Introduction to FileMaker Pro

Your First Database

Organizing and Editing Records

Building a New Database

Part II: Layout Basics

Layout Basics

Creating Layouts

Advanced Layouts and Reports

Part III: Multiple Tables and Relationships

Multiple Tables and Relationships

Advanced Relationship Techniques

Part IV: Calculations

Introduction to Calculations

Calculations and Data Types

Advanced Calculations

Extending Calculations

Part V: Scripting

Scripting Basics

Script Steps

Advanced Scripting

Part VI: Security and Integration

Security

Exporting and Importing

Sharing Your Database

Developer Utilities

Part VII: Appendixes

Appendix B. FileMaker Error Codes

Appendix A. Getting Help