This guide explains how to create a query compatible with a Specify Web Portal before using the Web Portal export feature. To learn how to use the export feature, see the article Export to Web Portal.
Create a Query
In Specify 7 queries are used as a mapping for exporting data to a Web Portal. In order for the Web Portal to ingest data properly, the query must be configured in a way that is compatible with its constraints.
Many-to-One relationships
The most important constraint to consider is that the query must only return one row for each collection Object. If more than one row represents a single Collection Object, the Web Portal will contain more than one Collection Object record with the same information. Consequently, any tables with a many-to-one relationship to the Collection Object table must be filtered or aggregated to avoid duplicates.
The following tables have a direct many-to-one relationship to Collection Object:
- Absolute Ages
- Collection Object Attachment
- Collection Object Citations
- Collection Object Properties
- Components
- Conservator Descriptions
- Determinations
- DNA Sequences
- Ecsiccata Items
- Left Side Rels
- Other Identifiers
- Preparations
- Relative Ages
- Right Side Rels
- Treatment Events
- Voucher Relationships
The same rule applies to any many-to-one relationships nested within another table that relates to a Collection Object. For example,
- Collectors
- Determination Citations
- Determiners
- Loan Preparations
- Material Samples
To view every table and its relationships, use the Specify Data Model Viewer or Schema Config.
One option for handling many-to-one relationships is to add conditions to a query so that it will only return one many-to-one record. However, this method only works for select tables that have have a field with a behavior that guarantees only one record will be returned.
Filtering is possible for the following tables:
-
Determination: Enforcing ‘isCurrent’ = True only returns the Determination marked as current.
-
Collectors: Enforcing ‘isPrimary’ = True returns only the Collector marked as primary.
-
Address: Enforcing ‘isPrimary’ = True returns only the Address marked as primary.
-
Collection Object Group Join: Enforcing ‘isPrimary’ = True returns only the Collection Object marked as primary.
To use any other many-to-one relationships, you must remove any fields from the related table and add the (aggregated) field to the query.
Before:
After:
Record Formatters
If your query relies on any table formatters or aggregators, ensure that a record formatter has been defined for that field and the data appears how you would like it to appear in the Web Portal. If a record formatter is not defined for a formatted or aggregated field, the data will not be available in the Web Portal.
Run the query and look at the columns for your formatted and aggregated fields. If the results look like the image below, you are missing a table format.
If the results look like this image, you are missing a table aggregator.
If you are creating Web Portals for multiple collections, remember to check the query results in every collection. Even if you use the same query, the record formatter must be accessible in each collection, meaning either one record formatter must be defined at the Discipline or Global Resource level, or individual record formatters must be defined at the Collection level in each Collection.
If the query results return one row per Collection Object and all formatted or aggregated fields are displayed correctly, your query is ready to be exported to a Web Portal.