CanCreate

Determines if a specified table has not had its creation restricted by PreventCreation().

CanCreate(‘Table’)

ConcatenateDateTime

Combines a time and date fields into one date-time value. The time component is added to the date field’s value.

ConcatenateDateTime('Date Field', 'Time Field')

ConcatenateFormattedDateTime

Combines a time and date fields into one date-time value. The time component is added to the date field’s value.

Due to the possibility of multiple date and time formats in the incoming data, this will try each date and time format in order with the data until it finds the one it matches. Any number of possible combinations can be added.

ConcatenateFormattedDateTime('Date Field', 'Time Field', 'M/d/yy,MM/dd/yyyy', 'HHmm,HH:mm')

(In this example it will try and match M/d/yy first, then MM/dd/yyyy second. With the time field, it will try HHmm first, then HH:mm second)

ExtractDate

Extracts the date component from a combined date-time value. A string is returned. This function requires that the input value is valid date-time value and could be obtained from the HBSI model during an export process.

Note that the argument is the value itself and not a single-quoted string containing the name of the field.

ExtractDate(DateTimeField)

ExtractTime

Extracts the time component from a combined date-time value. A string is returned. This function requires that the input value is valid date-time value and could be obtained from the HBSI model during an export process.

Note that the argument is the value itself and not a single-quoted string containing the name of the field.

ExtractTime(DateTimeField)

GetDeterminand

Attempts to ascertain the determinand for a known determinand and test type combination. The internal lookup table is fixed as it links AGS 3.1 determinands and test types to the HBSI equivalents. If a match cannot be found, an empty string is returned.

GetDeterminand(CNMT_TYPE, CNMT_TTYP)

GetDeterminandReverse

Reverses the operation of GetDeterminand().

GetDeterminandReverse(Determinand, TestType)

GetEntityValue

Obtains the raw value of a row given the source table name, field name and its unique identifier within that table. The ‘ID’ must be a GUID.

GetEntityValue(ID, ‘Table’, ‘Field’)

GetFieldUnit

Provides the unit of a field in the HBSI model, if available.

GetFieldUnit(‘Group’, ‘Field’)

GetPickListCode

Returns the value of a picklist from its unique identifier.

GetPickListCode(ID)

GetPicklistDescription

Returns the description of a picklist value associated with a given group’s field.

GetPicklistDescription(‘Group’,’Field’,’Value’)

GetTestType

Attempts to ascertain the test type value for a given determinand and test type combination. The internal lookup table is fixed as it links AGS 3.1 determinands and test types to the HBSI equivalents. If a match cannot be found, an empty string is returned.

GetTestType(CNMT_TYPE, CNMT_TTYP)

GetTimestamp

Adds the time component to a date-time field’s value. The time field can be a date-time value or a string containing a time value in a specific format. The final argument is mandatory if the time field is not a date-time value but optional otherwise.

GetTimestamp(DateValue, TimeValue)

GetTimestamp(DateValue, ’12:00’, ‘HH:mm’)

GetIsoTimestamp

Adds the time component to a date-time field’s value. The time field can be a date-time value or a string containing a time value in a specific format. The final argument is mandatory if the time field is not a date-time value but optional otherwise.

This is locale independent

GetIsoTimestamp(DateValue, '0000', 'hhmm')

GetOfficeName

Retrieves the name of an office when the raw identifier of the office is known. This identifier may be associated with a project.

GetOfficeName (OBJECT_ID)

GetUnit

Obtains the units associated with a given field.

GetUnit(‘HOLE_GL’)

GetYesNoValue

Converts a Boolean ‘True’ or ‘False’ to ‘Y’ or ‘N’, respectively.

GetYesNoValue(‘True’)

IsContainedIn

Returns true if a value exists in a list of strings.

IsContainedIn(Field, 'X’, ‘Y’, ‘Z’)

IsDefined

Returns true if a field contains a value. Note that the name of the field is required and not the actual value.

IsDefined(‘Reading’)

IsLocationValid

Returns true if the value held in the specified field exists in the set location IDs selected to be imported or exported.

IsLocationValid(‘LocationId’)

IsLocationValidByValue

Returns true if a specified string belongs to the set of location IDs selected to be imported or exported.

IsLocationValidByValue(‘BH127’)

IsNumeric

Returns true if a value is numeric. If the type of provided value is numeric, true will always be returned. If a string is passed, true if returned only if it contains a numeric value.

IsNumeric(1234.5)

IsNumeric(‘1234.5’)

IsNumeric(Field)

IsProjectValid

Determines if the value of a field for the current input row matches the current project’s ID.

IsProjectValue(‘Field’)

IsUndefined

Returns true if a field does not contain a value. Note that the name of the field is required and not the actual value.

IsUndefined(‘Reading’)

IsValueAssumed

Returns true if the value contains a single hash (‘#’).

IsValueAssumed(Field)

LookupSourceValue

Used to find a value elsewhere in the incoming dataset.

LookupSourceValue('Table Name', Field Name)

LookupSourceValueConditional

Determines the value of a field within an input table where the originating row satisfies a given condition.

LookupSourceValueConditional(‘Table’,’Condition’, ‘Field’)

LookupValue

Used to obtain a value in the destination/output tables.

LookupValue('Table Name', ‘Condition’ ‘Field Name’)

LookupValue('SampleInformation', 'LocationID = \'' + [PointID] + '\' AND Convert(DepthTop, \'System.Double\') <= ' + fixed(Depth, 3, false) + ' AND Convert(DepthBase, \'System.Double\') > ' + fixed(Depth, 3, false), 'DepthTop')

(The above expression will find the sample information key fields to try and join up lab test data to its parent samples where the sample data has been created from different sources)

LookupExistingValue

Used to join children records onto a parent record when they only have part of a parent match. For example, joining lab data onto samples with only the sample reference and none of the other key fields.

LookupExistingValue('SampleInformation', 'SampleID', 'LocationDetails.LocationID', [Location ID], 'SampleReference', [Sample Reference])

PreventCreation

Prevents the specified output table from being created for the current input row. Duplicate entries are permitted as only unique names are retained. This function always returns true.

PreventCreation(‘Table’)

RemoveAssumedPrefix

Removes all characters which are not numeric, decimal points (‘.’) or commas (‘,’).

RemoveAssumedPrefix(Field)

RemoveTrailingZeroes

Removes trailing zeroes from the provided string but only if it contains a numeric value. If the removal could not be performed, the provided value will be returned.

RemoveTrailingZeroes(Field)

ToDegreesMinutesSeconds

Converts a decimal coordinate value to the degrees:minutes:seconds format.

ToDegreesMinutesSeconds(Field)