Add a User-Defined Field (UDF) to a Crystal Report

Home | FAQs & Troubleshooting

                    

Expand/Collapse all Show/Hide All

Expand/Collapse item  Overview

You can add user-defined fields (UDFs) to graphical forms and custom reports using Crystal Designer. All UDFs are accessible in Crystal Designer; this allows you to incorporate UDFs in custom reports.

For some modules, you can add UDFs to all reports, journals, and registers, in addition to graphical forms and custom reports.

Note For information on creating UDFs, see Create a User-Defined Field (UDF).

Use one of the following methods to add a UDF to a Crystal Report:

 

To add an existing UDF to a Crystal report for modules other than Material Requirements Planning and Work Order

Note This procedure assumes that the UDF has already been created.

  1. Select Custom Office Main menu > User-Defined Field and Table Maintenance.

  2. Select the module and add the UDF to the work table for the report. For example, if you are adding a UDF in the GL Account Master table to the Chart of Accounts report, the UDF must be added to the GL Chart of Accounts work table. Use the following steps to add the UDF to the report work table:

  1. Select the report work table to which you are adding the UDF, and click the Edit Fields button.

  2. In the User-Defined Fields window, click the Add button.

  3. In the Add Field window, in the Field Name field, type the name of the existing UDF. At the Description field, type the description. For more information, see Add Field - Fields.

  4. In the Data Source field, select the table that contains the UDF to add to the report work table.

  5. In the Column field, select the UDF to add to the report work table. Click OK.

  6. In the Edit Field window, enter a description and caption if necessary, and click OK. For more information, see Add Field/Edit Field - Fields.

  7. In the User-Defined Fields window, click OK. A message appears asking if you want to begin the update process. Click OK.

  1. Start Crystal Designer. Select File menu > Open, and select the report file to which you will add the UDF. All standard reports are located in the Sage 100 Reports folder.

  2. Select Database menu > Verify Database.

  3. In the Sage 100 Database Signon window, select the company and user codes for the report. Click OK. A message appears indicating that the database has changed. Click OK.

  4. Select Insert menu > Field Object.

  5. In the Field Explorer window, select Database Fields, and then select the report work table the UDF is located in. In the work table, select the UDF you are adding.

  6. Drag and drop the UDF to the appropriate location on the report.

  7. Close the Field Explorer window and save your report changes.

The UDF is added to the report. You can enter ranges of UDF data to print in the Selections grid of any applicable report window.

 

To add an existing UDF to a Crystal report for the Material Requirements Planning and Work Order modules

Note This procedure assumes that the UDF is already created.

  1. Select Library Master Setup menu > Report Manager.

  2. In Report Manager, select the form or report and click the Maintain button. A second Report Manager window appears.

  3. In the Report Manager window, click Design.

  4. In SAP Crystal Reports, select Database menu > Log On Server.

  5. In the Log On Server dialog box, select ODBC-SOTAMAS90.

  6. Select Database menu > Add Database to Report.

  7. Scroll through the SQL tables, and select the desired UDF table. Click OK.

Note UDF files begin with the two-character module code followed by an underscore, followed by a number from 90 through 99 (for example, AP_90ABC.SOA for the Accounts Payable UDF data for company ABC).

ODBC file names begin with the "raw" file name followed by _UDF_, followed by descriptive text (for example, AP_90_UDF_AP_Vendor).

  1. From Crystal Visual Linking Expert, link an existing table field to the UDF table field.

Note Link table key fields whenever possible. If the UDF table is the only table in the report, linking is not required. Because UDF tables are not required, you should always link from the report's primary table to the UDF table using a LEFT OUTER JOIN. If an EQUAL JOIN is used instead of a LEFT OUTER JOIN, data will print only if records exist in both files. Do not use the SMART LINK feature in Crystal Visual Linking Expert; it can create invalid joins and return unexpected results.

  1. Click OK and return to the SAP Crystal Reports Form Design window.

  2. Select Insert and then select Database Field.

  3. Scroll through the Insert Fields Database. Select the desired UDF table field.

  4. Select Insert and place the selected UDF on the report.

  5. Preview the report to verify the results.

  6. Save the report.

The UDF is added to the report.