LawsonGuru Blog

If you haven’t discovered Ken Hamady, you need to check out his recent post on troubleshooting “duplicate records”.  This is perhaps the most frustrating issue that report designers encounter when developing reports against Lawson tables.

If you don’t have a good grasp on the JOINs/relationships that are used in Lawson’s S3 databases, you can really get in trouble in a hurry.  Some quick tips based on what I see quite often:

• Make sure to include COMPANY when joining HR tables.  This eliminates “table scans” and will greatly improve report performance.  An simple example of this is the relationship between EMPLOYEE and PAEMPLOYEE.  Join not only on EMPLOYEE, but also on COMPANY.
• Understand the difference between an “INNER JOIN” and a “LEFT OUTER JOIN”, and know when to use each.  An INNER JOIN between EMPLOYEE and PAYMASTR returns only the records that match the JOIN criteria on BOTH tables.  So, if you wanted to include employees on a report who have not been paid, an INNER JOIN will not work.
• Using SQL Expressions rather than Formulas for selection criteria. Using SQL Expressions pushes the selection criteria back to the database, making the server do the work.   By contrast, record selection based on Formulas brings all of the records over the network into Crystal.  This is not only a potential performance issue, but depending on your selection criteria, and how your tables are joined, can result in erroneous reports.
• Filter on record status fields where appropriate. For instance, a common mistake is reporting on all GLTRANS records—regardless of status.  Reporting on only “status 9” records will prevent you from including memo-entry, unreleased, and unposted entries in your report.
• Deal with Lawson’s quirky way of storing “null dates” in the database.
• Lastly, include the conditional/”subset switch” when JOINing tables based on a conditional index.  For example, when you JOIN from ACTRANS to PRDISTRIB to report payroll details, you need to include PRDSET5_SS_SW=’Y’ in the JOIN criteria in order to force the use of PRDISTRIB’s PRDSET5 index, which is based on ACTIVITY (not doing so will result in a table scan):

  WHERE PRD.PRDSET5_SS_SW = 'Y'
AND PRD.ACTIVITY = ATN.ACTIVITY
AND PRD.COMPANY = ATN.COMPANY
AND PRD.ATN_OBJ_ID = ATN.OBJ_ID

Again, just a few tips to help you whip your reports into shape, and keep you out of trouble with your DBA!