Working with 13-Review and Reporting > 13-3 Report Writer > Calculated Fields

Calculated Fields

Sage 100 Contractor enables you to create calculated fields and add them to form designs or report designs. A calculated field is a type of field representing an expression. When you generate a report, Sage 100 Contractor determines the result of the calculated field based on information stored in the databases. After creating a calculated field, you can add it to the list of calculated fields available for use in the design.

Pre-defined calculated fields

Pre-defined calculated fields are fields that Sage 100 Contractor recognizes and automatically replaces with the proper data. These are locked fields that cannot be changed. Unlike other fields, standard or calculated, which you insert, create and/or add to the report, pre-defined calculated fields must be typed directly into the form design.

Most, but not all of these pre-defined calculated fields are also global calculated fields. For example, cmpany^cmpnme is the same as CP^cmpnme. Both fields pull the company name from the database.

Important! There is only one difference between the two types of calculated fields. Pre-defined calculated fields must be typed directly into the form design, whereas global calculated fields must be added from the Calculated Fields window.

The fields listed in the table below work only in 13-5 Form/Report Page Design, not in 13-3 Report Writer. Many of these pre-defined fields exist in the form designs that come with Sage 100 Contractor.

Pre-Defined Field

Data

CP^cmpnme

Company Name

CP^cmpad1

Company Address 1

CP^cmpad2

Company Address 2

CP^ctyste

Company City & State

CP^zipcde

Company Zip Code

CP^mallbl

Company Mail Label

CP^licnum

Company License Number

CP^ctynme

Company City Name

CP^state

Company State Name

CP^phnnum

Company Phone Number

CP^curdte

Current Date

CP^curtme

Current Time

CP^curusr

Current User

CP^pagnum

Page Number

CP^pagnxt

Consecutive Page Number (will ignore new page one for new groups, and so on)

CP^stetax

Company State Tax ID#

CP^fedtax

Company Federal Tax ID#

CP^memnte

Notes entered on report selection window

CP^select

Report Selection Criteria

CP^subttl

Report Subtitle

CP^faxnum

Company Fax Number

CP^rslnum

Company Resale Number

CP^usrdf1

Company User Defined 1

CP^usrdf2

Company User Defined 2

CP^e_mail

Company Email address

CP^bnkact

Company Bank Account Number (for Direct Deposit)

CP^rtnmbr

Company Routing Number (for Direct Deposit)

CP^ntetxt

Company Note

CP^rptttl

Report Title

CP^rptopt

Report Option

CP^stmdte

Statement Date (for printing from 3-4 Statements)

Creating calculated fields

When you add the calculated field, its name displays in the Calculated Fields list. You can then insert the field as you would insert any other field.How?

About expressions

An expression is a formula used to compute the value of a calculated field. An expression can contain fields, constants, operators, and functions.

The examples below help illustrate the syntax of expressions:

Operators

Operators are symbols that represent a type of mathematical or relational process to carry out in an expression. You can select from the following operators:

Operator

Description

+

Addition

Subtraction

*

Multiplication

/

Division

=

Equal to

< >

Not equal to

( )

Open/close parenthesis.

<

Less than

>

Greater than

<=

Less than or equal to

>=

Greater than or equal to

&

Ampersand

If( )Then( )Else( )

Tests for a condition.

{S}

Inserts a find and replace field for an alphanumeric variable.

{N}

Inserts a find and replace field for a numeric variable.

{D}

Inserts a find and replace field for a date variable.

SQL Queries

Tests for a condition.

Parentheses

Using the parentheses, you can group operations in an expression to change the order in which they are performed. Without parentheses, operations are performed in the following order: multiplication/division, addition/subtraction, and relational operations (greater than, less than, not equal to, and so on).

By using parentheses, you can change the order of operations in an expression. That is, operations in parentheses are performed first, then operations outside the parentheses are performed.

You can also embed parentheses, where operations in the deepest parentheses are performed first.

About using If( )Then( )Else( ) in a calculated field

The If( )Then( )Else( ) operator enables you to conditionally return a value. Within the parentheses of the If( )Then( )Else( ) expression, you enter the fields, functions, and constants.

Syntax: If( expression )Then( result1 )Else( result2 )

About variables in calculated fields

Variables act as placeholders for actual numeric values. The variables allow you to build calculated fields and save them without having to enter actual figures until you generate the report. When you preview or print a report containing a variable, you assign a numeric value to each variable that you are using. Sage 100 Contractor substitutes the declared values for the variables and computes the results. You can insert a variable for a date ({D}), number ({N}), or alphanumeric string ({S}).

{S}

The {S} operator lets you insert a variable for an alphanumeric string in the calculated field.

Syntax: [variable{S}]

{N}

The {N} operator lets you insert a variable for a numeric string in the calculated field.

Syntax: [variable{N}]

{D}

The {D} operator lets you insert a variable for a date string in the calculated field.

Syntax: [variable{D}]

Constants

Constants are parameters or values in an expression that do not change.

Functions

An expression can contain functions, which perform special operations.

You can use a single function to represent the expression in a calculated field, or you can use functions in a larger expression.

You can also embed functions, which lets you nest functions within each other.

Saving calculations to the Global Calculated Fields list is a good way to copy a calculation from one report to another. Many calculations are in the Global Calculated Fields list, but not all. Here’s how to add the ones you want.

About Structured Query Language

With Structured Query Language (SQL), you design expressions to query databases for data that is not available in standard reports or documents. With SQL, you can apply arithmetic operations to select and obtain new data. This can be as simple as adding two different fields together, or as complex as computing the billings in excess for a project.

Before you create an SQL expression, it is important to understand how Sage 100 Contractor uses databases to store information. When you enter information in a window, Sage 100 Contractor stores the information in tables. Depending on the window, Sage 100 Contractor either stores information in one or two tables.

In windows such as 4-3 Vendor Payments, 3-5 Jobs, or 3-3-1 Cash Receipts a single database table exists, containing all the information. In other windows such as 4-2 Payable Invoices or 3-2 Receivable Invoices, there are two database tables. When Sage 100 Contractor uses two tables to store data, the first table stores information from the text boxes and lists and the second table stores data from the grid.

About SQL syntax

The SQL Queries operator enables you to return a value that meets specific conditions. Within the Select From Where expression, you enter the fields, functions, and constants.

When building an SQL expression, you can use any mathematical or relational operations, as well as language operators. The placement of operators is critical to proper calculation, and some operators are placed before rather than after the fields. The following list describes the language operators you can use in an SQL expression:

Operator

Description

Select

Locates data for the selected field.

Select Sum

Locates and totals all figures for the selected field. You can use Select Sum in place of the Select portion of the expression.

From

Indicates the source database. Usually follows the Select portion of the expression.

Where

Defines the criteria that data must meet for use in the query. Usually follows the From portion of the expression.

Between

Defines a range of data. The Between operator works similar to >= and <=. (See links)

Inner Join

Creates a relationship between two tables. (See links)

Example Syntax:

Syntax: Select table1 From table2 Where value

Important! The syntax for an SQL expression can vary greatly depending on the complexity. The syntax above only outlines a simple SQL expression.

Example:

If you store information in the user-defined fields in 7-2 Company Information, you can use an SQL query to extract the information.

Syntax: Select USRDF1 From CMPANY

Queries follow these specific guidelines:

Between

The Between operator defines a range of numbers similar to using to >= and <=. The range is inclusive of the two numbers you indicate.

Syntax: Between number1 and number2

Inner join

The inner join operator joins two tables and creates a one-to-one relationship between records in the table.

Syntax: Table1 Inner Join table2 on string1=string2

Example:

Suppose you create a change order report that includes the budgeted costs by job. The job number is found in the Change Order table, and the budgeted amounts and cost codes are found in the Subcontract Change Order Lines table.

More resources

For additional online resources for Sage 100 Contractor products, visit the Sage Customer Portal. Use the portal to access the knowledgebase, chat online with customer support, or enter a customer support ticket.

For additional training on Sage products, visit Sage University.