Panel Setup: Editing

Use the Editing page of the Property Editor: Depth Adjustment Panel (Bar Type) dialog to configure the SQL scripts that will be executed in response to various events triggered by the depth adjustment process. The events that need to be catered for are:

Updates

An existing interval is updated.

When interval data for the Panels is loaded into the Depth Adjust Tool and the Code for one of the intervals is NULL, Geobank will interpret and display the NULL values as an empty string. As a consequence, when depths are adjusted, Geobank may save back an empty string instead of a NULL to the database.

To avoid this, if you have a code field which allows nulls, always use the syntax: <Code_Field> = NULLIF('&ParamNewCode','') in your SQL Update or Insert statements, instead of just <Code_Field> = '&ParamNewCode'

Inserts

New intervals are inserted.

Deletes

An existing interval is deleted.

Other options

A number of options, related to the way in which events are handled, can be selected:

Updates involve a unique constraint based on depth range

To avoid table constraint violations, clear this check box.

As a rule, geological databases are not highly normalised and therefore the depth range is often used as the unique part of the primary key in interval data tables. When used on such tables, the depth adjustment process could potentially result in primary key violations.

Consider the following example, where the adjustments would result in a key violation if they were applied in depth order:

Before	After
51.5 to 52.0 m	51.5 to 52.2 m
52.0 to 52.2 m	52.2 to 52.4 m
52.2 to 52.4 m	52.4 to 52.6 m
52.4 to 52.8 m	52.6 to 52.8 m

With this possibility in mind, the depth adjustment process will sometimes defer an adjustment if there is a possibility that it could result in a duplicate depth range. In the above example, the third adjustment would be performed before the second one. It is therefore important to note that the order in which adjustments are executed cannot be guaranteed.

Despite these precautions, it is impossible to guarantee that no constraint violations will take place during the course of the adjustment process. Therefore, the best way to avoid this type of problem is to ensure that the depth columns are not being used as the basis for a unique database constraint.

The adjustment process will follow this logic irrespective of whether the depths form part of the primary key or not. This is due to the fact that Geobank is currently unable to retrieve constraint metadata from SQL query result sets.

Use a list of values for the annotation code

If you want to use an annotation column for this panel, define a lookup dataset for the annotation code. Click the Build button to load or create a customised SQL query that returns the list of valid codes for the annotation column. For example:

SELECT

CODE,

DESCR,

PATTERN_NO

FROM

GB_LKP_CODE

WHERE

CATEGORY = 'LITHOLOGY'

ORDER BY

CODE

The optional PATTERN_NO column is used to associate a hatch pattern with the code returned in the CODE column. This pattern number must be compatible with the nominated symbology set and legend.

By default this panel is affected by new tie lines

By default, new tie lines affect all adjustable panels. In other words, when a new tie line is added, the panels in the Affected Panels list are all checked. You can now override this default behaviour by leaving the check box unchecked.

Allow user to change this setting

If this option is selected, the user can also override the default settings by selecting a tie line in the Tie Lines list, then selecting or deselecting the panels in the Affected Panels list.

Refer to the Understanding Depth Events topic for more information.

EXAMPLES

The use of master-detail syntax in queries used to run the depth adjustment process (and elsewhere) is deprecated in Geobank 2018. System Column &Parameter syntax must be used instead, as shown in the following screenshot.

To change the queries:

Navigate to Drillhole Utilities | Depth Adjustment.
Double-click to run the Depth Adjustment Process
Click on the Configuration | Panels tab.
Make the following changes to the following queries:

Lithology

Double click to open the Lithology panel.

Click on the Configuration | Editing tab and make the following changes to the following queries:

Allow Updates

The query for 'Allow Updates' needs to be changed to:

UPDATE GB_LITHOLOGY  
   SET DEPTH_FROM = '&ParamNewDepthFrom',
   DEPTH_TO = '&ParamNewDepthTo',
   LITH = '&ParamNewCode'
WHERE
   PROJECT = '&ParamPROJECT' 
   AND SITE_ID = '&ParamSITE_ID' 
   AND INTERVAL_SEQ = &ParamInterval_Seq

Allow Deletes

The query for 'Allow Deletes' needs to be changed to:

DELETE FROM GB_LITHOLOGY 
WHERE 
   PROJECT = '&ParamPROJECT' 
   AND SITE_ID = '&ParamSITE_ID' 
   AND INTERVAL_SEQ = '&ParamInterval_Seq'

Allow Inserts

The query used to insert the first interval for the hole needs to be changed, and must also reference GB_LITHOLOGY rather than GB_NAMED_ZONE:

INSERT INTO GB_LITHOLOGY 
(     
	PROJECT
	,   SITE_ID
	,   INTERVAL_SEQ
	,   DEPTH_FROM
	,   DEPTH_TO
	,   LITH
	,   LITH_PCENT
	,   PARENT_INTERVAL
	,   INTERVAL_TYPE  
) 
VALUES  
(
	    '?Select a Project?'
	,   '?Select a drillhole?'
	,   dbo.ufnNextSequenceLithology('?Select a Project?','?Select a drillhole?')
	,   &ParamNewDepthFrom
	,   &ParamNewDepthTo
	,   '&ParamNewCode'
	,   100
	,   0
	,   'M'
)

The query used to insert a split from an existing interval needs to be changed to:

INSERT INTO GB_LITHOLOGY 
(     
	    PROJECT
	,   SITE_ID
	,   INTERVAL_SEQ
	,   PARENT_INTERVAL
	,   DEPTH_FROM
	,   DEPTH_TO
	,   INTERVAL_TYPE
	,   LITH
	,   LITH_PCENT
) 
VALUES  
(
	    '&ParamPROJECT'
	,   '&ParamSITE_ID'
	,   dbo.ufnNextSequenceLithology('&ParamPROJECT','&ParamSITE_ID')
	,   0
	,   &ParamNewDepthFrom
	,   &ParamNewDepthTo
	,   'M'
	,   '&ParamNewCode'
	,   100
)

Seams

Double click to open the Seams panel.

Click on the Configuration | Editing tab and make the following changes to the following queries:

Allow Updates

The query for 'Allow Updates' needs to be changed to:

UPDATE GB_LITHOLOGY  
   SET DEPTH_FROM = &ParamNewDepthFrom,
   DEPTH_TO = &ParamNewDepthTo,
   LITH = '&ParamNewCode'
WHERE
   PROJECT = '&ParamPROJECT' 
   AND SITE_ID = '&ParamSITE_ID' 
   AND INTERVAL_SEQ = &ParamInterval_Seq

Allow Deletes

The query for 'Allow Deletes' needs to be changed to:

DELETE FROM GB_LITHOLOGY 
   WHERE PROJECT = '&ParamPROJECT' 
   AND SITE_ID = '&ParamSITE_ID' 
   AND INTERVAL_SEQ = &ParamInterval_Seq

Samples

Double click to open the Samples panel.

Click on the Configuration | Editing tab and make the following changes to the following queries:

Allow Updates

The query for 'Allow Updates' needs to be changed to:

UPDATE DBO.GB_SAMPLE  
   SET DEPTH_FROM = &ParamNewDepthFrom,
   DEPTH_TO = &ParamNewDepthTo
WHERE
   PROJECT = '&ParamPROJECT' 
   AND SITE_ID = '&ParamSITE_ID' 
   AND SAMPLE_ID = &ParamSample_ID'