Function
Description
Example
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
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.
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
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)