Formula expressions

Related information

Related topics

• Use applications for custom solutions
• Workflow actions—Platform Services
• Email templates—Platform Services
• Document templates—Platform Services
• REST API, XML API, and developer resources

You define formula expressions using JavaScript, and Intacct executes the formula on the server side. Almost all JavaScript functionality is available to you when writing formulas.

About formula expressions

When creating a formula expression, you use merge fields to extract data from object records and related records. Place merge fields anywhere in a formula to use as a placeholder for data. When the formula executes, field values replace the merge fields.

Merge field syntax includes an object name, a period, and the field name inside exclamation marks and curly braces.

The following example contains the merge field {!contact.city!} in a formula. The formula returns a different value, depending on whether the record value for {!contact.city!} is equal to New York:

var rate = 6.5; 
if ("{!contact.city!}" == "New York") 
rate = 8.5; 
return rate;

When this formula executes for a city field with a value of San Francisco, the formula translates to the following code before Intacct executes it:

var rate = 6.5; 
if ("San Francisco" == "New York") 
rate = 8.5; 
return rate; 

San Francisco does not equal New York, so the result of the formula expression is 6.5.

Templates versus formulas

Formulas and templates both use merge fields to extract data from records. The difference is that templates only embed data into preformatted text, while formulas invoke a JavaScript engine to compute results.

Formulas treat merge fields the same way templates do. For example, to concatenate two merge fields in a formula expression, you can write:

"{!contact.lastName!}, {!contact.firstName!}"

You do not need to use a JavaScript concatenation:

"{!contact.lastName!}"+", "+"{!contact.firstName!}"

However, to avoid an unnecessary comma when the firstName field is empty, you can use conditional operators:

("{!contact.firstName!}"=="" ? 
"{!contact.lastName!}" : 
"{!contact.lastName!}, 
{!contact.firstName!}")

Best practices

Keep your formulas as simple as possible to make your applications maintainable and to improve application performance. The following tips can help you simplify formulas:

• Avoid using the return clause for one-line formulas without internal variables.
• Avoid unnecessary concatenation using the + symbol.
• Use conditional operators (X?A:B) instead of the if() function. The conditional operators (X?A:B) translate to if X is true, then return A; otherwise return B.

Process errors

When defining a formula expression, a Check for errors link displays below the formula text area. Use this link to check whether the formula is valid before saving your changes. The Check for errors option finds syntactical errors in your JavaScript.

Debug formulas

After you define and validate your formula expression, you can easily debug it. To debug a formula, select the magnifying glass icon to select a record, then execute the formula against the record. When Intacct executes the formula, a debug page shows the original formula, the parsed version of the formula, and the final result.

Use JavaScript

Because formula expressions are defined using JavaScript, you have much flexibility. To take full advantage of this capability, familiarity with JavaScript is recommended though not necessary.

For example, you can include:

• Logical operations such as if, else, and switch.
• Standard JavaScript objects and their methods such as String, Date, Math, and so on.
• Standard functions available in any JavaScript capable environment.
• Merge fields from both base records and all related records.

JavaScript limitations

Given the broad capabilities of JavaScript, it's possible to perform complex calculations and create powerful formula expressions. However, to ensure proper performance for all customers on the Intacct platform, the length of formula expression cannot exceed 1,000 characters. Despite this limitation, formulas offer a powerful way to automate your custom applications.

You might find that the complexity of your work regularly challenges this limit. If so, you can consider a process that includes handing off portions of a script to formula fields in the same object. You can also call external code and write values back through the Intacct Web Services API.

Formula fields

Formula fields allow you to create dynamically generated fields associated with any of your objects. For example, formula fields can present the average, sum, minimum, or maximum of several other fields. Likewise, you can perform calculations or string concatenations based on multiple other fields. Intacct does not store formula field values in the database; rather, they are calculated each time the field is displayed in the user interface. This approach means that formula field values are always changing in response to changes in the underlying data. Let's say that you set a formula field to return the sum of all sales orders in a month. That field will continue to change in response to the growing number of sales orders throughout the month.

Formula return types

Formula fields support the following return types:

• Decimal
• Currency
• Integer
• String
• Boolean
• Date

If a formula field returns a value that does not match the specified return type, Intacct ignores the value.

Examples of simple formula expressions include:

"{!contact.city!}".length

"{!contact.city!}".toUpperCase()

"{!contact.city!}" == "Houston"

(new Date("{!contact.createdAt!}")).getTime()+7*24*60*60*1000

Formula field types

{!department.role!}==90

{!account.account_name!}

var currentDate = new Date();
currentDate = Math.floor(currentDate / 86400000); // round to day value (24*60*60*1000 = 86400000)
var thisDate = new Date("{!task.due_date!}");
thisDate = Math.floor(thisDate / 86400000);
if(thisDate == currentDate){
     return "Due Today";
} 

var today = new Date();
return today.getTime();

Group functions in formulas

You can define relationships between any number of objects and perform simple and complex calculations among related objects in formula expressions. You can also use these group functions in templates, such as email and document templates, and in template fields. The following group functions are available:

#FUNC_SUM
#FUNC_COUNT
#FUNC_MAX
#FUNC_MIN

#FUNC_SUM.RB188912( {!RB188912.line_amount!} )

#FUNC_SUM.^product_component( {!^product_component.line_amount!} )

#FUNC_SUM.RB188912(1)

#FUNC_SUM.RB188912( {!RB188912.line_amount!} ) / #FUNC_SUM.RB188912(1)

#FUNC_COUNT.RB188912()

#FUNC_MAX.RB188912( {!RB188912.price!} )

Loop through related records

Formulas can be helpful in aggregating data from related records. For example, in a CRM, you might want a Lead object to calculate the revenue value of all the related Opportunities and display this in a Total Value formula field. You can use special loop merge fields to accomplish this. These fields instructIntacct to look at all the related records to the record in scope. Assuming that the integration name of the relationship is R11002, the formula for Total value can look something like this:

var total = 0;
{!#LOOP_BEGIN.R11002!}
total += {!R11002.revenue_value!};
     {!#LOOP_END.R11002!}
return total;

Alternatively, you can use the Lead object's lookup field preceded by a caret (^) instead of the integration name of the relationship:

var total = 0;
{!#LOOP_BEGIN.^opportunities!}
     total += {!^opportunities.revenue_value!};
     {!#LOOP_END.^opportunities!}
return total; 

Loop through unrelated records

You might find occasions where you need to loop through records in an object that's not related to the object in scope. You do this by looping through a list view for the unrelated object. By looping through a list view, you can set filters on the view to control what records your formula will loop through. To do this, you need to first obtain the list view's Original ID or the unrelated object's Integration name for the list view of the unrelated object.

To find a view's Original ID or Integration Name:

1. Go to the Object Definition page for the object that contains the desired view.
   • Find the Integration name in the Object properties section.
   • Find the Original ID in the System Information section.
2. Copy the Integration name or Original ID for the view you want to access.
3. In your formula, point the LOOP function at the Integration name or Original ID.

The following formula loops through all the records in the view with Original ID 116802@10083 and appends the record names in a single string:

var buff = '';
{!#LOOP_BEGIN.all#116802@10083!}
     buff += '{!name#text!}';
{!#LOOP_END.all!}
return buff;  

var buff = '';
{!#LOOP_BEGIN.all#116802@10083(20)!}
     buff += '{!name#text!}';
{!#LOOP_END.all!}
return buff;

Here's an example of using the object integration name instead of an original ID for specifying a view. The view used by this syntax is the first view of the object, meaning the view with the lowest Order No in the object view page.

var total = 0;
{!#LOOP_BEGIN.all#class!}
    total += {!class.recordno!};
{!#LOOP_END.all!}
return total;

Access record from a loop

You can access a record from a loop using LOOP_REC. In the following example, the loop prevents the creation of a duplicate vendor.

var recordNo = "{!vendor.recordno!}";
{!#LOOP_BEGIN.all#vendor@name@=@vendor.name!}
  if ("{!#LOOP_REC.recordno!}" != recordNo) {
        return "This name is already used by the vendor with the id - {!vendorid#text!}";
  }
{!#LOOP_END.all!}

Add conditions to a loop

You can add condition parameters in your JavaScript code, which add restrictions to records you loop through and allows your script to loop more efficiently.

var total = 0;
{!#LOOP_BEGIN.all#CLASS@name@=@12345!}
    total += {!class.recordno!};
{!#LOOP_END.all!}
return total;

var total = 0;
{!#LOOP_BEGIN.all#CONTACT@email@=@"john.smith@example.com"!}
    total += {!udd2.recordno!};
{!#LOOP_END.all!}
return total;

contact.city