Reports help: adding all collectors and identification_1_agent_1

[adhornsby]

I'm trying to match the following report to our legacy labels. https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=135

I'm stuck on two things:

1. Getting all of our collectors (agents and verbatim) into line "Collectors:"
2. Getting identification_1_agent_1 into line "ID by:"

Any help would be appreciated.

Related -- is there a master list of fields that I can reference when I'm trying to add things to reports?

[dustymc]

A few questions:

Are you trying to exclude records with no parts? The current SQL does that, I'd think that would not be desired.

You're pulling eg quad, I suspect it's not what you think, is that by design?

Maybe it would be best to start by telling me what you want?? Or I can roll with what you have, or whatever, but there are lots of little subtle places where we can get lost from here.

Do you want all collectors regardless of role? And all attribute value where type = verbatim agent? Any sorting or organization?

The bulkloader (flat) doesn't map to the DB (very not-flat) - there's not really an identification_1_agent_1. I can get the first agent of an arbitrary 1-ranked ID, or the first agent of the best-ranked ID, or ???? Or flat.identifiedby might be what you want.

master list of fields

See above - not really, Arctos is a complicated structure, there's not anything that looks like a spreadsheet hiding under there anywhere. There is FLAT, it might be useful for labels, writesql and select * from flat where guid='ABX:XYZ:123'

[Jegelewicz]

is there a master list of fields that I can reference when I'm trying to add things to reports?

Sort of - see https://arctos.database.museum/tblbrowse.cfm

[mkoo]

Related -- is there a master list of fields that I can reference when I'm trying to add things to reports?

Oh yes! See attached for fields in flat flat_variables.csv

You can do this yourself with tips in this Report Tutorial which is still being expanded so not yet in handbook but it is linked at the top of the page on the Report page in Arctos

[adhornsby]

@dustymc Well you identified one of the problems, which is that I frankensteined this together from other reports! I'm just trying to get something functional and will circle back later to clean it up (famous last words). The screen grab at bottom is what I'm aiming for, with the addition of the "collector" and "ID by" lines.

Are you trying to exclude records with no parts? The current SQL does that, I'd think that would not be desired.

No, I don't mean to exclude records with no parts. Oops!

You're pulling eg quad, I suspect it's not what you think, is that by design?

Nope, just a frankenstein artifact. I deleted it.

Do you want all collectors regardless of role? And all attribute value where type = verbatim agent? Any sorting or organization?

On the "Collector" line, I want everyone who is either agent with role=collector or attribute verbatim agent with method=collector. No particular order.

The bulkloader (flat) doesn't map to the DB (very not-flat) - there's not really an identification_1_agent_1. I can get the first agent of an arbitrary 1-ranked ID, or the first agent of the best-ranked ID, or ???? Or flat.identifiedby might be what you want.

On the "ID by" line... I'm just now seeing that our ID people were loaded as verbatim agents. So on this line I would want everyone who is attribute verbatim agent with method=identifier. No particular order. (flat.identifiedby would have been exactly what I was looking for to call up identification_1_agent_1, if my ID people were in the right place. Problem for another day.)

Huge thanks!

[dustymc]

I changed some stuff, give it a go.

Here's the pre-update version. BELL fish v2.csv.zip

[adhornsby]

Nuts... I very unhelpfully overwrote the changes. Are they easy enough to recreate @dustymc ? Sorry.

[dustymc]

I overwrote the overwriting.

[adhornsby]

That does it, thanks @dustymc !

[adhornsby]

@dustymc Your new tweaks worked, but it looks like part_count broke in the process. I'd like for part_count to be the number of whole organisms in the lot (part_1_count).

This was working in my previous version using "sum(specimen_part.part_count) as part_count". Now with "pc.pc as part_count" it's giving the number of types of parts in the record. When I tried to just change that line, I get caught in a vortex of errors way beyond my skill level!

Advice?

[dustymc]

It should be doing what it was before, but I added the filter.

Here's the SQL, Just In Case:

 select
    flat.individualcount,
    flat.parts, 
    pc.pc as part_count,  
    flat.collection_object_id,
    flat.cat_num,
    flat.scientific_name,
    flat.family,
    flat.state_prov,
    case when flat.country='United States' then 'USA' else flat.country end country,
    flat.county,
    flat.sea,
    flat.spec_locality,
    replace(flat.verbatim_coordinates,'/','<br>') Coordinates,
    concatLocalityAttributeValue(locality_id,'TRS township') as tn,
    concatLocalityAttributeValue(locality_id,'TRS range') as rn,
    concatLocalityAttributeValue(locality_id,'TRS section') as sc,
    flat.locality_remarks,
    flat.dec_lat,
    flat.dec_long,
    flat.collectors,
    flat.sex,
    flat. othercatalognumbers other_ids,
    flat.parts,
    flat.verbatim_date,
    flat.ACCESSION accn_number,
    flat.attributes,
    'AF'|| concatsingleotherid(flat.collection_object_id,'AF') ||', ' as af,
    concatsingleotherid(flat.collection_object_id,'other identifier') as oids,
    vcol.collectors,
    vidr.identifiers identifiedby
  FROM
    flat
    inner join #table_name# on flat.collection_object_id=#table_name#.collection_object_id
    left outer join (
    select derived_from_cat_item,count(*) pc from specimen_part where part_name='whole organism' group by derived_from_cat_item
    ) pc on flat.collection_object_id=pc.derived_from_cat_item
    left outer join (
    select collection_object_id, string_agg(cl,', ') as collectors from (
    select collection_object_id,attribute_value as cl from attributes where attribute_type='verbatim agent' and determination_method='collector'
    union 
    select collection_object_id, getPreferredAgentName(agent_id) as cl from collector where collector_role='collector'
    ) x group by collection_object_id,cl
    ) vcol on flat.collection_object_id=vcol.collection_object_id
    left outer join (
    select collection_object_id, string_agg(idr,', ') as identifiers from (
    select collection_object_id,attribute_value as idr from attributes where attribute_type='verbatim agent' and determination_method='identifier'
    ) x group by collection_object_id,idr
    ) vidr on flat.collection_object_id=vidr.collection_object_id
  order by flat.scientific_name,flat.cat_num::numeric

[adhornsby]

Everything looks good except for the "Specimens: #" near the upper right. On the original they gave the lot size from part_1_count, and now they all just show 1 as in one part on the record (a few records show 2 if they have two separate lots of whole organisms with different preservations). I assume the change from "sum(specimen_part.part_count) as part_count" to "pc.pc as part_count" is what's driving this?

Original (specimen counts correct = lot size)

Current (specimen counts are now just the number of separate parts on the record)

[dustymc]

part_1_count

That doesn't exist here, that's only for the loader, pre-actual-Arctos, there's no such thing once the record loads. Current code is for....

I'd like for part_count to be the number of whole organisms i

If that's not what you want, you can just drop the ....

 where part_name='whole organism'

to get back to " the number of types of parts in the record." - which seems to also not be what you want, I'm very lost on this, please let me know what it is that you want to see (but not involving things that don't exist!) and I'll write code for that!

[Jegelewicz]

@adhornsby it sounds like you want Arctos to come up with individual count, which it cannot do because it is complicated. However, it also sounds like we could sum all of the lot_count for any part name = whole organism in the record and get what you are looking for?

[adhornsby]

@Jegelewicz @dustymc Yes, I think sum of lot_count for parts = whole organism would do what I'm looking for? I'm trying to get the number in the part "Qty" field of the specimen record to show up on the "Specimens: #" line of the report.

E.g. https://arctos.database.museum/guid/JFBM:Fish:50405 has 7 whole organisms, so I want the label to show Specimens: 7.

[dustymc]

would do what I'm looking for

Report updated.

[adhornsby]

That does it -- thank you @dustymc !