Use post-processed query results in query exports

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”).

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

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)

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

@chanulee1

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.

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

@grantfitzsimmons

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

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

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

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)

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.

Navigation Menu

Repository navigation

Use post-processed query results in query exports #2814

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Use post-processed query results in query exports #2814

Description

Activity

specifysoftware commented on Jan 13, 2023

maxpatiiuk commented on Jan 13, 2023

maxpatiiuk commented on Feb 7, 2023

maxpatiiuk commented on Mar 22, 2023

maxpatiiuk commented on Mar 22, 2023

maxpatiiuk commented on Mar 27, 2023

maxpatiiuk commented on Mar 27, 2023

grantfitzsimmons commented on Jun 21, 2023

maxpatiiuk commented on Jun 29, 2023

grantfitzsimmons commented on Jun 17, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions