Help in Parasites with vial label form transfer or new? Arctos Report Template Request

[msbparasites]

Summary:

• MSB:Para_vial_label_small
  • Report: https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=154
  • Status: SQL written, needs confirmed
  • Layout template: https://github.com/ArctosDB/arctos/files/14016408/MSB.Para_Host.vial.labels.for.arctos.pdf
• MSB:Para_vial_label_small
  • Not started, adapt from MSB:Para_vial_label_small when complete
• MSB:Host_vial_label_small
  • Not started, should be same SQL as MSB:Para_vial_label_small (one uses sex and one uses verbatim_host_ID, it's a small cost but large simplification to pull but not use one of those for each label)
• MSB:Host_vial_label_large

  • Not started, will eventually be adapted from MSB:Host_vial_label_small

original:

Description of the report - [Write a brief description of the report you need. Include link or name of previous report if migrating from a Cold Fusion report.] I have vial labels that I print all of my specimen labels. I would at the very least like this one, but it needs a redo, but not sure when to do that. I can also get together with @campmlc ?

msb_para_4dram_label_origID.cfr | MSB_Para_4dram_Prep | 2023-12-20 (-26 days) -- | -- | --

Collection for which the report is needed - [Enter the GUID prefix of the requesting collection] MSB:Para

If this is a request to upgrade from an existing ColdFusion report in Arctos complete the following:

SQL used in ColdFusion report - copy the SQl from your old report and paste it here - begin and end it with ``` or it will be invisible. Let us know if there is a prefunction to migrate.

select
    concatsingleotherid(flat.collection_object_id,'UIMNH: University of Illinois Museum of Natural History') user_id_num,
    'UIMNH' user_id_type,
    scientific_name,
    case
    when trim(flat.sex)='male' then 'male'
    when trim(flat.sex)='female' then 'female'
    else '?'
    end as sex,
    flat.parts,
    cat_num,
    state_prov,
    country,
    quad,
    county,
    island,
    island_group,
    sea,
    feature,
    spec_locality,
    flat.verbatim_coordinates,
    MAXIMUM_ELEVATION,
    MINIMUM_ELEVATION,
    ORIG_ELEV_UNITS,
    flat.collectors,
    flat.preparators,
    concatotherid(flat.collection_object_id) as other_ids,
    concatsingleotherid(flat.collection_object_id,'collector number') collector_number,
    concatsingleotherid(flat.collection_object_id,'preparator number') preparator_number,
    concatsingleotherid(flat.collection_object_id,'NK') NK,
    concatsingleotherid(flat.collection_object_id,'original identifier')  original_identifier,
    verbatim_date,
    began_date,
    ended_date,
    flat.habitat
FROM
    flat
WHERE
    flat.collection_object_id IN (#collection_object_id#)
ORDER BY
    NK

Priority - [Please assign a priority so that the DBA knows whether there is any urgency associated with the new template} ASAP since legacy is going away

[dustymc]

@msbparasites please see https://github.com/ArctosDB/arctos/issues/7240#issuecomment-1888116711 - this looks like more variations of the same SQL, can we (somehow) unify? @mkoo not sure where the conversation has gone in 7240, perhaps we can merge?

[msbparasites]

There was a host one as well,

msb_host_4dram_label_origID.cfr	Clone_Of_msb_host_4dram_origID_1677889

But I don't know which one was the one that worked. I am happy to work with Mariel @campmlc ? She and I worked on the original labels together a long time ago. And perhaps given where we are right now, this is a good time to make changes we both need for different label options? Sorry, I really don't know how to do this to make it easier for you all.... But will do as suggested.

[dustymc]

different label options?

We can accommodate whatever, if there's no need to eg be uniform in encoding sex (and maybe there's not, different labels get different representations?) then we can just use the old SQL. If ya'll would like more consistency then now seems like a good time and we're happy to help.

Clone_Of_msb_host_4dram_origID_1677889

Here's the SQL:

select
    scientific_name,
   case 
when trim(flat.sex)='male' then 'male'
when trim(flat.sex)='male' then 'male'
else 'U'
end as sex,
    flat.parts,
    cat_num,
    state_prov,
    country,
    quad,
    county,
    island,
    island_group,
    sea,
    feature,
    spec_locality,
    flat.verbatim_coordinates,
    MAXIMUM_ELEVATION,
    MINIMUM_ELEVATION,
    ORIG_ELEV_UNITS,
    flat.collectors,
    flat.preparators,
    concatotherid(flat.collection_object_id) as other_ids,
    concatsingleotherid(flat.collection_object_id,'collector number') collector_number,
    concatsingleotherid(flat.collection_object_id,'preparator number') preparator_number,
    concatsingleotherid(flat.collection_object_id,'NK') NK,
    concatsingleotherid(flat.collection_object_id,'original identifier')  original_identifier,
    verbatim_date,
    began_date,
    ended_date,
    flat.habitat,
case 
  when concatsingleotherid(flat.collection_object_id, 'NK') is not null then 
    concatsingleotherid(flat.collection_object_id, 'NK')
  when  concatsingleotherid(flat.collection_object_id, 'AF') is not null then 
    concatsingleotherid(flat.collection_object_id, 'AF')
  when  concatsingleotherid(flat.collection_object_id, 'IF') is not null then 
    concatsingleotherid(flat.collection_object_id, 'IF')
  when  concatsingleotherid(flat.collection_object_id, 'collector number') is not null then 
    concatsingleotherid(flat.collection_object_id, 'collector number')
  when  concatsingleotherid(flat.collection_object_id, 'original identifier') is not null then 
    concatsingleotherid(flat.collection_object_id, 'original identifier')
  else null
end as someAlias
FROM
    flat
WHERE
    flat.collection_object_id IN (#collection_object_id#)
ORDER BY
    NK

[msbparasites]

First, if I want some help with this form so that I can start printing labels soon, can I request it here or put in a new ticket? Second, can I make some comments on the above SQL file for different fields or orders of fields?

[dustymc]

@msbparasites happy to work with whatever works for you. (One issue can get overwhelming, multiple can get scattered, I never know what's best...) Yes getting the SQL is the first step from here, what do you need? The order of the columns is independent of print layout, but we need to get the contents down.

[msbparasites]

Here is attempt #1 to give you as much information as I can without knowing computer speak....(I tried working in the SQL....at got confused.....)

Attached are 2 files. one is a pdf that has a template of what I would like them to look like now.

In the pdf, the top 2 are for Parasites (one with actual data and one with the Arctos fields) and the bottom 2 are for Hosts.

The image is what at least the Parasite labels look like now. MSB Para_Host vial labels for arctos.pdf

In the section that is in red (identifier suite...) this is related to the if then case in the SQL where if NK then use it, if not then AF, if not and so on. And I can make this more specific if you want. However, I always want to have the collector number, even if there is an NK, or Institutional identifier.

The size of these labels would be 4cm length and 0.9cm height.

Regarding the size, is there a way to choose the size I want if I have 2? This would help with larger jars where these little ones get lost. But I have WAY more of the small vials so if not possible, ok.

If I did have a choice for larger label, I would like 8cm length and 1.8cm height

File label would be (how it is written doesn't matter to me.....): MSB:Para_dramvial_label MSB:Host_vial_label

To make things simple, if I can have two sizes: MSB:Para_vial_label_small MSB:Para_vial_label_large

MSB:Host_vial_label_small MSB:Host_vial_label_large

So, at least you have an image of where I would like to be, now let me know what I need to and help you out better...THANK YOU!

[dustymc]

@msbparasites thanks, that PDF is perfect!

Find something then...

... then ....

It should dump out a table with data. I think it's all the same for your two reports, except one will use sex and the other verbatim_host_id. Let me know if anything needs changed. Once that's happy, we can get started on the layout (and - assuming my presumption about the SQL holds up - that first report can serve as a template for the other three).

[msbparasites]

Wow this is exciting! I looked up parasites and then opened the file:

--For the Parasite labels, could you please add attribute "location in host" It didn't show up on the table? --What is end with "preferred_identifier"? --Can I have order by MSB:Para:1234 catalog number (this will be same request for host, order by MSB:Host:1234)?

Parasite label attributes: verbatim host ID and location in host Host label attribute: sex

When I looked up some hosts and got the table, that looked good! Thus, I think at this point it looks like we can keep moving??

[dustymc]

"location in host"

Added.

preferred_identifier

First found of type:

• NK
• AF
• IF
• institutional catalog number

using function concatsingleotherid

order by MSB:Para:1234

Changed sort to catalog number as integer.

keep moving

Yep, sounds right, I think there might be some minor adjustments but as long as the data columns are (fairly) solid I think we can move. I'll add my (shiny and new) label, @lkvoong @mkoo I think we're ready for layout, there's a summary in the first comment.

[msbparasites]

Checking in to see when I might be able to start printing specimen vial labels to keep work moving? Not desperate yet, but please let me know expectation. Thanks!

[campmlc]

@mkoo @lkvoong

[msbparasites]

Checking in to see when I might be able to start printing specimen vial labels to keep work moving? I have vials piling up now particularly for the hosts, less so for parasites yet. Can we please move this up? Or what do I need to do to help with this? Thank you very much.

[campmlc]

@lkvoong @dustymc @mkoo

[campmlc]

@mkoo @Jegelewicz please help - this is actively impeding an entire collection's workflows!

[Jegelewicz]

I can drop everything I am working on and do this - I just want to know that is what everyone wants me to be doing....

[Jegelewicz]

What size paper are these being printed on?

[msbparasites]

Right now it is the datamax printer in Fishes/Herps. I am not there right now, but previously when I printed from Arctos, I set up the paper size as 4.25 in X 11.0 and it printed about 35-ish vial labels at a time on a sheet. I don't know what determines it.

[Jegelewicz]

The datamax is not working for things - but I will give it a try. When they print, how does it look on the paper? One column of 35 labels? two columns of 16-ish?

[Jegelewicz]

Also what orientation are they printing?

[msbparasites]

Printing landscape, probably 5x8 so 40-ish labels? Ha, I never thought about it....

[Jegelewicz]

What is SSpec Id?

[msbparasites]

That is a prefix that the USGS Pathology collection uses for their samples that they send to us to deposit. I copy it verbatim.

[Jegelewicz]

Where do I find that in Arctos?

[msbparasites]

I looked under MSB:Para then searched SSpec in "value" of the identifier cells and it gave me a list of those specimens for which the prefix is SSpec Id. Is this what you were asking?

[Jegelewicz]

I'm trying to figure out where that bit of information should come from in Arctos so that I can place it in the label. Is it an identifier?

[Jegelewicz]

Oh wait - you just told it me it is an identifier. Will all the parasites have one? If not, should something else go in that spot on the label?

[campmlc]

It is part of the identifier value: NWHC-29421-001 SSPec ID 19020 as an example, and these are recorded as institutional catalog numbers?

[Jegelewicz]

Yeah - I think I figured that out - but I don't have any way to separate that single identifier so the whole thing is what will show.

[Jegelewicz]

There is a label started (MSB:Para_vial_label_small), but this one will have the same issues that the UCM label does - PDF does not recognize a paper size of 4 x infinity or even 4 x 11, so the labels will end up too small and half the paper will be blank. I cannot see any way around this issue. In any case, there is something started and I will continue to investigate options for the odd sized paper.

[campmlc]

The previous report for these size labels printed on the MSB Datamatrix printer should have the specifications - that report is archived and available to @dustymc, correct? We are just trying to convert a previous report? @jtgiermakowski

[msbparasites]

Well that one is an odd one because most of my specimens are labeled by collector number and that is what I prefer. However, I remember that there was space on the labels for "IF" "THEN" so collector number was always there, but then there was another spot for NK, if not, then identifier, if not then, XXX, if not then YYY. However, since we use identifier for so many things that used to be identified as something else, I don't know how well it would work. For the case of the SSpec ID, the SSPec ID is for the individual and is the most useful in terms of what worm in in what vial. The other numbers are like accession host numbers, that all the SSpec ID from a particular host are under.

[campmlc]

So I think what we had is the ability to specify certain types of identifiers in a particular order: e.g. IF collector number use that, if not, use NK, if not, use AF, if not, use IF, etc. That would still be ideal, although with the different identifier types now, we might have to use issued by in some cases? I honestly don't know how that would work under the new model of identifiers. The identifier referenced previously is in Arctos as both an institional catalog number and a collector number - see attached.

[Jegelewicz]

ability to specify certain types of identifiers in a particular order

that is built in, but those SSpec numbers seem to be combined with some other number, so they may come out strange.

I decided to go with a thing I know and made some labels using the Word mail merge. They aren't perfect, but I can at least know they will print correctly (a test would be good though). Here is a sample.

MSB_Para_label_small_test.docx

[Jegelewicz]

Sizes are tricky because Word expects inches. The paper size is 4 x 6, so I can fit two labels across and about 16 down given the sizes in mm converted to inches. These are SUPER small which makes selecting fonts hard too. I am sorry this is such a pain, but I am flying blind with no way to test the output....

[campmlc]

The font for the MSB and the scientific name is really large compared to the small Locality and ID fonts. Can that be tweaked? The previous report in Cold Fusion Report builder was also super small font very trickiy to get just right, especially since to print on Datamatrix the paper is very costly to do lots of test runs. @msbparasites Do you need verbatim host ID, location in host? I thought Dusty said that was possible?

[Jegelewicz]

verbatim host ID, location in host

Those things are in the data - but I don't see them on the sample labels, so they aren't used. Do you guys have the old ColdFusion reporter files? I can see what fonts were used in them if you do.

[campmlc]

I don't know if I downloaded them - do you have a copy, Sara? @dusty should have backup files.

[mkoo]

Hi all, jumping in here: I also see data entry inconsistencies ( eg values of SSpec, SSPec, Sspec, etc-- Teresa has a file based on this query-- select display_value, other_id_type from coll_obj_other_id_num where display_value ilike '%SSpec%'). So if all agrees, Teresa can work with you all directly and fix so the data entry of display value and id_type and Issuer are consistent and predictable. Otherwise the labels will never be easy to work with. I suggest direct emails or a quick call together might be most efficient. Keep this open until completed!

[Jegelewicz]

@mkoo that is not really a concern yet - I need to get a label that works, then we can clean up data.

[campmlc]

Here are the cfr files from 2020 for these vial labels:

msb_host_4dram_label.zip

[Jegelewicz]

@msbparasites I now have two possible solutions for your labels.

1. use the print to pdf - MSB:Para_vial_label_small
2. download csv and print with Word Mail merge - MSB:Para_vial_label_csv (Instructions and the merge template can be found here.

I am sure that neither of them exactly meets your needs, but I can't really go any further without feedback. Once we have this label figured out, I can help create a larger version and versions for hosts.

[msbparasites]

Hi Teresa, I think we might need to meet and work on this? I tried to follow your directions and got a mess. Many of the fields don't exist, for example HIGHER-GEOG and if I select an acceptable one that it asks for, I can only select one, such as country. But can't select county for example. Then it doesn't DEC-LAT/DEC-LONG. So fine, for the purposes of testing, I selected the items but it asked me for every specimens, then it said that it was all invalid anyway and I had to force quit Word. I looked at the sample labels in Word that you sent and for now the set up looks ok, thought the date should be YYYY-MM-DD, and a few other things, but no worries right now as I know we are just trying to get to the point that I can print labels. Please advise on what to do next?

[Jegelewicz]

@msbparasites opening the merge file is a process. Did you use the instructions above? The merge document has to be associated with the data in excel (or csv) in order for it to work right. I could meet up to walk through and see what is going awry. Do you have any time this afternoon to zoom?

[msbparasites]

Sorry Teresa, I was out. I did use the instructions. My data was pulled in. In the merge, what was in the template did not match what was in the input. Also, I saw the pdf, but was not sure how to print to the datamax as the labels need to be printed on the thermal paper.

[campmlc]

@msbparasites I know Tom is out, but maybe we can get info from Emily about the format for printing on the Datamax? Can we just send a word doc or pdf to it in the right dimensions?

[Jegelewicz]

I know the pdf version is not going to work right because of the work we have done on UCM labels. I can zoom with you guys next week - or maybe you can come to Arctos Tea on Tuesday at 3:30PM MST?

[Jegelewicz]

Also, I can print to Word from the template that is set up - just pass me search results for the labels you need and I can send you a Word document - just temporary so that you can get something done before I can come meet with you....

[msbparasites]

Sadly I cannot make Arctos tea this semester. I like the idea of attempting a temp file to print on datamax from pdf? Then maybe meet in person next week? Thoughts?

[Jegelewicz]

Sounds good - let me know what time next week.

You can just print the pdf version to see what happens. If you want to try the Word mail merge, send me a saved search and I'll make the Word document for you to try.

[msbparasites]

Here is a link to the saved search for labels. It is for HOSTS, but I can create a parasite one too if that is easier.

https://arctos.database.museum/saved/MSB%20Host%20trial%20labels