Diversity Collection
FUNCTIONS and PROCEDURES
The following objects are not included:
- System objects
- Objects marked as obsolete
- Previous versions of objects
FUNCTIONS
Function AgentOneString
Returns a result set that contains a string with all Collectors of a Specimen
| Parameter | DataType | Description |
|---|---|---|
| @CollectionSpecimenID | int | Refers to ID of CollectionEvent (= foreign key and part of primary key) |
| Column | DataType | Description |
|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionEvent (= foreign key and part of primary key) |
| CollectorsNameString | nvarchar (1000) | - |
| CollectorsNameStringNumber | nchar (1500) | - |
Depending on:
- CollectionAgent
Function AnalysisChildNodes
Content of table Analysis containing all children for a given AnalysisID defined via the relation in column AnalysisParentID
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | The AnalysisID for which the children should be returned |
| Column | DataType | Description |
|---|---|---|
| AnalysisID | int | ID of the analysis (primary key) |
| AnalysisParentID | int | Analysis ID of the parent analysis, if it belongs to a certain type documented in this table |
| DisplayText | nvarchar (50) | Name of the analysis as e.g. shown in user interface |
| Description | nvarchar (500) | Description of the analysis |
| MeasurementUnit | nvarchar (50) | The measurement unit used for the analysis, e.g. mm, µmol, kg |
| Notes | nvarchar (1000) | Notes on this analysis |
| AnalysisURI | varchar (255) | URI referring to an external documentation of the analysis |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
| RowGUID | uniqueidentifier | - |
Depending on:
- Analysis
Function AnalysisHierarchyAll
Content of table Analysis including the hierarchy separated via “|” defined via the relation in column AnalysisParentID
| Column | DataType | Description |
|---|---|---|
| AnalysisID | int | ID of the analysis (primary key) |
| AnalysisParentID | int | Analysis ID of the parent analysis, if it belongs to a certain type documented in this table |
| DisplayText | nvarchar (50) | Name of the analysis as e.g. shown in user interface |
| Description | nvarchar (MAX) | Description of the analysis |
| MeasurementUnit | nvarchar (50) | The measurement unit used for the analysis, e.g. mm, µmol, kg |
| Notes | nvarchar (MAX) | Notes on this analysis |
| AnalysisURI | varchar (255) | URI referring to an external documentation of the analysis |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
| HierarchyDisplayText | varchar (900) | - |
Depending on:
- Analysis
Function AnalysisList
Returns the content of table Analysis related to the given project and a taxonomic group
| Parameter | DataType | Description |
|---|---|---|
| @ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
| @TaxonomicGroup | nvarchar (50) | Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) |
| Column | DataType | Description |
|---|---|---|
| AnalysisID | int | ID of the analysis (primary key) |
| AnalysisParentID | int | Analysis ID of the parent analysis, if it belongs to a certain type documented in this table |
| DisplayText | nvarchar (50) | Name of the analysis as e.g. shown in user interface |
| Description | nvarchar (500) | Description of the analysis |
| MeasurementUnit | nvarchar (50) | The measurement unit used for the analysis, e.g. mm, µmol, kg |
| Notes | nvarchar (1000) | Notes on this analysis |
| AnalysisURI | varchar (255) | URI referring to an external documentation of the analysis |
| DisplayTextHierarchy | nvarchar (255) | - |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
Depending on:
- Analysis
- AnalysisChildNodes
- AnalysisTaxonomicGroup
- ProjectAnalysis
Function AnalysisListForUnit
Returns the content of table Analysis available for a IdentificationUnit given by the IdentificationUnitID. The list depends upon the analysis types available for a taxonomic group and the projects available for an analysis
| Parameter | DataType | Description |
|---|---|---|
| @IdentificationUnitID | int | ID of the IdentificationUnit (= part of primary key). Usually one of possibly several organisms present on the collected specimen. Example: parasite with hyperparasite on plant leaf = 3 units, |
| Column | DataType | Description |
|---|---|---|
| AnalysisID | int | ID of the analysis (primary key) |
| AnalysisParentID | int | Analysis ID of the parent analysis, if it belongs to a certain type documented in this table |
| DisplayText | nvarchar (50) | Name of the analysis as e.g. shown in user interface |
| Description | nvarchar (1000) | Description of the analysis |
| MeasurementUnit | nvarchar (50) | The measurement unit used for the analysis, e.g. mm, µmol, kg |
| Notes | nvarchar (1000) | Notes on this analysis |
| AnalysisURI | varchar (255) | URI referring to an external documentation of the analysis |
| DisplayTextHierarchy | nvarchar (255) | - |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
Depending on:
- Analysis
- AnalysisTaxonomicGroup
- CollectionProject
- IdentificationUnit
- ProjectAnalysis
Function AnalysisProjectList
Returns content of table Analysis related to the given project
| Parameter | DataType | Description |
|---|---|---|
| @ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
| Column | DataType | Description |
|---|---|---|
| AnalysisID | int | ID of the analysis (primary key) |
| AnalysisParentID | int | Analysis ID of the parent analysis, if it belongs to a certain type documented in this table |
| DisplayText | nvarchar (50) | Name of the analysis as e.g. shown in user interface |
| Description | nvarchar (500) | Description of the analysis |
| MeasurementUnit | nvarchar (50) | The measurement unit used for the analysis, e.g. mm, µmol, kg |
| Notes | nvarchar (1000) | Notes on this analysis |
| AnalysisURI | varchar (255) | URI referring to an external documentation of the analysis |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
| RowGUID | uniqueidentifier | - |
Depending on:
- Analysis
- AnalysisChildNodes
- ProjectAnalysis
Function AnalysisTaxonomicGroupForProject
Returns the content of the table AnalysisTaxonomicGroup used in a project including the whole hierarchy
| Parameter | DataType | Description |
|---|---|---|
| @ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
| Column | DataType | Description |
|---|---|---|
| AnalysisID | int | ID of the analysis (primary key) |
| TaxonomicGroup | nvarchar (50) | Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) |
| RowGUID | uniqueidentifier | - |
Depending on:
- Analysis
- AnalysisList
- AnalysisTaxonomicGroup
- ProjectAnalysis
Function AverageAltitude
The average altitude for the points of a geography where the z value is given
DataType: float
| Parameter | DataType | Description |
|---|---|---|
| @Geography | geography (-1) | The geography for which the average altitude should be extracted |
Function BaseURL
Returns the basic URL for the database
DataType: varchar (255)
Function CollCharacterType_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | - |
| Description | nvarchar (500) | - |
| Abbreviation | nvarchar (50) | - |
Function CollDateCategory_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code that uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function CollectionChildNodes
Returns a result set that lists all the items within a hierarchy starting at the topmost item related to the given item.
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | ID of the collection (= CollectionID, PK) |
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (255) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (10) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (MAX) | A short description of the collection |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry |
| LocationPlanDate | datetime | The date when the plan for the collection has been created |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location |
Depending on:
- Collection
Function CollectionEventSeriesHierarchy
Returns a table that lists all the Series related to the given Series.
| Parameter | DataType | Description |
|---|---|---|
| @SeriesID | int | Primary key. The ID for this series of collection events (= primary key) |
| Column | DataType | Description |
|---|---|---|
| SeriesID | int | Primary key. The ID for this series of collection events (= primary key) |
| SeriesParentID | int | The ID of the superior series of collection events |
| DateStart | datetime | Point in time when the series of collection events started |
| DateEnd | datetime | Point in time when the series of collection events ended |
| DateSupplement | nvarchar (100) | Verbal or additional collection date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. The end date, if the collection event series comprises a period. The time of the event, if necessary. |
| SeriesCode | nvarchar (50) | The user defined code for a series of collection events |
| Description | nvarchar (500) | The description of the series of collection events as it will be printed on e.g. the label |
| Notes | nvarchar (500) | Notes on this series of collection events |
| Geography | geography (MAX) | The geography of the series of collection events |
Depending on:
- CollectionEventSeries
- EventSeriesChildNodes
- EventSeriesTopID
Function CollectionHierarchy
Returns a table that lists all the analysis items related to the given analysis.
| Parameter | DataType | Description |
|---|---|---|
| @CollectionID | int | Unique reference ID for the collection (= primary key) |
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (255) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (10) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (MAX) | A short description of the collection |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry |
| LocationPlanDate | datetime | The date when the plan for the collection has been created |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location |
Depending on:
- Collection
- CollectionChildNodes
Function CollectionHierarchyAll
Returns a table that lists all the collections including a display text with the whole hierarchy
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (500) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (50) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (MAX) | A short description of the collection |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry |
| LocationPlanDate | datetime | The date when the plan for the collection has been created |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | varchar (255) | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| DisplayText | varchar (900) | - |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location |
Depending on:
- Collection
- UserCollectionList
Function CollectionHierarchyMulti
Returns a table that lists all the collections related to the given collection in the list.
| Parameter | DataType | Description |
|---|---|---|
| @CollectionIDs | varchar (255) | Comma separated list of IDs of the collections that should be included. |
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (50) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (50) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (500) | A short description of the collection |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry |
| LocationPlanDate | datetime | The date when the plan for the collection has been created |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | varchar (255) | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location |
Depending on:
- Collection
Function CollectionHierarchySuperior
Returns a table that lists the given and all the items superior to the given collection
| Parameter | DataType | Description |
|---|---|---|
| @CollectionID | int | Unique reference ID for the collection (= primary key) |
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (255) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (10) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (MAX) | A short description of the collection |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry |
| LocationPlanDate | datetime | The date when the plan for the collection has been created |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors |
| LocationGeometry | geometry (MAX) | Geometry of the collection within the floor plan |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location |
Depending on:
- Collection
Function CollectionLocation
Returns a table that lists all the collection items related to the given collection
| Parameter | DataType | Description |
|---|---|---|
| @CollectionID | int | The ID of the collection for which the hierarchy should be listed |
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (255) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (10) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (MAX) | A short description of the collection |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry |
| LocationPlanDate | datetime | The date when the plan for the collection has been created |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location |
Depending on:
- Collection
- CollectionLocationChildNodes
Function CollectionLocationAll
Returns a table that lists all the collections including a display text with the whole hierarchy
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (500) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (50) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (MAX) | A short description of the collection |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry |
| LocationPlanDate | datetime | The date when the plan for the collection has been created |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | varchar (255) | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| DisplayText | varchar (900) | Hierarchy of the collection with acronym if present, otherwise name |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location |
Depending on:
- Collection
- UserCollectionList
Function CollectionLocationChildNodes
Returns a result set that lists all the items within a hierarchy starting at the topmost item related to the given item
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | The ID of the collection for which the hierarchy should be listed |
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (255) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (10) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (MAX) | A short description of the collection |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry |
| LocationPlanDate | datetime | The date when the plan for the collection has been created |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location |
Depending on:
- Collection
Function CollectionLocationMulti
List of collections according to the given CollectionIDs
| Parameter | DataType | Description |
|---|---|---|
| @CollectionIDs | varchar (4000) | Space separated list of CollectionIDs with a maximal length of 4000 |
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (50) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (50) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (500) | A short description of the collection |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry |
| LocationPlanDate | datetime | The date when the plan for the collection has been created |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | varchar (255) | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location |
Depending on:
- Collection
Function CollectionLocationSuperior
Returns a table that lists the given and all the items superior to the given collection
| Parameter | DataType | Description |
|---|---|---|
| @CollectionID | int | The ID of the collection for which the hierarchy should be listed |
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (255) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (10) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (MAX) | A short description of the collection |
| Location | nvarchar (255) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| LocationPlan | varchar (500) | URI or file name including path of the floor plan of the collection |
| LocationPlanWidth | float | Width of location plan in meter for calculation of size by provided geometry |
| LocationPlanDate | datetime | The date when the plan for the collection has been created |
| LocationHeight | float | Height from ground level, e.g. for the position of sensors |
| LocationGeometry | geometry (MAX) | Geometry of the collection within the floor plan |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| Type | nvarchar (50) | Type of the collection, e.g. cupboard, drawer etc. |
| LocationParentID | int | If the hierarchy of the location does not match the logical hierarchy, the ID of the parent location |
Depending on:
- Collection
Function CollectionSpecimenCoordinateList
| Parameter | DataType | Description |
|---|---|---|
| @CollectionSpecimenIDList | varchar (8000) | - |
| Column | DataType | Description |
|---|---|---|
| CollectionSpecimenID | int | Unique ID for the table CollectionSpecimen (primary key) |
| Latitude | float | - |
| Longitude | float | - |
Depending on:
- CollectionSpecimenCoordinates
Function CollectionSpecimenCoordinates
| Parameter | DataType | Description |
|---|---|---|
| @CollectionSpecimenID | int | Unique ID for the table CollectionSpecimen (primary key) |
| Column | DataType | Description |
|---|---|---|
| CollectionSpecimenID | int | Unique ID for the table CollectionSpecimen (primary key) |
| Latitude | float | - |
| Longitude | float | - |
| LocationAccuracy | nvarchar (50) | The accuracy of the determination of this locality |
| DistanceToLocation | nvarchar (50) | Distance from the specified place to the real location of the collection site (m) |
| DirectionToLocation | nvarchar (50) | Direction from the specified place to the real location of the collection site (Degrees rel. to north) |
Depending on:
- CollectionEventLocalisation
- CollectionSpecimen
Function CollectionSpecimenRelationInversList
Listing all internal relations in reverse perspective
| Column | DataType | Description |
|---|---|---|
| CollectionSpecimenID | int | Unique ID for the table CollectionSpecimen (primary key) |
| RelatedSpecimenCollectionSpecimenID | int | The CollectionSpecimenID of the dataset that refers to the current dataset |
| RelatedSpecimenAccessionNumber | nvarchar (50) | The AccessionNumber of the dataset that refers to the current dataset |
| RelationType | nvarchar (50) | Type of the relation between the specimen (= foreign key, see table CollRelationType_Enum) |
| RelatedSpecimenCollectionID | int | ID of the Collection as stored in table Collection (= foreign key, see table Collection) |
| Notes | nvarchar (MAX) | Notes on the relation to the specimen |
Depending on:
- BaseURL
- CollectionSpecimen
- CollectionSpecimenRelation
Function CollectionTaskChildNodes
Returns a result set that lists all children of a collection task within a hierarchy
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | The CollectionTaskID to which the inferior datasets are link to via column CollectionTaskParentID |
| Column | DataType | Description |
|---|---|---|
| CollectionTaskID | int | PK of the table |
| CollectionTaskParentID | int | Relation to PK for hierarchy within the data |
| CollectionID | int | Relation to table Collection. Every ColletionTask needs a relation to a collection |
| TaskID | int | Relation to table Task where details for the collection task are defined |
| DisplayOrder | int | Display order e.g. in a report. Data with value 0 will not be included |
| DisplayText | nvarchar (400) | The display text of the module related data as shown e.g. in a user interface |
| ModuleUri | varchar (500) | The URL of module related data |
| CollectionSpecimenID | int | Refers to the CollectionSpecimenID of CollectionSpecimenPart (= foreign key and part of primary key) |
| SpecimenPartID | int | Unique ID of the part of the collection specimen the task is related to. |
| TransactionID | int | ID of a transaction. Related to PK of table Transaction |
| TaskStart | datetime | The start date and or time of the collection tasks. The type is defined in table Task |
| TaskEnd | datetime | The end date and or time of the collection tasks. The type is defined in table Task |
| Result | nvarchar (400) | A text result either taken from a list or entered. The type is defined in table Task |
| URI | varchar (500) | The URI of the collection tasks. The type is defined in table Task |
| NumberValue | real | The numeric value of the collection tasks. The type is defined in table Task |
| BoolValue | bit | The boolean of the collection tasks. The type is defined in table Task |
| MetricDescription | nvarchar (500) | Description of the metric e.g. imported from a time series database like Prometheus |
| MetricSource | varchar (4000) | The source of the metric e.g. the PromQL statement for the import from a timeseries database Prometheus |
| MetricUnit | nvarchar (50) | The unit of the metric, e.g. °C for temperature |
| ResponsibleAgent | nvarchar (500) | The name of the responsible person or institution |
| ResponsibleAgentURI | varchar (500) | URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. |
| Description | nvarchar (MAX) | The description of the collection tasks. The type is defined in table Task |
| Notes | nvarchar (MAX) | The notes of the collection tasks. The type is defined in table Task |
Depending on:
- CollectionTask
Function CollectionTaskCollectionHierarchyAll
Returns a result set that lists all the collection tasks including a display text with the related collection as leading part
| Column | DataType | Description |
|---|---|---|
| CollectionTaskID | int | PK of the table |
| CollectionTaskParentID | int | Relation to PK for hierarchy within the data |
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| TaskID | int | Relation to table Task where details for the collection task are defined |
| DisplayText | nvarchar (400) | The display text of the module related data as shown e.g. in a user interface |
| DisplayOrder | int | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| ModuleUri | varchar (500) | The URL of module related data |
| CollectionSpecimenID | int | Refers to the CollectionSpecimenID of CollectionSpecimenPart (= foreign key and part of primary key) |
| SpecimenPartID | int | Unique ID of the part of the collection specimen the task is related to. |
| TransactionID | int | The ID of the transaction to which the collection task is linked |
| TaskStart | datetime | The start date and or time of the collection tasks. The type is defined in table Task |
| TaskEnd | datetime | The end date and or time of the collection tasks. The type is defined in table Task |
| Result | nvarchar (400) | A text result either taken from a list or entered. The type is defined in table Task |
| URI | varchar (500) | The URI of the collection tasks. The type is defined in table Task |
| NumberValue | real | The numeric value of the collection tasks. The type is defined in table Task |
| BoolValue | bit | The boolean of the collection tasks. The type is defined in table Task |
| MetricDescription | nvarchar (500) | Description of the metric e.g. imported from a time series database like Prometheus |
| MetricSource | varchar (4000) | The source of the metric e.g. the PromQL statement for the import from a timeseries database Prometheus |
| MetricUnit | nvarchar (50) | The unit of the metric, e.g. °C for temperature |
| ResponsibleAgent | nvarchar (500) | The name of the responsible person or institution |
| ResponsibleAgentURI | varchar (500) | URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. |
| Description | nvarchar (MAX) | A short description of the collection |
| Notes | nvarchar (MAX) | The notes of the collection tasks. The type is defined in table Task |
| HierarchyDisplayText | nvarchar (900) | Display text including the hierarchy of the collection separated by the string defined in function TaskCollectionHierarchySeparator followed by the hierarchy of the collection task separated by the string defined in function TaskHierarchySeparator followed by optional information about start, end and result of the collection task |
Depending on:
- CollectionHierarchyAll
- CollectionTask
- Task
- TaskCollectionHierarchySeparator
- TaskHierarchySeparator
Function CollectionTaskHierarchy
Returns a result set that lists all the collection tasks within a hierarchy starting at the topmost collection task related to the given collection task
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | The CollectionTaskID for which the hierarchy should be retrieved |
| Column | DataType | Description |
|---|---|---|
| CollectionTaskID | int | PK of the table |
| CollectionTaskParentID | int | Relation to PK for hierarchy within the data |
| CollectionID | int | Relation to table Collection. Every ColletionTask needs a relation to a collection |
| TaskID | int | Relation to table Task where details for the collection task are defined |
| DisplayOrder | int | Display order e.g. in a report. Data with value 0 will not be included |
| DisplayText | nvarchar (400) | The display text of the module related data as shown e.g. in a user interface |
| ModuleUri | varchar (500) | The URL of module related data |
| CollectionSpecimenID | int | Refers to the CollectionSpecimenID of CollectionSpecimenPart (= foreign key and part of primary key) |
| SpecimenPartID | int | Unique ID of the part of the collection specimen the task is related to. |
| TransactionID | int | The ID of the transaction to which the collection task is linked |
| TaskStart | datetime | The start date and or time of the collection tasks. The type is defined in table Task |
| TaskEnd | datetime | The end date and or time of the collection tasks. The type is defined in table Task |
| Result | nvarchar (400) | A text result either taken from a list or entered. The type is defined in table Task |
| URI | varchar (500) | The URI of the collection tasks. The type is defined in table Task |
| NumberValue | real | The numeric value of the collection tasks. The type is defined in table Task |
| BoolValue | bit | The boolean of the collection tasks. The type is defined in table Task |
| MetricDescription | nvarchar (500) | Description of the metric e.g. imported from a time series database like Prometheus |
| MetricSource | varchar (4000) | The source of the metric e.g. the PromQL statement for the import from a timeseries database Prometheus |
| MetricUnit | nvarchar (50) | The unit of the metric, e.g. °C for temperature |
| ResponsibleAgent | nvarchar (500) | The name of the responsible person or institution |
| ResponsibleAgentURI | varchar (500) | URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. |
| Description | nvarchar (MAX) | The description of the collection tasks. The type is defined in table Task |
| Notes | nvarchar (MAX) | The notes of the collection tasks. The type is defined in table Task |
Depending on:
- CollectionTask
- CollectionTaskChildNodes
Function CollectionTaskHierarchyAll
All CollectionTasks including a column displaying the hierarchy of the CollectionTask
| Column | DataType | Description |
|---|---|---|
| CollectionTaskID | int | PK of the table |
| CollectionTaskParentID | int | Relation to PK for hierarchy within the data |
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| TaskID | int | Relation to table Task where details for the collection task are defined |
| DisplayOrder | int | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
| Task | nvarchar (400) | Display text of the task the collection task is linked to |
| DisplayText | nvarchar (400) | The display text of the module related data as shown e.g. in a user interface |
| ModuleUri | varchar (500) | The URL of module related data |
| CollectionSpecimenID | int | Refers to the CollectionSpecimenID of CollectionSpecimenPart (= foreign key and part of primary key) |
| SpecimenPartID | int | Unique ID of the part of the collection specimen the task is related to. |
| TransactionID | int | The ID of the transaction to which the collection task is linked |
| TaskStart | datetime | The start date and or time of the collection tasks. The type is defined in table Task |
| TaskEnd | datetime | The end date and or time of the collection tasks. The type is defined in table Task |
| Result | nvarchar (400) | A text result either taken from a list or entered. The type is defined in table Task |
| URI | varchar (500) | The URI of the collection tasks. The type is defined in table Task |
| NumberValue | real | The numeric value of the collection tasks. The type is defined in table Task |
| BoolValue | bit | The boolean of the collection tasks. The type is defined in table Task |
| MetricDescription | nvarchar (500) | Description of the metric e.g. imported from a time series database like Prometheus |
| MetricSource | varchar (4000) | The source of the metric e.g. the PromQL statement for the import from a timeseries database Prometheus |
| MetricUnit | nvarchar (50) | The unit of the metric, e.g. °C for temperature |
| ResponsibleAgent | nvarchar (500) | The name of the responsible person or institution |
| ResponsibleAgentURI | varchar (500) | URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. |
| Description | nvarchar (MAX) | A short description of the collection |
| Notes | nvarchar (MAX) | The notes of the collection tasks. The type is defined in table Task |
| HierarchyDisplayText | nvarchar (4000) | Display text including the hierarchy of the collection task separated by the string defined in function TaskHierarchySeparator followed by the hierarchy of the collection separated by the string defined in function TaskCollectionHierarchySeparator |
| CollectionHierarchyDisplayText | nvarchar (4000) | Display text including the hierarchy of the collection separated by the string defined in function TaskCollectionHierarchySeparator followed by the hierarchy of the collection task separated by the string defined in function TaskHierarchySeparator |
| TaskHierarchyDisplayText | nvarchar (4000) | Deprecated |
| TaskDisplayText | nvarchar (4000) | Display text of the collection task based on diverse content in tables Task and CollectionTask |
Depending on:
- CollectionHierarchyAll
- CollectionTask
- Task
- TaskCollectionHierarchySeparator
- TaskHierarchySeparator
Function CollectionTaskParentNodes
Parent CollectionTasks for a given CollectionTask in the hierarchy
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | The CollectionTaskID for which the parent hierarchy should be retrieved |
| Column | DataType | Description |
|---|---|---|
| CollectionTaskID | int | PK of the table |
| CollectionTaskParentID | int | Relation to PK for hierarchy within the data |
| CollectionID | int | Relation to table Collection. Every ColletionTask needs a relation to a collection |
| TaskID | int | Relation to table Task where details for the collection task are defined |
| DisplayOrder | int | Display order e.g. in a report. Data with value 0 will not be included |
| DisplayText | nvarchar (400) | The display text of the module related data as shown e.g. in a user interface |
| ModuleUri | varchar (500) | The URL of module related data |
| CollectionSpecimenID | int | Refers to the CollectionSpecimenID of CollectionSpecimenPart (= foreign key and part of primary key) |
| SpecimenPartID | int | Unique ID of the part of the collection specimen the task is related to. |
| TransactionID | int | ID of a transaction. Related to PK of table Transaction |
| TaskStart | datetime | The start date and or time of the collection tasks. The type is defined in table Task |
| TaskEnd | datetime | The end date and or time of the collection tasks. The type is defined in table Task |
| Result | nvarchar (400) | A text result either taken from a list or entered. The type is defined in table Task |
| URI | varchar (500) | The URI of the collection tasks. The type is defined in table Task |
| NumberValue | real | The numeric value of the collection tasks. The type is defined in table Task |
| BoolValue | bit | The boolean of the collection tasks. The type is defined in table Task |
| MetricDescription | nvarchar (500) | Description of the metric e.g. imported from a time series database like Prometheus |
| MetricSource | varchar (4000) | The source of the metric e.g. the PromQL statement for the import from a timeseries database Prometheus |
| MetricUnit | nvarchar (50) | The unit of the metric, e.g. °C for temperature |
| ResponsibleAgent | nvarchar (500) | The name of the responsible person or institution |
| ResponsibleAgentURI | varchar (500) | URI of the person or institution responsible for the determination (= foreign key) as stored in the module DiversityAgents. |
| Description | nvarchar (MAX) | The description of the collection tasks. The type is defined in table Task |
| Notes | nvarchar (MAX) | The notes of the collection tasks. The type is defined in table Task |
Depending on:
- CollectionTask
Function CollEventDateCategory_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function CollEventImageType_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function CollExchangeType_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | - |
| Description | nvarchar (500) | - |
| Abbreviation | nvarchar (50) | - |
Function CollIdentificationCategory_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function CollIdentificationDateCategory_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function CollIdentificationQualifier_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function CollLabelType_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function CollMaterialCategory_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function CollSpecimenImageType_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function CollTranscriptionState_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | - |
| Description | nvarchar (500) | - |
| Abbreviation | nvarchar (50) | - |
Function CollTypeStatus_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function ColTaxonomicGroup_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| Code | nvarchar (50) | A text code which uniquely identifies each object in the enumeration (primary key). This value may not be changed, because the application may depend upon it. |
| Description | nvarchar (500) | Description of enumerated object, displayed in the user interface |
| Abbreviation | nvarchar (50) | - |
Function CuratorCollectionHierarchyList
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (50) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (50) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (500) | A short description of the collection |
| Location | nvarchar (1000) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | varchar (255) | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
Depending on:
- Collection
- CollectionHierarchy
- CuratorCollectionList
Function CurrentUser
Returns the information for the current user
| Column | DataType | Description |
|---|---|---|
| LoginName | nvarchar (50) | A login name which the user uses to access the DivesityWorkbench, Microsoft domains, etc. |
| CombinedNameCache | nvarchar (255) | The short name of the user, e.g. P. Smith |
| UserURI | varchar (255) | Deprecated. URI of a user in a remote module, e.g. refering to UserInfo.UserID in database DiversityUsers |
| AgentURI | varchar (255) | URI of a agent in the module DiversityAgents |
| UserName | nvarchar (255) | - |
Depending on:
- UserProxy
Function CurrentUserName
retrieval of the name of the current user
DataType: nvarchar (4000)
Depending on:
- UserProxy
Function DefaultProjectID
Returns the ID of the current project used by the user
DataType: int
Depending on:
- ProjectUser
- UserProxy
Function DiversityCollectionCacheDatabaseName
returns the name of the cache database containing the exported data for e.g. publication on the web
DataType: nvarchar (50)
Function DiversityWorkbenchModule
Name of the module
DataType: nvarchar (50)
Function EntityInformation_2
Returns the information about an entity
| Parameter | DataType | Description |
|---|---|---|
| @Entity | varchar (500) | The name of the entity, e.g. Table.Column.Content within the database or a unique string for e.g. a message within the DiversityWorkbench e.g. “DiversityWorkbench.Message.Connection.NoAccess”, PK |
| @Language | nvarchar (50) | The language to which the information should be restricted |
| @Context | nvarchar (50) | The context to which the information should be restricted |
| Column | DataType | Description |
|---|---|---|
| Entity | varchar (500) | The name of the entity, e.g. Table.Column.Content within the database or a unique string for e.g. a message within the DiversityWorkbench e.g. “DiversityWorkbench.Message.Connection.NoAccess”, PK |
| DisplayGroup | nvarchar (50) | If DiversityWorkbench entities should be displayed in a group, the name of the group |
| DisplayText | nvarchar (50) | Short abbreviated description of the object, displayed in the user interface |
| Abbreviation | nvarchar (20) | The abbreviation for the entity as shown e.g. in a user interface |
| Description | nvarchar (MAX) | Description of enumerated object, displayed in the user interface |
| Accessibility | nvarchar (50) | If the access of entity is resticted to e.g. read only or it can be edited without restrictions |
| Determination | nvarchar (50) | If a value is determined e.g. by the system or the user |
| Visibility | nvarchar (50) | If the entity is visible or hidden from e.g. a user interface |
| PresetValue | nvarchar (500) | If a value is preset, the value or SQL statement for the value, e.g. ‘determination’ for identifications when using a mobile device during an expedition |
| UsageNotes | nvarchar (4000) | Notes from table EntityUsage is present |
| DoesExist | bit | If an object does exist |
| DisplayTextOK | bit | If DisplayText is present |
| AbbreviationOK | bit | If Abbreviation is present |
| DescriptionOK | bit | If Description is present |
Depending on:
- Entity
- EntityContext_Enum
- EntityRepresentation
- EntityUsage
Function EventDescription
retrieval of the description including all superior events
DataType: nvarchar (4000)
| Parameter | DataType | Description |
|---|---|---|
| @CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) |
Depending on:
- CollectionEvent
- EventDescription
Function EventDescriptionSuperior
retrieval of the description of all superior events
DataType: nvarchar (4000)
| Parameter | DataType | Description |
|---|---|---|
| @CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) |
Depending on:
- CollectionEvent
- EventDescription
Function EventSeriesChildNodes
Returns a result set that lists all the items within a hierarchy starting at the topmost item related to the given item
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | The SeriesID for which the children should be returned |
| Column | DataType | Description |
|---|---|---|
| SeriesID | int | Primary key. The ID for this series of collection events (= primary key) |
| SeriesParentID | int | The ID of the superior series of collection events |
| DateStart | datetime | Point in time when the series of collection events started |
| DateEnd | datetime | Point in time when the series of collection events ended |
| DateSupplement | nvarchar (100) | Verbal or additional collection date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. The end date, if the collection event series comprises a period. The time of the event, if necessary. |
| SeriesCode | nvarchar (50) | The user defined code for a series of collection events |
| Description | nvarchar (MAX) | The description of the series of collection events as it will be printed on e.g. the label |
| Notes | nvarchar (MAX) | Notes on this series of collection events |
| Geography | geography (MAX) | The geography of the series of collection events |
Depending on:
- CollectionEventSeries
Function EventSeriesHierarchy
Returns a table that lists all the Series related to the given Series
| Parameter | DataType | Description |
|---|---|---|
| @SeriesID | int | Primary key. The ID for this series of collection events (= primary key) |
| Column | DataType | Description |
|---|---|---|
| SeriesID | int | Primary key. The ID for this series of collection events (= primary key) |
| SeriesParentID | int | The ID of the superior series of collection events |
| DateStart | datetime | Point in time when the series of collection events started |
| DateEnd | datetime | Point in time when the series of collection events ended |
| DateSupplement | nvarchar (100) | Verbal or additional collection date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. The end date, if the collection event series comprises a period. The time of the event, if necessary. |
| SeriesCode | nvarchar (50) | The user defined code for a series of collection events |
| Description | nvarchar (MAX) | The description of the series of collection events as it will be printed on e.g. the label |
| Notes | nvarchar (MAX) | Notes on this series of collection events |
| Geography | geography (MAX) | The geography of the series of collection events |
Depending on:
- CollectionEventSeries
- EventSeriesChildNodes
- EventSeriesTopID
Function EventSeriesSuperiorList
Returns a table that lists all the Series above the given Series.
| Parameter | DataType | Description |
|---|---|---|
| @SeriesID | int | Primary key. The ID for this series of collection events (= primary key) |
| Column | DataType | Description |
|---|---|---|
| SeriesID | int | Primary key. The ID for this series of collection events (= primary key) |
| SeriesParentID | int | The ID of the superior series of collection events |
| DateStart | datetime | Point in time when the series of collection events started |
| DateEnd | datetime | Point in time when the series of collection events ended |
| DateSupplement | nvarchar (100) | Verbal or additional collection date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. The end date, if the collection event series comprises a period. The time of the event, if necessary. |
| SeriesCode | nvarchar (50) | The user defined code for a series of collection events |
| Description | nvarchar (500) | The description of the series of collection events as it will be printed on e.g. the label |
| Notes | nvarchar (500) | Notes on this series of collection events |
| Geography | geography (MAX) | The geography of the series of collection events |
Depending on:
- CollectionEventSeries
Function EventSeriesTopID
Returns the top ID within the hierarchy for a given ID from the table CollectionEventSeries
DataType: int
| Parameter | DataType | Description |
|---|---|---|
| @SeriesID | int | Primary key. The ID for this series of collection events (= primary key) |
Depending on:
- CollectionEventSeries
Function EventSpecimenNumber
calculation of all specimen in the database that are assigned to this event, including the inferior events
DataType: int
| Parameter | DataType | Description |
|---|---|---|
| @EventID | int | The CollectionEventID for which the count should be calculated |
Depending on:
- CollectionSpecimen
Function EventSuperiorList
Returns a result set that lists all the CollectionEventIDs superior to a given event
| Parameter | DataType | Description |
|---|---|---|
| @EventID | int | - |
| Column | DataType | Description |
|---|---|---|
| CollectionEventID | int | Refers to the ID of table CollectionEvent (= foreign key and part of primary key) |
| EventParentID | int | - |
| Event | nchar (500) | - |
| SpecimenCount | int | - |
Depending on:
- EventSpecimenNumber
Function FirstLines_4
Returns a table that lists all the specimen with the first entries of related tables
| Parameter | DataType | Description |
|---|---|---|
| @CollectionSpecimenIDs | varchar (8000) | Comma separated list of CollectionSpecimenIDs for which the data should be retrieved |
| Column | DataType | Description |
|---|---|---|
| CollectionSpecimenID | int | Refers to ID of CollectionEvent (= foreign key and part of primary key) |
| Accession_number | nvarchar (50) | Accession number of the specimen within the collection, e.g. “M-29834752” |
| Data_withholding_reason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null |
| Data_withholding_reason_for_collection_event | nvarchar (255) | - |
| Data_withholding_reason_for_collector | nvarchar (255) | If the data of the collector are withhold, the reason for withholding the data, otherwise null |
| Collectors_event_number | nvarchar (50) | Number assigned to a collection event by the collector (= ‘field number’) |
| Collection_day | tinyint | The day of the date of the event or when the collection event started |
| Collection_month | tinyint | The month of the date of the event or when the collection event started |
| Collection_year | smallint | The year of the date of the event or when the collection event started |
| Collection_date_supplement | nvarchar (100) | Verbal or additional collection date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. The end date, if the collection event comprises a period. The time of the event, if necessary. |
| Collection_time | varchar (50) | The time of the event or when the collection event started |
| Collection_time_span | varchar (50) | The time span e.g. in seconds of the collection event |
| Country | nvarchar (50) | The country where the collection event took place. Cached value derived from an geographic entry |
| Locality_description | nvarchar (MAX) | Locality description of the locality exactly as written on the original label (i.e. without corrections during data entry) |
| Habitat_description | nvarchar (MAX) | Geo-ecological description of the locality exactly as written on the original label (i.e. without corrections during data entry) |
| Collecting_method | nvarchar (MAX) | Description of the method used for collecting the samples, e.g. traps, moist chambers, drag net |
| Collection_event_notes | nvarchar (MAX) | Notes about the collection event |
| Named_area | nvarchar (255) | Named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| NamedAreaLocation2 | nvarchar (255) | Link to a named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| Remove_link_to_gazetteer | int | Empty column reserved to remove a link to a named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| Distance_to_location | varchar (50) | Distance from the specified place to the real location of the collection site (m) |
| Direction_to_location | varchar (50) | Direction from the specified place to the real location of the collection site (Degrees rel. to north) |
| Longitude | nvarchar (255) | Longitude derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Latitude | nvarchar (255) | Latitude derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Coordinates_accuracy | nvarchar (50) | Accuracy of coordinates derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Link_to_GoogleMaps | int | Empty column reserved for retrieval of WGS84 coordinates via e.g. GoogleMaps corresponding to LocalisationSystemID = 8 |
| _CoordinatesLocationNotes | nvarchar (MAX) | Notes for coordinates derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Altitude_from | nvarchar (255) | Lower value of altitude range corresponding to LocalisationSystemID = 4 |
| Altitude_to | nvarchar (255) | Upper value of altitude range corresponding to LocalisationSystemID = 4 |
| Altitude_accuracy | nvarchar (50) | Accuracy of altitude range corresponding to LocalisationSystemID = 4 |
| Notes_for_Altitude | nvarchar (MAX) | Notes for altitude range corresponding to LocalisationSystemID = 4 |
| MTB | nvarchar (255) | TK25 corresponding to LocalisationSystemID = 3 |
| Quadrant | nvarchar (255) | Quadrant of TK25 corresponding to LocalisationSystemID = 3 |
| Notes_for_MTB | nvarchar (MAX) | Notes for TK25 corresponding to LocalisationSystemID = 3 |
| Sampling_plot | nvarchar (255) | Name of the sampling plot corresponding to LocalisationSystemID = 13 |
| Link_to_SamplingPlots | nvarchar (255) | Link to the sampling plot corresponding to LocalisationSystemID = 13 |
| Remove_link_to_SamplingPlots | int | Empty column reserverd to remove link to the sampling plot corresponding to LocalisationSystemID = 13 |
| Accuracy_of_sampling_plot | nvarchar (50) | Accuracy of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Latitude_of_sampling_plot | real | Latitude of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Longitude_of_sampling_plot | real | Longitude of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Geographic_region | nvarchar (255) | Geographic region corresponding to LocalisationSystemID = 10 |
| Lithostratigraphy | nvarchar (255) | Lithostratigraphy corresponding to LocalisationSystemID = 30 |
| Chronostratigraphy | nvarchar (255) | Chronostratigraphy corresponding to LocalisationSystemID = 20 |
| Biostratigraphy | nvarchar (255) | Biostratigraphy corresponding to LocalisationSystemID = 60 |
| Collectors_name | nvarchar (255) | Name of the Collector |
| Link_to_DiversityAgents | varchar (255) | Link for the first collector to DiversityAgents |
| Remove_link_for_collector | int | Empty column reserved for removal of ink for the first collector |
| Collectors_number | nvarchar (50) | Number assigned to a specimen or a batch of specimens by the collector during the collection event (= ‘field number’) |
| Notes_about_collector | nvarchar (MAX) | Notes about the first collector |
| Accession_day | tinyint | The day of the date when the specimen was acquired in the collection |
| Accession_month | tinyint | The month of the date when the specimen was acquired in the collection |
| Accession_year | smallint | The year of the date when the specimen was acquired in the collection |
| Accession_date_supplement | nvarchar (255) | Verbal or additional accession date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’ |
| Depositors_name | nvarchar (255) | The name of the depositor(s) (person or organization responsible for deposition). Where entire collections are deposited, this should also contain the collection name (e.g. ‘Herbarium P. Döbbler’) |
| Depositors_link_to_DiversityAgents | varchar (255) | The link for the depositor e.g. to DiversityAgents |
| Remove_link_for_Depositor | int | Empty column reserved to remove the link for the depositor e.g. to DiversityAgents |
| Depositors_accession_number | nvarchar (50) | Accession number of the specimen within the previous or original collection, e.g. ‘D-23948’ |
| Exsiccata_abbreviation | nvarchar (255) | If specimen is an exsiccata: Standard abbreviation of the exsiccata (not necessarily a unique identifier; editors or publication places may change over time) |
| Link_to_DiversityExsiccatae | varchar (255) | Link to DiversityExsiccatae |
| Remove_link_to_exsiccatae | int | Empty column reserved to remove the link to DiversityExsiccatae |
| Exsiccata_number | nvarchar (50) | If specimen is an exsiccata: Number of current specimen within the exsiccata series |
| Original_notes | nvarchar (MAX) | Notes found on the label of the specimen by the original collector or from a later revision |
| Additional_notes | nvarchar (MAX) | Additional notes made by the editor of the specimen record, e.g. ‘doubtful identification/locality’ |
| Internal_notes | nvarchar (MAX) | Internal notes which should not be published e.g. on websites |
| Label_title | nvarchar (255) | The title of the label e.g. for printing labels. |
| Label_type | nvarchar (50) | Printed, typewritten, typewritten with handwriting added, entirely in handwriting, etc. |
| Label_transcription_state | nvarchar (50) | The state of the transcription of a label into the database: ‘Not started’, ‘incomplete’, ‘complete’ |
| Label_transcription_notes | nvarchar (255) | User defined notes on the transcription of the label into the database |
| Problems | nvarchar (255) | Description of a problem which occurred during data editing. Typically these entries should be deleted after help has been obtained. Do not enter scientific problems here; use AdditionalNotes for such permanent problems! |
| External_datasource | int | External datasource ID |
| External_identifier | nvarchar (100) | The identifier of the external specimen as defined in the external data source |
| Taxonomic_group | nvarchar (50) | Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) |
| Relation_type | nvarchar (50) | Type of the relation between the specimen (= foreign key, see table CollRelationType_Enum) |
| Colonised_substrate_part | nvarchar (255) | If a substrate association exists: part of the substrate which is affected in the interaction (e.g. ’leaves’, if a fungus is growing on the leaves of an infected plant) |
| Life_stage | nvarchar (255) | Examples: ‘II, III’ for spore generations of rusts or ‘seed’, ‘seedling’ etc. for higher plants |
| Gender | nvarchar (50) | The sex of the organism, e.g. ‘female’ |
| Number_of_units | smallint | The number of units of this organism, e.g. 400 beetles in a bottle |
| Circumstances | nvarchar (50) | Circumstances of the occurence of the organism |
| Order_of_taxon | nvarchar (255) | Order of the first taxon |
| Family_of_taxon | nvarchar (255) | Family of the first taxon |
| Identifier_of_organism | nvarchar (50) | Identifier of the first organism |
| Description_of_organism | nvarchar (50) | Description of the first organism |
| Only_observed | bit | True, if the organism was only observed rather than collected. It is therefore not present on the preserved specimen. Example: Tree under which the collected mycorrhizal fungus grew. |
| Notes_for_organism | nvarchar (MAX) | Notes for the first organism |
| Taxonomic_name | nvarchar (255) | Valid name of the species (including the taxonomic author where available). Example: ‘Rosa canina L.’ |
| Link_to_DiversityTaxonNames | varchar (255) | Link for first identification of first organism to DiversityTaxonNames |
| Remove_link_for_identification | int | Empty column to remove link for first identification of first organism to DiversityTaxonNames |
| Vernacular_term | nvarchar (255) | Name or term other than a taxonomic (= scientific) name, e.g. ‘pine’, ’limestone’, ‘conifer’, ‘hardwood’ |
| Identification_day | tinyint | The day of the identification |
| Identification_month | tinyint | The month of the identification |
| Identification_year | smallint | The year of the identification. The year may be empty if only the day or month are known. |
| Identification_category | nvarchar (50) | Category of the identification e.g. ‘determination’, ‘confirmation’, ‘absence’ (= foreign key, see table CollIdentificationCategory_Enum) |
| Identification_qualifier | nvarchar (50) | Qualification of the identification e.g. “cf.”," aff.", “sp. nov.” (= foreign key, see table CollIdentificationQualifier_Enum) |
| Type_status | nvarchar (50) | If identification unit is type of a taxonomic name: holotype, syntype, etc. (= foreign key, see table CollTypeStatus_Enum) |
| Type_notes | nvarchar (MAX) | Notes on the typification of this specimen |
| Notes_for_identification | nvarchar (MAX) | Notes for first identification of first organism to DiversityTaxonNames |
| Determiner | nvarchar (255) | Agent responsible for first identification of first organism |
| Link_to_DiversityAgents_for_determiner | varchar (255) | Agent for responsible for first identification of first organism to e.g. DiversityAgents |
| Remove_link_for_determiner | int | Empty column reservet to remove link for agent for responsible for first identification of first organism to e.g. DiversityAgents |
| Analysis | nvarchar (50) | First analysis of first organism |
| AnalysisID | int | ID of the analysis (primary key) |
| Analysis_number | nvarchar (50) | Number of the analysis |
| Analysis_result | nvarchar (MAX) | The result of the analysis |
| Taxonomic_group_of_second_organism | nvarchar (50) | Taxonomic group of second organism |
| Life_stage_of_second_organism | nvarchar (255) | Life stage of second organism |
| Gender_of_second_organism | nvarchar (50) | Gender of second organism |
| Number_of_units_of_second_organism | smallint | Number of units of second organism |
| Circumstances_of_second_organism | nvarchar (50) | Circumstances of second organism |
| Identifier_of_second_organism | nvarchar (50) | Identifier for second organism |
| Description_of_second_organism | nvarchar (50) | UnitDescription of second organism |
| Only_observed_of_second_organism | bit | OnlyObserved of second organism |
| Notes_for_second_organism | nvarchar (MAX) | Notes for second organism |
| Taxonomic_name_of_second_organism | nvarchar (255) | Taxonomic name of second organism |
| Link_to_DiversityTaxonNames_of_second_organism | varchar (255) | Link to DiversityTaxonNames of second organism |
| Remove_link_for_second_organism | int | Remove link to DiversityTaxonNames for second organism |
| Vernacular_term_of_second_organism | nvarchar (255) | Vernacular term of second organism |
| Identification_day_of_second_organism | tinyint | Identification day of second organism |
| Identification_month_of_second_organism | tinyint | Identification month of second organism |
| Identification_year_of_second_organism | smallint | Identification year of second organism |
| Identification_category_of_second_organism | nvarchar (50) | - |
| Identification_qualifier_of_second_organism | nvarchar (50) | - |
| Type_status_of_second_organism | nvarchar (50) | - |
| Type_notes_of_second_organism | nvarchar (MAX) | - |
| Notes_for_identification_of_second_organism | nvarchar (MAX) | - |
| Determiner_of_second_organism | nvarchar (255) | - |
| Link_to_DiversityAgents_for_determiner_of_second_organism | varchar (255) | - |
| Remove_link_for_determiner_of_second_organism | int | - |
| Collection | int | - |
| Material_category | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. (= foreign key, see table CollMaterialCategory_Enum) |
| Storage_location | nvarchar (255) | A code identifying the place where the specimen is stored within the collection. Frequently the accepted scientific name is used as storage location code. |
| Stock | float | Number of stock units, if the specimen is stored in separated units e.g. several boxes or vessels (max. 255) |
| Part_accession_number | nvarchar (50) | - |
| Storage_container | nvarchar (500) | The container in which the part is stored |
| Preparation_method | nvarchar (MAX) | The method used for the preparation of the part of the specimen, e.g. the inoculation method for cultures |
| Preparation_date | datetime | Point in time when the part was preparated e.g when it was separated from the source object |
| Notes_for_part | nvarchar (MAX) | - |
| Related_specimen_URL | varchar (255) | - |
| Related_specimen_display_text | varchar (255) | The name of a related specimen as shown e.g. in a user interface |
| Link_to_DiversityCollection_for_relation | varchar (255) | - |
| Type_of_relation | nvarchar (50) | - |
| Related_specimen_description | nvarchar (MAX) | Description of the related specimen |
| Related_specimen_notes | nvarchar (MAX) | - |
| Relation_is_internal | bit | - |
| _TransactionID | int | Unique ID for the table Transaction (= foreign key and part of primary key) |
| _Transaction | nvarchar (200) | - |
| _CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) |
| _IdentificationUnitID | int | If relation refers to a certain organism within a specimen, the ID of an IdentificationUnit (= foreign key) |
| _IdentificationSequence | smallint | The sequence of the identifications. The last identification (having the highest sequence) is regarded as valid |
| _SecondUnitID | int | - |
| _SecondSequence | smallint | - |
| _SpecimenPartID | int | Unique ID of the part of the collection specimen (= part of primary key). |
| _CoordinatesAverageLatitudeCache | real | - |
| _CoordinatesAverageLongitudeCache | real | - |
| _GeographicRegionPropertyURI | varchar (255) | - |
| _LithostratigraphyPropertyURI | varchar (255) | - |
| _ChronostratigraphyPropertyURI | varchar (255) | - |
| _BiostratigraphyPropertyURI | varchar (255) | - |
| _NamedAverageLatitudeCache | real | - |
| _NamedAverageLongitudeCache | real | - |
| _LithostratigraphyPropertyHierarchyCache | nvarchar (MAX) | - |
| _ChronostratigraphyPropertyHierarchyCache | nvarchar (MAX) | - |
| _BiostratigraphyPropertyHierarchyCache | nvarchar (MAX) | - |
| _SecondUnitFamilyCache | nvarchar (255) | - |
| _SecondUnitOrderCache | nvarchar (255) | - |
| _AverageAltitudeCache | real | Calculated altitude as parsed from the location fields |
Depending on:
- Analysis
- CollectionAgent
- CollectionEvent
- CollectionEventLocalisation
- CollectionEventProperty
- CollectionSpecimen
- CollectionSpecimenID_UserAvailable
- CollectionSpecimenPart
- CollectionSpecimenRelation
- CollectionSpecimenTransaction
- Identification
- IdentificationUnit
- IdentificationUnitAnalysis
- Transaction
Function FirstLinesEvent_2
Returns a table that lists all the collection event with the first entries of related tables
| Parameter | DataType | Description |
|---|---|---|
| @CollectionSpecimenIDs | varchar (8000) | Comma separated list of CollectionSpecimenIDs for which the data should be retrieved |
| Column | DataType | Description |
|---|---|---|
| CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) |
| Data_withholding_reason_for_collection_event | nvarchar (255) | If the data of the collection event are withhold, the reason for withholding the data, otherwise null |
| Collectors_event_number | nvarchar (50) | Number assigned to a collection event by the collector (= ‘field number’) |
| Collection_day | tinyint | The day of the date of the event or when the collection event started |
| Collection_month | tinyint | The month of the date of the event or when the collection event started |
| Collection_year | smallint | The year of the date of the event or when the collection event started |
| Collection_date_supplement | nvarchar (100) | Verbal or additional collection date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. The end date, if the collection event comprises a period. The time of the event, if necessary. |
| Collection_time | varchar (50) | The time of the event or when the collection event started |
| Collection_time_span | varchar (50) | The time span e.g. in seconds of the collection event |
| Country | nvarchar (50) | The country where the collection event took place. Cached value derived from an geographic entry |
| Locality_description | nvarchar (MAX) | Locality description of the locality exactly as written on the original label (i.e. without corrections during data entry) |
| Habitat_description | nvarchar (MAX) | Geo-ecological description of the locality exactly as written on the original label (i.e. without corrections during data entry) |
| Collecting_method | nvarchar (MAX) | Description of the method used for collecting the samples, e.g. traps, moist chambers, drag net |
| Collection_event_notes | nvarchar (MAX) | Notes about the collection event |
| Named_area | nvarchar (255) | Named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| NamedAreaLocation2 | nvarchar (255) | Link to a named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| Remove_link_to_gazetteer | int | Empty column reserved to remove a link to a named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| Distance_to_location | varchar (50) | Distance from the specified place to the real location of the collection site (m) |
| Direction_to_location | varchar (50) | Direction from the specified place to the real location of the collection site (Degrees rel. to north) |
| Longitude | nvarchar (255) | Longitude derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Latitude | nvarchar (255) | Latitude derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Coordinates_accuracy | nvarchar (50) | Accuracy of coordinates derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Link_to_GoogleMaps | int | Empty column reserved for retrieval of WGS84 coordinates via e.g. GoogleMaps corresponding to LocalisationSystemID = 8 |
| Altitude_from | nvarchar (255) | Lower value of altitude range corresponding to LocalisationSystemID = 4 |
| Altitude_to | nvarchar (255) | Upper value of altitude range corresponding to LocalisationSystemID = 4 |
| Altitude_accuracy | nvarchar (50) | Accuracy of altitude range corresponding to LocalisationSystemID = 4 |
| MTB | nvarchar (255) | TK25 corresponding to LocalisationSystemID = 3 |
| Quadrant | nvarchar (255) | Quadrant of TK25 corresponding to LocalisationSystemID = 3 |
| Notes_for_MTB | nvarchar (MAX) | Notes for TK25 corresponding to LocalisationSystemID = 3 |
| MTB_accuracy | nvarchar (50) | - |
| Sampling_plot | nvarchar (255) | Name of the sampling plot corresponding to LocalisationSystemID = 13 |
| Link_to_SamplingPlots | nvarchar (255) | Link to the sampling plot corresponding to LocalisationSystemID = 13 |
| Remove_link_to_SamplingPlots | int | Empty column reserverd to remove link to the sampling plot corresponding to LocalisationSystemID = 13 |
| Accuracy_of_sampling_plot | nvarchar (50) | Accuracy of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Latitude_of_sampling_plot | real | Latitude of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Longitude_of_sampling_plot | real | Longitude of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Geographic_region | nvarchar (255) | Geographic region corresponding to LocalisationSystemID = 10 |
| Lithostratigraphy | nvarchar (255) | Lithostratigraphy corresponding to LocalisationSystemID = 30 |
| Chronostratigraphy | nvarchar (255) | Chronostratigraphy corresponding to LocalisationSystemID = 20 |
| _CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) |
| _CoordinatesAverageLatitudeCache | real | Average Latitude of WGS84 Coordinates |
| _CoordinatesAverageLongitudeCache | real | Average Longitude of WGS84 Coordinates |
| _CoordinatesLocationNotes | nvarchar (MAX) | Notes for WGS84 Coordinates |
| _GeographicRegionPropertyURI | varchar (255) | URI of Geographic Region |
| _LithostratigraphyPropertyURI | varchar (255) | URI for Lithostratigraphy |
| _ChronostratigraphyPropertyURI | varchar (255) | URI for Chronostratigraphy |
| _NamedAverageLatitudeCache | real | Average Latitude for Named Area |
| _NamedAverageLongitudeCache | real | Average Longitude for Named Area |
| _LithostratigraphyPropertyHierarchyCache | nvarchar (MAX) | Hierarchy for Lithostratigraphy |
| _ChronostratigraphyPropertyHierarchyCache | nvarchar (MAX) | Hierarchy for Chronostratigraphy |
| _AverageAltitudeCache | real | Average Altitude |
Depending on:
- CollectionEvent
- CollectionEventLocalisation
- CollectionEventProperty
- CollectionSpecimen
- CollectionSpecimenID_UserAvailable
Function FirstLinesPart_2
Returns a table that lists all the collection specimen part with the first entries of related tables
| Parameter | DataType | Description |
|---|---|---|
| @CollectionSpecimenIDs | varchar (8000) | Comma separated list of CollectionSpecimenIDs for which the data should be retrieved |
| @AnalysisIDs | varchar (8000) | Comma separated list of AnalysisIDs for which the data should be retrieved |
| @AnalysisStartDate | date | The start date of the range for analysis |
| @AnalysisEndDate | date | The end date of the range for analysis |
| @ProcessingID | int | ID of the processing method. Refers to ProcessingID in table Processing (foreign key) |
| @ProcessingStartDate | datetime | The start date of the range for processing |
| @ProcessingEndDate | datetime | The end date of the range for processing |
| Column | DataType | Description |
|---|---|---|
| SpecimenPartID | int | Unique ID of the part of the collection specimen (= part of primary key). |
| CollectionSpecimenID | int | Refers to ID of CollectionEvent (= foreign key and part of primary key) |
| Accession_number | nvarchar (50) | Accession number of the specimen within the collection, e.g. “M-29834752” |
| Data_withholding_reason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null |
| Data_withholding_reason_for_collection_event | nvarchar (255) | If the data of the collection event are withhold, the reason for withholding the data, otherwise null |
| Data_withholding_reason_for_collector | nvarchar (255) | If the data of the collector are withhold, the reason for withholding the data, otherwise null |
| Collectors_event_number | nvarchar (50) | Number assigned to a collection event by the collector (= ‘field number’) |
| Collection_day | tinyint | The day of the date of the event or when the collection event started |
| Collection_month | tinyint | The month of the date of the event or when the collection event started |
| Collection_year | smallint | The year of the date of the event or when the collection event started |
| Collection_date_supplement | nvarchar (100) | Verbal or additional collection date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. The end date, if the collection event comprises a period. The time of the event, if necessary. |
| Collection_time | varchar (50) | The time of the event or when the collection event started |
| Collection_time_span | varchar (50) | The time span e.g. in seconds of the collection event |
| Country | nvarchar (50) | The country where the collection event took place. Cached value derived from an geographic entry |
| Locality_description | nvarchar (255) | Locality description of the locality exactly as written on the original label (i.e. without corrections during data entry) |
| Habitat_description | nvarchar (255) | Geo-ecological description of the locality exactly as written on the original label (i.e. without corrections during data entry) |
| Collecting_method | nvarchar (255) | Description of the method used for collecting the samples, e.g. traps, moist chambers, drag net |
| Collection_event_notes | nvarchar (255) | Notes about the collection event |
| Named_area | nvarchar (255) | Named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| NamedAreaLocation2 | nvarchar (255) | Link to a named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| Remove_link_to_gazetteer | int | Empty column reserved to remove a link to a named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| Distance_to_location | varchar (50) | Distance from the specified place to the real location of the collection site (m) |
| Direction_to_location | varchar (50) | Direction from the specified place to the real location of the collection site (Degrees rel. to north) |
| Longitude | nvarchar (255) | Longitude derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Latitude | nvarchar (255) | Latitude derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Coordinates_accuracy | nvarchar (50) | Accuracy of coordinates derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Link_to_GoogleMaps | int | Empty column reserved for retrieval of WGS84 coordinates via e.g. GoogleMaps corresponding to LocalisationSystemID = 8 |
| Altitude_from | nvarchar (255) | Lower value of altitude range corresponding to LocalisationSystemID = 4 |
| Altitude_to | nvarchar (255) | Upper value of altitude range corresponding to LocalisationSystemID = 4 |
| Altitude_accuracy | nvarchar (50) | Accuracy of altitude range corresponding to LocalisationSystemID = 4 |
| Notes_for_Altitude | nvarchar (255) | Notes for altitude range corresponding to LocalisationSystemID = 4 |
| MTB | nvarchar (255) | TK25 corresponding to LocalisationSystemID = 3 |
| Quadrant | nvarchar (255) | Quadrant of TK25 corresponding to LocalisationSystemID = 3 |
| Notes_for_MTB | nvarchar (255) | Notes for TK25 corresponding to LocalisationSystemID = 3 |
| Sampling_plot | nvarchar (255) | Name of the sampling plot corresponding to LocalisationSystemID = 13 |
| Link_to_SamplingPlots | nvarchar (255) | Link to the sampling plot corresponding to LocalisationSystemID = 13 |
| Remove_link_to_SamplingPlots | int | Empty column reserverd to remove link to the sampling plot corresponding to LocalisationSystemID = 13 |
| Accuracy_of_sampling_plot | nvarchar (50) | Accuracy of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Latitude_of_sampling_plot | real | Latitude of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Longitude_of_sampling_plot | real | Longitude of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Geographic_region | nvarchar (255) | Geographic region corresponding to LocalisationSystemID = 10 |
| Lithostratigraphy | nvarchar (255) | Lithostratigraphy corresponding to LocalisationSystemID = 30 |
| Chronostratigraphy | nvarchar (255) | Chronostratigraphy corresponding to LocalisationSystemID = 20 |
| Collectors_name | nvarchar (255) | Name of the Collector |
| Link_to_DiversityAgents | varchar (255) | Link for the first collector to DiversityAgents |
| Remove_link_for_collector | int | - |
| Collectors_number | nvarchar (50) | Number assigned to a specimen or a batch of specimens by the collector during the collection event (= ‘field number’) |
| Notes_about_collector | nvarchar (MAX) | Notes about the first collector |
| Accession_day | tinyint | The day of the date when the specimen was acquired in the collection |
| Accession_month | tinyint | The month of the date when the specimen was acquired in the collection |
| Accession_year | smallint | The year of the date when the specimen was acquired in the collection |
| Accession_date_supplement | nvarchar (255) | Verbal or additional accession date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’ |
| Depositors_name | nvarchar (255) | The name of the depositor(s) (person or organization responsible for deposition). Where entire collections are deposited, this should also contain the collection name (e.g. ‘Herbarium P. Döbbler’) |
| Depositors_link_to_DiversityAgents | varchar (255) | The link for the depositor e.g. to DiversityAgents |
| Remove_link_for_Depositor | int | Empty column reserved to remove the link for the depositor e.g. to DiversityAgents |
| Depositors_accession_number | nvarchar (50) | Accession number of the specimen within the previous or original collection, e.g. ‘D-23948’ |
| Exsiccata_abbreviation | nvarchar (255) | If specimen is an exsiccata: Standard abbreviation of the exsiccata (not necessarily a unique identifier; editors or publication places may change over time) |
| Link_to_DiversityExsiccatae | varchar (255) | Link to DiversityExsiccatae |
| Remove_link_to_exsiccatae | int | Empty column reserved to remove the link to DiversityExsiccatae |
| Exsiccata_number | nvarchar (50) | If specimen is an exsiccata: Number of current specimen within the exsiccata series |
| Original_notes | nvarchar (MAX) | Notes found on the label of the specimen by the original collector or from a later revision |
| Additional_notes | nvarchar (MAX) | Additional notes made by the editor of the specimen record, e.g. ‘doubtful identification/locality’ |
| Internal_notes | nvarchar (MAX) | Internal notes which should not be published e.g. on websites |
| Label_title | nvarchar (255) | The title of the label e.g. for printing labels. |
| Label_type | nvarchar (50) | Printed, typewritten, typewritten with handwriting added, entirely in handwriting, etc. |
| Label_transcription_state | nvarchar (50) | The state of the transcription of a label into the database: ‘Not started’, ‘incomplete’, ‘complete’ |
| Label_transcription_notes | nvarchar (255) | User defined notes on the transcription of the label into the database |
| Problems | nvarchar (255) | Description of a problem which occurred during data editing. Typically these entries should be deleted after help has been obtained. Do not enter scientific problems here; use AdditionalNotes for such permanent problems! |
| Taxonomic_group | nvarchar (50) | Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) |
| Relation_type | nvarchar (50) | The relation of a unit to its substrate, e.g. parasitism, symbiosis etc. as stored in CollRelationType_Enum (= foreign key) |
| Colonised_substrate_part | nvarchar (255) | If a substrate association exists: part of the substrate which is affected in the interaction (e.g. ’leaves’, if a fungus is growing on the leaves of an infected plant) |
| Related_organism | nvarchar (200) | Last identification of the related organism |
| Life_stage | nvarchar (255) | Examples: ‘II, III’ for spore generations of rusts or ‘seed’, ‘seedling’ etc. for higher plants |
| Gender | nvarchar (50) | The sex of the organism, e.g. ‘female’ |
| Number_of_units | smallint | The number of units of this organism, e.g. 400 beetles in a bottle |
| Circumstances | nvarchar (50) | Circumstances of the occurence of the organism |
| Order_of_taxon | nvarchar (255) | Order of the first taxon |
| Family_of_taxon | nvarchar (255) | Family of the first taxon |
| Identifier_of_organism | nvarchar (50) | Identifier of the first organism |
| Description_of_organism | nvarchar (50) | Description of the first organism |
| Only_observed | bit | True, if the organism was only observed rather than collected. It is therefore not present on the preserved specimen. Example: Tree under which the collected mycorrhizal fungus grew. |
| Notes_for_organism | nvarchar (MAX) | Notes for the first organism |
| Taxonomic_name | nvarchar (255) | Valid name of the species (including the taxonomic author where available). Example: ‘Rosa canina L.’ |
| Link_to_DiversityTaxonNames | varchar (255) | Link for first identification of first organism to DiversityTaxonNames |
| Remove_link_for_identification | int | Empty column to remove link for first identification of first organism to DiversityTaxonNames |
| Vernacular_term | nvarchar (255) | Name or term other than a taxonomic (= scientific) name, e.g. ‘pine’, ’limestone’, ‘conifer’, ‘hardwood’ |
| Identification_day | tinyint | The day of the identification |
| Identification_month | tinyint | The month of the identification |
| Identification_year | smallint | The year of the identification. The year may be empty if only the day or month are known. |
| Identification_category | nvarchar (50) | Category of the identification e.g. ‘determination’, ‘confirmation’, ‘absence’ (= foreign key, see table CollIdentificationCategory_Enum) |
| Identification_qualifier | nvarchar (50) | Qualification of the identification e.g. “cf.”," aff.", “sp. nov.” (= foreign key, see table CollIdentificationQualifier_Enum) |
| Type_status | nvarchar (50) | If identification unit is type of a taxonomic name: holotype, syntype, etc. (= foreign key, see table CollTypeStatus_Enum) |
| Type_notes | nvarchar (MAX) | Notes on the typification of this specimen |
| Notes_for_identification | nvarchar (MAX) | Notes for first identification of first organism to DiversityTaxonNames |
| Determiner | nvarchar (255) | Agent responsible for first identification of first organism |
| Link_to_DiversityAgents_for_determiner | varchar (255) | Agent for responsible for first identification of first organism to e.g. DiversityAgents |
| Remove_link_for_determiner | int | Empty column reservet to remove link for agent for responsible for first identification of first organism to e.g. DiversityAgents |
| Analysis_0 | nvarchar (50) | Name of analysis 0 according to list of AnalysisIDs |
| AnalysisID_0 | int | ID of analysis 0 according to list of AnalysisIDs |
| Analysis_number_0 | nvarchar (50) | Number of analysis 0 according to list of AnalysisIDs |
| Analysis_result_0 | nvarchar (MAX) | Result of analysis 0 according to list of AnalysisIDs |
| Analysis_1 | nvarchar (50) | Name of analysis_1 according to list of AnalysisIDs |
| AnalysisID_1 | int | ID of analysis_1 according to list of AnalysisIDs |
| Analysis_number_1 | nvarchar (50) | Number of analysis_1 according to list of AnalysisIDs |
| Analysis_result_1 | nvarchar (MAX) | Result of analysis_1 according to list of AnalysisIDs |
| Analysis_2 | nvarchar (50) | Name of analysis_2 according to list of AnalysisIDs |
| AnalysisID_2 | int | ID of analysis_2 according to list of AnalysisIDs |
| Analysis_number_2 | nvarchar (50) | Number of analysis_2 according to list of AnalysisIDs |
| Analysis_result_2 | nvarchar (MAX) | Result of analysis_2 according to list of AnalysisIDs |
| Analysis_3 | nvarchar (50) | Name of analysis_3 according to list of AnalysisIDs |
| AnalysisID_3 | int | ID of analysis_3 according to list of AnalysisIDs |
| Analysis_number_3 | nvarchar (50) | Number of analysis_3 according to list of AnalysisIDs |
| Analysis_result_3 | nvarchar (MAX) | Result of analysis_3 according to list of AnalysisIDs |
| Analysis_4 | nvarchar (50) | Name of analysis_4 according to list of AnalysisIDs |
| AnalysisID_4 | int | ID of analysis_4 according to list of AnalysisIDs |
| Analysis_number_4 | nvarchar (50) | Number of analysis_4 according to list of AnalysisIDs |
| Analysis_result_4 | nvarchar (MAX) | Result of analysis_4 according to list of AnalysisIDs |
| Analysis_5 | nvarchar (50) | Name of analysis_5 according to list of AnalysisIDs |
| AnalysisID_5 | int | ID of analysis_5 according to list of AnalysisIDs |
| Analysis_number_5 | nvarchar (50) | Number of analysis_5 according to list of AnalysisIDs |
| Analysis_result_5 | nvarchar (MAX) | Result of analysis_5 according to list of AnalysisIDs |
| Analysis_6 | nvarchar (50) | Name of analysis_6 according to list of AnalysisIDs |
| AnalysisID_6 | int | ID of analysis_6 according to list of AnalysisIDs |
| Analysis_number_6 | nvarchar (50) | Number of analysis_6 according to list of AnalysisIDs |
| Analysis_result_6 | nvarchar (MAX) | Result of analysis_6 according to list of AnalysisIDs |
| Analysis_7 | nvarchar (50) | Name of analysis_7 according to list of AnalysisIDs |
| AnalysisID_7 | int | ID of analysis_7 according to list of AnalysisIDs |
| Analysis_number_7 | nvarchar (50) | Number of analysis_7 according to list of AnalysisIDs |
| Analysis_result_7 | nvarchar (MAX) | Result of analysis_7 according to list of AnalysisIDs |
| Analysis_8 | nvarchar (50) | Name of analysis_8 according to list of AnalysisIDs |
| AnalysisID_8 | int | ID of analysis_8 according to list of AnalysisIDs |
| Analysis_number_8 | nvarchar (50) | Number of analysis_8 according to list of AnalysisIDs |
| Analysis_result_8 | nvarchar (MAX) | Result of analysis_8 according to list of AnalysisIDs |
| Analysis_9 | nvarchar (50) | Name of analysis_9 according to list of AnalysisIDs |
| AnalysisID_9 | int | ID of analysis_9 according to list of AnalysisIDs |
| Analysis_number_9 | nvarchar (50) | Number of analysis_9 according to list of AnalysisIDs |
| Analysis_result_9 | nvarchar (MAX) | Result of analysis_9 according to list of AnalysisIDs |
| Preparation_method | nvarchar (MAX) | The method used for the preparation of the part of the specimen, e.g. the inoculation method for cultures |
| Preparation_date | datetime | Point in time when the part was preparated e.g when it was separated from the source object |
| Part_accession_number | nvarchar (50) | Accession number of part |
| Part_sublabel | nvarchar (50) | The label for a part of a specimen, e.g. “cone”, or a number attached to a duplicate of a specimen |
| Collection | int | ID of the Collection |
| Material_category | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. (= foreign key, see table CollMaterialCategory_Enum) |
| Storage_location | nvarchar (255) | A code identifying the place where the specimen is stored within the collection. Frequently the accepted scientific name is used as storage location code. |
| Storage_container | nvarchar (500) | The container in which the part is stored |
| Stock | float | Number of stock units, if the specimen is stored in separated units e.g. several boxes or vessels (max. 255) |
| Stock_unit | nvarchar (50) | If empty, the stock is given as a count, else it contains the unit in which stock is expressed, e.g. µl, ml, kg etc. |
| Notes_for_part | nvarchar (MAX) | Notes for specimen part |
| Description_of_unit_in_part | nvarchar (500) | Description of identification unit in specimen part |
| Processing_date_1 | datetime | Date of Processing_1 according to list of ProcessingIDs |
| ProcessingID_1 | int | ID of Processing_1 according to list of ProcessingIDs |
| Processing_Protocoll_1 | nvarchar (100) | Protocoll of Processing_1 according to list of ProcessingIDs |
| Processing_duration_1 | varchar (50) | Duration of Processing_1 according to list of ProcessingIDs |
| Processing_notes_1 | nvarchar (MAX) | Notes of Processing_1 according to list of ProcessingIDs |
| Processing_date_2 | datetime | Date of Processing_2 according to list of ProcessingIDs |
| ProcessingID_2 | int | ID of Processing_2 according to list of ProcessingIDs |
| Processing_Protocoll_2 | nvarchar (100) | Protocoll of Processing_2 according to list of ProcessingIDs |
| Processing_duration_2 | varchar (50) | Duration of Processing_2 according to list of ProcessingIDs |
| Processing_notes_2 | nvarchar (MAX) | Notes of Processing_2 according to list of ProcessingIDs |
| Processing_date_3 | datetime | Date of Processing_3 according to list of ProcessingIDs |
| ProcessingID_3 | int | ID of Processing_3 according to list of ProcessingIDs |
| Processing_Protocoll_3 | nvarchar (100) | Protocoll of Processing_3 according to list of ProcessingIDs |
| Processing_duration_3 | varchar (50) | Duration of Processing_3 according to list of ProcessingIDs |
| Processing_notes_3 | nvarchar (MAX) | Notes of Processing_3 according to list of ProcessingIDs |
| Processing_date_4 | datetime | Date of Processing_4 according to list of ProcessingIDs |
| ProcessingID_4 | int | ID of Processing_4 according to list of ProcessingIDs |
| Processing_Protocoll_4 | nvarchar (100) | Protocoll of Processing_4 according to list of ProcessingIDs |
| Processing_duration_4 | varchar (50) | Duration of Processing_4 according to list of ProcessingIDs |
| Processing_notes_4 | nvarchar (MAX) | Notes of Processing_4 according to list of ProcessingIDs |
| Processing_date_5 | datetime | Date of Processing_5 according to list of ProcessingIDs |
| ProcessingID_5 | int | ID of Processing_5 according to list of ProcessingIDs |
| Processing_Protocoll_5 | nvarchar (100) | Protocoll of Processing_5 according to list of ProcessingIDs |
| Processing_duration_5 | varchar (50) | Duration of Processing_5 according to list of ProcessingIDs |
| Processing_notes_5 | nvarchar (MAX) | Notes of Processing_5 according to list of ProcessingIDs |
| _TransactionID | int | Unique ID for the table Transaction (= foreign key and part of primary key) |
| _Transaction | nvarchar (200) | Title of first transaction |
| On_loan | int | If part is on loan according to first transaction |
| _CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) |
| _IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) |
| _IdentificationSequence | smallint | The sequence of the identifications. The last identification (having the highest sequence) is regarded as valid |
| _SpecimenPartID | int | Unique ID of the part of the collection specimen (= part of primary key). |
| _CoordinatesAverageLatitudeCache | real | Average Latitude of WGS84 Coordinates |
| _CoordinatesAverageLongitudeCache | real | Average Longitude of WGS84 Coordinates |
| _CoordinatesLocationNotes | nvarchar (255) | Notes for WGS84 Coordinates |
| _GeographicRegionPropertyURI | varchar (255) | URI of Geographic Region |
| _LithostratigraphyPropertyURI | varchar (255) | URI for Lithostratigraphy |
| _ChronostratigraphyPropertyURI | varchar (255) | URI for Chronostratigraphy |
| _NamedAverageLatitudeCache | real | Average Latitude for Named Area |
| _NamedAverageLongitudeCache | real | Average Longitude for Named Area |
| _LithostratigraphyPropertyHierarchyCache | nvarchar (255) | Hierarchy for Lithostratigraphy |
| _ChronostratigraphyPropertyHierarchyCache | nvarchar (255) | Hierarchy for Chronostratigraphy |
| _AverageAltitudeCache | real | Calculated average altitude as parsed from the location fields |
Depending on:
- Analysis
- CollectionAgent
- CollectionEvent
- CollectionEventLocalisation
- CollectionEventProperty
- CollectionSpecimen
- CollectionSpecimenID_UserAvailable
- CollectionSpecimenPart
- CollectionSpecimenProcessing
- CollectionSpecimenTransaction
- Identification
- IdentificationUnit
- IdentificationUnitAnalysis
- IdentificationUnitInPart
- Processing
- Transaction
Function FirstLinesSeries
Returns a table that lists all the collection event series with the first entries of related tables
| Parameter | DataType | Description |
|---|---|---|
| @CollectionSpecimenIDs | varchar (8000) | Comma separated list of CollectionSpecimenIDs for which the data should be retrieved |
| Column | DataType | Description |
|---|---|---|
| SeriesID | int | The ID of the related expedition. Relates to the PK of the table CollectionExpedition (foreign key). |
| SeriesParentID | int | The ID of the superior series of collection events |
| Description | nvarchar (500) | The description of the series of collection events as it will be printed on e.g. the label |
| SeriesCode | nvarchar (50) | The user defined code for a series of collection events |
| Geography | geography (MAX) | The geography of the series of collection events |
| Notes | nvarchar (500) | Notes on the collection event |
| DateStart | datetime | Point in time when the series of collection events started |
| DateEnd | datetime | Point in time when the series of collection events ended |
Depending on:
- CollectionEvent
- CollectionEventSeries
- CollectionSpecimen
- CollectionSpecimenID_UserAvailable
Function FirstLinesUnit_4
Returns a table that lists all the identification unit with the first entries of related tables
| Parameter | DataType | Description |
|---|---|---|
| @CollectionSpecimenIDs | varchar (8000) | Comma separated list of CollectionSpecimenIDs for which the data should be retrieved |
| @AnalysisIDs | varchar (8000) | Comma separated list of AnalysisIDs for which the data should be retrieved |
| @AnalysisStartDate | date | The start date of the range for analysis |
| @AnalysisEndDate | date | The end date of the range for analysis |
| Column | DataType | Description |
|---|---|---|
| IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) |
| CollectionSpecimenID | int | Refers to ID of CollectionEvent (= foreign key and part of primary key) |
| Accession_number | nvarchar (50) | Accession number of the specimen within the collection, e.g. “M-29834752” |
| Data_withholding_reason | nvarchar (255) | If the data set is withhold, the reason for withholding the data, otherwise null |
| Data_withholding_reason_for_collection_event | nvarchar (255) | If the data of the collection event are withhold, the reason for withholding the data, otherwise null |
| Data_withholding_reason_for_collector | nvarchar (255) | If the data of the collector are withhold, the reason for withholding the data, otherwise null |
| Collectors_event_number | nvarchar (50) | Number assigned to a collection event by the collector (= ‘field number’) |
| Collection_day | tinyint | The day of the date of the event or when the collection event started |
| Collection_month | tinyint | The month of the date of the event or when the collection event started |
| Collection_year | smallint | The year of the date of the event or when the collection event started |
| Collection_date_supplement | nvarchar (100) | Verbal or additional collection date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. The end date, if the collection event comprises a period. The time of the event, if necessary. |
| Collection_time | varchar (50) | The time of the event or when the collection event started |
| Collection_time_span | varchar (50) | The time span e.g. in seconds of the collection event |
| Country | nvarchar (50) | - |
| Locality_description | nvarchar (255) | Locality description of the locality exactly as written on the original label (i.e. without corrections during data entry) |
| Habitat_description | nvarchar (255) | Geo-ecological description of the locality exactly as written on the original label (i.e. without corrections during data entry) |
| Collecting_method | nvarchar (255) | Description of the method used for collecting the samples, e.g. traps, moist chambers, drag net |
| Collection_event_notes | nvarchar (255) | Notes about the collection event |
| Named_area | nvarchar (255) | Named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| NamedAreaLocation2 | nvarchar (255) | Link to a named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| Remove_link_to_gazetteer | int | Empty column reserved to remove a link to a named area as e.g. derived from DiversityGazetteer corresponding to LocalisationSystemID = 7 |
| Distance_to_location | varchar (50) | Distance from the specified place to the real location of the collection site (m) |
| Direction_to_location | varchar (50) | Direction from the specified place to the real location of the collection site (Degrees rel. to north) |
| Longitude | nvarchar (255) | Longitude derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Latitude | nvarchar (255) | Latitude derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Coordinates_accuracy | nvarchar (50) | Accuracy of coordinates derived from WGS84 corresponding to LocalisationSystemID = 8 |
| Link_to_GoogleMaps | int | Empty column reserved for retrieval of WGS84 coordinates via e.g. GoogleMaps corresponding to LocalisationSystemID = 8 |
| Altitude_from | nvarchar (255) | Lower value of altitude range corresponding to LocalisationSystemID = 4 |
| Altitude_to | nvarchar (255) | Upper value of altitude range corresponding to LocalisationSystemID = 4 |
| Altitude_accuracy | nvarchar (50) | Accuracy of altitude range corresponding to LocalisationSystemID = 4 |
| Notes_for_Altitude | nvarchar (255) | Notes for altitude range corresponding to LocalisationSystemID = 4 |
| MTB | nvarchar (255) | TK25 corresponding to LocalisationSystemID = 3 |
| Quadrant | nvarchar (255) | Quadrant of TK25 corresponding to LocalisationSystemID = 3 |
| Notes_for_MTB | nvarchar (255) | Notes for TK25 corresponding to LocalisationSystemID = 3 |
| Sampling_plot | nvarchar (255) | Name of the sampling plot corresponding to LocalisationSystemID = 13 |
| Link_to_SamplingPlots | nvarchar (255) | Link to the sampling plot corresponding to LocalisationSystemID = 13 |
| Remove_link_to_SamplingPlots | int | Empty column reserverd to remove link to the sampling plot corresponding to LocalisationSystemID = 13 |
| Accuracy_of_sampling_plot | nvarchar (50) | Accuracy of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Latitude_of_sampling_plot | real | Latitude of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Longitude_of_sampling_plot | real | Longitude of the sampling plot coordinates corresponding to LocalisationSystemID = 13 |
| Geographic_region | nvarchar (255) | Geographic region corresponding to LocalisationSystemID = 10 |
| Lithostratigraphy | nvarchar (255) | Lithostratigraphy corresponding to LocalisationSystemID = 30 |
| Chronostratigraphy | nvarchar (255) | Chronostratigraphy corresponding to LocalisationSystemID = 20 |
| Collectors_name | nvarchar (255) | Name of the Collector |
| Link_to_DiversityAgents | varchar (255) | Link for the first collector to DiversityAgents |
| Remove_link_for_collector | int | Empty column reserved for removal of ink for the first collector |
| Collectors_number | nvarchar (50) | Number assigned to a specimen or a batch of specimens by the collector during the collection event (= ‘field number’) |
| Notes_about_collector | nvarchar (MAX) | Notes about the first collector |
| Accession_day | tinyint | The day of the date when the specimen was acquired in the collection |
| Accession_month | tinyint | The month of the date when the specimen was acquired in the collection |
| Accession_year | smallint | The year of the date when the specimen was acquired in the collection |
| Accession_date_supplement | nvarchar (255) | Verbal or additional accession date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’ |
| Depositors_name | nvarchar (255) | The name of the depositor(s) (person or organization responsible for deposition). Where entire collections are deposited, this should also contain the collection name (e.g. ‘Herbarium P. Döbbler’) |
| Depositors_link_to_DiversityAgents | varchar (255) | The link for the depositor e.g. to DiversityAgents |
| Remove_link_for_Depositor | int | Empty column reserved to remove the link for the depositor e.g. to DiversityAgents |
| Depositors_accession_number | nvarchar (50) | Accession number of the specimen within the previous or original collection, e.g. ‘D-23948’ |
| Exsiccata_abbreviation | nvarchar (255) | If specimen is an exsiccata: Standard abbreviation of the exsiccata (not necessarily a unique identifier; editors or publication places may change over time) |
| Link_to_DiversityExsiccatae | varchar (255) | Link to DiversityExsiccatae |
| Remove_link_to_exsiccatae | int | Empty column reserved to remove the link to DiversityExsiccatae |
| Exsiccata_number | nvarchar (50) | If specimen is an exsiccata: Number of current specimen within the exsiccata series |
| Original_notes | nvarchar (MAX) | Notes found on the label of the specimen by the original collector or from a later revision |
| Additional_notes | nvarchar (MAX) | Additional notes made by the editor of the specimen record, e.g. ‘doubtful identification/locality’ |
| Internal_notes | nvarchar (MAX) | Internal notes which should not be published e.g. on websites |
| Label_title | nvarchar (255) | The title of the label e.g. for printing labels. |
| Label_type | nvarchar (50) | Printed, typewritten, typewritten with handwriting added, entirely in handwriting, etc. |
| Label_transcription_state | nvarchar (50) | The state of the transcription of a label into the database: ‘Not started’, ‘incomplete’, ‘complete’ |
| Label_transcription_notes | nvarchar (255) | User defined notes on the transcription of the label into the database |
| Problems | nvarchar (255) | Description of a problem which occurred during data editing. Typically these entries should be deleted after help has been obtained. Do not enter scientific problems here; use AdditionalNotes for such permanent problems! |
| Taxonomic_group | nvarchar (50) | Taxonomic group the organism, identified by this unit, belongs to. Groups listed in table CollTaxonomicGroup_Enum (= foreign key) |
| Relation_type | nvarchar (50) | The relation of a unit to its substrate, e.g. parasitism, symbiosis etc. as stored in CollRelationType_Enum (= foreign key) |
| Colonised_substrate_part | nvarchar (255) | If a substrate association exists: part of the substrate which is affected in the interaction (e.g. ’leaves’, if a fungus is growing on the leaves of an infected plant) |
| Related_organism | nvarchar (200) | Last identification of the related organism |
| Life_stage | nvarchar (255) | Examples: ‘II, III’ for spore generations of rusts or ‘seed’, ‘seedling’ etc. for higher plants |
| Gender | nvarchar (50) | The sex of the organism, e.g. ‘female’ |
| Number_of_units | smallint | The number of units of this organism, e.g. 400 beetles in a bottle |
| Circumstances | nvarchar (50) | Circumstances of the occurence of the organism |
| Order_of_taxon | nvarchar (255) | Order of the first taxon |
| Family_of_taxon | nvarchar (255) | Family of the first taxon |
| Identifier_of_organism | nvarchar (50) | Identifier of the first organism |
| Description_of_organism | nvarchar (50) | Description of the first organism |
| Only_observed | bit | True, if the organism was only observed rather than collected. It is therefore not present on the preserved specimen. Example: Tree under which the collected mycorrhizal fungus grew. |
| Notes_for_organism | nvarchar (MAX) | Notes for the first organism |
| Taxonomic_name | nvarchar (255) | Valid name of the species (including the taxonomic author where available). Example: ‘Rosa canina L.’ |
| Link_to_DiversityTaxonNames | varchar (255) | Link for first identification of first organism to DiversityTaxonNames |
| Remove_link_for_identification | int | Empty column to remove link for first identification of first organism to DiversityTaxonNames |
| Vernacular_term | nvarchar (255) | Name or term other than a taxonomic (= scientific) name, e.g. ‘pine’, ’limestone’, ‘conifer’, ‘hardwood’ |
| Identification_day | tinyint | The day of the identification |
| Identification_month | tinyint | The month of the identification |
| Identification_year | smallint | The year of the identification. The year may be empty if only the day or month are known. |
| Identification_category | nvarchar (50) | Category of the identification e.g. ‘determination’, ‘confirmation’, ‘absence’ (= foreign key, see table CollIdentificationCategory_Enum) |
| Identification_qualifier | nvarchar (50) | Qualification of the identification e.g. “cf.”," aff.", “sp. nov.” (= foreign key, see table CollIdentificationQualifier_Enum) |
| Type_status | nvarchar (50) | If identification unit is type of a taxonomic name: holotype, syntype, etc. (= foreign key, see table CollTypeStatus_Enum) |
| Type_notes | nvarchar (MAX) | Notes on the typification of this specimen |
| Notes_for_identification | nvarchar (MAX) | Notes for first identification of first organism to DiversityTaxonNames |
| Determiner | nvarchar (255) | Agent responsible for first identification of first organism |
| Link_to_DiversityAgents_for_determiner | varchar (255) | Agent for responsible for first identification of first organism to e.g. DiversityAgents |
| Remove_link_for_determiner | int | Empty column reservet to remove link for agent for responsible for first identification of first organism to e.g. DiversityAgents |
| Analysis_0 | nvarchar (50) | Name of analysis 0 according to list of AnalysisIDs |
| AnalysisID_0 | int | ID of analysis 0 according to list of AnalysisIDs |
| Analysis_number_0 | nvarchar (50) | Number of analysis 0 according to list of AnalysisIDs |
| Analysis_result_0 | nvarchar (MAX) | Result of analysis 0 according to list of AnalysisIDs |
| Analysis_1 | nvarchar (50) | Name of analysis_1 according to list of AnalysisIDs |
| AnalysisID_1 | int | ID of analysis_1 according to list of AnalysisIDs |
| Analysis_number_1 | nvarchar (50) | Number of analysis_1 according to list of AnalysisIDs |
| Analysis_result_1 | nvarchar (MAX) | Result of analysis_1 according to list of AnalysisIDs |
| Analysis_2 | nvarchar (50) | Name of analysis_2 according to list of AnalysisIDs |
| AnalysisID_2 | int | ID of analysis_2 according to list of AnalysisIDs |
| Analysis_number_2 | nvarchar (50) | Number of analysis_2 according to list of AnalysisIDs |
| Analysis_result_2 | nvarchar (MAX) | Result of analysis_2 according to list of AnalysisIDs |
| Analysis_3 | nvarchar (50) | Name of analysis_3 according to list of AnalysisIDs |
| AnalysisID_3 | int | ID of analysis_3 according to list of AnalysisIDs |
| Analysis_number_3 | nvarchar (50) | Number of analysis_3 according to list of AnalysisIDs |
| Analysis_result_3 | nvarchar (MAX) | Result of analysis_3 according to list of AnalysisIDs |
| Analysis_4 | nvarchar (50) | Name of analysis_4 according to list of AnalysisIDs |
| AnalysisID_4 | int | ID of analysis_4 according to list of AnalysisIDs |
| Analysis_number_4 | nvarchar (50) | Number of analysis_4 according to list of AnalysisIDs |
| Analysis_result_4 | nvarchar (MAX) | Result of analysis_4 according to list of AnalysisIDs |
| Analysis_5 | nvarchar (50) | Name of analysis_5 according to list of AnalysisIDs |
| AnalysisID_5 | int | ID of analysis_5 according to list of AnalysisIDs |
| Analysis_number_5 | nvarchar (50) | Number of analysis_5 according to list of AnalysisIDs |
| Analysis_result_5 | nvarchar (MAX) | Result of analysis_5 according to list of AnalysisIDs |
| Analysis_6 | nvarchar (50) | Name of analysis_6 according to list of AnalysisIDs |
| AnalysisID_6 | int | ID of analysis_6 according to list of AnalysisIDs |
| Analysis_number_6 | nvarchar (50) | Number of analysis_6 according to list of AnalysisIDs |
| Analysis_result_6 | nvarchar (MAX) | Result of analysis_6 according to list of AnalysisIDs |
| Analysis_7 | nvarchar (50) | Name of analysis_7 according to list of AnalysisIDs |
| AnalysisID_7 | int | ID of analysis_7 according to list of AnalysisIDs |
| Analysis_number_7 | nvarchar (50) | Number of analysis_7 according to list of AnalysisIDs |
| Analysis_result_7 | nvarchar (MAX) | Result of analysis_7 according to list of AnalysisIDs |
| Analysis_8 | nvarchar (50) | Name of analysis_8 according to list of AnalysisIDs |
| AnalysisID_8 | int | ID of analysis_8 according to list of AnalysisIDs |
| Analysis_number_8 | nvarchar (50) | Number of analysis_8 according to list of AnalysisIDs |
| Analysis_result_8 | nvarchar (MAX) | Result of analysis_8 according to list of AnalysisIDs |
| Analysis_9 | nvarchar (50) | Name of analysis_9 according to list of AnalysisIDs |
| AnalysisID_9 | int | ID of analysis_9 according to list of AnalysisIDs |
| Analysis_number_9 | nvarchar (50) | Number of analysis_9 according to list of AnalysisIDs |
| Analysis_result_9 | nvarchar (MAX) | Result of analysis_9 according to list of AnalysisIDs |
| Collection | int | - |
| Material_category | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. (= foreign key, see table CollMaterialCategory_Enum) |
| Storage_location | nvarchar (255) | A code identifying the place where the specimen is stored within the collection. Frequently the accepted scientific name is used as storage location code. |
| Stock | float | Number of stock units, if the specimen is stored in separated units e.g. several boxes or vessels (max. 255) |
| Preparation_method | nvarchar (MAX) | The method used for the preparation of the part of the specimen, e.g. the inoculation method for cultures |
| Preparation_date | datetime | Point in time when the part was preparated e.g when it was separated from the source object |
| Notes_for_part | nvarchar (MAX) | Notes for specimen part |
| _TransactionID | int | Unique ID for the table Transaction (= foreign key and part of primary key) |
| _Transaction | nvarchar (200) | Title of first transaction |
| On_loan | int | If part is on loan according to first transaction |
| _CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) |
| _IdentificationUnitID | int | Refers to the ID of IdentficationUnit (= foreign key and part of primary key) |
| _IdentificationSequence | smallint | The sequence of the identifications. The last identification (having the highest sequence) is regarded as valid |
| _SpecimenPartID | int | Unique ID of the part of the collection specimen (= part of primary key). |
| _CoordinatesAverageLatitudeCache | real | Average Latitude of WGS84 Coordinates |
| _CoordinatesAverageLongitudeCache | real | Average Longitude of WGS84 Coordinates |
| _CoordinatesLocationNotes | nvarchar (255) | Notes for WGS84 Coordinates |
| _GeographicRegionPropertyURI | varchar (255) | URI of Geographic Region |
| _LithostratigraphyPropertyURI | varchar (255) | URI for Lithostratigraphy |
| _ChronostratigraphyPropertyURI | varchar (255) | URI for Chronostratigraphy |
| _NamedAverageLatitudeCache | real | Average Latitude for Named Area |
| _NamedAverageLongitudeCache | real | Average Longitude for Named Area |
| _LithostratigraphyPropertyHierarchyCache | nvarchar (255) | Hierarchy for Lithostratigraphy |
| _ChronostratigraphyPropertyHierarchyCache | nvarchar (255) | Hierarchy for Chronostratigraphy |
| _AverageAltitudeCache | real | Average Altitude |
Depending on:
- Analysis
- CollectionAgent
- CollectionEvent
- CollectionEventLocalisation
- CollectionEventProperty
- CollectionSpecimen
- CollectionSpecimenID_UserAvailable
- CollectionSpecimenPart
- CollectionSpecimenTransaction
- Identification
- IdentificationUnit
- IdentificationUnitAnalysis
- Transaction
Function LocalisationSystem_List
| Parameter | DataType | Description |
|---|---|---|
| @LanguageCode | char (2) | - |
| Column | DataType | Description |
|---|---|---|
| LocalisationSystemID | int | Unique ID for the localisation system (= Primary key) |
| LocalisationSystemParentID | int | LocalisationSystemID of the superior LocalisationSystem |
| LocalisationSystemName | nvarchar (50) | Name of the system used for the determination of the place of the collection, e. g. Gauss-Krüger, MTB, GIS |
| MeasurementUnit | nvarchar (100) | - |
| DefaultAccuracyOfLocalisation | nvarchar (100) | The default for the accuracy of values which can be reached with this method |
| DiversityModule | nvarchar (50) | - |
| ParsingMethod | nvarchar (500) | - |
| LocalisationSystemType | nvarchar (500) | - |
| Description | nvarchar (255) | Description of the localisation method |
| Abbreviation | nvarchar (50) | - |
| DescriptionLocation1 | nvarchar (255) | Description of the attribute Location1 in the table CollectionGeography as displayed in the user interface |
| AbbreviationLocation1 | nvarchar (50) | - |
| DescriptionLocation2 | nvarchar (255) | Description of the attribute Location2 in the table CollectionGeography as displayed in the user interface |
| AbbreviationLocation2 | nvarchar (50) | - |
Function ManagerCollectionList
Returns a table that lists all the collections a Manager has access to, including the child collections.
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (255) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (50) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (4000) | A short description of the collection |
| Location | nvarchar (1000) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | varchar (255) | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
Depending on:
- Collection
- CollectionChildNodes
- CollectionManager
Function MethodChildNodes
All child nodes of a given method related via the MethodParentID
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | MethodID for which the depending methods should be returned |
| Column | DataType | Description |
|---|---|---|
| MethodID | int | ID of the Method (Primary key) |
| MethodParentID | int | MethodID of the parent Method, if it belongs to a certain type documented in this table |
| DisplayText | nvarchar (50) | Name of the Method as e.g. shown in user interface |
| Description | nvarchar (500) | Description of the Method |
| ForCollectionEvent | bit | If a method may be used during a collection event |
| Notes | nvarchar (1000) | Notes on this method |
| MethodURI | varchar (255) | URI referring to an external documentation of the Method |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
| RowGUID | uniqueidentifier | - |
Depending on:
- Method
- MethodChildNodes
Function MethodHierarchy
The hierarchy of a given method
| Parameter | DataType | Description |
|---|---|---|
| @MethodID | int | ID of the Method (Primary key) |
| Column | DataType | Description |
|---|---|---|
| MethodID | int | ID of the Method (Primary key) |
| MethodParentID | int | MethodID of the parent Method, if it belongs to a certain type documented in this table |
| DisplayText | nvarchar (50) | Name of the Method as e.g. shown in user interface |
| Description | nvarchar (500) | Description of the Method |
| Notes | nvarchar (1000) | Notes on this method |
| MethodURI | varchar (255) | URI referring to an external documentation of the Method |
| ForCollectionEvent | bit | If a method may be used during a collection event |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
Depending on:
- Method
- MethodChildNodes
Function MethodHierarchyAll
All methods including a column displaying the hierarchy of the method
| Column | DataType | Description |
|---|---|---|
| MethodID | int | ID of the Method (Primary key) |
| MethodParentID | int | MethodID of the parent Method, if it belongs to a certain type documented in this table |
| DisplayText | nvarchar (255) | Name of the Method as e.g. shown in user interface |
| Description | nvarchar (MAX) | Description of the Method |
| Notes | nvarchar (MAX) | Notes on this method |
| MethodURI | varchar (255) | URI referring to an external documentation of the Method |
| ForCollectionEvent | bit | If a method may be used during a collection event |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
| HierarchyDisplayText | nvarchar (900) | Hierarchy of the method separated by |
Depending on:
- Method
Function NameList
| Parameter | DataType | Description |
|---|---|---|
| @NameID | int | - |
| @NameStart | nvarchar (50) | - |
| Column | DataType | Description |
|---|---|---|
| NameID | int | - |
| TaxonName | nvarchar (255) | - |
Function NameListMyxomycetes
| Parameter | DataType | Description |
|---|---|---|
| @NameID | int | - |
| @NameStart | nvarchar (50) | - |
| Column | DataType | Description |
|---|---|---|
| NameID | int | - |
| TaxonName | nvarchar (200) | - |
Function NameListPlants
| Parameter | DataType | Description |
|---|---|---|
| @NameID | int | - |
| @NameStart | nvarchar (50) | - |
| Column | DataType | Description |
|---|---|---|
| NameID | int | - |
| TaxonName | nvarchar (200) | - |
Function NextFreeAccNr
returns next free accession number similar to given parameter. Assumes that accession numbers have a pattern like M-0023423 or HAL 25345 or GLM3453 with a leading string and a numeric end
DataType: nvarchar (50)
| Parameter | DataType | Description |
|---|---|---|
| @AccessionNumber | nvarchar (50) | Accession number of the specimen within the collection, e.g. “M-29834752” |
Depending on:
- CollectionSpecimen
Function NextFreeAccNumber
The next free accession number starting like parameter 1, optional inclusion of specimen (parameter 2) and parts (parameter 3)
DataType: nvarchar (50)
| Parameter | DataType | Description |
|---|---|---|
| @AccessionNumber | nvarchar (50) | Accession number of the specimen within the collection, e.g. “M-29834752” |
| @IncludeSpecimen | bit | If specimen should be included in search |
| @IncludePart | bit | If parts should be included in search |
Depending on:
- CollectionSpecimen
- CollectionSpecimenPart
Function PrivacyConsentInfo
Provides a link to common information about the DiversityWorkbench
DataType: varchar (900)
Function ProcessingChildNodes
Returns a result set that lists all the processings within a hierarchy starting at the topmost processing related to the given item
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | ID of the parent processing |
| Column | DataType | Description |
|---|---|---|
| ProcessingID | int | ID of the processing (primary key) |
| ProcessingParentID | int | The ID of the superior type of the processing |
| DisplayText | nvarchar (50) | The display text of the processing as shown e.g. in a user interface |
| Description | nvarchar (500) | Description of the processing |
| Notes | nvarchar (1000) | Notes on the processing |
| ProcessingURI | varchar (255) | A URI for a processing as defined in an external data source |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
| RowGUID | uniqueidentifier | - |
Depending on:
- Processing
- ProcessingChildNodes
Function ProcessingHierarchy
Returns a table that lists all the analysis items related to the given processing.
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | ID of the parent processing |
| Column | DataType | Description |
|---|---|---|
| ProcessingID | int | ID of the processing (primary key) |
| ProcessingParentID | int | The ID of the superior type of the processing |
| DisplayText | nvarchar (50) | The display text of the processing as shown e.g. in a user interface |
| Description | nvarchar (500) | Description of the processing |
| Notes | nvarchar (1000) | Notes on the processing |
| ProcessingURI | varchar (255) | A URI for a processing as defined in an external data source |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
| RowGUID | uniqueidentifier | - |
Depending on:
- Processing
- ProcessingChildNodes
Function ProcessingHierarchyAll
Returns a table that lists all the Processings including their hierarchy
| Column | DataType | Description |
|---|---|---|
| ProcessingID | int | ID of the processing (primary key) |
| ProcessingParentID | int | The ID of the superior type of the processing |
| DisplayText | nvarchar (50) | The display text of the processing as shown e.g. in a user interface |
| Description | nvarchar (MAX) | Description of the processing |
| Notes | nvarchar (MAX) | Notes on the processing |
| ProcessingURI | varchar (255) | A URI for a processing as defined in an external data source |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
| HierarchyDisplayText | varchar (900) | Hierarchy of the processings as DisplayText separated by |
Depending on:
- Processing
Function ProcessingListForPart
Returns a table that lists all the processing items related to the given part.Tthe list depends upon the processing types available for a material category and the projects available for a processing
| Parameter | DataType | Description |
|---|---|---|
| @CollectionSpecimenID | int | Refers to the ID of CollectionSpecimen (= foreign key and part of primary key) |
| @SpecimenPartID | int | Unique ID of the part of the collection specimen (= part of primary key). |
| Column | DataType | Description |
|---|---|---|
| ProcessingID | int | ID of the processing (primary key) |
| ProcessingParentID | int | The ID of the superior type of the processing |
| DisplayText | nvarchar (50) | The display text of the processing as shown e.g. in a user interface |
| Description | nvarchar (MAX) | Description of the processing |
| Notes | nvarchar (MAX) | Notes on the storage of the sample |
| ProcessingURI | varchar (255) | A URI for a processing as defined in an external data source |
| DisplayTextHierarchy | nvarchar (255) | Hierarchy of the processings as DisplayText separated by |
Depending on:
- CollectionProject
- CollectionSpecimen
- CollectionSpecimenPart
- Processing
- ProcessingChildNodes
- ProcessingHierarchy
- ProcessingMaterialCategory
- ProjectProcessing
Function ProcessingProjectList
Returns a table that lists all the Processing items related to the given project.
| Parameter | DataType | Description |
|---|---|---|
| @ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
| Column | DataType | Description |
|---|---|---|
| ProcessingID | int | ID of the processing (primary key) |
| ProcessingParentID | int | The ID of the superior type of the processing |
| DisplayText | nvarchar (50) | The display text of the processing as shown e.g. in a user interface |
| Description | nvarchar (500) | Description of the processing |
| Notes | nvarchar (1000) | Notes on the processing |
| ProcessingURI | varchar (255) | A URI for a processing as defined in an external data source |
| OnlyHierarchy | bit | If the entry is only used for the hierarchical arrangement of the entries |
| RowGUID | uniqueidentifier | - |
Depending on:
- Processing
- ProcessingChildNodes
- ProjectProcessing
Function ProjectDataLastChanges
retrieval of the last update in data of a project
DataType: datetime
| Parameter | DataType | Description |
|---|---|---|
| @ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
Depending on:
- CollectionEvent
- CollectionProject
- CollectionProject_log
- CollectionSpecimen
Function RequesterCollectionList
Returns a table that lists all the collections a requester has access to, including the child collections if allowed
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (50) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (50) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (500) | A short description of the collection |
| Location | nvarchar (1000) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | varchar (255) | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
Depending on:
- Collection
- CollectionHierarchy
- CollectionRequester
Function StableIdentifier
Returns a stable identfier for a dataset. Relies on an entry in ProjectProxy
DataType: varchar (500)
| Parameter | DataType | Description |
|---|---|---|
| @ProjectID | int | ID of the project to which the specimen belongs (Projects are defined in DiversityProjects) |
| @CollectionSpecimenID | int | CollectionSpecimenID that should be included in the StableIdentifier |
| @IdentificationUnitID | int | IdentificationUnitID that should be included in the StableIdentifier |
| @SpecimenPartID | int | SpecimenPartID that should be included in the StableIdentifier |
Depending on:
- ProjectProxy
Function TaskChildNodes
All child nodes of a given Task related via the TaskParentID
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | TaskParentID for which all child nodes of a given Task should be returned |
| Column | DataType | Description |
|---|---|---|
| TaskID | int | ID of the Task (primary key) |
| TaskParentID | int | The ID of the superior type of the Task |
| DisplayText | nvarchar (50) | The display text of the Task as shown e.g. in a user interface |
| Description | nvarchar (500) | Description of the Task |
| Notes | nvarchar (1000) | Notes on the Task |
| TaskURI | varchar (255) | A URI for a Task as defined in an external data source |
| Type | varchar (50) | The type of the task as defined in table TaskType_Enum |
| ModuleTitle | varchar (50) | The title for module related data for collection tasks. Not available in depending collection task if empty |
| ModuleType | varchar (50) | The DiversityWorkbench module to which a task is related. Not available in depending collection task if empty |
| SpecimenPartType | nvarchar (50) | The description of the collection specimen part to which a task is related. Not available in depending collection task if empty |
| TransactionType | nvarchar (50) | The description of the transaction to which a task is related. Not available in depending collection task if empty |
| ResultType | varchar (50) | The display text for the results as shown in a user interface. Not available in depending collection task if empty |
| DateType | varchar (50) | The date and time details defined for a task. Not available in depending collection task if empty |
| DateBeginType | nvarchar (50) | The definition of the begin for date and time details defined for a task. Not available in depending collection task if empty |
| DateEndType | nvarchar (50) | The definition of the end for date and time details defined for a task. Not available in depending collection task if empty |
| NumberType | varchar (50) | The definition for the numeric value as shown in a user interface. Not available in depending collection task if empty |
| BoolType | varchar (50) | The definition for the boolean value as shown in a user interface. Not available in depending collection task if empty |
| MetricType | nvarchar (50) | The definition for the metric as shown in a user interface. Not available in depending collection task if empty |
| MetricUnit | nvarchar (50) | The unit of the metric, e.g. °C for temperature |
| ResponsibleType | nvarchar (50) | The definition for the responsible agent as shown in a user interface. Not available in depending collection task if empty |
| DescriptionType | varchar (50) | The definition for the description as shown in a user interface. Not available in depending collection task if empty |
| NotesType | varchar (50) | The definition for the notes as shown in a user interface. Not available in depending collection task if empty |
| UriType | varchar (50) | The definition for the URI as shown in a user interface. Not available in depending collection task if empty |
| RowGUID | uniqueidentifier | - |
Depending on:
- Task
Function TaskCollectionHierarchySeparator
Returns a separator used between collection and task hierarchy
DataType: nchar (3)
Function TaskHierarchy
The hierarchy of a given Task
| Parameter | DataType | Description |
|---|---|---|
| @TaskID | int | ID of the Task (primary key) |
| Column | DataType | Description |
|---|---|---|
| TaskID | int | ID of the Task (primary key) |
| TaskParentID | int | The ID of the superior type of the Task |
| DisplayText | nvarchar (50) | The display text of the Task as shown e.g. in a user interface |
| Description | nvarchar (500) | Description of the Task |
| Notes | nvarchar (1000) | Notes on the Task |
| TaskURI | varchar (255) | A URI for a Task as defined in an external data source |
| Type | varchar (50) | The type of the task as defined in table TaskType_Enum |
| ModuleTitle | varchar (50) | The title for module related data for collection tasks. Not available in depending collection task if empty |
| ModuleType | varchar (50) | The DiversityWorkbench module to which a task is related. Not available in depending collection task if empty |
| SpecimenPartType | nvarchar (50) | The description of the collection specimen part to which a task is related. Not available in depending collection task if empty |
| TransactionType | nvarchar (50) | The description of the transaction to which a task is related. Not available in depending collection task if empty |
| ResultType | varchar (50) | The display text for the results as shown in a user interface. Not available in depending collection task if empty |
| DateType | varchar (50) | The date and time details defined for a task. Not available in depending collection task if empty |
| DateBeginType | nvarchar (50) | The definition of the begin for date and time details defined for a task. Not available in depending collection task if empty |
| DateEndType | nvarchar (50) | The definition of the end for date and time details defined for a task. Not available in depending collection task if empty |
| NumberType | varchar (50) | The definition for the numeric value as shown in a user interface. Not available in depending collection task if empty |
| BoolType | varchar (50) | The definition for the boolean value as shown in a user interface. Not available in depending collection task if empty |
| MetricType | nvarchar (50) | The definition for the metric as shown in a user interface. Not available in depending collection task if empty |
| MetricUnit | nvarchar (50) | The unit of the metric, e.g. °C for temperature |
| ResponsibleType | nvarchar (50) | The definition for the responsible agent as shown in a user interface. Not available in depending collection task if empty |
| DescriptionType | varchar (50) | The definition for the description as shown in a user interface. Not available in depending collection task if empty |
| NotesType | varchar (50) | The definition for the notes as shown in a user interface. Not available in depending collection task if empty |
| UriType | varchar (50) | The definition for the URI as shown in a user interface. Not available in depending collection task if empty |
Depending on:
- Task
- TaskChildNodes
Function TaskHierarchyAll
All Tasks including a column displaying the hierarchy of the Task
| Column | DataType | Description |
|---|---|---|
| TaskID | int | ID of the Task (primary key) |
| TaskParentID | int | The ID of the superior type of the Task |
| DisplayText | nvarchar (50) | The display text of the Task as shown e.g. in a user interface |
| Description | nvarchar (MAX) | Description of the Task |
| Notes | nvarchar (MAX) | Notes on the Task |
| TaskURI | varchar (255) | A URI for a Task as defined in an external data source |
| Type | varchar (50) | The type of the task as defined in table TaskType_Enum |
| ModuleTitle | varchar (50) | The title for module related data for collection tasks. Not available in depending collection task if empty |
| ModuleType | varchar (50) | The DiversityWorkbench module to which a task is related. Not available in depending collection task if empty |
| SpecimenPartType | nvarchar (50) | The description of the collection specimen part to which a task is related. Not available in depending collection task if empty |
| TransactionType | nvarchar (50) | The description of the transaction to which a task is related. Not available in depending collection task if empty |
| ResultType | varchar (50) | The display text for the results as shown in a user interface. Not available in depending collection task if empty |
| DateType | varchar (50) | The date and time details defined for a task. Not available in depending collection task if empty |
| DateBeginType | nvarchar (50) | The definition of the begin for date and time details defined for a task. Not available in depending collection task if empty |
| DateEndType | nvarchar (50) | The definition of the end for date and time details defined for a task. Not available in depending collection task if empty |
| NumberType | varchar (50) | The definition for the numeric value as shown in a user interface. Not available in depending collection task if empty |
| BoolType | varchar (50) | The definition for the boolean value as shown in a user interface. Not available in depending collection task if empty |
| MetricType | nvarchar (50) | The definition for the metric as shown in a user interface. Not available in depending collection task if empty |
| MetricUnit | nvarchar (50) | The unit of the metric, e.g. °C for temperature |
| ResponsibleType | nvarchar (50) | The definition for the responsible agent as shown in a user interface. Not available in depending collection task if empty |
| DescriptionType | varchar (50) | The definition for the description as shown in a user interface. Not available in depending collection task if empty |
| NotesType | varchar (50) | The definition for the notes as shown in a user interface. Not available in depending collection task if empty |
| UriType | varchar (50) | The definition for the URI as shown in a user interface. Not available in depending collection task if empty |
| HierarchyDisplayText | nvarchar (900) | Hierarchy of the task starting at top Task separated by string defined in dbo.TaskHierarchySeparator() |
| HierarchyDisplayTextInvers | nvarchar (900) | Hierarchy of the task ending at top Task separated by string defined in dbo.TaskHierarchySeparator() |
Depending on:
- Task
- TaskHierarchySeparator
Function TaskHierarchySeparator
Returns a separator used within a task hierarchy
DataType: nchar (3)
Function TaxonWithQualifier
Generates a valid name for a taxon using the taxonomic name and the identification qualifier
DataType: nvarchar (500)
| Parameter | DataType | Description |
|---|---|---|
| @Taxon | nvarchar (500) | Taxonomic name used as base for transformation |
| @Qualifier | nvarchar (50) | Qualifier used as base for transformation |
Function TransactionChildNodes
Returns a result set that lists all the transactions within a hierarchy starting at the topmost transaction related to the given transaction.
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | TransactionID of the topmost transaction within the hierarchy that should be returned |
| Column | DataType | Description |
|---|---|---|
| TransactionID | int | Unique ID for the transaction (= primary key) |
| ParentTransactionID | int | The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing |
| AdministratingCollectionID | int | ID of the collection which is responsible for the administration of the transaction. |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction |
| MaterialSource | nvarchar (500) | The source of the material within a transaction, e.g. a excavation |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift |
| FromTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift |
| ToCollectionID | int | The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift |
| ToTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift |
| ToRecipient | nvarchar (255) | The recipient receiving the transaction e.g. if not derived from the link to DiversityAgents |
| NumberOfUnits | int | The number of units which were (initially) included in the transaction |
| Investigator | nvarchar (200) | The investigator for whose study a transacted material was sent |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner |
| BeginDate | datetime | Date when the transaction started |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted |
| ActualEndDate | datetime | Actual end of the transaction after a prolonation when e.g. the date of return for a loan was prolonged by the owner |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) |
| DateSupplement | nvarchar (100) | Verbal or additional date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. |
Depending on:
- Transaction
Function TransactionChildNodesAccess
Returns a result set that lists all the items within a hierarchy starting at the topmost item related to the given item where the current user has no access according to the restriction in TransactionList.
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | TransactionID of the topmost transaction within the hierarchy that should be returned |
| Column | DataType | Description |
|---|---|---|
| TransactionID | int | Unique ID for the transaction (= primary key) |
| ParentTransactionID | int | The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing |
| AdministratingCollectionID | int | ID of the collection which is responsible for the administration of the transaction. |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction |
| MaterialSource | nvarchar (500) | The source of the material within a transaction, e.g. a excavation |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift |
| FromTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift |
| ToCollectionID | int | The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift |
| ToTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift |
| ToRecipient | nvarchar (255) | The recipient receiving the transaction e.g. if not derived from the link to DiversityAgents |
| NumberOfUnits | int | The number of units which were (initially) included in the transaction |
| Investigator | nvarchar (200) | The investigator for whose study a transacted material was sent |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner |
| BeginDate | datetime | Date when the transaction started |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted |
| ActualEndDate | datetime | Actual end of the transaction after a prolonation when e.g. the date of return for a loan was prolonged by the owner |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) |
| Accessible | tinyint | If a user has access to this dataset |
Depending on:
- Transaction
- TransactionChildNodesAccess
Function TransactionCurrency
The default curreny for payments
DataType: nvarchar (50)
Function TransactionHierarchy
Returns a table that lists all the transactions related to the given transaction.
| Parameter | DataType | Description |
|---|---|---|
| @TransactionID | int | Unique ID for the transaction (= primary key) |
| Column | DataType | Description |
|---|---|---|
| TransactionID | int | Unique ID for the transaction (= primary key) |
| ParentTransactionID | int | The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing |
| AdministratingCollectionID | int | ID of the collection which is responsible for the administration of the transaction. |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction |
| MaterialSource | nvarchar (500) | The source of the material within a transaction, e.g. a excavation |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift |
| FromTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift |
| ToCollectionID | int | The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift |
| ToTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift |
| ToRecipient | nvarchar (255) | The recipient receiving the transaction e.g. if not derived from the link to DiversityAgents |
| NumberOfUnits | int | The number of units which were (initially) included in the transaction |
| Investigator | nvarchar (200) | The investigator for whose study a transacted material was sent |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner |
| BeginDate | datetime | Date when the transaction started |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted |
| ActualEndDate | datetime | Actual end of the transaction after a prolonation when e.g. the date of return for a loan was prolonged by the owner |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) |
| DateSupplement | nvarchar (100) | Verbal or additional date information, e.g. ’end of summer 1985’, ‘first quarter’, ‘1888-1892’. |
Depending on:
- TransactionChildNodes
- TransactionList
Function TransactionHierarchyAccess
Returns a table that lists all the transactions related to the given transaction including the accessiblity.
| Parameter | DataType | Description |
|---|---|---|
| @TransactionID | int | Unique ID for the transaction (= primary key) |
| Column | DataType | Description |
|---|---|---|
| TransactionID | int | Unique ID for the transaction (= primary key) |
| ParentTransactionID | int | The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing |
| AdministratingCollectionID | int | ID of the collection which is responsible for the administration of the transaction. |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction |
| MaterialSource | nvarchar (500) | The source of the material within a transaction, e.g. a excavation |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift |
| FromTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift |
| ToCollectionID | int | The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift |
| ToTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift |
| ToRecipient | nvarchar (255) | The recipient receiving the transaction e.g. if not derived from the link to DiversityAgents |
| NumberOfUnits | int | The number of units which were (initially) included in the transaction |
| Investigator | nvarchar (200) | The investigator for whose study a transacted material was sent |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner |
| BeginDate | datetime | Date when the transaction started |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted |
| ActualEndDate | datetime | Actual end of the transaction after a prolonation when e.g. the date of return for a loan was prolonged by the owner |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) |
| Accessible | tinyint | If a user has access to this dataset |
Depending on:
- Transaction
- TransactionChildNodesAccess
- TransactionList
Function TransactionHierarchyAll
Returns a table that lists all the transactions including their hierarchy
| Column | DataType | Description |
|---|---|---|
| TransactionID | int | Unique ID for the transaction (= primary key) |
| ParentTransactionID | int | The ID of a preceeding transaction of a superior transaction, if transactions are organized in a hierarchy |
| TransactionType | nvarchar (50) | Type of the transaction, e.g. gift in or out, exchange in or out, purchase in or out |
| TransactionTitle | nvarchar (200) | The title of the transaction as e.g. shown in an user interface |
| ReportingCategory | nvarchar (50) | A group defined for the transaction, e.g. a taxonomic group as used for exchange balancing |
| AdministratingCollectionID | int | ID of the collection which is responsible for the administration of the transaction. |
| MaterialDescription | nvarchar (MAX) | Description of the material of this transaction |
| MaterialSource | nvarchar (500) | The source of the material within a transaction, e.g. a excavation |
| MaterialCategory | nvarchar (50) | Material category of specimen. Examples: ‘herbarium sheets’, ‘drawings’, ‘microscopic slides’ etc. |
| MaterialCollectors | nvarchar (MAX) | The collectors of the material |
| FromCollectionID | int | The ID of the collection from which the specimen were transfered, e.g. the donating collection of a gift |
| FromTransactionPartnerName | nvarchar (255) | Name of the person or institution from which the specimen were transfered, e.g. the donator of a gift |
| FromTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) |
| FromTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the source of the specimen, e.g. the donating collection of a gift |
| ToCollectionID | int | The ID of the collection to which the specimen were transfered, e.g. the receiver of a gift |
| ToTransactionPartnerName | nvarchar (255) | Name of the person or institution to which the specimen were transfered, e.g. the receiver of a gift |
| ToTransactionPartnerAgentURI | varchar (255) | The URI of the transaction partner (see e.g. module DiversityAgents) |
| ToTransactionNumber | nvarchar (50) | Number or code by which a transaction may be recorded by the administration of the destination of the specimen, e.g. the receiving collection of a gift |
| ToRecipient | nvarchar (255) | The recipient receiving the transaction e.g. if not derived from the link to DiversityAgents |
| NumberOfUnits | smallint | The number of units which were (initially) included in the transaction |
| Investigator | nvarchar (200) | The investigator for whose study a transacted material was sent |
| TransactionComment | nvarchar (MAX) | Comments on the exchanged material addressed to the transaction partner |
| BeginDate | datetime | Date when the transaction started |
| AgreedEndDate | datetime | End of the transaction period, e.g. if the time for borrowing the specimen is restricted |
| ActualEndDate | datetime | Actual end of the transaction after a prolonation when e.g. the date of return for a loan was prolonged by the owner |
| InternalNotes | nvarchar (MAX) | Internal notes on this transaction not to be published e.g. on a web page |
| ResponsibleName | nvarchar (255) | The person responsible for this transaction |
| ResponsibleAgentURI | varchar (255) | The URI of the person, team or organisation responsible for the data (see e.g. module DiversityAgents) |
| DisplayText | nvarchar (500) | Hierarchy of the transaction starting at top transaction separated by |
| HierarchyDisplayText | nvarchar (500) | Hierarchy of the transaction starting at top transaction separated by |
Depending on:
- Transaction
Function UserCollectionList
Returns a table that lists all the collections a User has access to, including the child collections.
| Column | DataType | Description |
|---|---|---|
| CollectionID | int | Unique reference ID for the collection (= primary key) |
| CollectionParentID | int | For a subcollection within another collection: CollectionID of the collection to which the subcollection belongs. Empty for an independent collection |
| CollectionName | nvarchar (255) | Name of the collection (e.g. ‘Herbarium Kew’) or subcollection (e.g. ‘cone collection’, ‘alcohol preservations’). This text should be kept relatively short. You may use Description for additional information |
| CollectionAcronym | nvarchar (50) | A unique code for the collection, e.g. the herbarium code from Index Herbariorum |
| AdministrativeContactName | nvarchar (500) | The name of the person or organisation responsible for this collection |
| AdministrativeContactAgentURI | nvarchar (255) | The URI of the person or organisation responsible for the collection e.g. as provided by the module DiversityAgents |
| Description | nvarchar (MAX) | A short description of the collection |
| Location | nvarchar (1000) | Optional location of the collection, e.g. the number within a file system or a description of the room(s) housing the (sub)collection |
| CollectionOwner | nvarchar (255) | The owner of the collection as e.g. printed on a label. Should be given if CollectionParentID is null |
| DisplayOrder | smallint | The order in which the entries are displayed. The order may be changed at any time, but all values must be unique. |
Depending on:
- Collection
- CollectionChildNodes
- CollectionManager
- CollectionUser
- ManagerCollectionList
Function UserID
ID of the User as stored in table UserProxy
DataType: int
Depending on:
- UserProxy
Function Version
Version of the database
DataType: nvarchar (8)
Function VersionClient
Minimal version of the client compatible with the database
DataType: nvarchar (11)
PROCEDURES
Procedure DeleteXmlAttribute
Deleting an attribute of an XML node in a column of datatype XML
| Parameter | DataType | Description |
|---|---|---|
| @Table | nvarchar (128) | Name of the table containing the XML column |
| @Column | nvarchar (128) | Name of the XML column |
| @Path | nvarchar (4000) | Path of the XML node |
| @Attribute | nvarchar (128) | Attribute that should be removed |
| @WhereClause | nvarchar (4000) | Where clause to select the data within the table |
Procedure DeleteXmlNode
Deleting a XML node in a column of datatype XML
| Parameter | DataType | Description |
|---|---|---|
| @Table | nvarchar (128) | Name of the table containing the XML column |
| @Column | nvarchar (128) | Name of the XML column |
| @Path | nvarchar (4000) | Path of the XML node |
| @WhereClause | nvarchar (4000) | Where clause to select the data within the table |
Procedure procCopyCollectionSpecimen2
Copy a collection specimen
| Parameter | DataType | Description |
|---|---|---|
| @CollectionSpecimenID | int | Refers to ID of CollectionEvent (= foreign key and part of primary key) |
| @OriginalCollectionSpecimenID | int | The CollectionSpecimenID of the CollectionSpecimen that should be copied |
| @AccessionNumber | nvarchar (50) | Accession number of the specimen within the collection, e.g. “M-29834752” |
| @EventCopyMode | int | 3 Options: -1 = dont copy the event, leave the entry in table CollectionSpecimen empty; 0 = take same event as original specimen; 1 = create new event with the same data as the old specimen |
| @IncludedTables | nvarchar (4000) | Contains list of tables that are copied according to the users choice |
Depending on:
- Annotation
- CollectionAgent
- CollectionEvent
- CollectionEventImage
- CollectionEventLocalisation
- CollectionEventMethod
- CollectionEventParameterValue
- CollectionEventProperty
- CollectionProject
- CollectionSpecimen
- CollectionSpecimenImage
- CollectionSpecimenImageProperty
- CollectionSpecimenPart
- CollectionSpecimenPartDescription
- CollectionSpecimenProcessing
- CollectionSpecimenProcessingMethod
- CollectionSpecimenProcessingMethodParameter
- CollectionSpecimenReference
- CollectionSpecimenRelation
- CollectionSpecimenTransaction
- ExternalIdentifier
- Identification
- IdentificationUnit
- IdentificationUnitAnalysis
- IdentificationUnitAnalysisMethod
- IdentificationUnitAnalysisMethodParameter
- IdentificationUnitGeoAnalysis
- IdentificationUnitInPart
Procedure procCopyCollectionSpecimenPart
Copy a collection specimen part
| Parameter | DataType | Description |
|---|---|---|
| @SpecimenPartID | int | Unique ID of the part of the collection specimen (= part of primary key). |
| @OriginalSpecimenPartID | int | The SpecimenPartID of the CollectionSpecimenPart that should be copied |
| @AccessionNumber | nvarchar (50) | Accession number of the part of the specimen within the collection, if it is different from the accession number of the specimen as stored in the table CollectionSpecimen, e.g. “M-29834752” |
| @PartSublabel | nvarchar (50) | The label for a part of a specimen, e.g. “cone”, or a number attached to a duplicate of a specimen |
| @StorageLocation | nvarchar (255) | A code identifying the place where the specimen is stored within the collection. Frequently the accepted scientific name is used as storage location code. |
| @IncludedTables | nvarchar (4000) | Contains list of tables that are copied according to the users choice |
Depending on:
- Annotation
- CollectionSpecimenPart
- CollectionSpecimenPartDescription
- CollectionSpecimenPartRegulation
- CollectionSpecimenProcessing
- CollectionSpecimenProcessingMethod
- CollectionSpecimenProcessingMethodParameter
- CollectionSpecimenReference
- CollectionSpecimenRelation
- CollectionSpecimenTransaction
- ExternalIdentifier
- IdentificationUnitInPart
Procedure procFillCacheDescription
Filling table CacheDescription
Depending on:
- CacheDescription
- EntityRepresentation
Procedure procInsertCollectionEventCopy
Copy a collection event
| Parameter | DataType | Description |
|---|---|---|
| @CollectionEventID | int | Unique ID for the table CollectionEvent (= primary key) |
| @OriginalCollectionEventID | int | The CollectionEventID of the CollectionEvent that should be copied |
Depending on:
- CollectionEvent
- CollectionEventImage
- CollectionEventLocalisation
- CollectionEventProperty
Procedure procSetVersionCollectionEvent
Setting the version of a dataset
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | CollectionEventID of the dataset |
Depending on:
- CollectionEvent
- CollectionProject
- CollectionSpecimen
- ProjectProxy
Procedure procSetVersionCollectionSpecimen
Setting the version of a dataset
| Parameter | DataType | Description |
|---|---|---|
| @ID | int | CollectionSpecimenID of the dataset |
Depending on:
- CollectionProject
- CollectionSpecimen
- ProjectProxy
Procedure SetXmlAttribute
Setting a value of an XML node
| Parameter | DataType | Description |
|---|---|---|
| @Table | nvarchar (128) | Name of the table containing the XML column |
| @Column | nvarchar (128) | Name of the XML column |
| @Path | nvarchar (4000) | Path of the XML node |
| @Attribute | nvarchar (128) | Attribute that should be set |
| @Value | nvarchar (4000) | The value for the attribute that should be set |
| @WhereClause | nvarchar (4000) | Where clause to select the data within the table |
Procedure SetXmlValue
Setting a value of an XML node
| Parameter | DataType | Description |
|---|---|---|
| @Table | nvarchar (128) | Name of the table containing the XML column |
| @Column | nvarchar (128) | Name of the XML column |
| @Path | nvarchar (4000) | Path of the XML node |
| @Value | nvarchar (4000) | The value for the node that should be set |
| @WhereClause | nvarchar (4000) | Where clause to select the data within the table |