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
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.
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.
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.
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:
- {BIRTHDATE} - returns the date in YYYYMMDD format.
- Year only:
- YYYY({datefield}) - returns the year in YYYY format. Can be written as YYYY or yyyy
- YY({datefield}) - returns the year in YY format. Can be written as YY or yy.
- Month only:
- 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
- MM({datefield}) - returns the month in numeric format with padded left: 01,02,03,...,12. Can be written as MM or mm
- Day only:
- 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.