Format query results on the backend

[grantfitzsimmons]

Most recently, see https://discourse.specifysoftware.org/t/pick-list-items-in-jaspersoft-labels/3155/2?u=grant:

Thanks for your question! This behavior has to do with how the Query Builder exports the values rather than the titles for pick lists.

For example, with the Agent Variant pick list:

A query will show “Variant” and “Author” in the results:

But when using the Create CSV button without selecting any results, it saves to my system with the “values” and not the “titles” for the pick list:

First Name,Last Name,Var Type,Variants
Grant,Fitzsimmons,0,Variant Name
Grant,Fitzsimmons,2,Author name

The Report & Label system effectively presents the results of a query export in a structured manner, inheriting this behavior as well.

See #5713 (comment) from @maxpatiiuk:

The heading issue is fixed in #2769.

The rest is because front-end does fancy formatting of the data it received from the back-end. The export is coming from the back-end so it shows you the raw data front-end receives.

A possible solution would be to offload some formatting from the front-end to back-end. Another solution would generate csv export on the front-end (though that would only work for records that are already loaded)

Also #5713 (comment):

Related to #1144 as both concern SQL generation for formatting/aggregating

Once this is fixed, front-end formatting of query results can be removed (which would also result in slight performance improvements)

From Garth at The University of Michigan:

We are observing an issue in how picklists are handled in labels & reports in the herbarium database in Specify 7. This issue is confined to pick list fields, as displayed in Specify 7. Specify 6 is displaying as intended.

Picklist values are being displayed in Specify 7 output, whereas picket titles are displayed in Specify 6. Here's an example of the reports output and the underlying xml report code.

From @sharadsw:

Changes made in #5417 can potentially fix issues with agent type and picklist formatting.
See: #5417 (comment)

Discussed in #5713

^{Originally posted by grantfitzsimmons January 13, 2023}

query_results_2023-01-13T19 25 25.499648.csv

Plarson:

I use the audit log query to gather data for quarterly reports. The query results display nicely in SP7 but when I export the results, much of it is unreadable and I spend a lot of time doing 40-50 find/replace operations to convert table numbers to the table names so that I can see what’s been done over the last 3 months. It would be more useful to export the table names. Likewise with the actions and field names (user-created caption vs, e.g., “text1”).

[specifysoftware]

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

https://discourse.specifysoftware.org/t/pick-list-items-in-jaspersoft-labels/3155/2

[grantfitzsimmons]

From @ZsPapp at Natural History Museum of Denmark:

I considered to use an expression for this field, as you suggested, but I was a bit reluctant given this pick list has 33 values and it’s not even read only…
So it was worth a shot, but at the end I set up an expression for all the values/titles anyway. :slight_smile: I’m still hoping for a nicer solution, but it works afterall. 😃