Setting the Formula Field

When entering a default value for a field, you can enter a formula.

Note:

A default value with the Store Value box checked is set only when the form for the new record is loaded. The value is then stored on the field. To evaluate the field default value every time the form is loaded, you must clear the Store Value box. For more information, see Setting the Store Value Field.

When you check the Formula box, the contents of the Default Value field are treated as an SQL expression, executing SELECT <formula text> FROM dual in the database to obtain the results. Formulas that contain tags will have the values substituted before the formula is executed.

However, if a value contains tags, it doesn't necessarily mean it's a formula. Make sure you're entering a formula before checking the Formula box. For example, if you enter Welcome, {firstname} as the default for a text area field, the content is substituted correctly only if the Formula box is cleared.

If you're entering a dynamic default for a hyperlink and you clear the Formula box, the following default value results in a valid URL: http://maps.google.com/maps?q={billaddr1}%20{billcity}%20{billstate}%20{billzip}

For more information about the setup for the preceding example, see Dynamic Hyperlinks.

However, if the Formula box is checked, the preceding URL results in an Error: Invalid Expression message. To use a formula for the preceding example, enter the formula like this: 'http://maps.google.com/maps?q='||{billaddr1}||'%20'||{billcity}||'%20'||{billstate}||'%20'||{billzip}

Note:

In the preceding example, the bill address can't be filled for a new record. Therefore, for this example to work, clear the Store Value field.

When you enter information in the Default Value field, the Formula box may be checked automatically. If you get an Error: Invalid Expression error or the default doesn't appear as expected, verify the Formula setting.

For more information, see Creating Formula Fields.

For a detailed example of creating a formula field, see Example of Creating a Formula Field (SuiteAnswers ID 1014946).

Related Topics

General Notices