We are trying to do some work behind the scenes on our data. We have some Boolian boxes that we fill in with a script because they are not fields that we can do batch edits on in the normal way. We have used the Boolian boxes in the Collection Object table to capture that data but would now like to transfer the data from those Boolian boxes to Boolian boxes in the Collection Object Attribute table. We have been able to see the table that seems to match these two tables (Attr perhaps?) but do not quite know how to work with it. We have information in the in the Collection Object Attribute table ( over 5000 lines) which makes sense as we have only recently started adding information to this table for a new project we are working on. These lines seem to a list of sequential numbers. However, when my IT person looks in the Attr table, it has nothing in it. Does it only make the match when you look at the collection object record or should we be seeing data in the Attr table. How would we script something to transfer the information we have in the Collection Object table to the Collection Object Attribute table? Any help appreciated. If it gets too esoteric, I will bring my IT person into the conversation. Thanks again.
@Leslie There are two tables listed in the scheme config for collection object attribute. I am not sure what the attr table does but it is not the table you need to populate with data. That is the Collection Object Attribute table
This table is linked to Collection Objects in a 1:1 relationship and contains a bunch of generic text and number fields that you use to populate the data you have. You could run scripts to transfer the data from the existing CO field to the COA field you want to use. Pay close attention to the properties (length, field type) of these fields so that you do not end up with data being cut off, etc.
Hi Leslie,
In addition to @AndyBentley’s comment, I have the following advice:
We have used the Boolian boxes in the Collection Object table to capture that data but would now like to transfer the data from those Boolian boxes to Boolian boxes in the Collection Object Attribute table.
If you are doing behind-the-scenes work on the database, you would need to use SQL commands to create new Collection Object Attribute records (if they do not exist already), link them with the Collection Objects, and then you could use an UPDATE statement to set a boolean field in that table to TRUE when the Collection Object boolean field is TRUE. At that point, you could unset the boolean field in the Collection Object table and the process would be complete.
This is a bit of a complicated maneuver, especially if there are not existing CoA records associated with your CO records.
(Attr perhaps?)
However, when my IT person looks in the Attr table, it has nothing in it.
The CollectionObjectAttrs relationship and table are not meant to be used at this time. You can however use the CollectionObjectAttribute relationship and table!
Thanks Andy,
We are using the Collection Object Attribute table to house our data, but thought that the Attr table may have been some sort of connector. We are not using 7 yet and were not sure what was the key(?) that linked the two tables together.
–Leslie
Thanks Grant,
I am forwarding both yours and Andy’s answers to my IT person.
There should be a primary key in that table that would link them but given that this is a 1:1 relationship to collection objects you could just use the CO PK to link the COA to the CO I would think
Is it really a one-to-one relationship? Because in Specify 7 and also in the Specify shema, it is noted that it is a one-to-many relationship.
Hi @Marion–
Thank you for your question! This is a quirk of how the data model is designed.
The table CollectionObject in the Specify database has a field known as CollectionObjectAttributeID which captures the identifier for the CollectionObjectAttribute record linked to it. Theoretically, a single CollectionObjectAttribute could be linked to multiple CollectionObject records thanks to this database design.
This technically gives CollectionObjectAttribute a one-to-many relationship to CollectionObject, but each CollectionObject has a one-to-one relationship to CollectionObjectAttribute.
TL;DR: A single CollectionObject can only have one CollectionObjectAttribute