Support nested -to-many relationships in WorkBench

[grantfitzsimmons]

The Determination Citation table does not display in the WB mapper even when it is not hidden in the schema.

https://suriname-edge.test.specifysystems.org/specify/workbench-plan/3/

user: spadmin
database: suriname
user agent: Windows 10 and Chrome
ver: edge

[maxpatiiuk]

This is because workbench does not support nested -to-many relationships (Collection Object -> Determination is a -to-many and Determination -> Determination Citation is a -to-many)

Until this is fixed, your best bet is to change the base table (if Determination is the base table, you can upload both to Determination Citation and Collection Object)

[grantfitzsimmons]

@benanhalt Is there any way to make this happen for Suriname, specifically for the Determination Citation table? They need to upload to this table while doing their data imports.

If we cannot implement a fix for this, I'm afraid we will need to help them import this data manually.

[maxpatiiuk]

@grantfitzsimmons Can you change the base table?
Or do two uploads?

[grantfitzsimmons]

It needs to be CO. If we change the base table, it will become much more complex. I can try to explain this behavior but if this can be resolved it should be.

[grantfitzsimmons]

@benanhalt Is this something we can make happen so that Suriname can upload their data without adding many additional steps?

[benanhalt]

It might be possible for tables that are configured to always upload. I.e. it doesn't try to match existing records. The reason it isn't allowed now is that trying to match existing records with chained -to-main relationships gets really complicated. For example imagine trying to match a collection object based on what determinations it has if the determiners' addresses are going to be relevant to the match.

[grantfitzsimmons]

What are the consequences for a table like Determination Citation? It doesn't need to be match an existing record. Is this something we could enable in preferences with an explanation?

[grantfitzsimmons]

@benanhalt @maxpatiiuk Can you enable this behavior in Specify 7.8?

[grantfitzsimmons]

@acwhite211 Is this something we can switch on for 7.8?

[maxpatiiuk]

@grantfitzsimmons this is not as simple as toggling a switch. There are complexities with matching -to-manys inside of -to-manys. But sounds like this is high enough priority to fix even despite the complexities. Jason or Alec need to look into this deeper to see what difficulties there are.

Once that is done, we can "switch" this on the front-end

[grantfitzsimmons]

Every conversion is heavily impeded by this issue. The other big ones (determination ordering, taxon being accepted, etc.) are easy enough to work around, but this is very difficult to do without

[grantfitzsimmons]

-to-manys that we do not want to create new records in:

All Tables
Left Side Rels
Left Side Rel Types
Right Side Rels
Right Side Rel Types
All tables that end in 'Attrs'

User Groups
Permissions
Specify Users

Taxon
Determinations
(we should not be creating determinations of a taxon through the determination rel, right!?)

[grantfitzsimmons]

If we solve this by allowing the user to create new records in nested -to-manys without matching behavior, we need to communicate that in some way. Documentation can "cover it", but there should be some embedded indication

[melton-jason]

It might be possible for tables that are configured to always upload. I.e. it doesn't try to match existing records. The reason it isn't allowed now is that trying to match existing records with chained -to-main relationships gets really complicated. For example imagine trying to match a collection object based on what determinations it has if the determiners' addresses are going to be relevant to the match.

From #2331 (comment)

Yes, it seems like the easiest approach for a first implementation would be to only allow creating new records for nested -to-manys and disable matching behavior for those records.

We can assess disambiguation for tables with these relationships after a prototype of this functionality is complete.

[grantfitzsimmons]

2: when mapping with Workbench using CO as base table, the determiners link table is not shown (only the old determiner field) so we can't assign several determiners to certain determination.

Requested By: Silvia at CSIC

[grantfitzsimmons]

This continues to become more and more important each day

[grantfitzsimmons]

Requested By: CSIRO for the Determiners table

From our point of view, this is a top priority to get fixed as it prevents us from using the Workbench for bulk upload of data.

[grantfitzsimmons]

This was brought up AGAIN by Soraya at Bern. This should be addressed soon. Please discuss this when discussing future priorities!

@acwhite211

[specifysoftware]

This issue has been mentioned on Specify Community Forum. There might be relevant details there:

https://discourse.specifysoftware.org/t/mapping-determiner-in-workbench/1635/4

[grantfitzsimmons]

Soraya mentioned this again on the forum:
https://discourse.specifysoftware.org/t/mapping-determiner-in-workbench/1635/3?u=specify

We should resolve ASAP.

[grantfitzsimmons]

Reported by: CSIRO on Asana

I assume this similar in nature to the Determination > Determiner issue and linked to GH ticket: #2331
 
I have just been building a specimen-load template for our insect collection. We are utilising the preparationProperties table to allow recording of further details on lifestage, gender etc per preparation. The table and its fields come up just fine in a query, but not in the WB mapper:

 
The lack of support for this in the Workbench is really concerning to us as it complicates our workflows. Instead of doing a singular WB load, we have to create three: the main specimen load, the determination data and the preparation data. It also increases the risk of ‘getting something wrong’.  Is there any ETA on when this may be fixed?

[grantfitzsimmons]

Jordi at Barcelona encountered an issue with this today

[specifysoftware]

This issue has been mentioned on Specify Community Forum. There might be relevant details there:

https://discourse.specifysoftware.org/t/table-format-and-aggregation-for-agent-groups/2001/5

[grantfitzsimmons]

@sharadsw Is this fixed in your first batch edit PR? If so, can you put it in the appropriate milestone?

[grantfitzsimmons]

Reported by MCNB:

We're working with the workbench (WB) and exploring data migration options. It seems that we can't reach the PreparationProperty table via Col. Object to add storage history. I understand that this might require a second workbench step to load locations into the preparation table. Is it correct?

We've noticed that not all tables can be accessed directly via Col Object in WB, and that multiple workbench steps might be necessary to fully load certain data. Is there a guide or recommendation on how to load data into each table?, and which tables should always be loaded after the initial catalog number assignment in Col Object?. This would be very helpful for evaluating a more complete data migration for pending records.

[sharadsw]

@grantfitzsimmons Not yet but should be fixed in #6126

[realVinayak]

Hmmm, nested-to-manys are separate from the batch-edit logic, so there's no harm in enabling that (other than testing I guess). Ha, you could probably say "Vinny, why didn't you make them separate PRs then"

[sharadsw]

You're right, I was thinking it would mess with batch edit but it really shouldn't. I'll make a separate PR for it just to keep testing simpler

[sharadsw]

@grantfitzsimmons Created #6216 for this