About formula types

There are four different types of formulas. These formulas vary in the way they are stored and the manner in which they are accessed.

Application formulas

Public formulas

Design formulas

Quick formulas

About the components of formulas

Creating formulas

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.

More resources

For additional online resources for Sage 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.


About the components of formulas

The elements to include in a formula depend on what you want the formula to accomplish.

Application formula functions

Formula constants

Formula fields

Formula functions

Formula operators

Using AND/OR in formulas

Other formulas

About functions (definition)

Creating formulas

List of functions

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.

More resources

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


About functions (definition)

» Formulas > About functions

About functions

Functions are expressions used in formulas to perform specific operations. When a function operation is performed, the result of the operation is used as a value in the formula.

Terminology

Usage Notes

List of functions

About the components of formulas

Creating formulas

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.

More resources


List of functions

ABS(number)—returns the absolute value of a number.

Accumulating functions—includes the following individual functions:

AAVG([break], number[,...])—calculates the average of a series of records.

ACOUNT([break], field)—counts the records containing the field.

AMAX([break], number[,...])—returns the highest value in a series of records.

AMIN([break], number[,...])—returns the lowest value in a series of records.

ANZAVG([break], number[,...])—calculates the average of the non-zero numbers in a series of records.

ASUM([break], number[,...])—totals the numbers in a series of records.

AVG(number1, number2[,...])—calculates the average value of the numbers listed.

COLHDG(field[,heading number])—returns the heading (all or part) of a Custom Descriptions field.

DAY(date)—returns the day from a date.

DAYS(date1, date2)—calculates the number of days between two dates.

FORMAT(number, mask)—apply a specific format of punctuation to a numeric item.

IF(condition, value if true[,value if false])—returns values based on a conditional statement.

INDEX(field)—returns the position of items in option button lists or drop-down list boxes.

JOIN(value1, value2[,...])—connects values into a string of characters in the order they are connected.

JUST(value, justification[,length])—aligns alphanumeric values.

LOOKUP(field, key value[,...])—locates information that normally cannot be accessed automatically.

MATCH(value to match, value[,...])—compares an item to a list of items and returns the position of the first match in the list.

MAX(number1, number2[,...])—returns the highest number in a list of numbers.

MIN(number1, number2[,...])—returns the lowest number in a list of numbers.

MONTH(date)—return the month from a date.

NEWDATE(date[,months][,days][,years])—calculates a new date or returns a numeric constant in a date format.

NZAVG(number1, number2[,...])— returns the average of the non-zero numbers listed.

PART(value, position, length)—returns a portion of a value.

PRCNT(number1, number2)—calculates a percentage as a whole number.

RAND( )—generates a random number.

ROUND(number, digits)—rounds numbers to a specific number of digits.

SQRT(number)—calculates the square root of a number.

STRMATCH(string1, string2)—compares capitalization of two parameters.

SUM(number1, number2[,...])—adds a list of numbers and returns the total.

SWITCH(value[,decimal])—converts alphanumeric values to numeric and numeric values to alphanumeric.

TABLE (position, list of values)—returns the value that occupies a specific position in a list.

TIMEDIFF(start date, start time, end date, end time)—calculates the time difference between the start date/start time and the end date/end time.

TRUNC(number, digits)—removes or adds a specified number of decimal positions or sets a specified number of digits to zero.

YEAR(date)—return the year from a date.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


About using functions

Functions use a standard syntax. A function:

  • Begins with the command name
  • Is followed by a left parenthesis
  • Includes the parameters on which the function operates
  • Ends with a right parenthesis.

For example, NAME(parameter1, parameter2[,...]).

These rules determine how a function is constructed and interpreted:

  • Optional parameters are indicated by square brackets in these Help topics. For example, the IF function syntax is: IF(condition, value if true[,value if false]). The value if false parameter is surrounded by brackets to indicate that it is optional. When entering an optional parameter, do not enter the brackets.
  • Each parameter, except the last, must be followed by a comma. If an optional parameter is omitted and it is followed by another parameter, a comma must be entered in its place. For example, the syntax for the AAVG function is: AAVG([break], number[,...]). If the break parameter is omitted, the comma following it must still be entered to signify the missing parameter, e.g., AAVG(, Total Estimate).

Here's another example: NEWDATE(date[,months][,days][,years]). If the optional months and days parameters are omitted, the syntax for adding 10 years to Dec. 15, 1992 would be: NEWDATE(121592, , , 10). This is equivalent to NEWDATE(121592, 0, 0, 10). To add two months to the same date, the syntax would be NEWDATE(121592, 2). Notice that no commas follow the months parameter since there are no additional parameters required.

  • If a parameter is followed by an optional ellipsis, [,...], the parameter may be repeated an unspecified number of times. For example, the AVG function syntax is: AVG(number1, number2[,...]). This syntax specifies that there must be at least two number parameters entered to return an average, but also that more than two may be entered.
  • The parameters provide the information needed to perform the function. For example, you might enter a field name as a parameter. This specifies that the function is to use the value from the field.
  • In some cases, a range of fields can be used as a parameter. To specify a range, enter the first field in the range, two periods ( . . ) and the last field in the range. The sequential order of the fields in each application's records are listed in the Item list in the Public Formula Entry window. For example, a formula could be set up to calculate the total of all commitments invoiced by specifying the range of fields: Commt Invoiced - Labor through Commt Invoiced - Other. The formula would be: SUM(Commt Invoiced - Labor..Commt Invoiced - Other).
  • Alphanumeric constants must be enclosed in single or double quotation marks, e.g., 'California' or "California".

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


About naming formulas

Each formula is given a name. Because predefined application formula names begin with two upper case letters followed by a space, you cannot begin public formula names using the same method. For example, you can name a public formula "JC-sub retainage", but not "JC sub retainage" (this would be accepted as "JCsub retainage"). Formula names can be up to 22 characters long, including spaces.

Changing formulas

Copying formulas

Creating formulas

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Creating formulas

Overview

Formulas allow you to create mathematical expressions that can be evaluated in numerous applications. Formulas can contain combinations of fields, numeric operators, functions, constants, and other formulas. These elements combine to create a formula which can then be used in different areas of the software to perform calculations.

Formulas might be used when:

  • Printing a report -- to assign conditions based on the result of a calculation.
  • Creating an inquiry in Inquiry Designer -- to add a column that performs a calculation based on existing values.
  • Designing a report in Report Designer -- to sum the totals of two or more fields.
  • Creating special calculations for items in Payroll -- to calculate a deduction.
  1. From the Tools menu, select Formulas.
  2. Click [New]. The Formula Entry window opens.
  3. Begin building the formula by entering function names, operators, and constants using the keyboard.
  4. Add fields by double-clicking them from the right portion of the window.
  5. If necessary, you can view different field and formula lists by clicking [Index].

How?

  6. Click [OK] to accept the formula. If the formula cannot be accepted in its current form, a message displays indicating that formula is invalid. Correct the formula and click [OK] again.
  7. Name the formula in the Save Formula window that opens.

How?

  8. Continue working with formulas, or click [Close].

Notes:

  • If a formula contains one or more fields from a record, the formula can be used whenever an application has access to those records. If a formula contains only numbers or functions, it can be used any time formulas are available.
  • If the name of a field is typed in the formula instead of selected from the list in step 4, a message displays indicating there is an unexpected character. Remove the typed field name and replace it by selecting the same field name from the list of fields.

About formula types

About the components of formulas

Changing formulas

Copying formulas

Printing formulas

About functions (definition)

List of functions

Indexing lists

Finding items in a list

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Changing formulas

  1. From the Tools menu, select Formulas.
  2. Select the formula you want to modify. If necessary, click [Index] to view different formula types.
  3. Click [Change].
  4. Add or change the function names, operators, and constants using the keyboard.
  5. Add or change fields by double-clicking them in the right portion of the window.
  6. If necessary, you can view different field and formula lists by clicking [Index].

How?

  7. Click [OK] to accept the formula. If the formula cannot be accepted in its current form, a message displays indicating that formula is invalid. Correct the formula and click [OK] again.
  8. Continue working with formulas, or click [Close].

Notes:

  • If a formula contains one or more fields from a record, the formula can be used whenever an application has access to those records. If a formula contains only numbers or functions, it can be used any time formulas are available.
  • If the name of a field is typed in the formula instead of selected from the list in step 4, a message displays indicating there is an unexpected character. Remove the typed field name and replace it by selecting the same field name from the list of fields.

About formula types

About the components of formulas

Creating formulas

Copying formulas

List of functions

Printing formulas

List of functions

Indexing lists

Finding items in a list

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Copying formulas

Overview

Copying formulas allows you to copy formulas, change copied versions of application formulas, rename formulas, and modify existing formulas, while saving the existing formula for reference.

  1. From the Tools menu, select Formulas.
  2. Select the formula you want to copy. If necessary, click [Index] to view different formula types.
  3. Click [Copy].
  4. Name of the new formula you are copying to. If in Report Designer, select whether the new formula should be a public or design formula.

How?

  5. Continue working with formulas, or click [Close].

Tip: If an application formula nearly fits your needs but requires some minor modification, copy the formula to another formula and modify the copied version.

About formula types

Changing formulas

Creating formulas

Printing formulas

Indexing lists

About naming formulas

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Printing formulas

Overview

You can print application, design, and public formulas whenever they are accessible. The Formulas report lists the name and content of each formula in the selected formula type.

  1. From the Tools menu, select Formulas.
  2. To view different formula types, click [Index].
  3. From the File menu, select Print.
  4. Make print selections as needed.
  5. Click [Start] to print all formulas in the selected formula type.
  6. Continue working with formulas, or click [Close].

About formula types

Changing formulas

Copying formulas

Creating formulas

Deleting formulas

Indexing lists

Printing from a print selection window

Printing Troubleshooter

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Deleting formulas

  1. From the Tools menu, select Formulas.
  2. Select the formula you want to delete. If necessary, click [Index] to view different formula types.
  3. Click [Delete], and click [Yes] when asked to confirm that you want to delete the formula.
  4. Continue working with formulas, or click [Close].

Changing formulas

Printing formulas

Indexing lists

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Formulas

Use to create expressions that can be used when displaying, reporting, or conditioning data. See Creating formulas for detailed instructions.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Formulas window

Use to view existing application, public, and design formulas, and to access options for creating, changing, copying, deleting, and printing formulas.

About formula types

About the components of formulas

Changing formulas

Creating formulas

Copying formulas

Deleting formulas

Printing formulas

Items in This Window

Formula Type

Displays the type of formulas listed below. Clicking this title line is the same as clicking [Index]. It will open the Index window to display all formula types currently available for selection.

Formulas

Select a formula you want to change, copy, or delete. Use [Index], [Find], and [Order] to change the list of formulas and to find a formula from the list. Depending on the application you are using, the formula list can display public, application, or design formulas.

Display area

Displays the contents of the selected formula. Double-click this area to modify the formula contents.

[Close]

Closes this window.

[New]

Opens the Formula Entry window to create a new formula.

[Change]

Opens the Formula Entry window to modify the selected formula.

[Copy]

Opens the Copy Formula window to copy the selected formula to a new name.

[Delete]

Deletes the selected formula.

[Help]

Starts Help and displays information about this window.

[Index]

Opens the Index window to list the different types of formulas: public, application, or design (in Report Designer only). Also in Report Designer, quick formulas do not appear in this list, but can be selected from the Tools menu.

[Find]

Allows you to enter the name (all or part) of the formula for which you are searching.

Find entry

Enter the name (all or part up to 25 characters) of the formula for which you are searching.

[Prev]

Selects the previous occurrence of the entered characters.

[Next]

Selects the next occurrence of the entered characters.

[Order]

The order of the list cannot be changed in this window.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Formula Entry window

Use to enter a new formula or change an existing one. If changing a formula, the formula name appears in the window title.

The item list appears on the right side of the window, and the actual area where you create the formula is on the left side of the window.

About the components of formulas

Changing formulas

Creating formulas

About formula types

List of functions

Items in This Window

Message display area

Contains abbreviated instructions to guide you through creating and modifying a formula. Select a field or formula in the formula design area to display its name, record source or formula type, field type, and length.

Formula design area

Enter and modify formulas by typing constants, operators, and functions, and by selecting fields from the field list. See About the components of formulas for more information.

Application - file

Displays the application and the file in which the listed items available for selection are located. Clicking this title line is the same as clicking [Index]. It will open the Index window to display all files currently available for selection.

Item list

Select from the list of items available for use in creating or modifying the current formula.

[Index]

Opens the Index window to display different items for selection.

[Find]

Allows you to enter the name (all or part) of the item for which you are searching.

Find entry

Enter the name (all or part up to 25 characters) of the item for which you are searching.

[Prev]

Selects the previous occurrence of the entered characters.

[Next]

Selects the next occurrence of the entered characters.

[Order]

Opens the Field Order window to change the order of the listed items.

[OK]

Accepts the current entries and closes this window.

[Cancel]

Closes this window without accepting additions or changes made to the formula.

[Help]

Starts Help and displays information about this window.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Save Formula window

Use to save and name the formula you are creating. Any combination of characters and spaces can be used, except that names cannot begin with two uppercase characters followed by a space. These names are reserved for application formulas.

About naming formulas

Creating formulas

Item in This Window

Formula name

Enter a name for the formula you are creating (up to 22 characters, including spaces). Because preset application formula names begin with two upper case letters followed by a space, you cannot begin a public formula name using the same method. For example, you can name a public formula "JC-sub retainage", but not "JC sub retainage" (the second example would be accepted as "JCsub retainage").

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Copy Formula window

Use to copy the contents of a formula to a new formula and name. In Report Designer, you can copy to public and design formulas. In all other applications, you can copy only to public formulas.

Tip: Copy an application formula when you want to modify it. Since application formulas cannot be changed, you can copy the formula and modify the copy.

About formula types

Copying formulas

Creating formulas

Items in This Window

Copy to

Enter a new name for the formula you are copying.

Public or design formulas

Select the type of formula you want to copy to. These options are available only in Report Designer.

  • Public Formulas -- The formula is copied to the public formula list.
  • Design Formulas -- The formula is copied to the design formula list which is stored with the report.

[OK]

Accepts the current entries and closes this window.

[Cancel]

Closes this window without copying the formula.

[Help]

Starts Help and displays information about this window.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


AAVG examples

  • To find the Accounts Payable average tax amount on all distributions for a vendor, use the following formula.

AAVG(Vendor, Tax)

The vendor and tax fields are pulled from the AP - Distribution record. For each vendor, this formula adds all the tax amounts, divides by the total number of tax amounts (including zero tax amounts) and returns the average tax amount per distribution.

  • To find the Accounts Payable average tax amount for all vendors, use this formula:

AAVG(, Tax)

  • To find the Payroll average amount of fringes you pay for an employee during each pay period, you would pull information from the Check Fringe record by using this formula:

AAVG(Period End Date, Amount)

  • To display this average for each employee over multiple pay periods, use this formula:

AAVG(Employee, Amount)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


AAVG function

Use to calculate the average of a series of records.

Syntax

AAVG([break], number[,...])

Related function

AVG

Examples

Other accumulating functions

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


About keys

About record keys

Record keys are used in LOOKUP functions. A group of fields make up a record. A group of records make up a file. Each record is unique from any other record because the primary keys assigned to each record are unique.

Tip To determine the keys on the record being looked up, use Available Fields (Tools menu) in Report Designer to print the list of fields for the record. At the bottom of the field list, in the Standard Order section, the keys are listed in parentheses under the first order listed. If you plan to design many reports, you may find it helpful to print the available fields for all records and put these printouts in a binder for quick reference.

Keys play an important part when creating LOOKUP formulas. Here are four examples:

Record | Primary Keys
AP Invoice | Vendor, Invoice
AP Invoice | Account, Accounting Date, Journal, Run number, Sequence number
PR Employee | Employee
PR Employee Pay | Employee, Pay Type, Pay ID

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


About using TABLE with the MATCH function

The TABLE function is often used with the MATCH function.

The MATCH function finds a match for a value and returns the numeric position in the list of values. The value returned by the MATCH function can then be used to determine what value is returned from the list of values in the TABLE function by using the result of the MATCH function for the position value in the TABLE function.

For example, Payroll checks store the pay periods. To print a report, create a formula that assigns an index number to each pay period type:

Note: There can be 52, 26, 24, 12, 2, and 1 pay periods each year.

MATCH(Pay periods, 52, 26, 24, 12, 2, 1)

In the MATCH formula above, when the Pay period type equals 52 a '1' is returned, when it equals 26 a '2' is returned, when it equals 24, a '3' is returned, and so forth. You could name this formula "PerYr".

The next step is to use the TABLE function to return an alpha description for each of the values returned from the above MATCH formula you named "PerYr".

Original value returned | Value from MATCH | Pay period description (alpha)
52 | 1 | Weekly
26 | 2 | Bi-weekly
24 | 3 | Semi-monthly
12 | 4 | Monthly
2 | 5 | Semi-annually
1 | 6 | Annually

You can use the TABLE formula to print the alpha description for each employee's time on a report:

TABLE(PerYr, 'Weekly', 'Bi-weekly', 'Semi-monthly', 'Monthly', 'Semi-annually', 'Annually')

In this formula, when PerYr returns 1, 'Weekly' is returned; when PerYr returns 2, 'Bi-weekly' is returned, etc. With the following data, the description prints as:

Emp | Pay Periods | Alpha Description
101 | 52 | Weekly
103 | 52 | Weekly
1001 | 26 | Bi-weekly
1002 | 26 | Bi-weekly
2001 | 24 | Semi-monthly
3001 | 12 | Monthly

Note: If you use a range of fields as a value, the function returns the position of the field in the range of fields. For example, in the range Period 1 Budget..Period 5 Budget in General Ledger, the Period 1 Budget would be in position 1, the Period 2 Budget in position 2, and so on. Multiple ranges are treated as though they are one continuous range. If you added another range to the above example, such as Period 1 Budget..Period 5 Budget, Period 7 Budget..Period 10 Budget, because Period 5 Budget was in position 5, the Period 7 Budget would be in position 6, the Period 8 Budget in position 7, and so on.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ABS comments

The number parameter can contain fields, constants, functions, other formulas, or arithmetic operations. The values must be numeric.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ABS examples

  • To find the contents of the General Ledger annual budget field as an absolute value, use this formula:

ABS (Annual Amount)

  • To calculate the General Ledger budget variance percentage and return the absolute value of the percentage, use this formula:

ABS((Current Balance - Annual Amount) / Current Balance)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ABS function

Use to return the absolute value of a number. An absolute value of a number (positive, negative or zero) is the same number without any positive or negative sign. For example, ABS(-34.19) = 34.19

Syntax

ABS(number)

Parameter

number -- the number used in the absolute value calculation.

Return

numeric

Comments

More examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Accumulating functions

Use these functions to perform calculations on a series of records to create subtotals, grand totals and other cumulative functions.

Syntax

AAVG([break], number[,...])

ACOUNT([break], field)

AMAX([break], number[,...])

AMIN([break], number[,...])

ANZAVG([break], number[,...])

ASUM([break], number[,...])

Parameters

[break] -- the data field or formula used to specify when a (break) total is to be taken.
(Click the Break parameter button below for more information.)

number -- specifies what is to be accumulated.

field -- the data field to be counted.

[,...] -- additional numbers, if any.

Return

numeric

Note If you are using an accumulating function on a report design that is sorted by fields originating in different records, please refer to the Report Designer Help topic "About using accumulating functions with custom sort orders" for important information.

About formula types

Comments

List of accumulating functions

Break parameter

Number parameter

Field parameter

Combined examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Accumulating functions—break parameter

The break parameter determines when the accumulation process should produce a total. This allows the break parameter to be used to create level totals. As the accumulating function reads data, it accumulates amounts specified by the number parameter. When the value specified in the break parameter changes, the accumulation is returned and cleared so that it can begin accumulating for the new value in the break field. The break parameter can be a data field or formula. If a formula is used, all items in the formula must either be associated with the same record or be accessed via the LOOKUP function.

The break parameter is optional, but the comma following the break parameter is always required. When the break parameter is left blank, you are choosing to accumulate the entire range of records into one total. Using the break parameter creates a total each time the break value changes.

Notes:

  • If you break on a field other than a key field which is part of the sort order, you must set up a custom sort in Report Designer and specify that break field as part of the sort order.
  • All fields in the custom sort order must either be from the same record as the number being accumulated or be accessed through a LOOKUP formula.
  • Sorting and breaking by non-key fields is not available in an inquiry.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Accumulating functions comments

  • Accumulating functions can create a total of similar amounts found in a series of records. In Payroll for example, you might want to create a total of a certain withholding type based on a set range of payroll transactions. By setting a break field of employee ID, the ASUM function would return a separate total each time the employee ID changed as the transactions were read in employee order.
  • You can use these functions to accumulate amounts from a lower level of detail up to a higher level with less detail. In Accounts Payable for example, if you need detail from the distribution items of invoices, but you only see one line per invoice, you could accumulate the detail from the distributions.
  • To perform calculations on totals from different records, you can create two function statements that are used in one formula. For example to add the accumulations from two different records, use this format:

ASUM(break, number) + ASUM(break, number)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Accumulating functions examples

In the following table, the YTD amounts for the Accounts Payable vendors are used to return the value for each type of function shown below the table. The break parameter has been omitted, which causes the function to be performed for the entire range of records.

Vendor | YTD amount
Allied Services | 200.00
Barnes Plumbing | 100.00
Johnson Electric | 500.00
Pacific Rentals | .00
Tool Shed, Inc. | 50.00

AAVG(, YTD Amount) = 170.00

ACOUNT(, YTD Amount) = 5

AMAX(, YTD Amount) = 500.00

AMIN(, YTD Amount) =.00

ANZAVG(, YTD Amount) = 212.50

ASUM(, YTD Amount) = 850.00

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Accumulating functions—field parameter

The field parameter in the ACOUNT function must contain a data field from the records being counted. This function simply counts the number of records that contain that field. If a break field is also entered, it must be from the same field list that you used to select the field parameter.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Accumulating functions—number parameter

The number parameter which is used for all accumulating functions except ACOUNT, can contain fields, constants, functions, other formulas, or arithmetic operations, which must be numeric. All items listed in the number parameters must be associated with the same record.

Tip When the data is not from the same record, you may need to use the LOOKUP function.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ACOUNT examples

  • To find the number of Accounts Payable invoices each vendor has, use this formula:

ACOUNT(Vendor, Invoice)

  • To find the total number of Account Payable invoices in the database, use this formula:

ACOUNT(, Invoice)

  • To find the number of Payroll checks for each employee, use this formula:

ACOUNT(Employee, Check Number)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ACOUNT function

Use to count the records containing a specific field.

Syntax

ACOUNT([break], field)

Examples

Other accumulating functions

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


AMAX examples

  • To compare the Accounts Payable invoice amounts for each vendor and determine the highest invoice amount for each vendor, use this formula:

AMAX(Vendor, Amount)

  • To determine the highest Accounts Payable invoice amount for all vendors, use this formula:

AMAX(, Amount)

  • To print a Payroll report that prints the highest amount of Workers' Compensation for each employee, use the following formula. This formula pulls information from the PR-Check Workers Comp record:

AMAX(Employee, Amount)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


AMAX function

Use to return the highest value in a series of records.

Syntax

AMAX([break], number[,...])

Related function

MAX

Examples

Other accumulating functions

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


AMIN examples

  • To determine the lowest invoice amount for each Accounts Payable vendor, use this formula:

AMIN(Vendor, Amount)

  • If you are tracking Payroll sick pay as a separate pay ID and you assume an overall condition of Pay ID = 'sick', to find the employee that had the least YTD units of sick pay, use this formula:

AMIN(, YTD Units)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


AMIN function

Use to return the lowest value in a series of records.

Syntax

AMIN([break], number[,...])

Related function

MIN

Examples

Other accumulating functions

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ANZAVG examples

  • To find the Accounts Payable average of year to date amounts (less discounts taken) when the results are not zero, use the following formula. This formula adds the total YTD amount minus the total discounts taken for all vendors and divides by the number of items that do not equal zero. If a vendor has no YTD amount, the vendor is not included in the calculation:

ANZAVG(, YTD Amount - YTD Discount Taken)

  • To find the Payroll average number of overtime hours paid, not including check records that have overtime hours of zero, use the following formula. This formula gives you a more accurate representation of the overtime hours being paid to employees who have worked overtime.

    • ANZAVG(, Overtime Hours) For example, if you have this employee data:

Employee | PE date | Overtime hours
101 | 5-15-98 |
105 | 5-15-98 | 12.00
108 | 5-15-98 |
115 | 5-15-98 |
125 | 5-15-98 | 10.00

If you use AAVG(, Overtime Hours), the average of overtime hours would be 22.00 divided by 5 employees for an average of 4.4 overtime hours per employee. However, if you use ANZAVG(, Overtime Hours), the average of overtime hours would be 22.00 divided by the two employee which had overtime hours for an average of 11.00 overtime hours per employee.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ANZAVG function

Use to calculate the average of the non-zero numbers in a series of records.

Syntax

ANZAVG([break], number[,...])

Related function

NZAVG

Examples

Other accumulating functions

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Application formulas

Sage 300 Construction and Real Estate provides application formulas with the following applications:

  • Address Book
  • Accounts Payable
  • Accounts Receivable
  • Contracts
  • Cash Management
  • Equipment Cost
  • Information Assistant
  • Job Cost
  • Payroll
  • Project Management
  • Property Management

You cannot modify application formulas, but you can copy them as public formulas or design formulas and then modify them. Application formula names begin with an application abbreviation (for example, AP Aging Amount).

View a list of application formulas

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Application formula functions

Application formula functions are predefined functions that perform operations specific to a particular application. They are accessible from the application itself, Information Assistant, Inquiry Designer, and Report Designer. Some application functions are based on specific records and are accessible only when the dependent records are available. For example, the Property Management function "EffectiveSquareFootage" is available only when the record, PM-Unit Square Footage, is available.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ASUM examples

  • To total the amounts in the AP - Distribution records and accumulate the amounts at the invoice level, use the following formula:

ASUM(Invoice, Amount)

When a new invoice is encountered, the formula starts calculating a new total for the next invoice.

  • To add numbers from a field list at the same break level and then total them, use the ASUM function. For example, on the Payroll check record, regular and overtime hours are tracked each time an employee is paid. To total these hours for each employee, use this formula:

ASUM(Employee, Regular Hours, Overtime Hours)

  • To accumulate a Payroll total at the highest level (a grand total) for employee's gross pay, you should leave the break field blank but include the comma:

ASUM(, Gross Pay)

  • To create a total on a vendor report that displays the amount of invoice distributions less paid amounts on the invoice distributions, you would pull data from the AP - Distribution record (Amount) and the AP - Distribution Payment record (Amount Paid) by using this formula:

ASUM(Vendor, Amount) - ASUM(Vendor, Amount Paid)

  • To get a total percent complete for all cost codes on each job, use this formula:

(ASUM(Job, JTD Cost) / ASUM (Job, Total Estimate)) * 100

  • To total Accounts Payable invoice amounts by a portion or section of the vendor ID, use a PART formula as the break parameter in the ASUM function. For example, if the two-letter state code was used as the first two characters of every vendor ID, you could use the following formula to obtain total invoice amounts by state.

ASUM(Vendor State ID, Amount)

The break parameter, Vendor State ID, is the formula: PART(Vendor, 1, 2)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ASUM function

Use to total the numbers in a series of records.

Syntax

ASUM([break], number[,...])

Related function

SUM

Examples

Other accumulating functions

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


AVG comments

The parameters can contain fields, constants, functions, other formulas or arithmetic operations. The values must be numeric.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


AVG examples

  • To calculate the General Ledger average of the current and previous 2 activity periods, use this formula:

AVG(Current Period Activity, Prior 1 Activity, Prior 2 Activity)

  • To calculate the Accounts Payable average vendor payments this year and last year, use this formula:

AVG(YTD Paid, Last Yr Amt Paid)

  • To calculate the Job Cost average for this month's and last month's cost code costs, use this formula:

AVG(MTD Cost, LM Cost)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


AVG function

Use to calculate the average value of the numbers listed.

Syntax

AVG(number1, number2[,...])

Parameters

number1 -- the first number in the list of numbers to be averaged.

number2 -- the second number in the list of numbers to be averaged.

[,...] -- remaining numbers in the list, if any.

Return

numeric

Comments

More examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


COLHDG comments

When the COLHDG function is used to create column headings for Custom Descriptions, any changes to the field descriptions are automatically carried over to the report headings.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


COLHDG examples

  • The description for the Commitment field in Job Cost can be changed by using Custom Descriptions. When the description is changed in Custom Descriptions, the change is printed on reports when the COLHDG function is used.

COLHDG(Commitment)

  • The following COLHDG formulas could be used in Accounts Payable, Payroll, and General Ledger, respectively.

COLHDG(Invoice Code 1)

COLHDG(Misc1)

COLHDG(GL Reference 1)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


COLHDG function

Use to return the heading (all or part) of a Custom Descriptions field. This function ensures that the column headings printed on reports match the descriptions entered and possibly changed in Custom Descriptions.

Syntax

COLHDG(field[,heading number])

Parameters

field -- the Custom Descriptions field whose description is to be used as a heading.

[,heading number] -- an optional number equal to 1 or 2. When a custom description is more than one word, it is separated into two parts in such a way as to fit the heading onto two lines. The number determines what part to return for a single line of the column heading. Omitting this parameter returns both lines.

Return

text

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Date parameter

Date parameters

The digits in date parameters are interpreted as shown.

Number of digits | Interpretation | Example
5 | mddyy | (10598) = 1-05-98
6 | mmddyy | (110898) = 11-08-98
7 | mddyyyy | (3081998) = 3-08-98
8 | mmddyyyy | (10161999) = 10-16-99

Date Format on the General tab (Tools, Options menu) determines whether the dates are entered beginning with the month, day or year.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


DAY comments

Using this function is the same as using PART(date, 5, 2).

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


DAY function

Use to return the day from a date field.

Syntax

DAY(date)

Parameters

date -- the date field containing the day to be returned.

Return

numeric

Comments

Date parameters

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


DAYS comments

The parameters can contain date fields, the system date or an entered date. The dates can be entered in any order. For example, DAYS(123194, 121593) = 381 and DAYS(121593, 123194) = 381. The years can be entered with two digits or four, e.g., DAYS(123099, 1012001) = 368.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


DAYS examples

  • To calculate the number of days between the system date and 12-04-98, use this formula:

DAYS(System Date, 120498)

  • To calculate the number of days between the Accounts Payable check date and invoice date for each invoice record, use this formula:

DAYS(Invoice Date, Check Date)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


DAYS function

Use this function to calculate the number of days between two dates. The number of days returned is always a positive number.

Syntax

DAYS(date1, date2)

Parameters

date1 -- one of the dates for the comparison.

date2 -- the other date for the comparison.

Return

numeric

Comments

Date parameters

More examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Design formulas

Design formulas are only created for report designs while using Report Designer and are only accessible when using the report design for which the design formula was created. However, design formulas may be used as often as necessary by the same design for other items in the report or within other design formulas. If necessary, you can copy a design formula to a public formula.

Design formulas are created by selecting Formulas from the Tools menu in Report Designer.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Entering key values

The key value parameters can be entered in any of the following formats:

  1. The contents of a field in the main record. For example, you could enter the Payroll ID of the project manager for each job in the first miscellaneous field on the General tab of the Job Setup window. Then, while printing job information, you can access any information the manager's employee record by selecting the "Project Manager" field from the Job record when entering the key value in the LOOKUP formula.
  2. A constant. For example, to retrieve the FUTA percent field from the Tax rate record while printing a list of employees and their YTD Gross pay, you can enter the key value as "FUTA_E" (assuming that this is the Tax ID).
  3. A function or a formula. For example, while printing a list of jobs, you may want to print the current balance of a specific General Ledger account based on the prefix entered on the AP and GL tab and the base entered in a field on the Custom Fields tab in the Job Setup window. The key value could be entered as JOIN(Cost Account Prefix, JUST(Base acct, LJ)). "Base acct" is the name given to the custom field. It must be left justified to remove any leading blanks. This entire key string could have been entered into a formula, and the formula name would have been inserted as the key value.
  4. An arithmetic operation. Although this format would be used rarely, the key example above could have been entered as SWITCH(SWITCH(Cost Account Prefix) * 10000 + Base acct). This example assumes a base account of four digits. The SWITCH functions are used to turn alphanumeric fields into arithmetic numbers for calculation and then back to alphanumeric for looking up the record's alphanumeric key.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


FORMAT comments

The number parameter can be a field, a constant, a function, a formula or an arithmetic operation. The value must be numeric. The model parameter is entered as an alphanumeric constant that indicates the format to be applied to the values in the number parameter. Only the punctuation in the model parameter is used; the actual placeholders can be any constant.

The number of digits in the number parameter does not need to match the number of digits in the model. The model is applied to the number from right to left. The decimal point location in the number parameter is ignored when the model is applied. If there are more digits in the number parameter than are in the model, the entire number prints. If there are fewer digits in the number parameter, extra positions in the model are ignored. For example:

FORMAT(12345.67, "00.0000") = 123.4567

FORMAT(12345.67, "00000.000") = 1234.567

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


FORMAT example

  • To apply model '99999-99' to the value in the General Ledger Account field, use the following formula. You might use this function to enhance reports that list account numbers by printing a dash instead of a decimal between the account and the suffix.

FORMAT(Account, '99999-99')

Tip: To format a field that is not numeric, use the SWITCH function to change the field from alphanumeric to numeric. For example, if the formula FORMAT(SWITCH(JOB), '11*111') is used, the job 98-001 that normally prints is returned as 98*001.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


FORMAT function

Use to apply specific punctuation to a numeric item.

Syntax

FORMAT(number, model)

Parameters

number -- the number to which the model is applied.

model -- the format in which the number should be returned.

Return

alphanumeric

Comments

Example

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Formula constants

A constant is a value included in the formula. For example, to multiply the contents of an amount field by six and one-half percent, enter .065 as a constant in the formula.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Formula fields

In formulas, you can include fields from records and system fields. Because formulas are typically used to perform mathematical calculations, the majority of fields that you select will be numeric. There are some formulas, however, that use dates, alphanumeric fields, or other types of non-numeric fields.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Formula functions

Functions perform specific operations within a formula. For example, the DAYS function calculates the difference between two specified dates. For more information about functions, click here.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Formula operators

Operators are used in formulas to enter arithmetic and relational operators in the standard mathematical format.

This operator | Performs the following mathematical action
+ | Addition
- | Subtraction
* | Multiplication
/ | Division
> | Greater than (can also be entered as GT)
< | Less than (can also be entered as LT)
= | Equal to (can also be entered as EQ)
>= | Greater than or equal to (can also be entered as GE)
<= | Less than or equal to (can also be entered as LE)
<> | Not equal to (can also be entered as NE)

Calculations are performed from left to right, with the information enclosed in parentheses calculated first, followed by multiplication and division, and then addition and subtraction. For example, the formula: 5 * (4 + 2) - 12 / 2 equates to 5 * 6 - 12 / 2, then 30 - 12 / 2, then 30 - 6, and finally, 24.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Formulas (Tools menu)

Use to create expressions that can be used when displaying, reporting, or conditioning data.

Windows

About formula types

About the components of formulas

Creating formulas

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Formulas version

Formulas Help

formulas.chm—version 12.1.0

April 12, 2012

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Formulas windows

Copy Formula

Formula Entry

Formulas

Save Formula

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


IF check box comments

When check boxes are used in the condition parameter of the IF function, no comparative operator or value is needed. Check boxes always return a true/false, yes/no, or marked/unmarked value.

On the 1099 Settings tab (Vendor Setup window), you can mark Accounts Payable vendors that should receive a 1099 form. The condition parameter for this box is 'Receives Form 1099.'

IF(Receives Form 1099, Recipient ID, 'Need ID')

If the vendor has been marked to receive 1099 forms, the formula returns the Recipient's tax ID from the 1099 Settings tab. If the box has not been marked, the text 'Need ID' is returned.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


IF comments

The parameters can contain fields, constants, functions, other formulas, or arithmetic operations. If the contents of the condition parameter is only a single field, it must be a check box field. Otherwise, the comparative operators must be used in the condition parameter. The condition parameter must be able to be marked true/false, yes/no, or marked/unmarked.

You can nest up to 20 IF functions per formula.

See About comparison operators, Comparison operator examples, and Examples of using multiple conditions for more information.

Check Box Comments

Multiple condition comments

Option button and list comments

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


IF comparative operators

The comparative operators available for use in the condition are:

> greater than (can also be entered as GT)

< less than (can also be entered as LT)

= equal to (can also be entered as EQ)

>= greater than or equal to (can also be entered as GE)

<= less than or equal to (can also be entered as LE)

<> not equal to (can also be entered as NE)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


IF examples

  • If the Accounts Payable amount paid is equal to the invoice amount, 'Paid' is returned. Otherwise, the return value is blank.

IF(Amount Paid = Amount, 'Paid')

  • You could return the balance as the value if false by changing the formula to:

IF(Amount Paid = Amount, 'Paid', Amount - Amount Paid)

  • If the Receives Lien Waiver check box in the Accounts Payable Vendor record is marked, the text Check lien waiver is returned; otherwise, n/a is returned.

IF(Receives Lien Waiver, 'Check lien waiver', 'n/a')

  • If the Accounts Payable invoice amount times the tax rate of .065 is greater than the tax amount on the invoice record or if the tax amount on the invoice is zero, the calculated tax is returned. If the calculated tax is less than or equal to the tax amount on the invoice (and the tax amount is not zero), the tax amount is returned.

IF(Amount * .065 > Tax OR Tax = 0, Amount * .065, Tax)

  • To create a Job Cost transaction report with separate columns for Accounts Payable, Payroll Costs, Direct Costs, Other Entries and Estimate Entries, use these formulas:

Accounts Payable column -- IF(Transaction Type = 'AP Cost', Amount)

Payroll Costs column -- IF(Transaction Type = 'PR Cost', Amount)

Direct Costs column -- IF(Transaction Type = 'JC Cost', Amount)

Other Entries column -- IF(Transaction Type NE 'AP Cost' AND Transaction Type NE 'PR Cost' AND Transaction Type NE 'JC Cost' AND Transaction type NE 'Original Estimate' AND Transaction Type NE 'Approved Estimate Changes', Amount)

Estimate Entries column -- IF(Transaction Type = 'Original Estimate' OR Transaction Type = 'Approved Estimate Changes', Amount)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


IF function

Use to return values based on a conditional statement. This function returns the first value if the statement is true or the second value if the statement is false.

Syntax

IF(condition, value if true[,value if false])

Parameters

condition—an expression or a field in which the value is interpreted as true/false, yes/no, or marked/unmarked.

value if true—the value returned if the condition is true, yes, or marked.

[,value if false]—the value returned if the condition is false, no, or unmarked. This parameter is optional and, if it is not used, false, no, and unmarked values return blanks or null values.

Return

If the true value and the false value are of the same field type, the return value is that type. Otherwise, the return is alphanumeric.

Comments

Comparative operators

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


IF multiple condition comments

You may need to create an IF function in which the condition parameter must meet a combination of conditions. This is done by connecting the condition parameters with AND or OR.

When using AND in an IF function, all conditions must be true in order for the value if true parameter to be returned. If they are not all true, the value if false parameter is returned.

When using OR in an IF function, at least one of the condition parameters must be true in order for the value if true parameter to be returned. If none are true, the value if false parameter is returned.

  • The IF formula below returns 'Print this' because both condition parameters (1=1 and 2=2) are true.

IF(1=1 AND 2=2, 'Print this', 'Print that')

  • The IF formula below returns 'Print this' because one of the condition parameters (1=1) is true.

IF(1=1 OR 1=2, 'Print this', 'Print that')

  • The IF formula below returns 'Print that' because one of the condition parameters (1=2) is false.

IF(1=1 AND 1=2, 'Print this', 'Print that')

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


IF option button comments

IF option button and list comments

Option buttons and drop-down list boxes allow you to choose from a group of options. Only one option in the group can be selected. When you want to print or process information based on a match to an option that was selected, you can use the IF function. You would enter the condition parameter with the:

  • Option button or list group title
  • Comparative operator
  • Option button or list title to match

For example, on the Status tab (Job Setup window) you can select Unstarted, In progress or Closed for the Status. If you want to print the completion date for jobs that are closed or a message for jobs that are in progress or started, the IF function needs the three condition parameters discussed above:

  • Option button group title of STATUS
  • Comparative operator of =
  • Option button to match of Closed

The IF function would be entered as:

IF(Status = 'Closed', Actual Complete Date, 'Not Complete')

If the job has been marked as Closed, this formula would return the actual completion date entered in the Job Setup window. If the job is In Progress or Unstarted, this formula returns the text "Not Complete."

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


INDEX comments

The position of items in option button lists or drop-down list boxes can be found in the Print Available Fields printout in the Special Values column. Use Available Fields (Tools menu) in Report Designer to print this printout.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


INDEX examples

INDEX example

  • To return the position instead of the literal description of the status in Job Cost, use this formula:

INDEX(Status)

  • You could then use this formula in another formulas, such as the following:

IF(Index(Status) = 3, Actual Complete Date, 'Not Complete')

If the job has been marked as Closed, this formula would return the actual completion date entered in the Job Setup window. If the job is In Progress or Unstarted, this formula returns the text "Not Complete."

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


INDEX function

Use to return the position of items in option button lists or drop-down list boxes.

Syntax

INDEX(field)

Parameter

field -- the data field that contains the values you want returned.

Return

numeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Indexing lists

Overview

Indexing changes the list of fields or formulas that display in some windows. Use the indexing feature to select items from different lists when creating conditions, formulas, reports, or inquiries.

  1. Click [Index] in a list. For example, these lists are found in the Add and Change Condition windows, Formula Setup and Formula Entry windows, in Report Designer, and in Inquiry Designer.
  2. If [Add] is available and you want to select from additional lists, click [Add], select the additional list, and click [OK].
  3. In the Index window, highlight the list you want to select items from, and click [OK].
  4. In the original window from step 1, select the item you want to use.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


JOIN comments

The parameters can contain fields, constants or functions. The values are joined, in the order listed, into a single alphanumeric value. Spaces at the end of the values are suppressed unless the value is a constant.

For example, if the length of the City field is 15 characters and you entered the city of Dover which is 5 characters, the 10 extra spaces are suppressed. If the literal "The city of " (note space after 'of') preceded the City field in the JOIN function, the four trailing spaces in the formula would be retained:
"The city of Dover" This is because 'The city of ' is considered to be a constant.

Include parameters for punctuation and spaces in the list by enclosing them in quotation marks. The return value can be up to 4000 characters in length. For example, JOIN("Connect"," ","the"," ","fields.") = Connect the fields. Note the use of quotation marks for the spaces.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


JOIN examples

  • In this JOIN formula, value1 is the City field in the Accounts Payable vendor address. Value2 is a constant comma followed by a space. Value3 is the state from the vendor address, Value4 is a constant space and Value5 is the ZIP Code from the vendor address. These values combine to return a single string of characters.

JOIN(City,", ",State," ", ZIP Code)

  • You may want to include single quotes, double quotes or both as part of the string. A single quote must be enclosed with double quotes (e.g., "John's pencil"). A double quote must be enclosed with single quotes (e.g., '"My pencil,"' said John.). An example of including both in a JOIN function is:

JOIN('"I',"'m late,",'" said the rabbit.')

Result: "I'm late," said the rabbit.

  • Value1 = '"I'—The opening double quotation mark is enclosed in single quotes.
  • Value2 = "'m late,"—The apostrophe in I'm is enclosed in double quotes.
  • Value3 = '"said the rabbit.'—The closing double quotation mark is enclosed in single quotes.
  • To JOIN constant text to each employee ID, use this formula. This can be useful to designate the beginning of each employee's information on a report.

JOIN("Emp#", Employee)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


JOIN function

Use to connect values into a string of characters which print in the order they are connected.

Syntax

JOIN(value1, value2[,...])

Parameters

value1 -- the first value in the list of values to be joined.

value2 -- the second value in the list of values to be joined.

[,...] -- remaining values in the list, if any.

Return

alphanumeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


JUST comments

The value parameter can contain fields, constants or functions.

The justification parameter specifies where the value is to be aligned in the return value. The options are:

  • LJ—Left Justify
  • RJ—Right Justify
  • C—Center
  • RJN—Right Justify Numeric

When using this function in an inquiry, justification occurs within the return value, not within the inquiry column. For example, if an inquiry column has a width of 20 and contains a return value with a length of 10, the characters always appear somewhere within the first 10 spaces of the column; the exact location depends on the justification option used.

When using JUST in Report Designer, the justification occurs first in the return value. Then, the Report Designer Field Format Justification option determines where the return value prints in the column.

The optional length parameter is the total number of characters to be returned. If the length requested in the third parameter is less than the number of characters in the value, the right-most characters of value are truncated. For example, JUST(Depreciation, C, 10) would return 'Depreciati', the first 10 characters.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


JUST examples

  • Left Justify (LJ)—Use to force each value returned to be left aligned. This is helpful if the field contains leading spaces you want to omit. Most alphanumeric fields are left aligned.

JUST(ZIP Code, LJ)

  • Right Justify (RJ)—Use to force each value returned to be right aligned. This is helpful if you have entered numbers in an alphanumeric field but want to display them aligned as though the field was numeric.

JUST(Misc Title 1, RJ, 15)

  • Center (C)—Use to center each value in the return value.

JUST(Project Manager, C)

  • Right Justify Numeric (RJN)—Use to combine Left Justify and Right Justify depending on the value in the field you are justifying. When the value to be returned contains any alphabetic characters, the value returned is left aligned. When the value to be returned contains numbers and punctuation, the value returned is right aligned. This is helpful when displaying fields which do not contain consistent information. RJN does not split a field that is part numeric and part alphabetic.

JUST(Description, RJN)

Description | RJN returns description of
98-003 | 98-003
Fort Wayne Officer's Club | Fort Wayne Officer's Club

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


JUST function

Use to align alphanumeric values within the maximum field length.

Syntax

JUST(value, justification[,length])

Parameters

value—the value to be justified.

justification—options for justification.

[,length]—length of justified value, optional.

Return

alphanumeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


List of accumulating functions

AAVG function

ACOUNT function

AMAX function

AMIN function

ANZAVG function

ASUM function

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


LOOKUP About data not on primary_record

LOOKUP - About data not on primary record

Sometimes the information necessary to complete the key value parameter is not available as fields on the main record. For example the keys for the PR-Employee Tax record are Employee, Tax level and Tax ID.

  • The Employee field can usually be found on other field lists.
  • The Tax level must be one of three values: 1 for Federal, 2 for State, and 3 for City. These value definitions can be found in the Available Fields Printout in the Special Values column.
  • Tax ID must be entered as a value matching one of the taxes that are set up. Because you are matching an alphanumeric field, you must put quotation marks around the ID.

For example, Federal Withholding tax has a tax level of 1 and an ID of 'FWH'.

LOOKUP(QTD Taxable, Employee, 1, 'FWH')

  • To return the YTD subject-to amount for Medicare from the Check record, use this formula. The Medicare deduction is a field on the Employee Tax record.

LOOKUP(YTD Subject-to, Employee, 1, 'MED')

Tax level is 1 for Federal because you are looking for the federal Medicare tax.

Tax ID for Medicare taxes is 'MED'.

Parameter

Source for parameter

Field

YTD Subject-to in PR Employee Tax record

Key values

Employee in PR Check record;
Tax Level is a constant = 1
Tax ID is a constant = 'MED'

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


LOOKUP - About using with accumulating functions

To total fields that originate in different records, use the LOOKUP function along with an accumulating function to locate and total these fields. Without the use of the LOOKUP function, all fields listed in accumulating functions must originate in the same record. For example, to find the skill level from the PR-Employee record when the primary record is PR-Time, use this formula:

ASUM(, IF(LOOKUP(Skill Level, Employee) = 'Journeyman', Units))

This formula totals units for all employees with a skill level of journeyman. The LOOKUP function allows you to access the field Skill Level from the PR-Employee record even though the field being accumulated, Units, is on the PR-Time record.

The components of this formula are:

  • ASUM—function used to create a total.
  • ,—indicates no break fields (break parameter omitted) and that you want a report total.
  • IF—function used to condition formula.
  • LOOKUP—you want to look up the Skill Level from the PR-Employee record. The key for this record is Employee, which is on the PR-Time record.
  • Skill Level—the field you want to look up.
  • Employee—the key from the PR-Employee record (where Skill Level is stored). Employee is on the PR-Time record.
  • =—indicates comparison for the IF function.
  • 'Journeyman'—is compared to the value found in the LOOKUP to determine if there is an exact match to the text. This field is case-sensitive.
  • Units—when a match is found, the Units from each PR-Time record are added to get a total units when the Skill Level is 'Journeyman'.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


LOOKUP - Accounts Payable example

To retrieve the General Ledger account title for each account when the main record is the AP-Distribution record, use this formula:

LOOKUP(Title, Accounts Payable Account)

Parameter | Source for parameter
Field | Title in GL-Account record
Key value | Accounts Payable Account in AP-Distribution record

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


LOOKUP comments

The field parameter must be a data file field and be located in the lookup record that contains the information you want. The key values must exist as valid primary keys in that lookup record.

The key values must be listed in the primary key order. They can be fields or combinations of fields in the main record, constants, functions, other formulas, or arithmetic operations.

All keys for the record being looked up must be listed in the function in the same order they are found on the lookup record. For example, when returning the Accumulate As field in the JC - Category record, there are four keys for the lookup record - job, extra, cost code, and category. The key value parameter must include all four in the correct order for the Accumulate As field to be returned.

LOOKUP(Accumulate As, Job, Extra, Cost Code, Category)

When there are multiple-field keys, as in the example above, all the key fields entered in the LOOKUP function must be associated with the lookup record. Do not be confused by the fact that there is a "job" key field in each of the job, extra, cost code, and category records. If you are looking up a cost code record, the "job" key field must be specified as the one belonging to the cost code record.

Tip: To determine the primary keys on the record being looked up, use Available Fields (Tools menu) in Report Designer to print the list of fields for the record. At the bottom of the field list, in the Standard Order section, the primary keys are listed in parentheses under the first order listed.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


LOOKUP examples

Payroll example

Accounts Payable example

About data needed to complete the key value not on the primary record

About using LOOKUP with accumulating functions

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


LOOKUP function

Use this function to locate information in another record that is not logically linked to the current record being accessed. Although the database already contains many direct links between various records, when a link does not normally exist, the LOOKUP function can be used.

Syntax

LOOKUP(field, key value[,...])

Parameters

field—the data field in the other record that contains the value you want returned.

key value—the first primary key value used to identify the other record containing the information you want.

[,...]—additional key values, if any.

Return

same as field

Note: There are two distinct records involved in the LOOKUP function. The first, or main, record is the record currently being accessed and reported on. The second, or lookup, record is the record that contains some information you want to access and report alongside the main record's information.

Entering key values

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


LOOKUP - Payroll example

To retrieve the Tax Description for a Tax ID from the PR-Tax record when the main record is PR-Employee Tax, use this formula:

LOOKUP(Description, Tax ID)

Parameter | Source for parameter
Field | Description field in PR Tax record
Key value | Tax ID field in PR Employee Tax record

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MATCH comments

The parameters can contain fields, constants, functions, other formulas or arithmetic operations. The values can be alphanumeric or numeric.

The function uses the value to match parameter to search for a matching value in a given list. The list of items is sequentially numbered internally from the first item to the last item, beginning with the number one (1). The number assigned to the item which matches in the list is the number returned for the function. If no match is found in the list, a zero is returned. For example., MATCH("r", "e", "t", "r", "x") = 3, but MATCH("h", "s", "b", "v", "d") = 0.

When an individual value parameter is entered as a range, a match to an item in that range returns the position of the range in the list of values, not the position of the matching item in the range. (Ranges of constants and fields are distinguished by 2 periods between the first and last value in that range.) For example, MATCH(15, 1..10, 11..20, 21..30) = 2. Although the number 15 is the fifteenth number of all possible numbers being considered, and is the fifth item in the second value parameter, it is located in the second value parameter (11..20) of the list. Thus, a 2 is returned.

This function is often used with the TABLE function. The result of the match is used to determine what value should be returned from the list of items in the TABLE function. See the TABLE function for more information.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MATCH examples

  • To compare the General Ledger current period ending date to the period ending dates for the current fiscal year, use the following formula. The number of the current period in the fiscal year is returned.

MATCH(Period Ending Date, Period Ending Date 1..Period Ending Date 13)

  • MATCH can also be used to condition a report to print specific types of items. In the formula below, any Pay IDs which do not match return a 0 (zero) while those that match return 1, 2 or 3. Only those returning a number greater than zero print. This would eliminate from the report any Pay Ids that are not REG, OT or SICK.

MATCH(Pay ID, "REG", "OT", "SICK") > 0

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MATCH function

Use to compare an item to a list of items and return the position of the first match in the list.

Syntax

MATCH(value to match, value-1, value-2[,...])

Parameters

value to match—the value to locate in the list of values.

value-1—the first value to compare to the value to match.

value-2—the second value to compare to the value to match.

[,...]—remaining values to compare, if any.

Return

numeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MAX comments

The parameters can contain fields, constants, functions, other formulas, or arithmetic operations. The values must be numeric. If you want to calculate a maximum on a number in an alphanumeric field, use the SWITCH function to make the field numeric.

The function reads through a list of numbers and returns the highest value found. For example, MAX(-456, 22, -65, 0, 350) = 350.

Note: Keep in mind that negative values are always less than any positive amount. Also, you can specify a range of fields in a record as long as the range is contiguous and is listed by the first and last fields, separated by two periods.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MAX examples

  • To compare the values in the General Ledger activity fields for the current period and for each of the last 11 periods and return the highest monthly activity over the past 12 months for each record, use this formula:

MAX(Current Period Activity..Prior 11 Activity)

  • To compare the values for each vendor's year-to-date, last and next year amounts in Accounts Payable and return the highest yearly activity for each vendor, use this formula:

MAX(YTD Amount, Last Yr Amount, Next Yr Amount)

  • To compare the values of the cost code totals for each cost type and return the total with the highest JTD costs amount, use this formula:

MAX(JTD Labor Cost, JTD Material Cost, JTD Commitment, JTD Equipment Cost, JTD Overhead Cost, JTD Other Cost)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MAX function

Use to return the highest number in a list of numbers.

Syntax

MAX(number1, number2[,...])

Parameters

number1—the first number in the list of numbers to be compared.

number2—the second number in the list of numbers to be compared.

[,...]—remaining numbers in the list, if any.

Return

numeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MIN comments

The parameters can contain fields, constants, functions, other formulas or arithmetic operations. The values must be numeric. If you want to calculate a minimum on a number in an alphanumeric field, use the SWITCH function to make the field numeric.

The function reads through a list of numbers and returns the lowest value encountered. For example, MIN(456, 22, -65, 0, 350) = -65

Note: Keep in mind that negative values are always less than any positive amount. Also, you can specify a range of fields in a record as long as the range is contiguous and is listed by the first and last fields, separated by two periods.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MIN examples

  • To compare the values in the General Ledger activity fields for the current period and for each of the last 11 periods and return the lowest monthly activity over the past 12 months for each record, use this formula:

MIN(Current Period Activity..Prior 11 Activity)

  • To compare the values for each vendor's year-to-date, last and next year amounts in Accounts Payable and return the lowest yearly activity for each vendor, use this formula:

MIN(YTD Amount, Last Yr Amount, Next Yr Amount)

  • To compare the values of the cost code totals for each cost type and return the total with the lowest JTD costs amount, use this formula:

MIN(JTD Labor Cost, JTD Material Cost, JTD Commitment, JTD Equipment Cost, JTD Overhead Cost, JTD Other Cost)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MIN function

Use to return the lowest number in a list of numbers.

Syntax

MIN(number1, number2[,...])

Parameters

number1—the first number in the list of numbers to be compared.

number2—the second number in the list of numbers to be compared.

[,...]—remaining numbers in the list, if any.

Return

numeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MONTH comments

This function is equal to PART(date, 7, 2).

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


MONTH function

Use to return the month from a date.

Syntax

MONTH(date)

Parameters

date—the date containing the month to be returned.

Return

numeric

Comments

Date parameters

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


NEWDATE comments

  • The date parameter can contain either a date field, a date returned from a formula, or a numeric constant. The months, days and years parameters must be numeric. For example:

NEWDATE(100598, 1, 1, 1) = 11-06-99

NEWDATE(32698) = 3-26-98

  • You can enter four to eight digits as a numeric constant in the date parameter. The months, days and years parameters are optional. In this example, 30 days are added to 3-12-98.

NEWDATE(31298, , 30) = 4-11-98

  • You do not have to use the NEWDATE function to add days to a date. You can enter the date as a formula. For example, Invoice Date + 15.
  • You can also use NEWDATE to create a date by entering 0 in the date parameter. The months, days, and years parameters are required and must be greater than 0. For example:

NEWDATE(0, 11, 16, 2000) = 11-16-00

  • You can return the last day of a new month by adding or subtracting the number of months to the original month-end. For example:

NEWDATE(05312000, 1, 0, 0) = 06-30-00 (Adds one month to the end of May.)

NEWDATE(02292000, -2, 0, 0) = 12-31-99 (Subtracts two months from the end of February.)

Caution:Adding months and/or years to the last day of any month always returns the last day of the newly calculated month. For example, NEWDATE(02282001, 6, 0, 0) returns 08312001, not 08282001.

  • To convert a date field with a set format of month, year (MM-YYYY) to a date that is in month, day, year (MM-DD-YY) format, enter a number, other than 0, in the day parameter. For example, consider a custom field named "due date" with a set format of MMYYYY. If the field contained 122000 (December, 2000) and you wanted to display it as 12/15/00, use the following function:

NEWDATE(due date, 0, 15 , 0 ) = 121500 (assuming a change from MMYYYY to MMDDYY format.)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


NEWDATE examples

  • To create a date in which only the year varied, use the following formula. In this example, the prompt field Calendar Year is being used to control the contents of a report.

NEWDATE(0, 01, 01, Calendar Year)

  • If the contents of DATE in the following formulas is July 1, 1998 (70198), these values are returned.

NEWDATE(DATE, 1, 1, 1) = 8-02-99

NEWDATE(DATE, 0, 30) = 7-31-98

NEWDATE(DATE, -3) = 4-01-98

NEWDATE(DATE, 0, 0, -1) = 7-01-97

  • To calculate the date two months previous to the date in the Accounts Payable Invoice Date field, use this formula:

NEWDATE (Invoice Date, -2)

  • To add ten years to the date in the Invoice Date field, use the following formula. Two extra commas must be inserted to indicate that the months and days parameters are omitted.

NEWDATE(Invoice Date, , , 10)

  • To condition a report to print transactions with an accounting date greater than one month prior to the General Ledger current period ending date, use this formula:

Accounting Date > NEWDATE(Period Ending Date, -1)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


NEWDATE function

Use to calculate a new date or return a numeric constant in a date format. Also, use to create a date.

Syntax

NEWDATE(date[, months][, days][, years])

Parameters

date—the numeric constant or the date from which the new date is calculated. Enter 0 to indicate that you want to create a date.

[, months]—the number of months, if any, to be added to the date. If the date parameter is 0, this is the month portion used to create the date.

[, days]— the number of days, if any, to be added to the date. If the date parameter is 0, this is the day portion used to create the date.

[, years]—the number of years, if any, to be added to the date. If the date parameter is 0, this is the year portion used to create the date.

Return

date

Comments

Date parameters

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


NZAVG comments

The parameters can contain fields, constants, functions, other formulas and arithmetic operations. The values must be numeric.

The average of a list of non-zero values is the sum of the values divided by the number of non-zero values in the list. For example, NZAVG(100, 150, 0, 350) = 100+150+350 or 600 divided by 3 non-zero values equals 200.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


NZAVG examples

  • To average the non-zero values in the General Ledger activity fields for the current period and for each of the last 11 periods, use this formula:

NZAVG(Current Period Activity..Prior 11 Activity)

  • To average the non-zero Accounts Payable vendor payments this year and last year, use the following formula.

NZAVG(YTD Paid, Last Yr Amt Paid)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


NZAVG function

Use to calculate the average of the non-zero numbers listed.

Syntax

NZAVG(number1, number2[,...])

Parameters

number1—the first number in the list of numbers to be averaged.

number2—the second number in the list of numbers to be averaged.

[,...]—remaining numbers in the list, if any.

Return

numeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Other formulas

Other available public formulas that you create can be included as part of another formula. This makes it easier to create a new formula because it eliminates the need to repeat all elements of a public formula in a new formula.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


PART comments

The value parameter can contain fields, constants, functions, other formulas or arithmetic operations. The position parameter must be a numeric value that is not in excess of the length of the value. For example, if a value contained a field of 24 characters, a starting position could contain any number from 1 through 24. Although the starting position and value parameters are typically numeric constants, they can be any expressions that provide valid, positive numeric values.

To return the day, month, or year from a date, you can use the DAY, MONTH, or YEAR functions instead of the PART function. These three functions simplify this operation. For example, to return the month from a Contract Date of 10-30-93, you can use either PART(Contract Date, 7, 2)=10 or MONTH(Contract Date)=10.

The length parameter must contain a numeric constant. Valid values for length begin at 1, but cannot exceed the number of positions in the value beyond the starting position. For example, PART("OREGON", 1, 2) = OR.

PART("OREGON", 2, 6) is not valid because it specifies more positions than the value contains beyond the starting position. The starting position is 'R' and there are only 5 characters available to the right to be returned.

For alpha values, the function counts the starting position and length parameters from left to right. All other types of values are counted from right to left. For example, PART("ABCDEFG", 5, 2) = EF, and PART(123456, 5, 2) = 12.

For numeric, date and time values, punctuation is not included in the position or length count, nor is it returned. If the original value was negative, however, the return is also negative.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


PART examples

The following table lists various types of values and the portion of the values returned by the PART function. The last column shows the original full contents of the value the formula worked upon.

Type | Example | Original Value
Alpha | PART(Vendor Name, 1, 5) = Murph | Murphy Hardware
Numeric | PART(JTD Cost, 3, 4) = 1763 | 176354
Date | PART(NEWDATE(Actual Start Date, -2), 7, 2) = 10 | 123099
Time | PART(Time IN, 3, 2) = 1 | 120100
Telephone | PART(Site Phone #, 6, 3) = 555 | (503)555-4092

Notes:

  • In the Date example above, the original value of Actual Start Date was 123099. The NEWDATE function subtracted two months from the date before the PART function returned the month portion. This is an example of a function being used in another function.
  • Before the function begins its operation on a date, the year is expanded to four digits to include the century. The full date passed by the NEWDATE function to the PART function was 10301999. Therefore, the starting position in the example must be 7 rather than 5.
  • Keep in mind that for alpha value types, the counting is from left-to-right, and for all other types, the counting is right-to-left.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


PART function

Use to return a portion of a value.

Syntax

PART(value, starting position, length)

Parameters

value—the value containing the portion to be returned.

starting position—the starting position in value of the portion to be returned.

length—the number of positions to include in the return, including the starting position.

Return

alphanumeric if value is alphanumeric, numeric for all others.

Related functions

DAY, MONTH, and YEAR

Comments

More examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


PRCNT comments

The parameters can contain fields, constants, functions, other formulas or arithmetic operations. The values must be numeric.

This function is equivalent to the expression number1 / number2 * 100. The percentage is rounded to a hundredth of one percent.

Note: This function includes multiplying by 100; it should not be used to replace division for an expression. For example, ½ = .50, but PRCNT(1, 2) = 50.00.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


PRCNT examples

  • The value in the General Ledger Current Period Activity field is divided by the value in the Current Balance field and multiplied by 100 to return the current percentage of the year-to-date account balance.

PRCNT(Current Period Activity, Current Balance)

  • The value in Commitments invoiced is divided by the value in Total invoiced and multiplied by 100 to return the current percentage of commitments invoiced for an Accounts Payable vendor.

PRCNT(Commitments Invoiced, Total Invoiced)

  • The Payroll values in Vacation and Sick pay are added and divided by Gross pay and then multiplied by 100. This returns the percentage of gross pay paid in vacation and sick pay.

PRCNT((Vacation Pay + Sick Pay), Gross Pay)

  • In Job Cost, the value in Job to Date cost is divided by the value in Total estimate to calculate the percent complete based on actual cost.

PRCNT(JTD Cost, Total Estimate)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


PRCNT function

Use to calculate and return a percentage as a whole number as if the percentage sign followed.

Syntax

PRCNT(number1, number2)

Parameters

number1-- the number to be divided, numerator.

number2 -- the number to divide by, denominator.

Return

numeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Public formulas

Public formulas are created from scratch by you, or by copying an application formula to a public formula. Public formulas are accessible whenever formulas can be used.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Quick formulas

Quick formulas are used for only one item in a report design in Report Designer. Quick formulas are easily set up, have no name, and are used only once in the report design at the location where they were created. Quick formulas cannot be used by other locations in the same report design. They cannot be used within other design formulas.

Quick formulas are created by selecting Quick Formula from the Tools menu in Report Designer.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


RAND comments

No parameters are inserted in the parentheses for the RAND function. A whole number between 1 and 32,767 is returned, e.g., RAND( ) = 471; RAND( ) = 30. The function chooses a number randomly each time it is run.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


RAND example

A random number is calculated and returned. This is the only valid use of the RAND function.

RAND( )

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


RAND function

Use to generate and return a random number.

Syntax

RAND( )

Parameters

none

Return

numeric

Comments

Example

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ROUND comments

The number parameter can contain fields, constants, functions, other formulas or arithmetic operations. The values must be numeric. The digits parameter must be a number from -15 through 15.

Positive digits parameter

If the number in the digits parameter is positive and less than the number of decimal positions, it counts from the decimal point right, dropping ending decimal positions beyond the number specified. The last decimal retained is rounded up or down, e.g., ROUND(538.327, 2) = 538.33 and ROUND(5.324, 1) = 5.3.

If the number in the digits parameter is positive and is greater than the number of decimal positions, trailing zeros are added. For example, ROUND(538.327, 5) = 538.32700.

Negative digits parameter

If the number in the digits parameter is negative (-), it counts from the decimal point left and decimal positions are set to zero. The right-most digit that is not set to zero is rounded up or down, e.g., ROUND(4009.827, -2) = 4000.000, ROUND(538.327, -2) = 500.000, and ROUND(388.327, -1) = 390.000.

If the value of digits is greater than the number of digits to the left of the decimal, zero is returned,. For example, ROUND(538.327, -4) = 0.

If the value of digits is equal to the number of digits to the left of the decimal, the number is rounded up if the left-most digit is five or greater. Zero is returned if the left-most digit is four or less. For example, ROUND(539.327, -3) = 1000, but ROUND(439.327, -3) = 0.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ROUND examples

  • The value in the YTD Amount field is multiplied by the Discount Percent (.xxx). The result is rounded to two decimal positions and returned.

ROUND(YTD Amount * Discount Percent, 2)

  • The number in the YTD Amount field, (representing dollars and cents) is rounded so only whole dollars are returned.

ROUND(YTD Amount, 0)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


ROUND function

Use to remove or add decimal positions or to round numbers upward or downward to a specified multiple of ten.

Syntax

ROUND(number, digits)

Parameters

number—the number to be rounded.

digits—the decimal positions to round.

Return

numeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


SQRT comments

The number parameter can contain fields, constants, functions, other formulas or arithmetic operations. The values must be numeric.

If number is a negative value, zero is returned. For example, SQRT(36) = 6; SQRT(-36) = 0

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


SQRT examples

SQRT example

To calculate the square root of the Job Size field, use this formula:

SQRT (Size)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


SQRT function

Use to calculate the square root of a number.

Syntax

SQRT(number)

Parameters

number—the number used in the square root calculation.

Return

numeric

Comments

Example

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


STRMATCH comments

  • Every alpha character in string1 must match every corresponding character in string2 with regard to capitalization or the function returns "false".
  • Both strings must be the exact same length, including leading and trailing blanks, or the function returns "false". For example, "Johnson Enterprises" will not match "Johnson Enterprises ". (String2 has trailing spaces.) You can use the PART function as one of the strings to compare a part of a field with a string shorter than the field.
  • Digits 0-9 always compare as equal respectively.
  • Because most condition tests on alpha fields are case-insensitive (capitalization is ignored), you may have need to use this function to eliminate records or fields whose alpha characters are the same as those you are looking for but are the wrong case. For example, you may have coded some descriptive fields in a series of records with "ABC" and others with "abc". A comparison condition in an inquiry looking for "abc" would also display the "ABC" records. By using the STRMATCH function in the condition, looking for "abc" only, you will prevent the "ABC" records from displaying.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


STRMATCH example

In a Job Cost inquiry, if the first character of the Unit Description is a capital "Y", the inquiry column is to display the square root of the Size field. Otherwise, it should display the Size as is. The formula is:

IF(STRMATCH((PART(Unit Description, 1, 1)), "Y",), SQRT(Size), Size)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


STRMATCH function

Use to compare the capitalization (upper-case vs. lower-case) of two parameters.

Syntax

STRMATCH(string1, string2)

Parameters

string1—the first string of characters to be compared.

string2—the second string of characters to be compared.

Return

check box mark ("X" for true, or " " for false)

Comments

Example

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


SUM comments

The parameters can contain fields, constants, functions, other formulas or arithmetic operations. The values must be numeric.

The SUM function reads through a list of numbers and returns the total of the numbers:

SUM(-456, 22, -65, 0, 350) = -149

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


SUM examples

  • To total the values in the General Ledger Budget fields for the first three periods of the year and return a total for every account, use this formula:

SUM(Period 1 Budget..Period 3 Budget)

  • To total the values for the Payroll regular hours and overtime hours on each check and return total hours for each check, use this formula:

SUM(Regular Hours, Overtime Hours)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


SUM function

Use to add a list of numbers and return the total.

Syntax

SUM(number1, number2[,...])

Parameters

number1—the first number in the list of numbers to be added.

number2—the second number in the list of numbers to be added.

[,...]—remaining numbers in the list, if any.

Return

numeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


SWITCH comments

The number can contain fields, constants, functions, other formulas or arithmetic operations. The values can be alphanumeric or numeric.

Using this function automatically justifies to the defaults for the field type. Numeric fields are right justified and alphanumeric fields are left justified.

If the value is alphanumeric, the conversion begins with the left-most digit found and continues to the right. The conversion ends when one of the following is found:

  • a character that is not a number, comma, decimal point or minus sign
  • a second decimal point
  • a minus sign following numbers already converted

If a minus sign immediately precedes or follows the alphanumeric number, the return is negative. If there are no numeric values to convert, a zero is returned.

The decimal parameter is optional and can be used only when converting alphanumeric values to a numeric format. It indicates the number of decimal positions to be displayed in the return. If you do not enter a decimal parameter, the converted number contains no decimal positions, and numbers following a decimal point in the original value are truncated with no rounding. If there are fewer decimal positions in value than specified by decimal, trailing zeros are added in the return. If there are more decimal positions in value than specified by decimal, the excess positions are truncated in return with no rounding.

Warning When suffixes are used in your General Ledger account format, the SWITCH function ignores the automatic decimal between the base and the suffix. When the account number is switched, the function returns the base plus the suffix as one number with no decimal.

Examples of numbers returned for alphanumeric values

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


SWITCH examples

  • The value in the Accounts Payable Vendor Tax Group is returned as a number.

SWITCH (Vendor Tax Group)

  • The value in the General Ledger Account ID is returned as a number with right justification. If suffixes are used in the account format, the decimal does not appear in the return of this function. An Account ID of 1001.00 returns as 100100, right justified with no decimal.

SWITCH(ACCOUNT)

  • This example uses the FORMAT function with the SWITCH function. Because the FORMAT function requires all items to be numeric, you must include the SWITCH function to FORMAT an alphanumeric field. In this example, Job is an alphanumeric field from the Job record.

FORMAT(SWITCH(Job), "99/9999") = 98/001 for Job 98-001.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


SWITCH - examples of numbers returned for alphanumeric values

Decimal | Alphanumeric value | Number returned
0 | "1234" | 1234
0 | "model 334" | 334
2 | "-12474" | 12474.00-
0 | "KL 345-X" | 345-
0 | "12.34" | 12
2 | "1234" | 1234.00
2 | "12.345" | 12.34

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


SWITCH function

Use to convert alphanumeric values to numeric and numeric values to alphanumeric.

Syntax

SWITCH(value[,decimal])

Parameters

value—the value to be switched.

[,decimal]—the number of decimal positions to be assigned, if any, when switching to numeric values from alphanumeric.

Return

alphanumeric or numeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


TABLE - Accounts Payable example

In Accounts Payable the hold status for each invoice is stored. When printing a report, the hold status of an invoice prints as a number from the level the hold status is stored (invoice or invoice distribution). To print an alphanumeric description for the hold status, you can use the TABLE function. The function assigns an alpha description to each number. The description that is printed for each numeric hold status is shown in the following table.

Numeric Hold status | Hold description (Alpha)
1 | Vendor on hold
2 | Invoice on hold
3 | Vendor & Invoice on hold
4 | Distribution on hold
5 | Vendor & distribution on hold
6 | Invoice & distribution on hold
7 | Vendor, invoice & distribution on hold

The following formula would print an alpha description for the hold status of invoices (codes 1, 2 and 3) in the table above.

TABLE(On hold status, "Vendor on hold", "Invoice on hold", "Vendor and Invoice on hold")

When the function searches the data, it will print 'Vendor on hold' when it finds a hold status of 1, 'Invoice on hold' when it finds a hold status of 2 and 'Vendor and Invoice on hold' when it finds a hold status of 3.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


TABLE comments

The parameters can contain fields, constants, functions, other formulas or arithmetic operations. The value in the position parameter must be numeric. The values in the list of values parameter can be alphanumeric or numeric, but if you mix types, the return value will be alphanumeric. Each item in the list is internally assigned a number in sequential order, beginning with the first item.

The TABLE function returns the value from a list of values according to the position it occupies in the list. For example, TABLE(4, 723, 632, 63, 1463, 9373) = 1463. 1463 is the fourth item after the position parameter.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


TABLE examples

Accounts Payable example

Payroll example

About using TABLE with the MATCH function

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


TABLE function

Use to return the value that occupies a specific position in a list.

Syntax

TABLE (position, list of values)

Parameters

position—the position of the value to return from the list of values.

list of values—the list of values assigned as items for the table.

Return

alphanumeric or numeric

Comments

More examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


TABLE - Payroll example

Payroll checks store the pay periods. To print a report, with an alpha description for each pay period, you could use the TABLE and MATCH functions.

You first need to create a formula that produces an index number for each pay period type. There can be 52, 26, 24, 12, 2, and 1 pay periods each year.

MATCH(Pay periods, 52, 26, 24, 12, 2, 1)

In the MATCH formula above, when the Pay period type equals 52 a '1' is returned, when it equals 26 a '2' is returned, when it equals 24, a '3' is returned, etc. You could name this formula "PerYr".

The next step is to use the TABLE function to return an alpha description for each of the values returned from the above MATCH formula you named "PerYr".

Original value returned | Value from MATCH | Pay period description (alpha)
52 | 1 | Weekly
26 | 2 | Bi-weekly
24 | 3 | Semi-monthly
12 | 4 | Monthly
2 | 5 | Semi-annually
1 | 6 | Annually

You can use the TABLE formula to print the alpha description for each employee's time on a report:

TABLE(PerYr, 'Weekly', 'Bi-weekly', 'Semi-monthly', 'Monthly', 'Semi-annually', 'Annually')

In this formula, when PerYr returns 1, 'Weekly' is returned; when PerYr returns 2, 'Bi-weekly' is returned, etc. With the following data, the description prints as:

Emp | Pay Periods | Alpha Description
101 | 52 | Weekly
103 | 52 | Weekly
1001 | 26 | Bi-weekly
1002 | 26 | Bi-weekly
2001 | 24 | Semi-monthly
3001 | 12 | Monthly

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Function terminology

The terms syntax, parameter, return and expression are used when creating functions.

Syntax—the format in which the function must be entered. The order of the elements and the punctuation that are used determines how the information entered is evaluated.

Parameter—one of the variables in the function. Each parameter must be entered as shown for the function. For example, the syntax of the rounding function is: ROUND(number, digits). In this example, number and digits are the parameters for the function ROUND.

Return—the result of the function. The function is performed and the result is returned. Because the result of a function is not always numeric, the return type is important when determining whether a function is appropriate for the operation needed.

Expression—a mathematical term that is defined as one or more symbols representing a value or relationship. For example, "JTD Cost - YTD Cost" is an expression that represents the to-date cost amount at the beginning of the year.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


TIMEDIFF comments

This function is useful if you ever need to measure elapsed time. The time returned is the elapsed hours, minutes and seconds. All four parameters are required.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


TIMEDIFF function

Use to calculate the time difference between the start date/start time and end date/end time.

Syntax

TIMEDIFF(start date, start time, end date, end time)

Parameters

start date—the starting date to begin tracking elapsed time for.

start time—the starting time to begin tracking elapsed time for.

end date—the ending date to stop tracking elapsed time for.

end time—the ending time to stop tracking elapsed time for.

Return

a time

Comments

Date parameters

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


TRUNC comments

The number parameter can contain fields, constants, functions, other formulas or arithmetic operations. The value to be truncated must be numeric. The digits parameter must be a number between -15 and 15, inclusive.

If the number in the digits parameter is positive, the function drops ending decimal positions beyond the number specified. If the number in digits is greater than the number of decimal positions in the number, trailing zeros are added.

If the number in the digits parameter is negative, that number of digits immediately preceding the decimal and any decimal positions are set to zero, e.g., TRUNC(538.327, -2) = 500.000. If the value of digits is greater than or equal to the number of digits to the left of the decimal, zero is returned.

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


TRUNC examples

  • The value in the YTD Amount field is multiplied by the Discount Percent. The result is truncated to two decimal positions and returned.

TRUNC(YTD Amount * Discount Percent, 2)

  • The number in the YTD Amount field (representing dollars and cents) is truncated so that the whole dollars are returned.

TRUNC(YTD Amount, 0)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


TRUNC function

Use to remove or add decimal positions or round numbers downward to a specified multiple of ten.

Syntax

TRUNC(number, digits)

Parameters

number—the number to be truncated.

digits—indicates the decimal positions to return or the whole numbers and any following decimals to be set to zero.

Return

numeric

Comments

Examples

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


Using AND/OR in formulas

Formulas can be used as conditions to determine whether a record should be included in an inquiry or a report. These conditions must always start with the IF function. There may be instances when more than one condition is required to make this determination. In these cases, each condition must be separated from the next by using the AND or OR operator. Using AND between conditions requires both conditions to be met. Using OR requires only one of the two conditions to be met.

When you use more than two conditions, the order of the conditions is crucial. To make the conditions easier to construct and understand, We recommend the use of parentheses to group the conditions.

For example, "IF condition1 AND condition2 OR condition3 AND condition4..." is a string of conditions in which it is not readily apparent how the individual conditions relate to each other. By using parentheses, you can create a more logical and understandable testing combination. For example, "IF (condition1 AND condition2) OR (condition3 AND condition4)..." means that if conditions 1 and 2 are met, OR if conditions 3 and 4 are met, the record is accepted. Formula components inside parentheses are always interpreted before those that are outside parentheses.

Refer to the Evaluating multiple AND/OR conditions topic for detailed steps on how multiple conditions are interpreted.

Creating formulas

About functions (definition)

List of functions

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


View a list of application formulas

To view a list of application formulas, follow these steps:

  1. Open an application that uses application formulas.
  2. If you opened Report Designer or Inquiry Designer in step 1, open a report or inquiry for a specific application.
  3. Select Tools > Formulas.
  4. In the Formulas window, click [Index].
  5. In the Index window, select the application formulas that you want to view, then click [OK].

Learn about application formulas

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


YEAR comments

This function is equal to PART(date, 1, 4). To return only the last two digits of the year, use:

PART(Last Inv Date), 1, 2)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


YEAR example

To return the year of the last invoice date for an Accounts Payable vendor, use the following formula:

YEAR(Last Inv Date)

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.


YEAR function

Use to return the year from a date.

Syntax

YEAR(date)

Parameters

date—the date containing the year to be returned.

Return

numeric

Comments

Example

Date parameters

Help us improve our user assistance.

Was this topic helpful to you?

Sending...
Your comments have been sent. Thank you!
Comment not sent -- Please check your internet connection and try again.
A server error has occurred -- Please try again later.