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:
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:
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 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.
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.
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.
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.
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.
|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:
The Script Step Options area shows a Specify button.
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.
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.
18.104.22.168. 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:
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.
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.
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.
The Edit Find Request window appears.
The selected field is now highlighted.
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.
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.
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.
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.
The script step belongs below the Set Error Capture step. Make sure you don't select Perform Find/Replace by accident.
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.
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:
Adding a Set Variable script steps is easy. Open your "- find unbilled activity" script in ScriptMaker:
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.
You need to check this value after the current script is finished running, so use the "$$" prefix to make the variable available globally.
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 22.214.171.124 to see how the Data Viewer can make your life a whole lot easier.
126.96.36.199. 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:
FileMaker creates an exact copy of the selected steps. They all wind up below the first set.
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.
This time you want to work with Timeslips records, so you need to go to the Timeslips layout.
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.
These two clicks close the Edit Find Request and Specify Find Requests windows, respectively. You're back in the script.
The Set Error Capture and Perform Find steps don't need to be changed at all, so you skip ahead to the last step.
This time you don't want to replace the value in the script variable, so this calculation simply adds your new count to it.
You can do this in one shot by clicking the first one, Shift-clicking the last, and the clicking the Clear button.
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.
188.8.131.52. 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:
The next step you add appears after this comment.
You can't add an invoice record from the Jobs layout, so you're switching to a layout attached to the Invoices table first.
The step appears after the second comment.
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.
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.
Subscripts should always return the database to its previous state, so the main script doesn't get confused about its context.
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.
184.108.40.206. 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.
220.127.116.11.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:
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:
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:
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.
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.
18.104.22.168.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:
The new step appears after the highlighted comment.
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.
The Set Field step shows it will set the Line Items::Invoice ID field.
This puts the invoice ID from the script variable into the new line item's Invoice ID field.
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.
22.214.171.124.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:
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.
126.96.36.199.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:
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.
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:
You need to grab the Line Item ID before you start the If test.
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.
You'll tackle this one first.
You can't work on a timeslip if you're not on the Timeslips layout.
Set the options for the Set Field step so it targets the Timeslips::Line Item ID field and uses the calculation, $LineItemID.
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.)
If your field names match exactly, FileMaker keeps the Line Item ID field highlighted for you. If it doesn't, select it yourself.
Again, you need to make sure you're on the right layout.
Isn't it nice to knock things off your to-do Figure 15-28 shows how your script should look now.
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:
This step makes the link to the line item. So your new Set Field steps go after it.
The Specify Field window arrives on cue.
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
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 188.8.131.52 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.
You're now targeting the right field. As explained before, you want to put the timeslip date, time, and description in it.
Don't worry if the box cuts off a bit of the end. Click the Duplicate button when you're done.
You want to set the price to $20.
Since you duplicated the previous step, the Timeslip Line Items table occurrence is already selected; you just need to pick the field.
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.
This is the same sort of maneuver you took in step 5.
Easy enough. On to number two.
One more to go.
Expenses always have a quantity of one.
184.108.40.206.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:
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.
When you're done, you can remove the "TODO: Loop through each record" comment line.
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.
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).
220.127.116.11. 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
18.104.22.168. -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 ]
22.214.171.124. - 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 ]
126.96.36.199. - 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
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
Part V: Scripting
Part VI: Security and Integration
Exporting and Importing
Sharing Your Database
Part VII: Appendixes
Appendix A. Getting Help