Add Collecting Trip to existing Collecting Events

[pverley]

[Using self-hosted Specify v7.10.2.3 at the time of writing]

Hi there,

A colleague asked me whether we could add in our base information about collecting trips ? A quick glimpse at the Specify Schema showed me that Collecting Trip table already exists

Next, I thought about how to input the information about the trips with Sp7 GUI

Batch-edit ?

I crafted my query to output COs from the same trip and added empty columns Trip Name, Trip Start Date, Tripe End Date, etc. I saw the new button Batch Edit in the Query tab , but as explained in the documentation:

@system

Relationships to a base table will be read-only and can be viewed but not edited.

Bummer, that was not the way to do it.

Workbench ?

I tried in many ways to relate existing CE entries (even tried with COs) with new Collecting trips columns, but I got either errors like attempting to duplicate catalog numbers or existing CE detected as new Cells as soon as I added collecting trip information.
The failure was expected though, since the Workbench is all about

@AndyBentley

augmentations of existing one-to-many relationships

can it be achieved with existing Sp7 tools ?

It is kinda straightforward to to do it from the mariadb prompt by:

1. creating the Collecting Trip from the Data Entry tab
2. updating the collectingevent:collectingtrip_id field with a SQL statement

but I feel like I might be making poor use of the existing GUI tool and I’d rather ask here.

[markp]

Hi @pverley

I believe what you are trying to do is entirely possible in v7.10.2.3 using batch edit, you just have to work from Collecting Trip backwards. Since the fields you want to modify are in Collecting Trip, that should be your base table. You can use Collection Objects as part of the query if you need that to filter for records you are looking for. Note in the screenshot below Text3 would be the name field (Trip Name doesn’t exist as a caption in the demo instance)

Let me know if this achieves what you are after!

[pverley]

Hi Mark,

Thanks for the quick and sharp response, as always !

It makes complete sense to base my query on the Collecting Trip table if I where to modify existing fields or add values to empty fields, and narrow down my search with a list of catalog numbers (or any other criteria that would help to identify the group of specimen). But my Collecting Trip table is empty so far (we never used it), so any query based on Collecting Trip table does return Results: (0)

That is why I based my initial query on existing Catalog numbers and added collecting trip fields (expected to be empty).

Since they are related fields, they are not editable from the Batch Edit.

[markp]

Hi @pverley

Ah, yes, I had considered this may have been what you meant later that afternoon. I don’t know of a GUI solution in that case, I solved this problem by putting the ability to do it in the edit bot (which will soon hopefully become obsolete!)

The ability to batch edit relationships is slated for v7.11.0 and has been merged (though some bugs remain, I don’t think any would affect this), probably best to just wait for that.

[Grant]

Hi @pverley,

@markp is spot on—what you’re asking for will be possible as soon as we ship v7.11.0, which should be out in the near future. Right now, neither the WorkBench nor Batch Edit can update the relationship that links Collecting Trips (CTs) to existing Collecting Events (CEs), but that capability is coming in the next release.

The reason this isn’t supported today comes down to how the database tables are related. With objects like Citations, Preparations, or Determinations, the foreign key^[1] lives on the new record side. For example, each preparation row carries a CollectionObjectID that points back to its parent Collection Object (CO).

Because the link is on the preparation side, you can easily add new preparations to an existing CO through the WorkBench:

create table preparation
(
    PreparationID    int auto_increment primary key,
    /* … other columns … */
    foreign key (CollectionObjectID)
        references collectionobject (CollectionObjectID)
);

Collecting Trips work differently: the link is stored on the Collecting Event side. In other words, the CE record must be updated to point at a different CT. That update requires true batch‐editing of the CollectingTripID field—which, as mentioned, isn’t enabled until v7.11.0:

create table collectingevent
(
    CollectingEventID   int auto_increment primary key,
    /* … other columns … */
    foreign key (CollectingTripID)
        references collectingtrip (CollectingTripID)
);

Once the update is out, you’ll be able to select multiple CEs and change their CollectingTripID in one go. We’ll keep you posted on the release, and as soon as it’s available you can go ahead and batch‐link those trips!

Hope that helps, and let me know if you have any other questions!

---

1. A foreign key is a column (or set of columns) in a table that establishes a link between that table and another table in a database. ↩︎

[markp]

Batch editing truly was the greatest increase to productivity since the steam engine, another round of congrats to the team (perhaps like the people that cheer you on as you approach kilometre 35 of the marathon, the end is in sight!)

[pverley]

Thanks Grant, explanation is Cristal clear !

Looking forward to seeing this “feel natural” expansion of the Sp7 Batch Edit capability.

I fully subscribe to what Mark wrote down (and flattered to see Denis Papin’s genius acknowledged ). Batch Edit was a long awaited tool in Sp7 and you all worked hard and delivered. Thumbs-up !

[pverley]

In the first post I wrote that it would be easy to do it from SQL prompt. I put it down on paper in case it may help someone not fully confident with the process.

As always when attempting to work directly on SQL database, make a database backup beforehand. Quoting Grant Master :

@Grant

Note: These instructions will require direct access to the database via SQL. If you are hosted on Specify Cloud, please reach out to Specify Support for assistance. If you are self-hosted, please contact your IT department.

Make a database backup before proceeding with any of the following steps.

I first created the Collecting Trip “Mission Wallis et Futuna” from the Data Entry tab (I had to previously edit the Data Entry tab to include the Collecting Trip table, not visible by default)

1. Retrieved the newly created collectingTripID

select CollectingTripID, Collectingtripname from collectingtrip;
+------------------+--------------------------+
| CollectingTripID | Collectingtripname       |
+------------------+--------------------------+
|              798 | Mission Wallis et Futuna |
+------------------+--------------------------+

2. Selected specific collecting events

In our case we had indicated the collecting events were part of this trip with a note in text fied collectingevent:text2.

SELECT text1, integer1, startDate, collectingtripid FROM collectingevent WHERE text2 LIKE "%Mission Wallis et Futuna%";
+-----------+----------+------------+------------------+
| text1     | integer1 | startDate  | collectingtripid |
+-----------+----------+------------+------------------+
| CE-094791 |     5245 | 2008-11-11 |             NULL |
| CE-094796 |     5246 | 2008-11-11 |             NULL |
| CE-094964 |     5247 | 2008-11-11 |             NULL |
| CE-094781 |     5252 | 2008-11-11 |             NULL |
| CE-094780 |     5253 | 2008-11-11 |             NULL |
...

3. Updated the field collectingevent:collectingtripid for those CEs

UPDATE collectingevent
SET collectingtripid = 798
WHERE text2 LIKE "%Mission Wallis et Futuna%";

4. Validated the update from the query builder

I created a new query including Collecting Trip columns and confirmed that everything went smoothly.

Done