Arctos Report SQL Request: conditional SQL for Identifier IssuedBy, Value based on Type (MVZ Bird labels)

[mkoo]

Hi @dustymc We are need of some SQL magic for MVZ Bird label (https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=180)

Right now we have:

case 
			when cn.n is not null then cn.n 
			when pn.n is not null then pn.n 
			when pl.n is not null then pl.n 
			else null 
		end as collectornumber,

(translated as: show collector number, preparator number or PLC number in this order)

What we need is a different set of conditions so that the identifier value shown matches the name on the label.

So if identifier type = collector, preparator, then show identifier value as collectnumber AND identifiedBy name is collectorname. (We want the name and the number to match from the same identifier)

(and to make it a little more complicated) if identifiedBy name = "MVZ Prep Lab Catalog" (or even identifier value contains 'PLC') then make collectorname the same as the preparator name. (because in these cases, we do NOT want the collectorname to be "MVZ Prep Lab Catalog" but rather the person who prepared it.

All other edge cases we will manually edit (since they are rare). Does that make sense? if not, happy to explain! Thanks!

[dustymc]

An example or two would be super useful - "for {guid} print {whatever}"

[mkoo]

For MVZ:Bird:186793, print MVZ Bird Narrow (https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=180) so that #collectorname# is Carla Cicero and the #collectornumber# = 4612 even though she is listed as preparator. (There is typically only one identifier listed as collector or preparator but not both-- it does happen but rare). In this case there is a collector but no identifier associated with the specimen.

In the case of PLC numbers, https://arctos.database.museum/guid/MVZ:Bird:183073 Print the PLC identifier value but list the preparator as the #collectorname#

Let me know if this makes more sense

[dustymc]

I think I'm getting loster....

For https://arctos.database.museum/guid/MVZ:Bird:186793

IF agent in some collector role == agent who issued some identifier THEN promote them to collector (and ignore the actual collector????)

That can't be right....

For https://arctos.database.museum/guid/MVZ:Bird:183073

IF identifier issuedby==MVZ Prep Lab Catalog THEN null ELSE identifier issuedby ???

Zoom?

[mkoo]

yea loster is not good! let me email you a time....

[dustymc]

Try this:

change

select collection_object_id, string_agg(display_value,', ') n from coll_obj_other_id_num where other_id_type='collector number' group by collection_object_id

to include issued by agent

do something with it on the label

[dustymc]

Updated https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=180

MVZ Bird Narrow box label.csv

Variables

• collector_number_issued_by
• preparator_number_issued_by

should do what you need.

[dustymc]

I think this is all happy and implemented, please reopen if I'm lost.