Enable uploading Collection Relationships in the WorkBench when Collection Object is the base table

[grantfitzsimmons]

The update (v7.8.12) introduced the ability to upload to the Collection Relationship table directly as the base table rather than from the Collection Object level.

Willem at SAIAB is requesting the ability to upload and create collection relationships when the Collection Object table is the base table by simply uploading through the right side rels matching to collection objects on the other side of the relationship by catalogNumber.

See the mapping that he had created before realizing this is not currently possible:

The mapping specifies the relationship name and the catalog number, which theoretically should be all that you need.

I spoke to @melton-jason about this and he believes there may be a way to do this currently, but most likely with a less intuitive workflow. The mapping he created should work out of the box.

Requested By: Willem at SAIAB

It will be wonderful if you can do that because the workflow will obviously be much less complicated.

[zoewarner1]

Hi @grantfitzsimmons - just wondering if Jason was able to provide a way to do this.
I'm re-jigging some WB templates for our Fish collection. Specimens and Tissues are in separate collections. Most tissues are related to a Specimen, which is known at time of uploading the Tissue.
Currently load the Tissue csv twice, one to create the tissue objects, and then again to create the relationship to Specimen. I tried the above shown in screenshot, to reduce it to one upload. It ended up creating two Collection Objects in Tissue collection, one with the Specimen catalogue number, the other with the Tissue cat. no. and linked them together.

[realVinayak]

Actually, I tested it out, and if my understanding of collection relationships is correct, then this bug is resolved in #4929 (I heavily refactored and reimplemented collection relationships in workbench, and this was a side-effect). That is, the mapping worked out of the box for me.

Here is the mapping I used, and was able to successfully upload into different collection.

@zoewarner1 your best bet would be to wait that release out. I'll need to add unit tests for this.

[zoewarner1]

Ok, thanks very much for quick response @realVinayak

[realVinayak]

@zoewarner1 ofcourse! You should still try using collectionrelationships as the base table (instead of collectionobject) which is supported right now -- can't think of cases where that won't work or any bugs at the top of my head, are there in your case?

[zoewarner1]

No bugs at the moment using collectionrelationships as base table :)
So currently, uploading relationship data is a second upload after the first tissues collection object upload. Was just trying to combine them into one upload.

[zoewarner1]

Actually maybe bug? @realVinayak
Our tissue upload is mainly tissues that have a link to a registered specimen. Occasionally though there are 'orphan' tissues captured within same upload, that don't link to a specimen. In the 'orphan tissue' rows the collection relationship columns are left blank.
The orphan tissues upload fine in the Collection Object wb upload.
But the collectionrelationships wb upload errors if these 'orphan tissue' rows are included (even if I have set mapping for the collection relationship columns to 'ignore when blank').
I have to sort the wb data set and remove the 'orphan' rows before it doesn't error. It would be nice if I didn't have to do that, and it ignored when blank (as I set in the mapping). Though as these columns are the only columns mapped for the collectionrelationship wb upload, perhaps that is not possible but if we are hoping to eventually have them together in single upload, that could be a limitation.

[realVinayak]

@zoewarner1 Ah, yes, sorry that's a limitation. Yeah, removing those rows manually is needed, just because the basetable is collectionrelationship. If you remove all the values in that row, it should ignore it -- but that's equivalent (almost) to removing the row itself. In single upload though, simply putting blank values for the CollectionRelType mapping should be enough though. Just for sanity, you're getting something like (1048, "Column 'RightSideCollectionID' cannot be null"), right?

The ignore when blank just applies to matching (and not uploads). This is because blank values are ignored when uploading. In your case it isn't because you likely have some other values on that row, so that's why removing all values from that row will also do the trick.

[zoewarner1]

@realVinayak Ah yes, I see! Removing the row is probably easier than removing all the values in the 'orphan' rows, but I understand now re: ignore when blank for matching (not uploads).
The error when validating is:

[realVinayak]

@zoewarner1 ding ding ding, you found a bug! I'm able to recreate it if collectionreltype's fields are all null.

Yeah, that's gonna be a "won't fix" for a WHILE -- too complicated, even after the reimplementation branch. I'm actually gonna crash more loudly than that, a bit more useful error message to users.