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 #7240 (comment) - 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.

[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

[Jegelewicz]

The label template I created is for parasites, so these may look weird or be missing something, but here are the labels from those search results.
MSB_Para_Word_labels.docx

[dustymc]

Closing per time since last activity, please let us know if more help is needed.

[campmlc]

Re-opening as this is a priority for MSB as was discussed with @mkoo yesterday. The label still needs adjustments to be functional. @msbparasites

[mkoo]

Due to the Mariana trench deep comments here this issue will be closed; please refer to #8437 where the web developer will be paying attention