Use post-processed query results in query exports

[grantfitzsimmons]

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/audit-log-export-displays-query-result-values/990/3

[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)

[maxpatiiuk]

This issue also manifests itself with Agent Type. See #2954

[maxpatiiuk]

From @chanulee1:

Agent type not displayed as expected in qcbx search results.

mIlF2yyx5P.mp4

https://coldfish-xml-editor.test.specifysystems.org/specify/view/collectionobject/61610/

From @maxpatiiuk:

This is because when the back-end does formatting, it uses pick list item value, rather than the title - not sure how simple that would be to fix (because it constructs a SQL query to do the formatting - it's very performant, but also very feature limited)

In the preview, front-end formatting is used, which supports more features.

[maxpatiiuk]

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

[maxpatiiuk]

Yet another outcome of this issue:

From @grantfitzsimmons:

This should be using the title. This is on the UWFC label in the uwfc database.

($F{1,9-determinations.determination.typeStatusName}==null?"":$F{1,9-determinations.determination.typeStatusName})

To make it so that I could make this string uppercase at the start, I wrote the following expression for Jaspersoft Studio:

($F{1,9-determinations.determination.typeStatusName}==null?"":($F{1,9-determinations.determination.typeStatusName}.substring(0,1).toUpperCase()+$F{1,9-determinations.determination.typeStatusName}.substring(1,$F{1,9-determinations.determination.typeStatusName}.length()).toLowerCase()))

[maxpatiiuk]

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

[grantfitzsimmons]

This has come up again with Willem at SAIAB.

For reference, here are the numbers for each action in the SpAuditLog:

ID	Action
0	Create
1	Update
2	Delete
3	Merge
4	Move
5	Synonymize
6	Desynonymize

[maxpatiiuk]

Partial work-around:
Caroline added ability to export only selected rows.
That export is done on the front-end - and if I remember correctly, it is done on the formatted data (or if not, won't be hard to change it to work on formatted data)

[grantfitzsimmons]

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.

[sharadsw]

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