Avg(variable)

General

Returns the average of a series of values.

You can set this within Properties > Function, but you can also use it as a function in expressions.

Example

• To show the average of all customer account balances on a report:

  Avg(SALES_LEDGER.BALANCE)

Ceiling(variable)

Value

Rounds up to the next whole number.

Examples:

• To round the customer balance up to the next whole number:

  Ceiling(SALES_LEDGER.BALANCE) 

  A balance of 500.59 returns 501 and a balance of 500.01 also returns 501. A negative balance of -500.59 returns -500.

• Use this function within calculations. For example, a pallet holds 12 items, divide the item quantity by 12 to find out the number of pallets are needed to contain a sales order quantity. Then, use the ceiling function to round up the result. 

          Ceiling(SOP_ITEM.QUANTITY/12)

This shows the number of pallets needed.

Concat(variable1, variable2)

String

To combine two or more fields into one value.

Combine text or variables or a combination of the two.

Example:

• To show the text and variable together rather than worrying about spacing: 

  Concat("Tel:",SALES_LEDGER.TELEPHONE) 

  or 

  Concat("Dept: ",SALES_LEDGER.DEPT_NUMBER," - ",SALES_LEDGER.DEPT_NAME)

• 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)))

  Set the properties of this expression to signed. If not, when running the layout, the following message appears: 'Sage.expressions.DefaultLibraries.Accounts.Unsigned(system.string) is not defined.'

Contains(Variable, what it contains)

String

To determine whether certain characters are contained within a field.

• 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

• Used for text fields only. To use a numeric field, you must use Cstring to convert to text

Example:

• To see whether the post code contains NE to pull out North East based companies: 

  Contains(SALES_LEDGER.ADDRESS_5,"NE") 

• You can then use this in an if statement, for example: 

  Contains(SALES_LEDGER.ADDRESS_5,"NE") ? "North East" : "Other"

  If the post code contains NE this shows North East, if not it shows Other.

Count(variable)

General

Counts the number of instances of a field on a report.

• You can set this within Properties then Function, but you can also use it as a function in expressions

• 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 find the total number of customers on a report, add the following expression to the Report Footer:

Count(SALES_LEDGER.ACCOUNT_REF)

Ensure that Properties then Reset Section is set to Report Footer.

CountIf(condition)

General

Counts the number of instances on a report if the specified conditions are met.

• 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 isn't case-sensitive

Example:

To count the number of customer records with a balance greater than £1000.00, add the following expression to the Report Footer:

CountIf(SALES_LEDGER.BALANCE > 1000.00)

Ensure that Properties then Reset Section is set to Report Footer.

CountUnique(variable)

General

Counts the number of unique instances of a specified variable.

Example:

To show the number of unique products bought by a customer on an invoice, add the following expression to the INVOICE.ACCOUNT_REF footer:

CountUnique(INVOICE_ITEM.STOCK_CODE)

Ensure that Properties then Reset Section is set to INVOICE.ACCOUNT_REF footer.

CreateDateTime(year,month,day)

Date

Creates a date out of its three component parts.

This has limited uses and is usually used in conjunction with YearFromDate, MonthFromDate, DayFromDate and DaysInMonth functions.

Examples:

• To show the start of the month a transaction was entered in: 

  CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE),01) 

  This takes the Year and Month from the transaction date and then adds 01 as the day.

• To show the end of the month a transaction was entered in: 

  CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE), DaysInMonth(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE)))

  This takes the year and month from the transaction date. Then to calculate the last day of the month it uses the DaysInMonth function.

• To show the end of the next month: 

  MonthFromDate(AUDIT_HEADER.DATE) < 12 ? CreateDateTime(YearFromDate(AUDIT_HEADER.DATE), MonthFromDate(AUDIT_HEADER.DATE)+1, DaysInMonth (YearFromDate(AUDIT_HEADER.DATE), MonthFromDate(AUDIT_HEADER.DATE) +1)) : CreateDateTime(YearFromDate(AUDIT_HEADER.DATE)+1, 1, 31) 

  First, this works out if the month from the transaction is December. If not, it takes the year from the transaction date, takes the month plus one and then uses DaysInMonth to return the last day of that month. If the transaction date is in December, it takes the year from the date and adds one, uses 01 as the month and 31 as the day to return the last day of the following January.

Credit(variable)

Sign

Shows only credit values.

You can set this within Properties > Function, but you can also use it as a function in expressions.

Example:

Within a footer section, you can use this function to show either the total of all credits, or to show the overall total only if it's a credit value. The way this function behaves depends on whether Properties > Apply Sign After Group is set to True or False.

For example, in Nominal > Reports, on the Nominal Activity report the credit total is a total of all of the credits, where as the history balance is a total of all of the transactions expressed as a debit or credit.

The credit total and the credit part of the history balance have the same credit function applied. The difference is that the total is set to Apply Sign After Group = False and the history balance is set to Apply Sign After Group = True.

CString(variable)

String

Converts a numeric field to an alphanumeric string or text.

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)

DateFromPeriod(period)

Date

Returns the first day of the month that the period number relates to.

Uses the numbers 1to 12 as the different months of the company's financial year.

Examples:

• To show the start of the financial year:

  DateFromPeriod(1) 

• To show the end of the financial year: 

  CreateDateTime(YearFromDate(DateFromPeriod(12)),MonthFromDate(DateFromPeriod(12)),

  DaysInMonth(YearFromDate(DateFromPeriod(12)), MonthFromDate(DateFromPeriod(12))))

DateTimeToFormattedString

(date, format)

Date

Converts DateTime into a text string which you can format.

You can use this function to only return part of a date or to control how an entire date appears.

A common use of this function in Sage Accounts is to show dates in the subject lines of emails.

You can use this for grouping reports by date.

• You can use this in conjunction with If statements on column based reports.

• In the format section the month option is always in uppercase but year and date lowercase.

• Popular formats are: 

  - dd/MM/yy which would display as 13/06/13.

  - MMMM yyyy which would display as June 2013.

  - dddd dd MMMM yyyy which would display as Thursday 13 June 2013.

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 return the date as year and month only to group a report by month: 

  DateTimeToFormattedString(AUDIT_SPLIT.DATE,"yyyy,MM")

• To show the company name and invoice date on an email subject line:

  COMPANY.NAME + " " + DateTimeToFormattedString(INVOICE.INVOICE_DATE, "dd-MM-yyyy")

• If a date variable or expression shows a time stamp, for example, 10/02/11 00:00:00, you can also use the DateTimeToFormattedString function to remove the time stamp:

  DateTimeToFormattedString(Expression,"dd/MM/yy") 

• To show the long date including the st, th or rd you can use the following expression: 

  (DateTimeToFormattedString(REPORT.DATE,"dddd") + " " + CString(DayFromDate(REPORT.DATE))) +

  ((DayFromDate(REPORT.DATE) % 10 = 1 and DayFromDate(REPORT.DATE) <> 11) ? "st" : 

  (DayFromDate(REPORT.DATE) % 10 = 2 and DayFromDate(REPORT.DATE) <> 12) ? "nd" : 

  (DayFromDate(REPORT.DATE) % 10 = 3 and DayFromDate(REPORT.DATE) <> 13) ? "rd" : "th")

  + FormatString(" {0:MMMM yyyy}", REPORT.DATE)

DayFromDate(date)

Date

Returns the day from a date.

This function is usually used as part of a CreateDateTime expression.

Examples:

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

  DayFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED) 

• To return the day from a transaction date: 

  DayFromDate(AUDIT_HEADER.DATE) 

  It is most often used as part of the CreateDateTime function: 

  CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE),

  DayFromDate(AUDIT_HEADER.DATE))

DaysInMonth(year,month)

Date

Returns the number of days in the specified month and year.

Usually used as part of a CreateDateTime expression and often in conjunction with YearFromDate and MonthFromDate.

Examples:

• To show the number of days in any given February, including accounting for leap years:

  DaysInMonth(2013,02) 

• To find the end of a month a transaction was posted in:

  CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE),

  DaysInMonth(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE))) 

  This takes the year and month from the transaction date, then uses the DaysInMonth function to find the total number of days in that month and return the date as the last day of that month.

Debit(variable)

Sign

Shows only debit values.

You can set this within Properties > Function, but you can also use it as a function in expressions.

Example:

Within a footer section, you can use this function to show either the total of all debits, or to show the overall total only if it's a debit value. The way this function behaves depends on whether Properties > Apply Sign After Group is set to True or False.

For example, in Nominal > Reports, on the Nominal Activity report the debit total is a total of all of the debits, whereas the history balance is a total of all of the transactions expressed as a debit or credit.

The debit total and the debit part of the history balance have the same debit function applied. The difference is that the total is set to Apply Sign After Group = False and the history balance is set to Apply Sign After Group = True.

EndsWith(variable 1, what it ends with)

String

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

• 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.

• Used for text fields only. To use a numeric field you must use Cstring to convert to text.

Examples:

• If the product code ends with an A, the following function displays 1. If not, it displays 0:

  EndsWith(STOCK.STOCK_CODE,"A")

  You can then use this in an if statement, for example:

  EndsWith(STOCK.STOCK_CODE,"A") ? "Yes" : "No"

• 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) : ""

Floor(variable)

Value

Rounds down to the next whole number.

You can use this to display pounds and pence separately.

Examples:

• To round the customer balance down to the next whole number: 

  Floor(SALES_LEDGER.BALANCE) 

  So a balance of 500.59 would return 500 and a balance of 500.01 would also return 500, but a negative balance of -500.59 would return -501.

• To show the pounds element of a value: 

  Floor(Unsigned(INVOICE.FOREIGN_INVOICE_GROSS)) 

  To show the pence element of a value: 

  INVOICE.FOREIGN_INVOICE_GROSS - Floor(Unsigned(INVOICE.FOREIGN_INVOICE_GROSS))

FormatString(text,placeholder1,text, placeholder2,variable1,variable2)

String

Prints a string, for example, a piece of text, with one or more predefined expressions inserted into the string using placeholders.

The function is written as:

FormatString("string before placeholder zero {0} string after placeholder zero {1} string after placeholder one etc.", placeholder expression 0, placeholder expression 1)

The placeholders appear as {0}, {1} and so on. They can be individually formatted, for example:

• {0:f2} - The value shown by placeholder zero is formatted to two decimal places.

• {1:c2} - The value shown by placeholder one is formatted as a currency with two decimal places.

Examples:

• To show a customer's balance and remaining credit as one string:

  FormatString("Customer {0} has a balance of {1} and {2} credit remaining.", SALES_LEDGER.ACCOUNT_REF, SALES_LEDGER.BALANCE,(SALES_LEDGER.CREDIT_LIMIT- SALES_LEDGER.BALANCE))

  This would show as Customer A1 has a balance of 1000.00 and 200.00 credit remaining.

  FormatString("Customer {0} has a balance of {1:c2} and {2:c2} credit remaining.", SALES_LEDGER.ACCOUNT_REF, SALES_LEDGER.BALANCE,(SALES_LEDGER.CREDIT_LIMIT- SALES_LEDGER.BALANCE))

  This would show as Customer A1 has a balance of £1000.00 and £200.00 credit remaining

• To show an invoice item quantity, unit price and net total as one string but with different decimal places and showing the £ symbol:

  Formatstring("{0:f0} items costing {1:c4} each, totaling {2:c2}", INVOICE_ITEM.QUANTITY, INVOICE_ITEM.UNIT_PRICE, INVOICE_ITEM.NET_AMOUNT)

  This would show as 8 items costing £7.590 each, totaling £60.72

If statement

General

To display certain values depending on the condition specified.

• This can be used in conjunction with other functions.

• You can nest If statements to build up more complex criteria.

• The ? command is equivalent to Then.

• The : command is equivalent to 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 .

IndexOf(variable, text to find)

String

Reports the position of of the first occurrence of the specified characters in a string.

• You can combine this with the Substring function to only return the value of a field up to a certain character.

• Used for text fields only. To use a numeric field you must use Cstring to convert to text.

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 name before the space:

  Substring(SALES_LEDGER.CONTACT_NAME,0,IndexOf(SALES_LEDGER.CONTACT_NAME," "))

• The same can be used to return everything after the space, for example, to show only the customer's surname:

  Substring(SALES_LEDGER.CONTACT_NAME, (IndexOf(SALES_LEDGER.CONTACT_NAME," "))) 

  When you are returning the remainder of the string, you don't need to specify the number of characters to return.

Insert(string to insert into, what to insert, start point)

String

Inserts a specified string at a specified index position in another string.

Used for text fields only. To use a numeric field you must use Cstring to convert to text.

The start point value uses an index starting with zero, for example to insert at the very beginning, the start point is 0. To insert after the first character the start point is 1 and so on.

Example:

• To insert the letters UK after the first character of each stock code:

  Insert(STOCK.STOCK_CODE,"UK",1)

  So a stock code of A123 shows as AUK123.

IsLeapYear(year)

Date

Indicates whether the specified year is a leap year.

• 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.

• Most commonly used in expressions to account for leap years in prior year date calculations.

Example:

• The following expression is used in reports where you enter a date range as criteria and the report then calculates the dates to represent the same date range for exactly one year prior: 

  CreateDateTime(YearFromDate(CRITERIA.TRAN_DATE_TO)-1, MonthFromDate(CRITERIA.TRAN_DATE_TO),IsLeapYear(YearFromDate(CRITERIA.TRAN_DATE_TO)) = 1 and MonthFromDate(CRITERIA.TRAN_DATE_TO) = 2 and DayFromDate(CRITERIA.TRAN_DATE_TO) = 29 ? 28 :IsLeapYear(YearFromDate(CRITERIA.TRAN_DATE_TO)-1) = 1 and MonthFromDate(CRITERIA.TRAN_DATE_TO) = 2 and DayFromDate(CRITERIA.TRAN_DATE_TO) = 28 ? 29 : DayFromDate(CRITERIA.TRAN_DATE_TO)) 

  The section in bold calculates what the day should show as and translates as:

  If the year from the date the report was ran to is a leap year, and the month is February, and the day was the 29th, then return the day as 28th. If the year the report was ran to - 1 is a leap year, and the month is February, and the day was 28th then instead show as 29th, otherwise show whatever day was entered.

LastIndexOf(variable, text to find)

String

Reports the index position of the last occurrence of the specified string.

Used for text fields only. To use a numeric field you must use Cstring to convert to text.

Examples:

• If a customer's contact name has a title, forename and surname, and you want to return only the surname, the following expression finds the position of the last space in a persons name:

  LastIndexOf(SALES_LEDGER.CONTACT_NAME," ") 

• You can combine this with the substring function to return the name after the last space:

  Substring(SALES_LEDGER.CONTACT_NAME,(LastIndexOf(SALES_LEDGER.CONTACT_NAME," "))) 

  When you are returning the remainder of the string, you don't need to specify the number of characters to return.

Length( )

String

Displays how many characters exist in a string.

Used for text fields only. To use a numeric field you must use Cstring to convert to text.

Example:

To display the length of a customer's account reference:

Length(SALES_LEDGER.ACCOUNT_REF)

Max(val1, val2)

General

Returns the largest result in one field or compares several different fields and returns the largest.

When returning the larger instance of one field, you can set this within Properties > Function, but you can also use it as a function in expressions.

Examples:

• To return the largest instance of one field:

  Max(SALES_LEDGER.BALANCE) 

  Adding this to the Report Footer shows the largest balance of all the customers on the report. 

• To compare two different fields: 

  Max(NOMINAL_LEDGER.BALANCE_MTH1,NOMINAL_LEDGER.BALANCE_MTH2) 

  You can extend this to compare as many fields as you like, with each field separated by a comma.

Min(val1, val2)

General

Returns the smallest result in one field or compares several different fields and returns the smallest.

• 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.

• When returning the smallest instance of one field, you can set this within Properties > Function, but you can also use it as a function in expressions.

• To return the smallest instance of one field:
  
  Min(SALES_LEDGER.BALANCE) 
  
  Adding this to the Report Footer shows the smallest balance of all the customers on the report. 
  
  
• To compare two different fields: 
  
  Min(NOMINAL_LEDGER.BALANCE_MTH1,NOMINAL_LEDGER.BALANCE_MTH2) 
  
  You can extend this to compare as many fields as you like, with each field separated by a comma.

• If a customer's account was opened on 14/02/2001, the following function shows 2: 
  
  MonthFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED)
  
  
• It is most often used as part of the CreateDateTime function: 
  
  CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),
  MonthFromDate(AUDIT_HEADER.DATE),DayFromDate(AUDIT_HEADER.DATE))

• To return all transaction types except for SI: 
  
  Not(AUDIT_SPLIT.TYPE Like "SI")

• 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.
• Between the brackets is always left blank.

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

          Now( ) + 30

To ensure a field contains a certain number of characters, this function inserts additional characters to the left of the value.

When using this function with a numeric field, you must also use CString to convert the numeric value to an alphanumeric string.

The total number of characters value is the total string length required and not the number of padding characters.

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.

When using this function with a numeric field, you must also use CString to convert the numeric value to an alphanumeric string.

The total number of characters value is the total string length required and not the number of padding characters.

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.'

If a document contains an expression that uses the ParseDateTime() function on a text field, the document only runs for records that have a date value in the relevant field.

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

ParseDateTime(INVOICE.NOTES_1)

To add thirty days to the date entered in the first line of the notes:

ParseDateTime(INVOICE.NOTES_1) + 30

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 + " *"

You must always enter the starting position, where the first character is 0, and the number of characters to remove.

When using this function with a numeric field, you must also use CString to convert the numeric value to an alphanumeric string.

• 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) 
  
  
• To remove the last three characters of the customer's account reference:
  
  Remove(SALES_LEDGER.ACCOUNT_REF,Length(SALES_LEDGER.ACCOUNT_REF)-3,3)

This function is case sensitive.

When using this function with a numeric field, you must also use CString to convert the numeric value to an alphanumeric string.

• Most often used to remove a certain character from a string by replacing it with nothing, for example: 
  
  Replace(SALES_LEDGER.ACCOUNT_REF,"-","")
  
  This removes each - from the customer account references.
  
  
• 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") 
  
  
• The following expression can be used to show a comma as the decimal separator for a foreign currency:
  
  Replace( FormatString("{0:f2}", AUDIT_HEADER.FOREIGN_GROSS_AMOUNT), "." , "," )
  
  The FormatString specifier ‘f2’ gives a string fixed to two decimal places. This expression only works if it's set to Signed. To show the values as Unsigned, Debit or Credit, you must include this in the expression, but leave the Properties set to Signed, for example:
  
  

Replace( FormatString("{0:f2}", Unsigned(AUDIT_HEADER.FOREIGN_GROSS_AMOUNT)), "." , "," )

Replace( FormatString("{0:f2}", Debit(AUDIT_HEADER.FOREIGN_GROSS_AMOUNT)), "." , "," )

Replace( FormatString("{0:f2}", Credit(AUDIT_HEADER.FOREIGN_GROSS_AMOUNT)), "." , "," )

To reverse the sign of a value based on transaction type:

AUDIT_SPLIT.TYPE Like "SI" ? Reversed(AUDIT_SPLIT.NET_AMOUNT) : AUDIT_SPLIT.NET_AMOUNT

To round the customer balance to the nearest whole number: 

Round(SALES_LEDGER.BALANCE) 

So a balance of 500.58 would show as 501, but a balance of 500.36 would show 500.

Rounds a value to the specified number of decimal places.

You can use this to avoid slight rounding differences between Sage Accounts and Report Designer.

You can also set this in Properties > Formatting > Numeric.

• 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)

Most often used in conditional formatting, for example, to show negative values in red on a statement, set the conditional filter to:

Sign(AUDIT_HEADER.FOREIGN_NET_AMOUNT) = -1 

And set the conditionals properties to Text Style > Red.

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.

Used for text fields only. To use a numeric field you must use Cstring to convert to text.

• 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) : ""
  
  
• To see whether a product code starts with an A:
  
  StartsWith(STOCK.STOCK_CODE,"A")
  
  
• The following If statement shows Yes or No depending on whether the product code starts with an A:
  
  StartsWith(STOCK.STOCK_CODE,"A") ? "Yes" : "No"

All records must have a number entered in the appropriate format, there are any non-numeric values, blank fields or whole numbers the following message appears:

'Input string is not in correct format.'

• To convert a text field into a numeric field: 
  
  StringToFloat(INVOICE.NOTES_1) 
  
  So you can use it in a filter or expression: 
  
  StringToFloat(INVOICE.NOTES_1) > 100
  
  
• 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

All records must have a number entered in the appropriate format, there are any non-numeric values, blank fields or decimal numbers the following message appears:

'Input string was not in correct format.'

• To convert a text field into a numeric field: 
  
  StringToInteger(INVOICE.NOTES_1) 
  
  So you can use it in a filter or expression: 
  
  StringToInteger(INVOICE.NOTES_1) > 100.50
  
  
• To multiply the quantity in stock by a number entered in Product Record > Location:
  
  StringToInteger(STOCK.LOCATION) * STOCK.QTY_IN_STOCK

• To multiply the quantity in stock by a number entered in Product Record > Unit of Sale:
  
  StringToInteger(STOCK.UNIT_OF_SALE) * 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.

You can also use this function to show the last characters in a string.

When you are returning the remainder of a string you don't need to enter the number of characters to return.

• 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)
  
  
• To calculate the sales ledger analysis 1 length and then display the last 3 characters:
  
  Substring(SALES_LEDGER.ANALYSIS_1,(Length(SALES_LEDGER.ANALYSIS_1) - 3)) 
  
  
• 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.
  
  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 return the first three characters from the stock code: 
  
  Substring(STOCK.STOCK_CODE,0,3) 
  
  
• Combined with IndexOf to return the text before the space in a customer contact name:
  
  Substring(SALES_LEDGER.CONTACT_NAME,0,IndexOf(SALES_LEDGER.CONTACT_NAME," "))

You can set this within Properties > Function, but you can also use it as a function in expressions.

To ensure the totals are correct, you must check that the Evaluate Section and Reset Section settings are correct on the expression.

To total the customer balances: 

Sum(SALES_LEDGER.ACCOUNT_REF)

To total only the values that meet the condition specified.

The Properties > Function should be set to None.

To ensure the totals are correct, you must check that the Evaluate Section and Reset Section settings are correct on the expression.

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")

On the Project Activity report, the following function shows the net total of project transactions with the details 'Project1':

SumIf(Project_Net, PROJECT_ONLY_TRAN.DETAILS Like “Project1”)

To create a summary total at the end of an activity report to show a total per transaction type: 

SumIf(AUDIT_HEADER.NET_AMOUNT, AUDIT_HEADER.TYPE like "SI") 

This shows the total for all SI transactions on the report. The same can be done for each required transaction type.

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

ToLower(SALES_LEDGER.ACCOUNT_REF)

When grouping a report by a text value in Sage Accounts 2011 or below, groups can be duplicated due to inconsistent capitalisation. For example, when grouping a report by SALES_LEDGER.ANALYSIS_1, values of HELEN, Helen and helen, all create separate groups. You can use the ToLower( ) function in the group expression to stop capitalisation causing additional groups on the report:

ToLower(SALES_LEDGER.ANALYSIS_1)

To calculate the number of months you've run a report for: 

TotalMonths(CRITERIA.TRAN_DATE_BFCF_FROM,CRITERIA.TRAN_DATE_BFCF_TO)

To calculate the number of whole months you've run a report for: 

TotalWholeMonths(CRITERIA.TRAN_DATE_BFCF_FROM,CRITERIA.TRAN_DATE_BFCF_TO)

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

ToUpper(INVOICE.INVOICE_OR_CREDIT)

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)

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) 

This can be useful if, for example, spaces at the end are causing problems with an If statement.

Removes all the spaces from the beginning of a string.

When you despatch a sales order in Sage Accounts, the invoice that's created contains spaces at the beginning of the sales order number. You can use this function to remove the spaces.

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.

In the case of positive values, the number rounds down towards zero. For example, 100.30 rounds down to 100.00.

• To round an invoice value down to the nearest whole number: 
  
  Truncate(INVOICE.INVOICE_GROSS)
  
  
• To round a 5% commission value down to the nearest whole number:
  
  Truncate(AUDIT_SPLIT.NET_AMOUNT * 0.05)

To show all transaction net values as positive:

Unsigned(AUDIT_HEADER.NET_AMOUNT)

To return the year from a transaction date: 

YearFromDate(AUDIT_HEADER.DATE)

It's most often used as part of the CreateDateTime function:

CreateDateTime(YearFromDate(AUDIT_HEADER.DATE),MonthFromDate(AUDIT_HEADER.DATE),
DayFromDate(AUDIT_HEADER.DATE))

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

YearFromDate(SALES_LEDGER.DATE_ACCOUNT_OPENED)