Mapping Old Fields to New Names

Database changes can cause a mismatch of field names in your report. For example, your original report may be developed against a Microsoft Access database that includes a field named Account Number (note that the space between the words is significant to Crystal Reports). You may need to eventually have the same report work with a SQL Server database where the field is known as Account_Number (notice that the space has been replaced with an underscore ).

Whenever Crystal Reports detects these kinds of database changes, it can t be sure which new database field the old report object should be associated with. By using Field Mapping, you can point the old object to the new database field. Field mapping simply allows you to change the field name that a report refers to, so that, for example, all objects that used to be associated with Account Number will now be associated with Account_ Number. No formulas have to be changed, no old objects have to be removed, and no new objects need to be added.

The Field Mapping function cannot be chosen from a menu. It is triggered when Crystal Reports detects any field name changes in the source database. Crystal Reports will check for these changes whenever you verify the database or use the Set Datasource Location function to change the database or data source connection. When Crystal Reports detects that fields in the report are no longer in the database, it displays the Map Fields dialog box, as shown in Figure 18-1.

Figure 18-1: Map Fields dialog box

The Map Fields dialog box is divided into four boxes or lists. The upper-left Unmapped Fields list shows report fields that don t match up to any field names in the new database. The upper-right list shows a choice of fields in the new database that you can map the report fields to. To map the report field to the new database field, select the report field you want to map in the upper-left box, select a matching database field in the upper-right box, and then click the Map button. The fields will be moved from the upper boxes to the lower boxes.

The list of database fields will change according to whether or not you click the Match Type check box on the right side of the dialog box. If the box is checked, only fields of the same data type (string, number, date, and so forth) as that of the selected report field will show up. This helps to ensure that you re making the correct match of fields, by not inadvertently mapping a string field on the report to a number or date field in the new database (although you may want to do this sometimes). There may be times that you won t see the field you want to map to in the right list, however, because of data type mismatches . For example, if you re remapping fields originally from a Microsoft Access database to a SQL Server database, you may not find any SQL Server field that matches a currency field in the Access database. In this situation, uncheck Match Type and look for the field to map to.

When the Map Fields dialog box first appears, some fields probably will already be present in the lower two Mapped Fields boxes. The lower-left list shows report fields that already have a matching field in the new database. And once you ve mapped fields from the upper lists, they will be moved to the lower lists as well. If existing mapping was assumed by Crystal Reports (because of identical field names), or you mistakenly mapped fields that don t belong together, you can select a field in the lower-left or lower-right list. The mapped field will appear highlighted in the other box. You can then click the Unmap button to unmap the fields and move them back to the top lists.

Once you have finished mapping fields, click the OK button to close the Map Fields dialog box. Crystal Reports will now associate the mapped fields with the new database. The Design tab, as well as any formulas, will reflect the new field names. If additional tables have mismatched fields, you ll be presented with the Map Fields dialog box for each subsequent table.

Caution

Any fields that no longer exist in the source database must be remapped to new fields. If you don t map old field names to new fields, the old fields and objects they re based on will be deleted from your report.