Skip to content

Lookup & Data Cleanse Functions

Check

Description

Checks that an expression is True, and raises an error if it is False.

Syntax

Check( expression, errorCode, message)

Arguments

  • expression
    • The expression to evaluate. It must evaluate to True or False. If it evaluates to False, Check raises an error using the next two arguments.
  • errorCode
    • The error code assigned to the error when the expression evaluates to False.
    • In a WebAPI context, the error code can be an HTTP status code, for example 404 (Not Found). IMan then returns the matching HTTP status code.
  • message
    • The error message.

Example

Checks that %AccountValid is True.

Check(%AccountValid, "InvalidGLAccount", "The G/L Account is invalid.")

Checks that the CustomerExists field is True. If it is not, Check raises a 404 error with the message "The customer [ABC] does not exist."

Check(%CustomerExists, 404, "The customer [" & %CustomerId & "] does not exist.")

If CustomerExists were to evaluate to false the following error would be returned in a WebAPI context.

{
  "Type": "Exception",
  "Status": 404,
  "Title": "The customer [ABB001] does not exist."
}

ISOCOUNTRY Table Lookup Fields

The IMan database holds a table of the ISO codes and other data for every country. Three ‘CountryName’ functions query this table.

The table holds these fields:

ISO2

The two-letter ISO Country Code (ISO 3166-1 alpha-2)

ISO3

The three-letter ISO Country Code (ISO 3166-1 alpha-3)

ISEU

Shows whether the country is within the European VAT directive.

NUMBER\_CODE

The three-digit ISO numeric Country Code (ISO 3166-1 numeric)

PHONE\_PFX

The international dialling code, prefixed with +.

IANA\_TLD

The internationally assigned top-level domain suffix.

CURRENCY

ISO 4217 currency code.

xxx\_NAME

The country name in the language denoted by the xxx prefix, written in that language's own script. JPN_NAME, for example, is in Japanese characters.

xxx\_DEMONYM

The demonym, the name for the country's people, in the language denoted by the xxx prefix. For example, people from Italy are called Italians.

Only the English (ENG) language has this field populated.

xxx\_SYNONYMS

English synonyms for the country name. For example, Myanmar is often called Burma.

FuzzyCountryNameLookup queries this field.

Update this field with other synonyms as required.

Each language has three fields, NAME, SYNONYMS and DEMONYM, whose values are in that language.

Language Name
ARA Arabic
ENG English
DEU German
FRA French
JPN Japanese
POR Portuguese
RUS Russian
SPA Spanish
ZHO Chinese (Simplified)

CountryNameLookup

Description

Returns the name of a country in the language you choose.

Syntax

CountryNameLookup( isocode, returnlanguage )

Arguments

  • isocode
    • The country's numeric, two-letter or three-letter ISO code. If no country matches isocode, the function raises an error.
  • returnlanguage
    • The language in which to return the name of the country. If the language is not valid, the function raises an error.

Example

To get the English name of Belarus from its two-letter ISO code:

CountryNameLookup(“BY”, “ENG”)

CountryTableLookup

Description

Returns a field from the ISOCOUNTRY table of the database.

Syntax

CountryTableLookup( isocode, returnlanguage, returnfield )

Arguments

  • isocode
    • The country's numeric, two-letter or three-letter ISO code. If no country matches isocode, the function raises an error.
  • returnlanguage
    • The language in which to return the name of the country. If the language is not valid, the function raises an error.
  • returnfield
    • The field of the ISOCOUNTRY table to return: ISO2, ISO3, NUMBER_CODE, ISEU, PHONE_PFX, IANA_TLD, CURRENCY, NAME, DEMONYM or SYNONYMS. Give NAME, DEMONYM and SYNONYMS without a language prefix. The function adds the prefix from returnlanguage. Any other value raises an error. "DEU_NAME" fails, where "NAME" with a returnlanguage of "DEU" works.

Example

To get the German name of Belarus from its two-letter ISO code:

CountryTableLookup("BY", "DEU", "NAME")

To get the international dialling code of Belarus. The function ignores returnlanguage for this field, but it must still be a valid language:

CountryTableLookup("BY", "ENG", "PHONE_PFX")

FuzzyCountryNameLookup

Description

Returns a field from the ISOCOUNTRY table, by querying both the xxx_NAME and xxx_SYNONYM fields with a SQL LIKE query.

Syntax

FuzzyCountryNameLookup( lookupvalue, returnlanguage, returnfield, mustreturnvalue )

Arguments

  • lookupvalue
    • The value to query the ISOCOUNTRY table with. The function adds the % wildcard to the start and end of lookupvalue.
  • returnlanguage
    • The language in which to return the name of the country. If the language is not valid, the function raises an error.
  • returnfield
    • The field of the ISOCOUNTRY table to return. If returnfield is not valid, the function raises an error.
  • mustreturnvalue
    • When True, the function raises an error if the lookup returns no record.
    • When False, the function returns an empty string if the lookup returns no record.

Example

To get the IANA top-level domain from Weißrussland or Belarus, where a match is required:

FuzzyCountryNameLookup (“Weißrussland”, “ENG”, “IANA_TLD”, True)

To get the ISO currency code from Weißrussland or Belarus, where a match is not required:

FuzzyCountryNameLookup (“Weißrussland”, “ENG”, “Currency”, False)

GetCounterSequence

Description

Returns the next sequence number from a counter and updates the counter. See Counter setup for how to set up and use a counter.

Syntax

GetCounterSequence( counterid )

Arguments

  • counterid
    • The id of the counter.

Example

' IMan-specific. Returns the next number from a counter
' defined in Setup, formatted with that counter's
' prefix, padding and suffix.
'
' EVERY CALL CONSUMES A NUMBER. It is not idempotent, so
' calling it twice in one expression yields two
' different values -- assign it to a variable if the
' value is needed more than once. Numbers are allocated
' inside a database transaction, so concurrent
' integrations drawing on one counter cannot be issued
' the same number.
Dim DocNo
DocNo = GetCounterSequence("SalesOrder")
DocNo   ' Returns e.g. "SO-00001234"

Lookup Function

Description

Runs a parameterised SQL query and returns one or more values from it. The lookup can query any database, or the lookup tables you set up and maintain in IMan.

See VBScript & Lookups for how to set up and use the lookup query.

Syntax

Lookup( lookupid, returnfield, wherevalues, mustreturnvalue [, customMessage])

Arguments

  • lookupid
    • The id of the lookup.
  • returnFields
    • The value(s) to return from the lookup.
    • To return one value, name the single field. To return several, put the fields in an array.
    • If you name several return fields, the function returns an array with the values in the same order as this argument.
    • For an external lookup, each return field must be one of the fields in the lookup's select.
    • For a lookup that queries the IMan lookup tables, each return field must be LKUPRESULT for the result field, LKUPDESC for the Description field, or FLD01, FLD02, … FLD20 for the custom fields.
  • wherevalues
    • The value or values to query with. For an external lookup, wherevalues is a single value or an array with one value for each dynamic replacement in the lookup's where field.
    • For internal lookups, this value is the Key field.
  • mustreturnvalue
    • True or False: whether the lookup query must return one record.
    • True
      • The function raises an error if the lookup returns no record.
    • False
      • The function returns an empty string if the lookup returns no record.
  • customMessage
    • Optional. The message to report if the lookup fails, that is, if the query returns several records or none. If this argument is omitted or empty, the function uses the standard message.

Example

To query the PRODITEMS IMan lookup table for the Result field, with ItemCode as the key:

Lookup(“PRODITEMS”, “LKUPRESULT”, %ItemCode, true)

To query the PRODITEMS IMan lookup table for the first custom field, with ItemCode as the key:

Lookup(“PRODITEMS”, “FLD01”, %ItemCode, true)

To run the PROJTYPE lookup for a single field (TYPECODE), with one field replacement in the where clause:

Lookup(“PROJTYPE”, “TYPECODE”, %ProjectId, true)

To run the MULTILKUP lookup for a single field (RESULT), with three field replacements in the where clause:

Lookup(“MULTILKUP”, “RESULT”, Array(%Fld1, %Fld2, %Fld3), true)

To run the CUST lookup and return three values (IDCUST, CUSTNAME, NAMECITY) in an array:

Values = Lookup(“CUST”, Array(“IDCUST”, “CUSTNAME”, “NAMECITY”), %Email, true)

To read the individual values of the returned array:

Values(0) ‘The first return field (IDCUST)

Values(1) ‘The second return field (CUSTNAME)

Values(2) ‘The third return field (NAMECITY)

To run the PROJTYPE lookup with a custom error message for when the lookup fails (returns several records or none):

Lookup(“PROJTYPE”, “TYPECODE”, %ProjectId, true, “Project code does not exist”)

The same example, using the FormatMessage function to build a parameterised message:

Lookup(“PROJTYPE”, “TYPECODE”, %ProjectId, true, FormatMessage(“Project %1 does not exist”, %ProjectId))

Lookupdb Function

Description

Extends the Lookup Function with a parameterised database connection string. You can then query different databases without setting up a separate lookup and database connection for each one.

This function is not available for lookups that query the IMan lookup tables.

See VBScript & Lookups for how to set up and use the lookup query.

Syntax

LookupDb( lookupid, returnfield, wherevalues, mustreturnvalue, dbContext [, customMessage])

Arguments

  • lookupid
    • The id of the lookup.
  • returnFields
    • The value(s) to return from the lookup.
    • To return one value, name the single field. To return several, put the fields in an array.
    • If you name several return fields, the function returns an array with the values in the same order as this argument.
    • Each return field must be one of the fields in the lookup's select.
  • wherevalues
    • The value or values to query with: a single value or an array with one value for each dynamic replacement in the lookup's where field.
  • mustreturnvalue
    • True or False: whether the lookup query must return one record.
    • True
      • The function raises an error if the lookup returns no record.
    • False
      • The function returns an empty string if the lookup returns no record.
  • dbContext
    • One or more values to parameterise the database connection string. The dbContext is either a single value or an array of values.
    • The function replaces each numbered token in the lookup's connection string (%1, %2, %3) with the matching value from this argument.
  • customMessage
    • Optional. The message to report if the lookup fails, that is, if the query returns several records or none. If this argument is omitted or empty, the function uses the standard message.

Example

See the examples under Lookup Function for the other arguments.

With this connection string, where the Database token is parameterised:

Driver={SQL Server Native Client 11.0};Server=SQLSERVER;Database=%1;Uid=user;Pwd=pword;

To run the ITEMS lookup with a single replacement in the dbContext argument:

LookupDb(“ITEMS”, “ItemName”, %ItemCode, true, %Dbase)

With this connection string, where both the Server and Database tokens are parameterised:

Driver={SQL Server Native Client 11.0};Server=%1;Database=%2;Uid=user;Pwd=pword;

To run the ITEMS lookup with several replacements in dbContext:

LookupDb(“ITEMS”, “ItemName”, %ItemCode, true, Array(%Svr, % Dbase))

LookupWhere Function

Description

LookupWhere is a variant of the Lookup Function that overrides the where clause set in the lookup.

This function takes the same parameterised connection

This function also implements the parameterised database connection described in Lookupdb Function.

See VBScript & Lookups for how to set up and use the lookup query.

Syntax

LookupWhere( lookupid, returnfield, whereclause, mustreturnvalue, dbContext [, customMessage])

Arguments

  • lookupid
    • The id of the lookup.
  • returnFields
    • If you name several return fields, the function returns an array with the values in the same order as this argument.
    • For an external lookup, each return field must be one of the fields in the lookup's select.
    • For a lookup that queries the IMan lookup tables, each return field must be LKUPRESULT for the result field, LKUPDESC for the Description field, or FLD01, FLD02, … FLD20 for the custom fields.
  • whereclause
    • The where clause to use in the query. If the lookup targets an external database, the function ignores the where clause in the Lookup setup and uses this argument.
    • If the lookup targets an IMan lookup table, this argument overrides the filter or where clause that IMan builds internally for the query.
  • mustreturnvalue
    • True or False: whether the lookup query must return one record.
    • True
      • The function raises an error if the lookup returns no record.
    • False
      • The function returns an empty string if the lookup returns no record.
  • dbContext
    • One or more values to parameterise the database connection string. The dbContext is either a single value or an array of values.
    • The function replaces each numbered token in the lookup's connection string (%1, %2, %3) with the matching value from this argument.
  • customMessage
    • Optional. The message to report if the lookup fails, that is, if the query returns several records or none. If this argument is omitted or empty, the function uses the standard message.

Example

See the examples under Lookup Function for the other arguments.

Use LookupWhere against an IMan lookup table to perform a reverse translation, that is, to filter on the LKUPRESULT field and return the LKUPKEY field. You do not need to name the IMan lookup table, because IMan adds it to the query.

LookupWhere(“IMANLOOKUP”, “LKUPKEY”, “LKUPRESULT = ‘value’”, true, “”)

dbContext is ignored for an IMan lookup

The last argument, dbContext, is an empty string. The lookup is an IMan lookup, so the function ignores any value there.

To run LookupWhere against a lookup on a G/L or nominal account table, with a where clause filtering on the ACSEGVAL01 and ACSEGVAL02 columns:

LookupWhere(“GLACC”, “ACCTID”, “ACSEGVAL01 = ‘4000' and ACSEGVAL02 = '100'”, true, “”)

Populate dbContext only for a parameterised connection

The last argument, dbContext, is an empty string. Fill it in if the database connection string accepts parameters.

The same example, using the FormatMessage function:

LookupWhere(“GLACC”, “ACCTID”, FormatMessage(“ACSEGVAL01 = ‘%1' and ACSEGVAL02 = '%2'”, Array(%Segment1, %Segment2), true, “”)

TieredLookup Function

Description

The TieredLookup function queries the values defined in a Tiered Lookup.

Because the hierarchies in a tiered lookup vary, base any call on the code snippet at the foot of the Tiered Lookup maintenance page.

Syntax

TieredLookup( lookupid, returnFields, ruleNames, ruleValues, mustreturnvalue )

Arguments

  • lookupid
    • The id of the lookup.
  • returnFields
    • Not used at present, because there is only one field to return. Pass an empty string, "".
  • ruleNames
    • An array of strings containing the rule names.
    • Because the hierarchies in a tiered lookup vary, the rule names do not have to match the entry you are querying. The function scans the names, with their ruleValues, for rules in the hierarchy. Rule names that match no rule have no effect on the result.
  • ruleValues
    • The values for the rule names in ruleNames.
  • mustreturnvalue
    • True or False: whether the lookup query must return one record.
    • True
      • The function raises an error if the lookup returns no record.
    • False
      • The function returns an empty string if the lookup returns no record.

Example

TieredLookup("SHIP", "", Array("Website", "Supplier", "Cost", "Qty", "Weight“, "Post Code"), Array("UK", "Makita", 250.80, 5, 31.2, "E1"), True)

This queries the tiered lookup SHIP.

The function works as follows:

  • It finds the first rule in the SHIP tiered lookup and matches it to one of the names in ruleNames.
  • It matches the corresponding value against the rule.
  • It finds the name of the second rule and matches it to one of the names in ruleNames.
  • It matches the corresponding value against the second rule.
  • This continues until there are no more rules.
  • If it finds a match, it returns the corresponding value.