Set Material Name

Overview

This tool writes data to fields named Material_Name, Material_Term_# (where # can be 1 to 9) on depth related tables based on the data stored in STRATA_MAIN table and its children such as STRATA_SOIL_AS.  Hence it can be used to write the material name to each record on a depth related table, such as a lab test.

Use

If a depth related table has the fields Material_Name and Material_Term_# , they will be written according to the configuration stored in PROJECT_OPTIONS.Material_Name_SQL and PROJECT_OPTIONS.Material_Term_SQL_#.  Where # can be a number between 1 and 9.

Project Tables and Fields

Project_Options table


In this example, data from STRATA_MAIN table will be written to Consistency/Density Table.


Usage

Run the Add-Ins > DGD Tool > Set Material Name to run the calculations.

Considerations

  1. The SQL syntax for column sources is editable and is stored in PROJECT_OPTIONS.Material_Name_SQL, PROJECT_OPTIONS.Material_Term_SQL_#
  2. The resulting text is processed to replace multiple spaces with a single space, and () are removed.  Sub queries must be used to reference tables other than STRATA_MAIN.
  3. Calculation takes place:
    1. Add-In command – will calculate every PointID for Material_Name and Material_Term_# fields in all depth related tables
    2. Upon leaving a given depth table after making an edit, it will calculate that table for the current PointID
    3. Upon leaving a STRATA_MAIN or its child tables after making an edit, it will calculate all tables for the current PointID

The current database structure has Material_Term_1 and Material_Term_2, but the code allows up to Material_Term_9. So you can add more fields (from Material_Term_3 to Material_Term_9) to the desired tables and add the required fields Material_Term_SQL_3 to Material_Term_SQL_9 to the PROJECT_OPTIONS table. 


Existing examples:

Material Name SQL
(
select UCase([STRATA_SOIL_COMPOSITE_AS].[Conjunction_Qualifier])
from STRATA_SOIL_COMPOSITE_AS
where STRATA_SOIL_COMPOSITE_AS.PointID = STRATA_MAIN.PointID and STRATA_SOIL_COMPOSITE_AS.Depth = STRATA_MAIN.Depth and STRATA_SOIL_COMPOSITE_AS.Bottom = STRATA_MAIN.Bottom
)
& ' ' &
(
select StrConv([STRATA_SOIL_AS].[Modification_Qualifier], 3) &
(
select StrConv([DG_LOG_ST_AS_S_NAME].[Adjective], 3)
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_AS].[Secondary_Component_2_Name]
)
& ' ' &
(
select StrConv([DG_LOG_ST_AS_S_NAME].[Adjective], 3)
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_AS].[Secondary_Component_1_Name]
)
& ' ' &
UCase([STRATA_SOIL_AS].[Soil_Name_Qualifier])
& ' ' &
UCase([STRATA_SOIL_AS].[Soil_Name])
& ' ' & UCase([STRATA_SOIL_AS].[Soil_Name_Suffix])
& ' (' & [STRATA_SOIL_AS].[USCS] & ')'
& ' ' &
(
iif(
'' <>
(
select [DG_LOG_ST_AS_S_NAME].[After_Primary]
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_AS].[Secondary_Component_1_Name]
)
,
iif(
'' <>
(
select [DG_LOG_ST_AS_S_NAME].[After_Primary]
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_AS].[Secondary_Component_2_Name]
)
,
'with ' &
(
select UCase([DG_LOG_ST_AS_S_NAME].[After_Primary])
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_AS].[Secondary_Component_1_Name]
)
& ' and ' &
(
select UCase([DG_LOG_ST_AS_S_NAME].[After_Primary])
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_AS].[Secondary_Component_2_Name]
)
,
(
select 'with ' & UCase([DG_LOG_ST_AS_S_NAME].[After_Primary])
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_AS].[Secondary_Component_1_Name] and len([DG_LOG_ST_AS_S_NAME].[After_Primary]) > 0
)
)
,
''
)
)
& ' ' &
[STRATA_SOIL_AS].[Minor_Component_1_Name_Qualifier]
& ' ' &
[STRATA_SOIL_AS].[Minor_Component_1_Name]
& ' ' &
[STRATA_SOIL_AS].[Minor_Component_2_Name_Qualifier]
& ' ' &
[STRATA_SOIL_AS].[Minor_Component_2_Name]
from STRATA_SOIL_AS
where STRATA_SOIL_AS.PointID = STRATA_MAIN.PointID and STRATA_SOIL_AS.Depth = STRATA_MAIN.Depth and STRATA_SOIL_AS.Bottom = STRATA_MAIN.Bottom
)
& ' ' &
(
select [STRATA_SOIL_COMPOSITE_AS].[Conjunction]
from STRATA_SOIL_COMPOSITE_AS
where STRATA_SOIL_COMPOSITE_AS.PointID = STRATA_MAIN.PointID and STRATA_SOIL_COMPOSITE_AS.Depth = STRATA_MAIN.Depth and STRATA_SOIL_COMPOSITE_AS.Bottom = STRATA_MAIN.Bottom
)
& ' ' &
(
select StrConv([STRATA_SOIL_COMPOSITE_AS].[Modification_Qualifier], 3)
& ' ' &
(
select StrConv([DG_LOG_ST_AS_S_NAME].[Adjective], 3)
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_COMPOSITE_AS].[Secondary_Component_2_Name]
)
& ' ' &
(
select StrConv([DG_LOG_ST_AS_S_NAME].[Adjective], 3)
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_COMPOSITE_AS].[Secondary_Component_1_Name]
)
& ' ' &
UCase([STRATA_SOIL_COMPOSITE_AS].[Soil_Name_Qualifier])
& ' ' &
UCase([STRATA_SOIL_COMPOSITE_AS].[Soil_Name])
& ' ' &
UCase([STRATA_SOIL_COMPOSITE_AS].[Soil_Name_Suffix])
& ' (' & [STRATA_SOIL_COMPOSITE_AS].[USCS] & ')'
& ' ' &
iif(
'' <>
(
select [DG_LOG_ST_AS_S_NAME].[After_Primary]
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_COMPOSITE_AS].[Secondary_Component_1_Name]
)
,
iif(
'' <>
(
select [DG_LOG_ST_AS_S_NAME].[After_Primary]
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_COMPOSITE_AS].[Secondary_Component_2_Name]
)
,
'with ' &
(
select [DG_LOG_ST_AS_S_NAME].[After_Primary]
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_COMPOSITE_AS].[Secondary_Component_1_Name]
)
& ' and ' &
(
select [DG_LOG_ST_AS_S_NAME].[After_Primary]
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_COMPOSITE_AS].[Secondary_Component_2_Name]
)
,
'with ' &
(
select [DG_LOG_ST_AS_S_NAME].[After_Primary]
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_COMPOSITE_AS].[Secondary_Component_1_Name]
)
)
,
(
select 'with ' & [DG_LOG_ST_AS_S_NAME].[After_Primary]
from [<<LibFileSpec>>].[DG_LOG_ST_AS_S_NAME]
where [DG_LOG_ST_AS_S_NAME].[Name] = [STRATA_SOIL_COMPOSITE_AS].[Secondary_Component_2_Name] and len([DG_LOG_ST_AS_S_NAME].[After_Primary]) > 0
)
)
& ' ' &
[STRATA_SOIL_COMPOSITE_AS].[Minor_Component_1_Name_Qualifier]
& ' ' &
[STRATA_SOIL_COMPOSITE_AS].[Minor_Component_1_Name]
& ' ' &
[STRATA_SOIL_COMPOSITE_AS].[Minor_Component_2_Name_Qualifier]
& ' ' &
[STRATA_SOIL_COMPOSITE_AS].[Minor_Component_2_Name]
from STRATA_SOIL_COMPOSITE_AS
where STRATA_SOIL_COMPOSITE_AS.PointID = STRATA_MAIN.PointID and STRATA_SOIL_COMPOSITE_AS.Depth = STRATA_MAIN.Depth and STRATA_SOIL_COMPOSITE_AS.Bottom = STRATA_MAIN.Bottom
)
& ' (' & STRATA_MAIN.CLASSIFICATION_SYMBOL &') ' &
(
Select UCase([STRATA_ROCK_COMPOSITE_AS].[Conjunction_Qualifier])
from STRATA_ROCK_COMPOSITE_AS
where STRATA_ROCK_COMPOSITE_AS.PointID = STRATA_MAIN.PointID and STRATA_ROCK_COMPOSITE_AS.Depth = STRATA_MAIN.Depth and STRATA_ROCK_COMPOSITE_AS.Bottom = STRATA_MAIN.Bottom
)
& ' ' &
(
Select UCase([STRATA_ROCK_AS].[Rock_Name_Qualifier])
& ' ' &
UCase([STRATA_ROCK_AS].[Rock_Name])
& ' ' &
UCase([STRATA_ROCK_AS].[Rock_Name_Suffix])
from STRATA_ROCK_AS
where STRATA_ROCK_AS.PointID = STRATA_MAIN.PointID and STRATA_ROCK_AS.Depth = STRATA_MAIN.Depth and STRATA_ROCK_AS.Bottom = STRATA_MAIN.Bottom
)
& ' ' &
(
Select [STRATA_ROCK_COMPOSITE_AS].[Conjunction]
& ' '&
UCase([STRATA_ROCK_COMPOSITE_AS].[Rock_Name_Qualifier])
& ' ' &
UCase([STRATA_ROCK_COMPOSITE_AS].[Rock_Name])
& ' ' &
UCase([STRATA_ROCK_COMPOSITE_AS].[Rock_Name_Suffix])
from STRATA_ROCK_COMPOSITE_AS
where STRATA_ROCK_COMPOSITE_AS.PointID = STRATA_MAIN.PointID and STRATA_ROCK_COMPOSITE_AS.Depth = STRATA_MAIN.Depth and STRATA_ROCK_COMPOSITE_AS.Bottom = STRATA_MAIN.Bottom
)
Material Term 1
[Geology_Unit_1]
Material Term 1
[Origin]
On this page