To show only outstanding transactions on a report.

SLPostedCustomerTrans.OutstandingValue <> 0

or

PLPostedSupplierTrans.OutstandingValue <> 0

To exclude Customers or Suppliers with a zero balance

SLCustomerAccounts.AccountBalance <> 0

or

PLSupplierAccounts.AccountBalance <> 0

To pick up Customers or Suppliers where the balance has exceeded the credit limit

SLCustomerAccounts.AccountBalance <> SLCustomerAccounts.CreditLimit

or

PLSupplierAccounts.AccountBalance <> PLSupplierAccounts.CreditLimit

To pick up invoices over a certain value on Supplier or Customer reports, for example, Supplier or Customer invoices over £100

SLPostedCustomerTrans.NetValue > 100

or

PLPostedSupplierTrans.NetValue > 100

To only show Customers or Suppliers who have terms agreed on there account.

The terms agreed is a flag with 0 meaning the terms are not agreed and 1 meaning they have been.

SLCustomerAccounts.TermsAgreed = 1

or

PLSupplierAccounts.TermsAgreed = 1

To only include lines that have a reference which begins with a set text.

In this example the text always starts with ABC, however it would work for any other text. The percentage sign allows the reference to contain any text after the ABC

NLPostedNominalTrans.Reference like "ABC%"

To exclude accounts flagged as inactive

You can use 1 of 2 available fields to achieve this. The active flag or the active text

StockItems.StockItemStatusID=1

or

StockItems.Status like "Inactive"

To only show lines when the despatched quantity does not equal the order quantity

(SOPOrderReturnLines.LineQuantity - SOPOrderReturnLines.DespatchReceiptQuantity) <> 0

To show the VAT element of the outstanding balance

This will only be correct if the full amount is outstanding

SLPostedCustomerTrans.OutstandingValue - ((SLPostedCustomerTrans.OutstandingValue * SLPostedCustomerTrans.BaseNetValue) / SLPostedCustomerTrans.BaseGrossValue)

To show agents commission based on the net amount

In this example the commission is calculated at 10%.

SLPostedCustomerTrans.BaseNetValue* 0.10

To show the first name and surname of a contact name as separate fields

To show the first name from the Customer contact name:

Substring(SLCustomerAccounts.ContactName,0,IndexOf(SLCustomerAccounts.ContactName," "))

To show the surname from the Customer contact name:

Substring(SLCustomerAccounts.ContactName,IndexOf(SLCustomerAccounts.ContactName," "),
(Length(SLCustomerAccounts.ContactName)-IndexOf(SLCustomerAccounts.ContactName," ")))

To show the first name from the Customer contact name and add a comma:

Concat(Substring(SLCustomerAccounts.ContactName,0,IndexOf(SLCustomerAccounts.ContactName," ")) ,",")

The Concat( ) function is used to combine the first name of the contact name with a comma. The Substring( ) function is used to extract the first name from the SLCustomerAccounts.ContactName. The IndexOf( ) function is used to make the Substring function return all information before the first space in the contact name.

TIP:  For Supplier contact names, replace SLCustomerAccounts.ContactName with PLSupplierAccounts.ContactName.

To change the case used for the Customer contact name

To change the Customer contact name to initial capitals, regardless of whether it was entered in all capitals or all lower case:

NOTE: This expression only works correctly with a first name and surname in the contact name box. It does not work if the contact name includes a middle name, or if it has only one name.

ToUpper(Substring(SLCustomerAccounts.ContactName, 0,1)) + ToLower(Substring(SLCustomerAccounts.ContactName, 1, (IndexOf(SLCustomerAccounts.ContactName, " ")))) + ToUpper(Substring(SLCustomerAccounts.ContactName, (IndexOf(SLCustomerAccounts.ContactName," " )+1),1)) + ToLower(Substring(SLCustomerAccounts.ContactName, (IndexOf(SLCustomerAccounts.ContactName, " ")+2)))

To show only the end of a value, for example, the last 3 characters

Substring(SLCustomerAccounts.AnalysisCode1,Length(SLCustomerAccounts.AnalysisCode1) - 3)

This calculates the length of the string, in this example the analysis 1 value, and then instructs the substring to start 3 digits from the end, that is, the length minus 3.

To add the invoice payment due date to an invoice based on a fixed number of days, for example, 30 days

SOPInvoiceCredits.DocumentDate + Days(30)

To add the invoice payment due date based on the terms from the Customer record

SOPInvoiceCredits.DocumentDate + SLCustomerAccounts.PaymentTermsInDays

To show the profit based on the cost

Profit / Cost * 100

To show the profit based on sales

Profit / Sales * 100

To show a numeric value in an email subject

In the email settings of a document, to show a numeric value in the email subject you can use the CString() function, however this removes zeros after the decimal place, for example, 100.00 appears as 100 and 100.20 appears as 100.2. If required, you can use an if statement expression to show the zeros. This example uses the sales ledger balance but you can use different amount variables as required.

FormatString ("{0:N2}",SLCustomerAccounts.AccountBalance)

To show the ratio between two values as a percentage

FormatString ("{0:P2}", Value1/Value2))

To show a value with a currency symbol if there is one or else the currency code

This example uses the gross amount but you can use different amount variables as required.

FormatString("{0},{1:N2}", (SYSCurrencies.Symbol!="" ? SYSCurrencies.Symbol : SYSCurrencies.Symbol + " "), SLPostedCustomerTrans.BaseGrossValue)

NOTE: The Sign properties of this expression must be set to signed.

To replace the decimal separator with a comma, for example, when displaying foreign currencies

This example uses the gross amount but you can use different amount variables as required.

Replace( FormatString("{0:f2}", SLPostedCustomerTrans.GoodsValueInAccountCurrency), "." , "," )

The FormatString specifier 'f2' gives a string fixed to two decimal places. You can change this if required. The above 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(SLPostedCustomerTrans.GoodsValueInAccountCurrency)), "." , "," )
• Replace( FormatString("{0:f2}", Debit(SLPostedCustomerTrans.GoodsValueInAccountCurrency)), "." , "," )
• Replace( FormatString("{0:f2}", Credit(SLPostedCustomerTrans.GoodsValueInAccountCurrency)), "." , "," )
  
  

Date Expressions

For example, to show the end of the month that a transaction is dated in.

CreateDateTime(YearFromDate(SLPostedCustomerTrans.TransactionDate),
MonthFromDate(SLPostedCustomerTrans.TransactionDate),
DaysInMonth(YearFromDate(SLPostedCustomerTrans.TransactionDate),
MonthFromDate(SLPostedCustomerTrans.TransactionDate)))

For example, if the transaction date is 12/11/12, the expression shows 30/11/12.

For example, an invoice due date may be the end of the month following the invoice date. To show this:

MonthFromDate(SLPostedCustomerTrans.TransactionDate) < 12 ?
CreateDateTime( YearFromDate(SLPostedCustomerTrans.TransactionDate),
MonthFromDate(SLPostedCustomerTrans.TransactionDate)+1,
DaysInMonth (YearFromDate(SLPostedCustomerTrans.TransactionDate), MonthFromDate(SLPostedCustomerTrans.TransactionDate) +1) ) :
CreateDateTime(YearFromDate(SLPostedCustomerTrans.TransactionDate)+1, 1, 31)

For example, if the invoice date is 12/11/12, the expression shows 31/12/12.

SLPostedCustomerTrans.TransactionDate +
((DaysInMonth(YearFromDate(SLPostedCustomerTrans.TransactionDate),
MonthFromDate(SLPostedCustomerTrans.TransactionDate)))) -
(StringToInteger(DateTimeToFormattedString(SLPostedCustomerTrans.TransactionDate, "dd"))) +1

For example, an invoice due date may be the 20th of the month following the invoice date. The following expression shows this due date:

CreateDateTime(YearFromDate(SLPostedCustomerTrans.TransactionDate),
MonthFromDate(SLPostedCustomerTrans.TransactionDate),
DaysInMonth(YearFromDate(SLPostedCustomerTrans.TransactionDate),
MonthFromDate(SLPostedCustomerTrans.TransactionDate))) + 20

For example, if the invoice date is 12/11/12, the expression shows 20/12/12.