Fields from the preparation table displaying as null in jaspersoft reports despite data

[markp]

Hello everyone,

v7.10.1

Finishing up some labels and running into what I think is either a bug (unlikely), or something I have missed (more likely). I have added 1,63-preparations.preparation.remarks to my report, in the same manner as any other field, but it is displaying as null in the report when rendered by Specify, despite there being data for that field. The same thing happens when I add other fields from the preparations table. Other tables are rendering as expected.

Label

 <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.21.2.final using JasperReports Library version 6.21.2-8434a0bd7c3bbc37cbf916f2968d35e4b165821a  -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Nigerian Birds" columnCount="3" pageWidth="612" pageHeight="792" whenNoDataType="NoPages" columnWidth="174" columnSpacing="14" leftMargin="30" rightMargin="30" topMargin="20" bottomMargin="20" uuid="8e2bd8fa-bd7c-47dc-9a89-1f48374f248f">
	<property name="com.jaspersoft.studio.unit.columnSpacing" value="mm"/>
	<import value="net.sf.jasperreports.engine.*"/>
	<import value="java.util.*"/>
	<import value="net.sf.jasperreports.engine.data.*"/>
	<field name="1.collectionobject.catalogNumber" class="java.lang.String"/>
	<field name="1,9-determinations.determination.alternateName" class="java.lang.String"/>
	<field name="1,9-determinations,4-preferredTaxon.taxon.Genus" class="java.lang.String"/>
	<field name="1,9-determinations,4-preferredTaxon.taxon.Species" class="java.lang.String"/>
	<field name="1,9-determinations,4.taxon.Subspecies" class="java.lang.String"/>
	<field name="1,10.collectingevent.startDate" class="java.lang.String"/>
	<field name="1,93.collectionobjectattribute.text1" class="java.lang.String"/>
	<field name="1,10,2.locality.localityName" class="java.lang.String"/>
	<field name="1,10.collectingevent.text6" class="java.lang.String"/>
	<field name="id" class="java.lang.Integer"/>
	<field name="resultsetsize" class="java.lang.String"/>
	<field name="1,93.collectionobjectattribute.text33" class="java.lang.String">
		<fieldDescription><![CDATA[Age]]></fieldDescription>
	</field>
	<field name="1,10.collectingevent.startDateNumericYear" class="java.lang.String">
		<fieldDescription><![CDATA[Start date year]]></fieldDescription>
	</field>
	<field name="1,10.collectingevent.startDateNumericMonth" class="java.lang.String">
		<fieldDescription><![CDATA[Start date month]]></fieldDescription>
	</field>
	<field name="1,10.collectingevent.endDateNumericYear" class="java.lang.String"/>
	<field name="1,63-preparations.preparation.remarks" class="java.lang.String">
		<fieldDescription><![CDATA[prep remarks]]></fieldDescription>
	</field>
	<background>
		<band splitType="Stretch"/>
	</background>
	<detail>
		<band height="58" splitType="Stretch">
			<rectangle>
				<reportElement mode="Transparent" x="0" y="0" width="170" height="56" uuid="8da4d884-b86a-4d13-aa6b-405d4357d3d2"/>
			</rectangle>
			<textField>
				<reportElement x="20" y="0" width="20" height="56" uuid="8af99c2d-b2bc-494a-a59e-0ce08a4995c0">
					<property name="com.jaspersoft.studio.unit.leftIndent" value="px"/>
				</reportElement>
				<textElement textAlignment="Center" rotation="Left">
					<font size="10" isBold="true"/>
					<paragraph leftIndent="2"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{1.collectionobject.catalogNumber}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="40" y="32" width="130" height="12" uuid="665c8d65-c4b4-40f5-9579-7608083ae5ca"/>
				<textElement verticalAlignment="Bottom" markup="styled">
					<font size="8"/>
				</textElement>
				<textFieldExpression><![CDATA["<style isItalic='true' isBold='true'>" + ($F{1,9-determinations,4-preferredTaxon.taxon.Genus}==null?"":$F{1,9-determinations,4-preferredTaxon.taxon.Genus}) + " "  + ($F{1,9-determinations,4-preferredTaxon.taxon.Species}==null?"":$F{1,9-determinations,4-preferredTaxon.taxon.Species}) + " " +  ($F{1,9-determinations,4.taxon.Subspecies}==null?"":$F{1,9-determinations,4.taxon.Subspecies}) +"</style>"]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="40" y="44" width="130" height="10" uuid="70017e64-cfb7-4716-9742-413d6aebbe5d">
					<property name="com.jaspersoft.studio.unit.spacingAfter" value="px"/>
				</reportElement>
				<textElement verticalAlignment="Bottom">
					<font size="8"/>
					<paragraph spacingAfter="0"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{1,9-determinations.determination.alternateName}]]></textFieldExpression>
			</textField>
			<staticText>
				<reportElement x="40" y="1" width="90" height="10" uuid="6a625620-1008-4e71-b11e-2caf3b714e61">
					<property name="com.jaspersoft.studio.unit.spacingBefore" value="px"/>
				</reportElement>
				<textElement>
					<font size="7" isItalic="true"/>
					<paragraph spacingBefore="0"/>
				</textElement>
				<text><![CDATA[Cowan Tetrapod Collection]]></text>
			</staticText>
			<textField>
				<reportElement x="100" y="21" width="70" height="10" uuid="319d4f86-6165-4306-827f-610fc862bd73">
					<property name="com.jaspersoft.studio.unit.rightIndent" value="px"/>
				</reportElement>
				<textElement textAlignment="Right">
					<font size="8"/>
					<paragraph rightIndent="3"/>
				</textElement>
				<textFieldExpression><![CDATA[($F{1,10.collectingevent.startDateNumericYear}==null?"":($F{1,10.collectingevent.endDateNumericYear}==null?(($F{1,10.collectingevent.startDateNumericMonth}.equals("1")?$F{1,10.collectingevent.startDateNumericYear} + "-Jan":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("2")?$F{1,10.collectingevent.startDateNumericYear} + "-Feb":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("3")?$F{1,10.collectingevent.startDateNumericYear} + "-Mar":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("4")?$F{1,10.collectingevent.startDateNumericYear} + "-Apr":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("5")?$F{1,10.collectingevent.startDateNumericYear} + "-May":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("6")?$F{1,10.collectingevent.startDateNumericYear} + "-Jun":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("7")?$F{1,10.collectingevent.startDateNumericYear} + "-Jul":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("8")?$F{1,10.collectingevent.startDateNumericYear} + "-Aug":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("9")?$F{1,10.collectingevent.startDateNumericYear} + "- Sept":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("10")?$F{1,10.collectingevent.startDateNumericYear} + "-Oct":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("11")?$F{1,10.collectingevent.startDateNumericYear} + "-Nov":"") + ($F{1,10.collectingevent.startDateNumericMonth}.equals("12")?$F{1,10.collectingevent.startDateNumericYear} + "-Dec":"")):$F{1,10.collectingevent.startDateNumericYear} + "-" + $F{1,10.collectingevent.endDateNumericYear}))]]></textFieldExpression>
			</textField>
			<rectangle>
				<reportElement x="0" y="0" width="20" height="56" uuid="b5c106b2-0b04-407e-affd-2376ce7cd830"/>
			</rectangle>
			<textField isBlankWhenNull="true">
				<reportElement x="130" y="0" width="40" height="18" uuid="dd4cf093-287d-492e-a34a-59cacd0b90a4">
					<property name="com.jaspersoft.studio.unit.spacingAfter" value="px"/>
					<property name="com.jaspersoft.studio.unit.rightIndent" value="px"/>
				</reportElement>
				<textElement textAlignment="Right" verticalAlignment="Middle" markup="styled">
					<font size="8"/>
					<paragraph rightIndent="5" spacingAfter="0"/>
				</textElement>
				<textFieldExpression><![CDATA[($F{1,93.collectionobjectattribute.text1}.equals("Male")?"\u2642":"") + ($F{1,93.collectionobjectattribute.text1}.equals("Female")?"\u2640":"") + " " + ($F{1,93.collectionobjectattribute.text33}==null?"":$F{1,93.collectionobjectattribute.text33})]]></textFieldExpression>
			</textField>
			<line>
				<reportElement x="37" y="0" width="1" height="56" uuid="d3d8db73-66e7-430e-9a7f-d9dfd7554235"/>
			</line>
			<textField>
				<reportElement x="40" y="11" width="70" height="10" uuid="99b8b425-5713-49f0-a20a-16c489817aaa"/>
				<textElement>
					<font size="6"/>
				</textElement>
				<textFieldExpression><![CDATA[$F{1,63-preparations.preparation.remarks}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
</jasperReport>

Query:

Label rendered:

[NielsKlazenga]

Hi @markp, not sure if this is the case here, but I get this when there are things like ‘&’ in the data. Maybe it works if you escape them?

[Grant]

Hi @markp,

Are you still having this issue? Can you share the name of the label you are working on so I can investigate it more closely?

[markp]

Hi Grant,

Yes, unfortunately haven’t been able to solve it as of yet. The problematic field is below.

$F{1,63-preparations.preparation.remarks}

The report is Nigerian Birds - Front, within the Birds Collection of the Tetrapods discipline. Feel free to use B000038 to test with, as it has a value in that field.

I have checked against the the Datamax Jar Labels example in Report & Label Examples to make sure that the syntax for the preparation field is the same, and can find no difference.

@NielsKlazenga apologies did not mean to ignore you! Thank you for your reply! This problem went on the backburner for a bit because I wasn’t making any progress towards a solution. I don’t think that & is the issue in this case, however that is helpful to keep in mind for the future!

[Grant]

Hi @markp,

The query associated with that report, named “Birds Label - Frontside” (/specify/query/217/), does not have the ‘Preparation - Remarks’ field included, leaving the report unable to present the data from that field.

Even if you add the string ID to the report (e.g. $F{1,63-preparations.preparation.remarks}), if the data is not returned by the query associated with the report, the report runner will display null in its place.

If I add the preparation.remarks field to the query, it increases the number of returned results (from 23411 to 23532) since there are cases where more than one preparation is linked to a Collection Object.

To show only the remarks for the preparation while still leaving the preparations aggregated, you could prepare a table format and aggregation that only displays the “Remarks” field value for all preparations and select it explicitly in the query builder for this report only:

Query Builder in Specify 7
Some tables can be aggregated or formatted, outputting the structure specified in the schema once queried. If multiple formats or aggregations have been defined for one table, you may choose your preferred format/aggregation by clicking the (gear) icon. Otherwise, the default record formatter indicated in the schema will be used automatically. Learn more about record formatters here.

Would you like me to update this query directly? I can also help set up the table format and aggregation for this purpose!

[markp]

This is something that I should have thought of! I can take it from here, and thanks so much for your help!