• Left
• Right
• Mid
• If

• Ceiling
• Concat
• Contains
• Count
• CountIf
• CountUnique
• Cstring
• DateTimeToFormattedString
• DayfromDate
• EndsWith
• Floor
• If
• Indexof

• Length
• Max
• Min
• MonthfromDate
• Now
• PadLeft
• PadRight
• ParseDateTime
• Previous
• Remove
• Replace
• RoundDP
• StartsWith

• StringToFloat
• StringToInteger
• Substring
• SumIf
• ToLower
• ToUpper
• Trim
• TrimEnd
• TrimStart
• Truncate
• YearfromDate

This function does not exist

Workaround:

The following function returns the same thing as a left(SALES_LEDGER.ACCOUNT_REF,X) function would

substring(SALES_LEDGER.ACCOUNT_REF,0,length(SALES_LEDGER.ACCOUNT_REF)-X)

This function does not exist

Workaround:

The following function returns the same thing as a right(SALES_LEDGER.ACCOUNT_REF,X) function would

substring(SALES_LEDGER.ACCOUNT_REF,length(SALES_LEDGER.ACCOUNT_REF)-X)

This function does not exist

Workaround:

The substring function returns the same thing.  mid(SALES_LEDGER.ACCOUNT_REF,X,Y) function would be

substring(SALES_LEDGER.ACCOUNT_REF,X,Y)

Example:

The following function returns a customer account balance of £20.11 as £21.00:

Ceiling(SALES_LEDGER.BALANCE)

This is almost the equivalent of Floor(SALES_LEDGER.BALANCE)+1 but not quite... can you see the difference?

Example:

• To combine the word Tel: and the customer's telephone number into one field, making it easier to align and move, use the following function:
  
  Concat("Tel: ",SALES_LEDGER.TELEPHONE)
  
• You can also use Concat function to get the currency symbol to print directly in front of a numeric value when using foreign trader. For example, to get the currency symbol to print directly before the INVOICE.FOREIGN_INVOICE_GROSS, use the following function:
  
  Concat(CURRENCY.SYMBOL, FormatString("{0:N2}",(INVOICE.FOREIGN_INVOICE_GROSS)))
  
  Note: The properties of the FormatString expression must be set to signed. If not, when running the layout, the following message appears: 'Sage.expressions.DefaultLibraries.Accounts.Unsigned(system.string) is not defined.'
  
• I don't use this expresion much, probably because you can achieve the same thing by appending strings to each other with the + operand eg CURRENCY.SYMBOL+FormatString("{0:N2}",(INVOICE.FOREIGN_INVOICE_GROSS)) which to my mind reads much more easily.

• If the statement is true it shows a value of 1, if it's false it shows 0.
  
• This function is case sensitive.

Example:

• If a customer's fifth address line contains the uppercase letters NE, the following function returns 1 (TRUE). else it returns 0 (FALSE)
  
  Contains(SALES_LEDGER.ADDRESS_5, "NE")
  


• If the customer's fifth address line contains the uppercase letters NE, the following function returns the line. If not, it returns an empty string:
  
  Contains(SALES_LEDGER.ADDRESS_5, "NE") ? (SALES_LEDGER.ADDRESS_5) : ""

• If added to a details section it shows a running count.
• If added to a footer section it shows the total count for the group or the report.

Example:

To count the number of customer records on a report:

Count(SALES_LEDGER.ACCOUNT_REF )

• If added to a details section it shows a running count.
• If added to a footer section it shows the total count for the group or the report.
• This function is not case sensitive.

Example:

To count the number of customer records on a report that contain an A in the account reference:

CountIf(SALES_LEDGER.ACCOUNT_REF Like "%A%" )

Example:

On the Product Profit by Customer - Itemised report, to show the number of unique products purchased by a customer:

CountUnique(INVOICE_ITEM.STOCK_CODE)

You can use this in Email Options > Subject to show a numeric value, as numeric variables in the email subject line generate the message:

'You must enter an expression that returns a system string value.'

Examples:

• To convert an invoice number to an alphanumeric string:
  
  CString(INVOICE.INVOICE_NUMBER)
  


• To show the text Sales Invoice and the invoice number on an email subject line:
  
  "Sales Invoice" + " " + CString(INVOICE.INVOICE_NUMBER)

• You can use this for grouping reports by date.
• You can use this in conjunction with If statements on column based reports.
• Using this function, you can separate the date values with various characters, for example / or -.
• A common use of this function in Sage Accounts is to show dates in the subject lines of emails.

Examples:

• To show an invoice date in the format dd/MM/yyyy:
  
  DateTimeToFormattedString(INVOICE.INVOICE_DATE, "dd/MM/yyyy")
  


• To show only the month part of an invoice date, for example, 01, 02, 03 and so on:
  
  DateTimeToFormattedString(INVOICE.INVOICE_DATE, "MM")
  
• To show the month and year of an invoice date, for example, September 2011:
  
  DateTimeToFormattedString(INVOICE.INVOICE_DATE, "MMMM yyyy")
  


• To show the company name and invoice date on an email subject line:
  
  COMPANY.NAME + " " + DateTimeToFormattedString(INVOICE.INVOICE_DATE, "dd-MM-yyyy")

Example:

If a customer's account was opened on 14/02/2001, the following function shows 14:

DayFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED)

I have used this extensively in statement layouts to show conditional text depending on the ages of the debt (and to calculate the days overdue)

Displays whether or not a string ends with a specified character.

• If the statement is true it shows a value of 1, if it's false it shows 0.
  
• You can use this with If statements.
  
• This function is case sensitive.
  

Examples:

• If the customer's account reference ends with R, the following function displays 1. If not, it displays 0:
  
  EndsWith(SALES_LEDGER.ACCOUNT_REF, "R")
  


• If the customer's account reference ends with R, the following function displays the account reference. If not, it displays nothing:
  
  EndsWith(SALES_LEDGER.ACCOUNT_REF, "R") ?(SALES_LEDGER.ACCOUNT_REF) : ""

Examples:

• The following function displays a customer account balance of £75.85 as £75.00:
  
  Floor(SALES_LEDGER.BALANCE)
  


• The following function displays the pounds element of an invoice total:
  
  Floor(unsigned(INVOICE.FOREIGN_INVOICE_GROSS))
  
  Tip: To display the pence element, the following expression subtracts the Floor value from the original value:
  
  INVOICE.FOREIGN_INVOICE_GROSS - Floor(unsigned(INVOICE.FOREIGN_INVOICE_GROSS))

• This can be used in conjunction with other functions.
• You can nest If statements to build up more complicated arguments.
• The ? command is equivalent to Then.
• The : command is equivalent to Else.

This function is badly documented: The function does exist and is used in conditional formating espressions, however for normal if statements you need to use the form

<Expression>?<expression to use if true>:<expression to use if false>

The IF is implied, the ? is THEN and : is ELSE 

Example:

• When viewing prior year values, sales nominal codes show negative values. The following function states that if the nominal code is within the sales nominal code 4000, then reverse the signing. Otherwise display the value as normal:
  
  NOMINAL_LEDGER.ACCOUNT_REF IN ("4000") ? Reversed (NOMINAL_LEDGER.PRIOR_YR_MTH1) : NOMINAL_LEDGER.PRIOR_YR_MTH1
  


• The following nested If statements calculate the VAT element of a product sales price, based on the product record tax code:
  
  STOCK.TAX_CODE Like "T1" ? STOCK.SALES_PRICE * 0.175 : STOCK.TAX_CODE Like "T2" ? 0 : STOCK.TAX_CODE Like "T3" ? STOCK.SALES_PRICE * 0.05 : 0

For further information about constructing If statement expressions, please refer to article 17511.

Note: that in this explanation of the function, they do not use the function!

Examples:

• To find the position of the first space in a customer's contact name:
  
  IndexOf(SALES_LEDGER.CONTACT_NAME," ") - returns the position of the first space


• You can then combine this function with the substring function to return the first name held in the SALES_LEDGER.CONTACT_NAME as follows:
  
  Substring(SALES_LEDGER.CONTACT_NAME,0,IndexOf(SALES_LEDGER.CONTACT_NAME," "))
  
• Alternatively, to show only the surname of a customer's contact name:
  
  Substring(SALES_LEDGER.CONTACT_NAME, (IndexOf(SALES_LEDGER.CONTACT_NAME," ")))

Example:

To display how many characters appear in a customer's account reference:

Length(SALES_LEDGER.ACCOUNT_REF)

• If added to the details section it shows the maximum value at that point on the report.
• If added to a footer it shows the maximum value for the group or the report.

Example:

To show the highest net amount on a transaction report:

Max(AUDIT_HEADER.NET_AMOUNT)

• If added to the details section it shows the minimum value at that point on the report.
• If added to a footer it shows the minimum value for the group or the report.

Example:

To show the lowest net amount on a transaction report:

Min(AUDIT_HEADER.NET_AMOUNT)

Example:

If a customer's account was opened on 14/02/2001, the following function shows 2:

MonthFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED)

• As it is not possible to use the REPORT.DATE variable in an expression, the Now() function can be used instead.
• Shows the PC date and not the Sage software date.

Example:

• To show the PC date on a report:
  
  Now( )
  


• To show a date 30 days from now:

  Now( ) + 30

To ensure a field contains a certain number of characters, this function inserts additional characters to the left of the value. The function is written as:

PadLeft(the value to pad, the character used to pad the field, the number of characters the field should contain)

Example:

• On an invoice layout, to always display the invoice number as a 6 digit number, use the following function:
  
  PadLeft(CString(INVOICE.INVOICE_NUMBER),"0",6)
  
  Using the above function, an invoice number of 1 appears as 000001 and an invoice number of 123 appears as 000123.
  


• On a product report, to always show stock codes with 10 characters, use the following function:
  
  PadLeft(STOCK.STOCK_CODE,"0",10)

To ensure a field contains a certain number of characters, this function inserts additional characters to the right of the value. The function is written as:

PadRight(the value to pad, the character used to pad the field, the number of characters the field should contain)

Example:

• On a product report, to always show stock codes with 10 characters, use the following function:
  
  PadRight(STOCK.STOCK_CODE,"0",10)

For each record the relevant database value must contain a date and must be in a recognised date format. If not, when running the report, the following message appears:

'String was not recognised as a valid DateTime.'

Example:

To convert the first line within Invoice List > New /Edit > Order Details > Notes to a date field in a report:

ParseDateTime(INVOICE.NOTES_1)

Example:

On a report showing each invoice item line and displaying the invoice number, the invoice number repeats for each of its item lines. To show an asterisk against the first item line of each invoice reference:

AUDIT_SPLIT.INV_REF = Previous(AUDIT_SPLIT.INV_REF) ? AUDIT_SPLIT.INV_REF : AUDIT_SPLIT.INV_REF + " *"

Examples:

• Where a customer's account reference is ABC123, the following function removes the first 3 characters and displays 123:
  
  Remove(SALES_LEDGER.ACCOUNT_REF,0,3)
  


• Where a customer's account reference is ABC123, the following function removes the third and fourth characters and displays AB23:
  
  Remove(SALES_LEDGER.ACCOUNT_REF,2,2)

Examples:

• If the invoice order number is 1231, the following function displays the order number as A23A:
  
  Replace(INVOICE.ORDER_NUMBER, "1", "A")
  


• If the transaction paid flag is Y, the following function displays the value as Yes:
  
  Replace(AUDIT_HEADER.PAID_FLAG, "Y", "Yes")

Examples:

• To round a transaction net amount to zero decimal places:
  
  RoundDP(AUDIT_HEADER.NET_AMOUNT,0)
  


• To round a transaction net amount to two decimal places:
  
  RoundDP(AUDIT_HEADER.NET_AMOUNT,2)

• If the statement is true it shows a value of 1, if it's false it shows 0.
  
• You can use this with If statements.
  
• This function is case sensitive.

Examples:

• If the customer's account reference starts with R, the following function displays 1. If not, it displays 0:
  
  StartsWith(SALES_LEDGER.ACCOUNT_REF, "R")
  


• If the customer's account reference starts with R, the following function displays the account reference. If not, it displays nothing:
  
  StartsWith(SALES_LEDGER.ACCOUNT_REF, "R") ? (SALES_LEDGER.ACCOUNT_REF) : ""

If the report is run for fields containing non numeric information, the following message appears:

'Input string is not in correct format.'

Examples:

• To multiply the quantity in stock by a number entered in Product Record > Location:
  
  StringToFloat(STOCK.LOCATION) * STOCK.QTY_IN_STOCK
  


• To multiply values entered in the transaction references by 10:
  
  StringToFloat(AUDIT_HEADER.INV_REF) * 10

If you run the report for fields containing non numeric information, the following message appears:

'Input string is not in correct format.'

Examples:

• To multiply the quantity in stock by a number entered in Product Record > Location:
  
  StringToInteger(STOCK.LOCATION) * STOCK.QTY_IN_STOCK
  


• To multiply values entered in the transaction references by 10:
  
  StringToInteger(AUDIT_HEADER.INV_REF) * 10

Displays part of a string from a specified starting point.

The function assigns the first character in a value the number 0, each subsequent character is numbered incrementally. For example, in the text ABCDE, A is equal to 0, B is equal to 1, and so on.

• You can use this in conjunction with Cstring and StringToInteger to convert fields of one type to another.
• This function only works with alphanumeric values.
• If required, use the CString function to convert a numeric field to alphanumeric before performing the Substring function.
• You can use this function within sorts and expressions.

Examples:

• If an invoice order number is ABC123, the following function displays BC123:
  
  Substring(INVOICE.CUST_ORDER_NUMBER ,1)
  
  1 refers to the starting point in the string.
  


• If an invoice order number is ABC123, the following function displays BC1:
  
  Substring(INVOICE.CUST_ORDER_NUMBER,1,3)
  
  1 refers to the starting point in the string and 3 refers to the number of characters to display.
  


• You can use a combination of the StringToInteger( ) and Substring( ) functions to sort a report by tax code. The following function converts a tax code into a numeric value, for example, T1 converts to numeric 1:
  
  StringToInteger(Substring(AUDIT_SPLIT.TAX_CODE, 1))
  


• To calculate the nominal code name length and then display the last 4 characters:
  
  Substring(NOMINAL_LEDGER.NAME, Length(NOMINAL_LEDGER.NAME) - 4)
  
• You can use a combination of Substring( ) and Length( ) to ensure a value contains a certain number of characters. The following function adds zeros to the left of a purchase order number, ensuring that the number is always 5 digits long:
  
  Substring("00000" + PURCHASE_ORDER.ORDER_NUMBER, Length(cstring(PURCHASE_ORDER.ORDER_NUMBER)))
  
  To change the length of the number that appears, increase or decrease the number of zeros in the function.
  
  Tip: You can also achieve this using the PadLeft( ) function.
  
• The following function shows the first digit of a numeric order number:
  
  Substring(CString(SALES_ORDER.ORDER_NUMBER),1,1)
  

To total only the values that meet the condition specified. The function is written as:

SumIf(the value to sum, the condition that is to be met)

Example:

• On an aged debtors report, to display the total for sales invoice (SI) transactions only, use the following function:
  
  SumIf(AUDIT_HEADER.AGED_BALANCE, AUDIT_HEADER.TYPE Like "SI")

Example:

To convert a customer's account reference to lower case:

ToLower(SALES_LEDGER.ACCOUNT_REF)

Example:

When printing invoices or credit notes, to convert the words invoice or credit note to upper case:

ToUpper(INVOICE.INVOICE_OR_CREDIT)

Example:

If an invoice order number is 123 B 22, including the spaces, the following function displays the order number as 123B22:

Trim(INVOICE.ORDER_NUMBER)

Example:

If an order number is 1_2_3_, where _ represents a space, the following function displays the order number as 1_2_3:

TrimEnd(INVOICE.ORDER_NUMBER)

Example:

If an invoice order number is _1_2_3, where _ represents a space, the following function displays the order number as 1_2_3:

TrimStart(INVOICE.ORDER_NUMBER)

• In the case of negative values, the number rounds up towards zero. For example, -100.30 rounds up to -100.00.
  

Example:

• To round a commission value down to the nearest whole value, use the following function:
  
  Truncate(AUDIT_SPLIT.NET_AMOUNT * 0.05)

Example:

If a customer's account was opened on 14/02/2001, the following function shows 2001:

YearFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED)