Arctos Report Template Request - CHAS:Herb

[wellerjes]

Could someone please help me with creating a new template for CHAS:Herb?

Description of the report We have been using the mail merge, but since Excel likes to reformat dates/numbers, I would like to get the new reporter setup so we don't have to spend as much time reformatting and checking for errors.

Collection for which the report is needed CHAS:Herb

SQL used in ColdFusion report This is the original, but it no longer works when I try to pull use the old reporter: CHAS_label_herb.cfr

  scientific_name,
  family,
  coll_event_remarks,
  habitat,
  CASE WHEN verbatim_coordinates IS NULL THEN '' ELSE CONCAT('Verbatim coordinates: ', verbatim_coordinates) END coordinates,
  CASE WHEN identifiedby = 'unknown' THEN '' ELSE CONCAT('Det. ', identifiedby) END formatted_identifiedby,
  CASE collection_id WHEN '124' THEN 'BOTANY COLLECTION' WHEN '144' THEN 'TEACHING COLLECTION' ELSE NULL END collection,
  CASE WHEN CONCATSINGLEOTHERID(flat.collection_object_id,'collector number') IS NULL THEN flat.collectors ELSE CONCAT(flat.collectors,'; ', CONCATSINGLEOTHERID(flat.collection_object_id,'collector number')) END as collectors_numbers,
  CASE WHEN flat.identification_remarks = 'Former nature_of_id:%' THEN '' ELSE coalesce(SUBSTR(flat.identification_remarks,0,INSTR(flat.identification_remarks, '.')),flat.identification_remarks) END common_name,
  CASE flat.collection_id WHEN '124' THEN 'BOT-' WHEN '130' THEN 'ENTO-' WHEN '126' THEN 'FISH-' WHEN '131' THEN 'MALA-' WHEN '113' THEN 'MAM-' WHEN '114' THEN 'ORN-' WHEN '115' THEN 'OOL-' WHEN '144' THEN 'TEACH-' WHEN '132' THEN 'EH-' END || flat.cat_num formatted_cat_num,
  CASE WHEN isdate(began_date)=1 and length(began_date)=10 and began_date=ended_date then to_char(began_date::date,'DD FMMonth YYYY') ELSE verbatim_date END as formatted_date,
  TRIM(TRIM(leading ',' FROM CASE WHEN flat.higher_geog = 'no higher geography recorded' THEN '' ELSE REPLACE(flat.higher_geog,'North America, United States','USA') END || ', ' || flat.spec_locality)) locstring
FROM
  flat
WHERE
  collection_object_id IN (#collection_object_id#)
ORDER BY
  formatted_cat_num```

This is a newer version of the above label I was trying to create before it was announced we were no longer using the old reporter. It still works when I use the old reporter, but the format is now out of date: CHAS_label_herb_date_test2.cfr

```SELECT
  scientific_name,
  family,
  coll_event_remarks,
  habitat,
  began_date,
  ended_date,
  verbatim_date,
  flat.spec_locality,
  flat.higher_geog,
  higher_geog as locstring,
  CASE WHEN verbatim_coordinates IS NULL THEN '' ELSE CONCAT('Verbatim coordinates: ', verbatim_coordinates) END coordinates,
  CASE WHEN identifiedby = 'unknown' THEN '' ELSE CONCAT('Det. ', identifiedby) END formatted_identifiedby,
  CASE collection_id WHEN '124' THEN 'BOTANY COLLECTION' WHEN '144' THEN 'TEACHING COLLECTION' ELSE NULL END collection,
  CASE WHEN CONCATSINGLEOTHERID(flat.collection_object_id,'collector number') IS NULL THEN flat.collectors ELSE CONCAT(flat.collectors,'; ', CONCATSINGLEOTHERID(flat.collection_object_id,'collector number')) END as collectors_numbers,
  CASE WHEN flat.identification_remarks = 'Former nature_of_id:%' THEN '' ELSE coalesce(SUBSTR(flat.identification_remarks,0,INSTR(flat.identification_remarks, '.')),flat.identification_remarks) END common_name,
  CASE flat.collection_id WHEN '124' THEN 'BOT-' WHEN '130' THEN 'ENTO-' WHEN '126' THEN 'FISH-' WHEN '131' THEN 'MALA-' WHEN '113' THEN 'MAM-' WHEN '114' THEN 'ORN-' WHEN '115' THEN 'OOL-' WHEN '144' THEN 'TEACH-' WHEN '132' THEN 'EH-' END || flat.cat_num formatted_cat_num,
 ConcatAttributevalue(flat.collection_object_id,'verbatim preservation date') as verbatim_preservation_date
FROM
  flat
WHERE
  collection_object_id IN (#collection_object_id#)
ORDER BY
  formatted_cat_num```

**Updates**
This is the HTML I have so far for the updated labels. I'm stuck on:
1. How to make sure the label size is consistent (the mail merge label size is 1.8in x 3.5in)
2. How to eliminate punctuation if information isn't available (remove the parentheses if a habitat isn't provided; avoid two commas in a row if a specimen does not have county-level ID).

```<table style="width: 25%; border-collapse: collapse; border: medium solid rgb(215); font-family: Arial, Helvetica, sans-serif; font-size: 8px; padding: 8px;">
    <tbody>
        <tr>
            <td style="width: 100%; border: medium solid rgb(215, 215, 215); padding: 8px;">
                <div data-empty="true" style="text-align: center;"><span style="font-family: Arial, Helvetica, sans-serif;"><strong>Chicago Academy of Sciences<br>BOTANY COLLECTION</strong></span></div>
                <p><span style="font-family: Arial, Helvetica, sans-serif;"><strong>HERB-#cat_num#<br>#FAMILY#<br><em>#scientific_name#</em></strong><br>#identification_remarks#<br>Det. #identified_by#, #made_date#</span></p>
                <p><span style="font-family: Arial, Helvetica, sans-serif;">#continent_ocean#, #country#, #state_prov#, #county#, #spec_locality# (#habitat#)</span></p>
                <p><span style="font-family: Arial, Helvetica, sans-serif;">Coll: #collectors#, #collector_num#<br>Between: #began_date# and #ended_date# (#verbatim_date#)<br></span></p>
            </td>
        </tr>
    </tbody>
</table>

Here's where I've been working on the HTML: https://codepen.io/wellerjes/pen/xxJXYKe

I started a [new report here](https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=65), but there's nothing saved to it.

I've also attached the mail merge document, if that is helpful. [Backlog Botany Labels_Mail Merge_2023-04-04.docx](https://github.com/ArctosDB/arctos/files/11181409/Backlog.Botany.Labels_Mail.Merge_2023-04-04.docx)

Thanks!

**Priority** - [Please assign a priority so that the DBA knows whether there is any urgency associated with the new template]

[dustymc]

Excel likes to reformat dates/numbers

That must be a universal problem, surely there's a solution for it??

(One might involve avoiding Excel - does sheets or something do this without that??)

@Jegelewicz ??

I think @mkoo has a better recipe for sizing, but it will come down to CSS - separately, in <style> tags, or inline as you have it (but that's not much fun to maintain).

https://www.w3schools.com/cssref/css_units.php - you probably DON'T want this as a percentage (but it's also probably possible if you have some reason, CSS is full of crazy unit conversion assumptions that can be used in 'interesting' ways).

Formatting should be done in the SQL (or in the post-query processing - anywhere but the label!). I'll probably need more specifics, but instead of:

#continent_ocean#, #country#, #state_prov#, #county#, #spec_locality# (#habitat#)

I would use this:

concat_ws(', ',continent_ocean,country,state_prov,county,concat(spec_locality,' ('||habitat||')'))

concat_ws is "concat with separator"

Separator is comma-space

concat ignores nulls, the || concat operator does not, so the last term is a concat of specloc and either a NULL (nothing in concat-land) or the term with the punctuation (which all evaluates to NULL when the term itself is NULL).

You can use that same logic to simplify eg

CASE WHEN verbatim_coordinates IS NULL THEN '' ELSE CONCAT('Verbatim coordinates: ', verbatim_coordinates) END coordinates,

to

'Verbatim coordinates: ' || verbatim_coordinates coordinates

https://github.com/ArctosDB/arctos/issues/6098 is happening now and might change how you want to deal with collector numbers.

And do please feel free to just let me know what you want to see and I'll write SQL for it, in small steps (eg one "label section" at a time) if necessary.

[Jegelewicz]

@wellerjes if you import data to Excel rather than just opening it there you can avoid the automatic changes. See https://handbook.arctosdb.org/how_to/How-to-Excel-for-Arctos.html#downloading-data

[Jegelewicz]

@wellerjes I just finished making a mail merge report for UWBM:Mamm. I found that if you don't open the csv, everything works great. If you MUST edit something in the csv, editing in Notepad will also ensure that nothing changes.

If you still want to use mail merge - let me know if I can help.