Password Formulas

  • Formulae are constructed in segments.

  • Segments are separated by the plus (+) symbol.

  • Segments comprise of functions that can be applied to Sage 300 view fields as follows: 

{fieldname}

RIGHT() function

LEFT() function

YYYY() or YY() year function

MMM() or MM() month function

DD() day function

Example:

The following example sets the password to the Customer number, followed by the last 3 characters of the customer name and the first 5 characters of the customer's postcode.

{IDCUST}+RIGHT({NAMECUST},3)+LEFT({CODEPSTL},5)

Customer: ORC001

Customer Name: Orchid Systems

Zipcode: NSW2060

 

The resulting password would be ORC001emsNSW20

There are three segments to this formula:

1. {IDCUST} - ORC001 - the customer number

2. RIGHT({NAMECUST},3) - ems - the last 3 characters of the customer name

3. LEFT({CODEPSTL},5) - NSW20 - the first 5 characters of the postcode

 

 

Password Segments

Segment 1:

{IDCUST} - refers to the IDCUST field in AR0024 – AR Customers

- fields are enclosed in curly braces {}

- field names must be written in UPPERCASE.

 

Tip: See below for Sage 300 Views and Fields that can be included in the password formulae.

Segment 2:

RIGHT({NAMECUST},3)

Right 3 characters of the {NAMECUST} field.

RIGHT() is the function.

Note: the function can be written as: RIGHT, Right, or right.

 

Segment 3:

LEFT({CODEPSTL},5)

Left 5 characters of the postcode field in ARCUS.

Note: the function can be written as: LEFT, Left, or left.

Sage 300 Views that can be included in the PDF formulas

Customer’s remittance advice AR0024 – AR Customers
Vendor’s remittance advice AP0015 – AP Vendors
Employee’s remittance advice, Pay Stub/Slip, T4 and Relevé 1s

CP0014 – CP Employees

UP0014 – UP Employees

Tip: You can find the field names to use for each view in the Sage 300 Object Model.

Sage security groups and passwords

In the latest Sage 300 product updates, there is a new security group for Tax Number (in AP) and SIN Number maintenance (CT7.3M and UT7.3N for Canadian Payroll and US Payroll).

Users who don't have Sage 300 security clearance to view the Tax Number or SIN Number will not see the full number. If you use the number to password protect the PDF, the password will not be what you expect based on the formula. Therefore we suggest that users who generate the PDF have security rights in Sage 300 to view the fields included in the password formula.

Example:

AP Remittance Advice and Tax Number field

If you use {TAXNBR} in the password for AP Advices, the user emailing the remittance advices needs to be in a Sage 300 Security Profile where the Tax Number Maintenance security group is selected.

Example:

Payslip and SIN Number

You can use {SSN} in the password for payslips (in EFT Options > Payroll Tab).

For example RIGHT({SSN},4) to get the last 4 digits of the SIN Number.

The user running the payroll and generating the PDF needs to be in a Sage 300 Security Profile where the SIN Number Maintenance security group is selected. If they don't have rights to view the field, the password will not be what is expected.

Formulae and date fields.

Example: Password formula using date fields

RIGHT({LASTNAME},2)+LEFT({EMPLOYEE},3)+LEFT({FIRSTNAME},4)+YYYY({BIRTHDATE})+mmm({BIRTHDATE})+DD({BIRTHDATE})

Employee code: 23456754

Emplyee Name: Sean Smith

Date of Birth: 1/12/1970

The above formula would result in the following password applied to the PDF:

 

th234Sean1970DEC01

th: the last 2 characters of the last name

234: the first 3 characters of the employee code

Sean: the first 4 characters of the first name

1970: the year of the DOB

DEC: the month of DB

01: day of birth.

 

Date fields can be specified as follows:

  1. {BIRTHDATE} - returns the date in YYYYMMDD format.
  2. Year only:
  1. YYYY({datefield}) - returns the year in YYYY format. Can be written as YYYY or yyyy
  2. YY({datefield}) - returns the year in YY format. Can be written as YY or yy.
  1. Month only:
  1. MMM({datefield}) - returns the month in abbreviated format: JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC. Month is returned in UPPERCASE.  Can be written as MMM or mmm
  2. MM({datefield}) - returns the month in numeric format with padded left: 01,02,03,...,12.  Can be written as MM or mm
  1. Day only:
  1. DD({datefield}) - returns the day portion of the date: 01,02,03,...,31. Can be written as DD or dd

Integers and Booleans in Formulae.

Integer and boolean type fields can be included in a formula.

BCD Numbers in Formulae.

BCD numbers are presently not supported.