How to replace the ALE (Advanced Lookup Engine) query files for Sage 100

Description

How to replace the Advanced Lookup Engine (ALE) query files for Sage 100 ERP from backup or via reinstall

Disclaimer

Backup Warning
Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions. If necessary, seek the assistance of a qualified Sage business partner, network administrator, or Sage customer support analyst.

Cause

• Lookup columns are misaligned
• Lookup missing data
• Field values do not belong to the column (e.g. Customer Zip Code displays under Terms Code)
• F2 lookup data is incorrect

Resolution

1. From the Sage 100 ERP Desktop, click File menu, select Run
2. For the Program field enter the following command and press the Enter key: SYZCON
3. Click OK
4. At the prompt type the following: PRINT %SYS_SS’CreateTable(“table“,”COMPANY:xxx“)
   • Note: Replace table with the name of the table the lookup is using. Replace xxx with the company code affected
5. Press the Enter key
6. Repeat this process for each table and company code where this issue appears

In some situations replacing the ALE query files may be necessary, such as if the lookups are blank, code displays instead of records, field values don’t match columns, or if certain errors occur.

• Restore the query files from backup (preferred method):
  (Note: This procedure overwrites any customizations made since the time of the backup! If Providex.dde and Providex.ddf are restored, user-defined fields (UDFs) created since the time of the backup may need to be updated!)
  1. Have all users exit Sage 100 ERP
  2. Via Windows Explorer, go to the “..\MAS90\SOA” location where Sage 100 ERP is installed.
  3. Rename the SY1Q01.soa, SY1Q02.soa, SY1Q03.soa, SY1Q04.soa, and SY1QDD.soa query files
  4. Go to the “..\MAS90” directory
  5. Rename the Providex.dde and Providex.ddf data dictionary files
  6. Restore these files from the last good backup
• Replace the ALE query files via reinstallation of programs (proceed with caution).
  (Note: This procedure overwrites any customizations made since the time of the backup! If Providex.dde and Providex.ddf are restored, user-defined fields (UDFs) created since the time of the backup may need to be updated!)
  1. Have all users exit Sage 100 ERP
  2. Via Windows Explorer, go to the “..\MAS90\SOA” location where Sage 100 ERP is installed.
  3. Rename the SY1Q01.soa, SY1Q02.soa, SY1Q03.soa, SY1Q04.soa, and SY1QDD.soa query files
  4. Go to the “..\MAS90” directory
  5. Rename the Providex.dde and Providex.ddf data dictionary files
  6. Reinstall all modules – including Library Master, the specific Product Update, and any third-party enhancements, and any individual program or hot fixes.
     Note: It is not necessary to reinstall the System Setup module.
     Note: To avoid excess downtime, consider installing Sage 100 ERP (and updates and enhancements, etc.) to a different location and copy the needed files to the original installation. Users will only have to exit when the files are being replaced.

Support

If necessary, seek the assistance of Acute Data Systems to help provide assistance with this process.