Field disappears during Verify Database

Thursday 30 October 2014 @ 11:07 am

All reports pull their data from tables of some kind, and these tables do change over time. Fields are added or removed and sometimes field names or their data types are changed.  Crystal has the “Verify Database” feature to update the report so that the table structure stored with the report matches what is in the database.   If a field used by the report is removed, renamed or has been changed to a different data type, you will have to map the old field to the new field in a small mapping window.

But I found an interesting exception.  One of my customers had a character field that was changed from a long string to a memo field.  Apparently the Verify Database ‘mapping’ feature can’t see memo fields, even when they have the exact same name as the original string field.  So when I went to map the original string field to the newer memo field, that new field wasn’t listed.   I had to leave the field unmapped to complete the Verify Database step.

Normally Crystal will remove unmapped fields from the visible layout of the report, but it can’t remove them from any formulas.  So normally when a field is left unmapped it will generate an error when it is referenced in a formula, because the field it references is no longer there.  However in this case the formula referenced a field that still existed under the new data type, so the formulas all worked fine despite the fact that I couldn’t map the field.

So if you ever wonder why fields that exist in the table are not listed during the “Verify Database” mapping step, one thing to check is if the fields are memo fields.

One other interesting note about this case is that the structure of the table hadn’t actually changed.  For some reason, when the report is run in the design environment the field is seen as a string, but when the report is run from within the application, the same field is seen as a memo.  Once we got the report working I told them that the behavior was probably because the application used either a different set of Crystal drivers or a different database driver.  I haven’t heard anymore about the problem so I am not sure if they pursued it. If anyone knows more specifically what would cause this behavior, please drop me a line – I am curious.

Leave a Reply

Recrystallize Pro