Shared vs. Embedded Records & Configuration

[Grant]

For some database relationships in Specify, you can choose to either share or embed a related record between two tables.

This is most commonly asked when configuring the following tables:

• Collecting Event
• Paleo Context

Shared vs. Embedded

Collecting Event

A Collecting Event (CE) is a place and time that a collection is made. In some disciplines, a field number is used as the primary designation for place and time information, while in others, locality name and date are used to link specimens. Localities are always shared between Collecting Event records and cannot be embedded. A CE with the same exact information often generates multiple Collection Object (CO) specimens. CE records are “scoped” within the same discipline.

If the CE is embedded, the COs cannot share the same CE record. Even if multiple objects were all collected during the same event, updating a note on the CE record will not affect the other COs, as they have their own copies of this data in their respective CE records. The CE will appear as a subview similar to Collection Object Attribute, where each is unique to the CO record.

If the CE is shared, then you can have these COs all link to the same CE and therefore if you change the CE data, then all COs will pick up that change since they point to a singular CE. The CE will be searched using a query combo box and found from any CO within the current discipline.

Paleo Context

The paleontological information, chronostratigraphy, and lithostratigraphy for a Collection Object, Locality (Loc), or Collecting Event depending on the configuration are described in a Paleo Context (PC) record. PC records are “scoped” within the same discipline.

The “linked table record” refers to the record in the CO, Loc, or CE table that is linked to a PC.

If PC is embedded, the linked table record cannot share the same PC record. Updating the PC record will not affect the other linked table record , as they have their own copies of this data in their respective PC records. The PC will appear as a subview similar to Collection Object Attribute, where each is unique to the linked table record.

If PC is shared, then you can have these linked table record all link to the same PC and therefore if you change the PC data, then all linked table records will pick up that change since they point to a singular PC. The PC will be searched using a query combo box and found from any CO within the current discipline.

Configuration

If you are hosted on Specify Cloud, please reach out to support@specifysoftware.org so that we can make this change for you!

Switching from embedded to shared records is straightforward, but the reverse process is more complex. This is because once a shared record is linked to multiple other records, it cannot be truly “embedded” until it is cloned and made distinct for all linked records. If you would like to make this change, please contact us before proceeding.

To modify the configuration of Collecting Event sharing, you must edit the collection or discipline record based on what configuration you wish to accomplish. This is best done using SQL, so this should be followed only by advanced users who are familiar with what they are doing!

Paleo Context

This change will affect all collections within the same discipline.

• If IsPaleoContextEmbedded is set to TRUE, Paleo Context records will be embedded. If it is set to FALSE, Paleo Context records will be shared with the linked base table records.

To make this switch, change X with the UserGroupScopeID of the discipline you would like to update. Make sure to execute this script on the correct database!

UPDATE discipline
SET IsPaleoContextEmbedded = FALSE
WHERE UserGroupScopeID = X;

UserGroupScopeID is the same as DisciplineID

Once this is complete, make sure to revise the form definitions so that PC records are shown either as a subview or a query combo box depending on the configuration.

Embedded Paleo Context XML (Subview)

<row>
   <cell type="subview" viewname="SubPaleoContext" id="9" name="paleoContext" defaulttype="table" colspan="12"/>
</row> 

Shared Paleo Context XML (Query Combo Box)

<row>
   <cell type="label" labelfor="pcsh"/>
   <cell type="field" uitype="querycbx" id="pcsh" name="paleoContext" initialize="name=PaleoContext" colspan="12"/>
</row> 

Collecting Event

Warning: While this change will affect only the current collection, it is strongly advised to set all collections within a discipline to the same setting.

If one collection is configured to use embedded Collecting Events, other collections within the same discipline that use shared Collecting Events can look up and use the embedded events. This could potentially lead to data being changed inadvertently in the linked embedded Collecting Events.

• If IsEmbeddedCollectingEvent is set to TRUE, Collecting Event records will be embedded. If it is set to FALSE, Collecting Event records will be shared between Collection Objects.

To make this switch, change X with the UserGroupScopeID of the Collection you would like to update. Make sure to execute this script on the correct database!

UPDATE collection
SET IsEmbeddedCollectingEvent = FALSE
WHERE UserGroupScopeID = X;

UserGroupScopeID is the same as CollectionID

Once this is complete, make sure to revise the form definitions so that CE records are shown either as a subview or a query combo box depending on the configuration.

Embedded Collecting Event XML (Subview)

<row>
   <cell type="subview" viewname="CollectingEventSub" id="21"  name="collectingEvent" colspan="14" rows="5"/>
</row>

Shared Collecting Event XML (Query Combo Box)

<row>
   <cell type="label" labelfor="7"/>
   <cell type="field" id="7" name="collectingEvent" uitype="querycbx" initialize="name=CollectingEvent;clonebtn=true" colspan="11"/>
</row>