VISUAL QUICKPRO GUIDE FileMaker Pro 7 Advanced FOR WINDOWS AND MACINTOSH [Electronic resources] نسخه متنی

Setting the Next Auto-Entry Serial Number

When you import data, you can choose whether or not to update auto-enter and lookup fields. This choice is useful, but unfortunately it makes the two updates a package deal. So if you want to enter serial numbers but don't want to update the lookup fields, you'll handle the serial numbers in a separate step.

The easiest way to enter new serial numbers would be to use the Replace command. However, this simple solution could turn ugly in a multiuser environment. Imagine the mess if users were editing the new records as you tried to update their serial numbers. None of the active records would be updated.

FileMaker provides a valuable pair of toolsthe GetNextSerialValue function and the Set Next Serial Value script stepto rescue us. Using these helpful tools, we can create a script that checks through the newly imported records, setting the new serial numbers and taking note if any were not updated properly because they were in use.

Because this script could wreak serious havoc if it ran on the entire database, we'll include a check to verify that there is a Find in place before it runs.

This example uses Id Number as the serialized field in a database called Customer.fp7.

To specify next serial number after importing

1 .In the file where you'll be importing records, choose File > Define > Database (Control+Shift+D/Command+Shift+D).

2 .Click the Fields tab. Type gNoSerialCount for the field name, select Number (Control+N/Command+N), and click Create.

3 .Click Options. When the Options for Field dialog box appears, click the Storage tab. In the Global Storage section, check the "Use global storage" check box. Click OK twice.

This script will use the gNoSerialCount field to note if there are any records that are not updated.

4 .Choose Scripts > ScriptMaker (Control+Shift+S/Command+Shift+S). When the Define Scripts dialog box appears, click New. In the Edit Script dialog box, name the script "Serialize."

5 .In the Control category of the step list on the left, double-click Set Error Capture and leave it set to On in the Script Step Options section (Figure 12.27).

Figure 12.27. To trap for errors in the script and suppress the default error message, Set Error Capture must be set to On.

6 .In the step list, double-click Allow User Abort and leave it set to Off.

7 .Now we'll check to make sure that a Find is in use. Back in the Edit Script dialog box, in the Control category of the step list, double-click If. In the Script Step Options section, click the Specify button.

8 .

When the Specify Calculation dialog box appears, choose "Get functions" from the View drop-down menu on the right.

9 .In the function list, double-click Get(FoundCount). Click to the right of the parenthesis, then double-click the equals (=) operator.

10 .In the function list on the right, double-click the Get(TotalRecordCount) function (Figure 12.28). Click OK.

Figure 12.28. If the number of records in the database is the same as the number of records in the found set, there is no Find in use.

This If step checks to see if the current number of found records equals the current number of records in the table. If they're the same, there is no found set.

11 .Back in the Edit Script dialog box, scroll down to the Miscellaneous category of the step list. Double-click Show Custom Dialog, then click Specify in the Script Step Options section.

12 .In the "Show Custom Dialog" Options dialog box, type a warning message. Ours is "There is no Find in use. You don't want to run this script on the entire database."

13 .Leave the Default button set to OK and delete "Cancel" from the Button 2 box. Click OK (Figure 12.29).

Figure 12.29. Since the script will end after the message is displayed, the dialog box should only offer OK as an option.

The "Show Custom Dialog" Options dialog box closes and the Edit Script dialog box remains.

14 .

In the Control category of the step list, double-click Exit Script (Figure 12.30).

Figure 12.30. If there is no found set, the script will exit without making any changes.

If there is no found set, the script will exit without making any changes.

15 .Click the End If step to select it so the next script step will appear below it.

16 .

In the Fields category of the step list, double-click Set Field. In the Script Step Options section, check "Specify target field" (Figure 12.31).

Figure 12.31. Set Field needs two pieces of information: the target field and what you want to put in it.

17 .When the Specify Field dialog box appears, double-click gNoSerialCount.

18 .In the Script Step Options section, click the Calculated result: Specify button. When the Specify Calculation box appears, type 0. Click OK (Figure 12.32).

Figure 12.32. The gNoSerialCount field must be reset to 0 each time the script is run.

This Set Field step resets the gNoSerialCount field to 0.

19 .In the Navigation category of the step list, double-click Go to Record/Request/Page. From the Specify drop-down menu in Script Step Options, choose First (Figure 12.33).

Figure 12.33. The updating must start at the first record in the found set.

This step sends the script to the first record in the file.

20 .In the Control category of the step list, double-click Loop.

21 .

Scroll to the Fields category of the step list and double-click Set Field. In the Script Step Options section, check the "Specify target field" box. When the Specify Field dialog box appears, double-click Id Number (Figure 12.34).

Figure 12.34. Choose Id Number from the field list.

22 .In the Script Step Options section of the Edit Script dialog box, click Calculated result: Specify. When the Specify Calculation dialog box appears, double-click GetNextSerialValue in the function list (Figure 12.35).

Figure 12.35. GetNextSerialValue looks at a specified database and returns the next serial number for the specified field.

[View full size image]

23 .Double-click "fileName" inside the parentheses to select it. Click the quotes button and type the name of your database inside the quotes. (We use Customer.fp7.)

24 .Double-click "fieldName," click the quotes button, and select Id Number from the field list on the left. Click OK (Figure 12.36).

Figure 12.36. In the GetNextSerialValue function, both the database and field names must be in quotes.

25 .In the step list of the Edit Script dialog box, double-click If. In the Script Step Options section, click Specify.

26 .When the Specify Calculation dialog box appears, choose "Get functions" from the View drop-down menu. Double-click Get(LastError).

27 .Click to the right of the parenthesis and double-click the not-equal operator (¤). Type 0 (Figure 12.37). Click OK.

Figure 12.37. If the Set Field command was unsuccessful, the last error will be something other than 0.

If the script's attempt to set the Id Number field succeeds (the record is not in use), the Get(LastError) function will return 0. If it fails (the record is in use), it will move on to the next record and change nothing.

28 .Back in the Edit Script dialog box, in the Fields category of the step list double-click Set Field. In Script Step Options, check the "Specify target field" box. When the Specify Field dialog box appears, double-click gNoSerialCount.

29 .In the Script Step Options section of the Edit Script dialog box, click Calculated result: Specify. When the Specify Calculation dialog box appears, double-click gNoSerialCount in the field list. Click the plus (+) button and type 1 (Figure 12.38). Click OK.

Figure 12.38. If the Set Field command was not successful, the gNoSerialCount field will be incremented by one.

[View full size image]

30 .

Back in the step list of the Edit Script dialog box, scroll up to the Control category and double-click Else.

31 .

Scroll down to the Fields category and double-click Set Next Serial Value (Figure 12.39). In Script Step Options, check the "Specify target field" box. When the Specify Field dialog box appears, double-click Id Number.

Figure 12.39. The Set Next Serial Value step allows you to update auto-enter serial numbers.

32 .In Script Step Options back in the Edit Script dialog box, click Calculated result: Specify. When the Specify Calculation dialog box appears, double-click GetNextSerialValue in the functions list.

33 .Double-click "fileName" inside the parentheses to select it. Click the quotes button and type the name of your database inside the quotes.

34 .Double-click "fieldName," click the quotes button, and select Id Number from the field list.

35 .Click to the right of the parenthesis, click the + button and type 1 (Figure 12.40). Click OK.

Figure 12.40. If the Set Field command was successful, the next serial value will be incremented by one.

36 .In the Navigation category of the step list, double-click Go to Record/Request/Page. Position it between the End If and End Loop steps in the script-assembly list.

37 .In Script Step Options, choose Next from the Specify drop-down menu. Click the "Exit after last" check box (Figure 12.41).

Figure 12.41. The "Exit after last" option will exit the Loop when the script reaches the last record in the found set.

The script to this point goes to the first record and sets the Id Number field to the next serial number. If the script is successful, it increments the next serial number by one. If it is not successful, it increments the global field gNoSerialCount by one instead.

38 .

Now we need to test to see if there were any records that were not updated properly. Select the End Loop step in the script-assembly list. In the step list, double-click If, then click Specify in the Script Step Options section.

39 .When the Specify Calculation dialog box appears, double-click gNoSerialCount in the field list. Double-click the = operator and type 0 (Figure 12.42). Click OK.

Figure 12.42. If all records were updated successfully, the gNoSerialCount field will still be 0.

40 .In the step list, double-click Exit Script.

If there were no records that failed to update, the script will end here. If not, it will continue and find the problem records.

41 .

In the step list, double-click Else, then scroll down to the Navigation category and double-click Enter Find Mode. Uncheck the Pause box (Figure 12.43).

Figure 12.43. You specify Enter Find Mode to allow you to set Find criteria in the next step.

42 .In the step list, double-click Set Field. In Script Step Options, check the "Specify target field" box. When the Specify Field dialog box appears, double-click Id Number.

43 .Back in the Edit Script dialog box, in Script Step Options, click Calculated result: Specify. In the Specify Calculation dialog box, click the quotes button, then double-click the = operator (Figure 12.44). Click OK.

Figure 12.44. In Find Mode, setting a field to = will find any records where that field is blank.

44 .In the Found Sets category, double-click Perform Find (Figure 12.45).

Figure 12.45. You need to perform a find to see if the Id Number field contains data.

The Perform Find will find all the records in which the Id Number field does not have a value.

45 .In the step list, scroll down to the Miscellaneous category and double-click Show Custom Dialog. In Script Step Options, click Specify.

46 .

When the "Show Custom Dialog" Options dialog box appears, type in the Message section:

The current found set contains the records that were not updated properly.

47 .Delete "Cancel" from the Button 2 box (Figure 12.46). Click OK.

Figure 12.46. This message offers no choices, so you don't need the second button.

48 .Back in the Edit Script dialog box, you can specify a layout to go to after the script finds unserialized records: In the Navigation category of the step list, double-click Go to Layout (Figure 12.47). Choose the layout from the Specify drop-down menu in Script Step Options. Click OK twice to finish.