Catalog number not being returned for reports

[grantfitzsimmons]

Describe the bug
There is a behavioral difference between v7.9.6.2 and v7.10.2, introduced in v7.10.0 and present in v7.10.1, where the field $F{1.collectionobject.catalogNumber} in reports and labels fails to show the catalog number.

To Reproduce
Repeat these steps in both v7.9.6.2 and v7.10.2 to see the behavioral discrepancy:

1. Login as vdelnavaz
2. Click on Reports in the navigation menu
3. Run the "Standard Label" report
4. Select a record set (e.g. "Veneridae 2 9")

See that the catalog number is missing in v7.10.2:

http://localhost/specify/query/295/?recordsetid=8328

The strange thing is that the catalog number is visible in the query results:

Expected behavior
It should display the catalog number just as it did in v7.9.6.2.

Crash Report
macOS, Arc

Reported By
Santa Barbara Museum of Natural History

[grantfitzsimmons]

Issue reported by Shell Museum as well (BMSM)

[grantfitzsimmons]

Happening on OS as well

v7.10.1:

v7.9.6.2:

[melton-jason]

The underlying cause in the change of behavior here is because of changes from #5431, #5485, and #6245 (all of which addressing part of #5232).

Specifically, with how CollectionObject -> CatalogNumber is formatted (and returned) in the query results.

Consider a Query of base table CollectionObject with a single field: catalogNumber.
If we want to generate a report or label for the collectionObject with the catalogNumber of 000000004 (using the CatalogNumberNumeric field formatter), the query results will return something like the following and pass it on to the report runner:

Prior to v7.10

{
    "fields": [
        "id",
        "1.collectionobject.catalogNumber"
    ],
    "rows": [
        [
            77960,
            "4"
        ]
    ]
}

After v7.10

{
    "fields": [
        "id",
        "1.collectionobject.catalogNumber"
    ],
    "rows": [
        [
            77960,
            "000000004"
        ]
    ]
}

Many (if not all) of these instances are using the CatalogNumberNumeric formatter, which prior to #6245 would cast the text-based catalog number to a Numeric-based result (which would trim the leading zeroes of the catalogNumber); the query results are then directly used by the report runner to populate the report.

Consider the following text field which shows the Collection Object's catalogNumber in Santa Barbara Museum of Natural History's Standard Label

			<textField isBlankWhenNull="false">
				<reportElement key="textField" x="5" y="13" width="64" height="11" uuid="2dab24ba-f775-4185-89bf-23bfa8dd32f8"/>
				<textElement markup="html">
					<font size="8" pdfEncoding="Cp1250"/>
				</textElement>
				<textFieldExpression><![CDATA["SBMNH "+($F{1.collectionobject.catalogNumber}==null?"":$F{1.collectionobject.catalogNumber})]]></textFieldExpression>
			</textField>

This textField has a specific width and is not configured to stretch when the resulting textExpression overflows the textField bounds. Because of this, when the full non-formatted value of thecatalogNumber is inserted into the report, the textField can not contain the SBMNH prefix and the catalogNumber; As the catalogNumber is what is causing the textField to overflow, JasperSoft handles this by removing it from the generated report/label.
(This can be shown by increasing the textField width, adding isStretchWithOverflow="true" to the textField element, etc.)

The most-likely fix here will involve making sure the returned Query Results from the backend cast numeric catalogNumbers to a numeric data type (thus trimming leading zeroes), while ensuring that catalogNumbers for CollectionObjects of a different CollectionObjectType with a non-numeric catalogNumber format are not implicitly cast as numeric.

---

This Issue does highlight a concerning flaw in the Reports and Labels which are exhibiting this behavior: even when this Issue is fixed, it is nearly guaranteed these instances will run into this Issue again without changes to the Reports/Labels when their maximum catalogNumbers reach a sufficient point (depending on the maximum width of the field which displays their catalogNumber).

In the case of Santa Barbara Museum of Natural History for example, if they no make no further modifications to their Standard Label (and likely other Reports/Labels) this Issue will happen again when their catalogNumbers exceed 5 digits (i.e., for every Collection Object after 99999 is catalogued).

Below is a video taken in v7.9.6.2:

insidious_issue.mov

[specifysoftware]

This issue has been mentioned on Specify Community Forum. There might be relevant details there:

https://discourse.specifysoftware.org/t/specify-7-10-2-release-announcement/2455/1