Learning About the Environment

Troubleshooting

Formulas in Scripts Require Explicit Table Context

I'm used to being able to type field names into calculation formulas rather than selecting them from the field list. Sometimes, even if I've typed the field name correctly, I get a Field not found message when trying to leave the calculation dialog. It seems that sometimes calculations need the table occurrence name before the field name, and sometimes they don't. What are the rules for this?

When you define calculation fields, any fields within the current table can be entered into the formula without the table context being defined. For instance, you might have a FullName field defined to be FirstName & " " & LastName.

All formulas you write anywhere within ScriptMaker require that the table context be explicitly defined for every field, even when there's only a single table in the file. For instance, if you wanted to use a Set Field script step to place a contact's full name into a field, you wouldn't be able to use the preceding formula as written. Instead, it would need to be something like Contact::FirstName & " " & Contact::LastName.

If you're used to being able to manually type field names into formulas, be aware that the table context must be included for every field referenced in the formula.

The reason for this is that the table context for a script is determined by the active layout when the script is executed. Contact::FirstName may have a very different meaning when evaluated on a layout tied to the Contact table than it would, say, on one tied to an Invoice table.

Errors Due to Improper Data Type Selection

I've heard that the data type selection for calculation fields is important. What kind of problems will I have if I select the wrong data type, and how do I know what type to choose?

Every time you define a calculation field, no matter how simple, be sure to check the data type that the formula is defined to return. The default data type is number, unless you're defining multiple calculations in a row, in which case the default for subsequent fields will be the data type defined for the previous calculation.

A number of errors can result from selecting the improper data type. For instance, if your formula returns a text string but you leave the return data type as number, any finds or sorts you perform using that field will not return expected results.

Be especially aware that formulas that return dates, times, and timestamps are defined to have date, time, and timestamp results. If you leave the data type as number, your field displays the internal serial number that represents that date and/or time. For instance, the formula Date ( 4 ; 26 ; 2004 ) returns 731697 if the date type is set to number.