The functions below can only be used in Import/Export Mappings.
For additional examples of how these functions are used, it is highly recommended to reference our Import Mapping Examples.
For additional information on other functions and expressions in general, please see our Functions Guide and Expressions Guide.
Note that many of the General Purpose Functions documented here can also be used in Import Mappings.
These are general purpose functions that are most commonly used. They can be used in AGS mappings, as well as for other file types, such as gINT, CSV, or Access files.
Returns true if a value exists in a list of strings.
IsContainedIn(Field, 'X’, ‘Y’, ‘Z’)Returns true if a field contains a value. Note that the name of the field is required and not the actual value.
IsDefined(‘Reading’)Returns true if the value held in the specified field exists in the set location IDs selected to be imported or exported.
IsLocationValid(‘LocationId’)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 is returned only if it contains a numeric value.
IsNumeric(Field)IsNumeric(1234.5)IsNumeric(‘1234.5’)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’)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('Table Name', 'Field Name', 'Query Field 1', Query Field Value 1, 'Query Field 2', Query Field Value 2)LookupExistingValue('SampleInformation', 'SampleID', 'LocationDetails.LocationID', [Location ID], 'SampleReference', [Sample Reference])Used to find a value elsewhere in the incoming dataset.
LookupSourceValue('Table Name', Field Name)Determines the value of a field within an input table where the originating row satisfies a given condition.
LookupSourceValueConditional(‘Table’,’Condition’, ‘Field’)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)
These functions can be used to reformat Dates and Times during import. This is commonly required.
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')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.
(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)
ConcatenateFormattedDateTime('Date Field', 'Time Field', 'M/d/yy,MM/dd/yyyy', 'HHmm,HH:mm')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)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)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')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’)These are general purpose functions that are less-commonly used than those above. They can be used in AGS mappings, as well as for other file types, such as gINT, CSV, or Access files.
Determines if a specified table has not had its creation restricted by PreventCreation().
CanCreate(‘Table’)Removes all characters which are not numeric, decimal points (‘.’) or negation symbols (‘-’).
CleanNumericValue(Field)Generates a new GUID.
CreateGUID()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)Reverses the operation of GetDeterminand().
GetDeterminandReverse(Determinand, TestType)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’)Provides the unit of a field in the HBSI model, if available.
GetFieldUnit(‘Group’, ‘Field’)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)Returns the value of a row’s parent. This can only be used where the source data has parent relations configured.
GetParentValue(‘ParentField’)Returns the value of a picklist from its unique identifier.
GetPickListCode(ID)Returns the description of a picklist value associated with a given group’s field.
GetPicklistDescription(‘Group’,’Field’,’Value’)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)Obtains the units associated with a given field.
GetUnit(‘HOLE_GL’)Converts a Boolean ‘True’ or ‘False’ to ‘Y’ or ‘N’, respectively.
GetYesNoValue(‘True’)Returns true if a specified string belongs to the set of location IDs selected to be imported or exported.
IsLocationValidByValue(‘BH127’)Determines if the value of a field for the current input row matches the current project’s ID.
IsProjectValue(‘Field’)Returns true if the value contains a single hash (‘#’).
IsValueAssumed(Field)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’)Removes all characters which are not numeric, decimal points (‘.’) or commas (‘,’).
RemoveAssumedPrefix(Field)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)Converts a decimal coordinate value to the degrees:minutes:seconds format.
ToDegreesMinutesSeconds(Field)These functions are primarily intended for importing Geodasy Files. These functions are not commonly used.
Combines all values from a particular column where the value in a different column is blank. The values for PROJ_ID and HOLE_ID must be specified either directly or indirectly via a field.
CombineRemarks(‘P001’, ‘BH001’, ‘Blank’, ‘Source’)Combines all remarks in the current input table for a project and location combination where the depth value is equal to the current row’s depth.
CombineRemarksForDepth(‘P001’, ‘BH001’, ‘DepthColumn’)Gets the correct top depth value within the GEOL table. This function assumes that GEOL_TOP is present.
GetGeodasyTopDepth(PROJ_ID, HOLE_ID)Provides the correct top depth value (as GetGeodasyTopDepth) but allows the column names to be specified.
GetGeodasyTopDepthCustomColumns(PROJ_ID, HOLE_ID, ‘Project Column’, ‘Location Column’, ‘Stored Depth Column’, ‘Actual Depth Column’)‘Actual Depth Column’ will be created if it does not exist and will be used to store the effective top depth value.
Creates a new temporary table which contains individual entries for sieve and percentage passing values. This exists to provide support for lists delimited with a single character, such as ‘^’.
The temporary table will contain a row for each pair and a field per value. The names of the fields will be the same as those specified as the first two arguments.
PrepareParticleTable('SIEVE', 'PPS', '^', 'TemporaryTable')These functions are intended for lower-level interaction with the model and data rows. These functions are very rarely used.
Provides low-level access to the current input row. The result can be used as an argument for functions which require access to the entire row.
GetCurrentRow()Determines the unique identifier of a row’s parent. This can only be used where the source data has parent relations configured.
GetParentId(GetCurrentRow(),GetSourceData())Returns a data structure that contains all source tables. The result can be used by functions which need access to all incoming tables and their metadata.
GetSourceData()Returns a data structure that contains all destination tables. The result can be used by functions which need access to all destination tables and their metadata.
GetTargetData()Determines the unique identifier of the first row which matches a particular value. Non-string values are currently unsupported.
GetObjectId(GetSourceData(), ‘Table’, ‘Field’, ‘Value’)