How to use Data File Display and Maintenance in Sage 100 Premium (SQL)

 CAUTION: The following procedure requires in-depth knowledge of database engines for your Sage product, such as Microsoft SQL Server, Pervasive SQL, and MySQL. Sage can’t assist with database administration and isn’t responsible for errors resulting from database changes. Back up your system and application databases required for a complete restore. Consider contacting a certified consultant or your database administrator for assistance.

Data File Display and Maintenance has been upgraded in Sage 100 2019 (and later versions) with several new features.

• If you’re using Sage 100 Premium, you can now access files stored in a SQL database.
• To select a file in the File Name field, you can enter the company code, followed by a colon, followed by the file name. For example: ABC:AR_Customer
• For files in modules upgraded to Business Framework, the Data File Display and Maintenance – Edit window now lists all fields and values in a grid.

Encrypted fields are not available to edit, but you can select the Allow Read-Only Fields to Be Changed check box to edit the read-only fields.

Validation is now performed on date and numeric fields.

• For multi-segment keys, the key scan filter allows you to filter the records using one or more segments of the key. Enter the selected key column separators as needed to indicate which segments of the key your search criteria must match.

For example, the key for customer records is ARDivisionNo:CustomerNo. To search for customer number 02-CAPRI, you can enter the following in the Key field:

:CAPRI

Alternately, you can select a different character to separate the segments of the key in the Key Column Separator field. If you select the tilde (~) character, you can then search by entering the following in the Key field:

~CAPRI

Note that you don’t need to enter the full value contained in the segment. Continuing with the above example above, you could also enter the following:

~CAP

You can filter by multiple segments. For example, to view records in the GL_DailyPosting file from 2018 with AP as the source journal, enter the following in the Key field:

2018:AP

• For modules that have been upgraded to Business Framework, you can click Add to add a record.
  • If you adjust the width of the Data File Display and Maintenance – Key Scan window, and then close the window to run a second scan, the window reopens at the width that you used previously. The Key and Data columns are automatically adjusted to fill the width of the window.
  • In the Data File Display and Maintenance – Key Scan window, when entering search criteria in the Search field, you can use a special character to define the scope of the search:
  • To search for text in any location within the current key, add an asterisk (*) at the beginning of your search text. For example: *14072
  • To search for text within the current key and the data record, add a percent symbol (%) at the beginning of your search text. For example: %14072
  • The Print button has been removed.

When starting Data File Display and Maintenance using the Run Program window accessed on the File > Run menu, you can now enter SY_FileDisplay_UI.

NOTE: if you are not using Sage 100 Premium but instead have Sage 100 Standard or Advanced, please see the Related Resources on general use of the Data File Display and Maintenance (in Standard or Advanced you can browse over network to see the ProvideX files contained in MAS90 folder – or other locations if appropriate).