Hey all,
I’m trying to edit one of our labels in Jaspersoft and Sp7 to include fields from the PreparationAttribute table. Specifically, I want to have a string that includes a mixture of field values from the Preparation table and Prep Attribute table.
I want the values of each preparationAttribute associated with a prep to appear after the count of that prep in the formatted aggregation of preps on a given label.
I’m presenting a simplified example of my problem:
- Our mollusk collection has 2 main prep types,
wet, and dry.
- Each prep record has following relevant prepAttributes:
whole, bodyOnly, and separated.
whole = the number of specimens in a wet preparation which have their body and shell intact.bodyOnly = the number of specimens in a wet preparation which have had their shell removed (and stored dry), meaning only the soft body/bodies are stored wetseparated = the number of shells in the dry lot which were removed from specimens in the wet lot.dry preps are always null for whole and bodyOnly,wet prep are always null for separated- The sum of the prepAttributes always equals the count for the prep.
The desired outcome is to display the counts in the prepAttribute fields within a single string alongside the counts of preparations on the label.
e.g. “Wets - 10 (5 whole; 5 bodyOnly); Dry - 7 (5 separated)”. And to have conditional logic where there’s only 1 prepAttribute value. i.e.: “Wets - 10 whole”, if bodyOnly is null for a given record.
Since it’s not possible to create aggregated/formatted fields using text from multiple tables, table aggregation cannot be used to make the desired strings within specify itself. Currently, I’d only be able to present the Prep aggregate string: “Wets - 10; Dry - 7” and I’d have to add the prepAttribute fields individually elsewhere in the report, not within the prep aggregated string.
A related issue – to create the string I want manually in Jaspersoft, I need each preparation and prepAttribute field to be in the query linked to the label. But because of the one-to-many relationships involved, the query results contain many duplicate rows for each CO. This means the report will generate a label for each row, which is not desirable.
Is there a way around this? It seems generally useful to be able to display counts of parts of preparations on labels. I’m sure someone else must have made a report that does something similar in the past!
Hi @nfshoobs -
The desired outcome is to display the counts in the prepAttribute fields within a single string alongside the counts of preparations on the label.
e.g. “Wets - 10 (5 whole; 5 bodyOnly); Dry - 7 (5 separated)”.
I found in testing that the main limitation to achieving this format is the closing parenthesis. While you can select prepAttribute text and table format fields in the preparation table format, a static separator at the end of a format does not appear unless associated with a data-holding field. There is an existing enhancement request for this on GitHub, Support postfixes and static separators in table formats · Issue #6066 · specify/specify7 · GitHub.
If the order of fields in the preparation format isn’t crucial, you can sandwich Preparation Attributes (formatted) field between Count and Prep Type to enforce the separator.
<switch single="true">
<fields>
<field type="int" format="%d">countAmt</field>
<field sep=" (">preparationAttribute</field>
<field formatter="PrepType" sep=") ">prepType</field>
</fields>
</switch>
</format>
Preview: 2 (Wets: Whole Specimen - 2) Wets
The query results for this format aggregated:
I did not adjust any separators in this prepAttribute format, so they may need tweaking.
And to have conditional logic where there’s only 1 prepAttribute value. i.e.: “Wets - 10 whole”, if bodyOnly is null for a given record.
A conditional format is possible, although there are limitations
Thus, creating a conditional format based on the bodyOnly field is possible, but only in so far as associating an entered value with a custom format. However, the format will only include field separators and values if the field is not null, so it may be the case that the behavior you describe already happens within the prepAttribute format. It might help to share some example records showing preparation/prepAttribute formats either working or not working for you and why.
A related issue – to create the string I want manually in Jaspersoft, I need each preparation and prepAttribute field to be in the query linked to the label. But because of the one-to-many relationships involved, the query results contain many duplicate rows for each CO.
A resolution here may be finding a happy medium in table format customization given the info above to create an aggregated string that works around the non-distinct query setup you describe.
I hope this helps! Let us know what you think and of any other questions you may have 
1 Like
