Lookup & Data Cleanse Functions

Back to top

ISOCOUNTRY Table Lookup Fields

A complete table containing the ISOCodes and other relevant data pertaining to all the countries is located in the IMAN database. Three ‘CountryName’ functions support the querying of this table.

The following lists the fields in this table:

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

Indicates if the country participates within the European VAT directive.

NUMBER_CODE

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

PHONE_PFX

The international dialling code. All codes are prefixed with ‘+’

IANA_TLD

International assigned top level domain suffix.

CURRENCY

ISO (4127) Currency code.

xxx_NAME

The country name in the language denoted by the xxx prefix. The value in this field is in character set of the language spoken by the country.

xxx_DEMONYM

The ‘gentillic’ name of the country in the language denoted by the xxx prefix. E.g. People originating from Italy are called Italians.

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

xxx_SYNONYMS

English Synonyms for the country name. E.g. Myanmar is often referred to as Burma.

The FuzzyCountryLookup uses this field to query against.

Update this field with other synonyms as required.

Each language has three fields (NAME, SYNONYMNS & DEMONYM) where the values in the field are represented in the language spoken in the country.

Language

Name

ARA

Arabic

ENG

English

DEU

German

FRA

French

JPN

Japanese

POR

Portuguese

RUS

Russian

SPA

Spanish

ZHO

Chinese (Simplified)
   
Back to top

CountryNameLookup

Description

Returns the name of a country in the character set of the language spoken in the country.

Syntax

  • CountryNameLookup( isocode, returnlanguage )

Arguments

  • isocode
    • The numeric or the 2 character or 3 letter ISO code assigned to the country. If there is no corresponding result to iscocode argument an error is raised.
  • returnlanguage
    • The language in which to return the name of the country. If an invalid language is specified an error is raised.

Example

To obtain the English name of Belarus using the 2 letter ISO Code.

CountryNameLookup(“BY”, “ENG”)

Back to top

CountryTableLookup

Description

Returns a field from the ISOCOUNTRY table of the database.

Syntax

  • CountryTableLookup( isocode, returnlanguage, returnfield )

Arguments

  • isocode
    • Can be the numeric or the 2 character or 3 letter ISO code assigned to the country. If there is no corresponding result to iscocode argument an error is raised.
  • returnlanguage
    • The language in which to return the name of the country. If an invalid language is specified an error is raised.
  • returnfield
    • The field from the ISOCODE table to return. If the returnfield is invalid an error is raised.

Example

To obtain the German synonyms, (returning Weißrussland) for Belarus using the 2 letter ISO Code.

CountryTableLookup (“BY”, “ENG”, “DEU_SYNONYM”)

Back to top

FuzzyCountryNameLookup

Description

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

Syntax

  • FuzzyCountryNameLookup( lookupvalue, returnlanguage, returnfield, mustreturnvalue )

Arguments

  • lookupvalue
    • The value used to query the ISOCOUNTRY table. The lookupvalue is pre-prended and appended with the ‘%’ wildcard.
  • returnlanguage
    • The language in which to return the name of the country. If an invalid language is specified an error is raised.
  • returnfield
    • The field from the ISOCODE table to return. If the returnfield is invalid an error is raised.
  • mustreturnvalue
    • When specified as true, the lookup will raise an error if the lookup fails to return a record.
    • When false if no record is returned, an empty string is returned.

Example

To obtain the IANA top-level domain using Weißrussland or Belarus, where a match must be made.

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

To obtain the ISO currency code using Weißrussland or Belarus, where no matching result needs to be returned.

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

Back to top

GetCounterSequence

Description

Returns and updates the next sequence number from a counter. See the Counter setup section for usage and setup.

Syntax

  • GetCounterSequence( counterid )

Arguments

  • counterid
    • The id of the counter.
Back to top

Lookup Function

Description

Executes a parameterised SQL query to return one or more values from the query. The lookup may query any database or the Lookup tables setup and maintained within IMan.

See the VBScript & Lookups for usage and setup of 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.
    • A single value is returned by specifying the single field to return or to return multiple fields enclose the fields within an array.
    • If multiple return fields are specified an array is returned by the function where the values are in the same sequence as specified in this argument.
    • Where the lookup is an external lookup the return field(s) must be one of the fields in the select portion of the lookup.
    • For lookups which query the IMan lookup tables the return field(s) must be ‘LKUPRESULT’ to return the result field; or ‘LKUPDESC’ to return the Description field; or FLD01, FLD02, …, FLD20 to return the custom defined fields.
  • wherevalues
    • The value or values used to query. For external lookups the wherevalues are either a single value or an array of values, where the size of the array is equal to the number of dynamic replacements in the wherefield of the lookup.
    • For internal lookups, this value is the Key field.
  • mustreturnvalue
    • True or False to specify the Lookup query should return one record.
    • True
      • An error will be raised if the lookup fails to return a record.
    • False
      • The function will return an empty string if the lookup fails to return a record.
  • customMessage
    • Optional. The message if the lookup fails i.e. the query returns multiple (or no) records. The standard message will be used if this argument is omitted or empty.

Example

To query the PRODITEMS IMan lookup table returning the Result field by specifying the ItemCode parameter as the key:

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

To query the PRODITEMS IMan lookup table returning the first custom field using the ItemCode parameter as the key:

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

To execute the PROJTYPE lookup to return a single field (TYPECODE) with a single field replacement in the where clause:

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

To execute the MULTILKUP lookup returning a single field (RESULT) where there are three field replacement in the where clause:

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

To execute the CUST lookup returning three values (IDCUST, CUSTNAME, NAMECITY) in an array:

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

To access the individual values within 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 execute the PROJTYPE lookup to return a custom error message if the lookup fails (returning multiple or no records).

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

Extending the example above, by using the FormatMessage (link) function to provide a parameterised message.

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

Back to top

Lookupdb Function

Description

Extends the Lookup Function by allowing the database connection string used by Lookup function to be parameterised. This provides the ability to query different databases without having to setup individual lookups and database connections for each database.

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

See the VBScript & Lookups for usage and setup of 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.
    • A single value is returned by specifying the single field to return to, or to return multiple fields enclose the fields within an array.
    • If multiple return fields are specified an array is returned by the function where the values are in the same sequence as specified in this argument.
    • The return field(s) must be one of the fields specified in the select portion of the lookup.
  • wherevalues
    • The value or values used to query. The wherevalues are either a single value or an array of values, where the size of the array is equal to the number of dynamic replacements in the wherefield of the lookup.
  • mustreturnvalue
    • True or False to specify the Lookup query should return one record.
    • True
      • An error will be raised if the lookup fails to return a record.
    • False
      • The function will return an empty string if the lookup fails to return a 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 database connection string used by the Lookup will be parameterised by replacing each of the numerically ordered tokens (%1, %2, %3) with the values specified in this argument.
  • customMessage
    • Optional. The message if the lookup fails i.e. the query returns multiple (or no) records. The standard message will be used if this argument is omitted or empty.

Example

See the examples listed in the Lookup Function to see how the other arguments are used.

Using the following connection string where the Database token within the connection string is parameterised:

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

To execute the ITEMS lookup with a single replacement to the databaseContext argument:

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

Using the following connection string where both Server and Database tokens are parameterised:

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

To execute the ITEMS lookup with multiple replacements to the databaseContext:

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

Back to top

LookupWhere Function

Description

The LookupWhere clause is a variant of the Lookup Function providing the means to override the where clause specified in the lookup.

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

See the VBScript & Lookups for usage and setup of the lookup query.

Syntax

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

Arguments

  • lookupid
    • The id of the lookup.
  • returnFields
    • If multiple return fields are specified an array is returned by the function where the values are in the same sequence as specified in this argument.
    • Where the lookup is an external lookup the return field(s) must be one of the fields in the select portion of the lookup.
    • For lookups which query the IMan lookup tables the return field(s) must be ‘LKUPRESULT’ to return the result field; or ‘LKUPDESC’ to return the Description field; or FLD01, FLD02, …, FLD20 to return the custom defined fields.
  • whereclause
    • The where clause to use when executing the query. If a lookup targets an external database the where clause specified in the Lookup setup will be ignored and the value specified in this argument used.
    • If a lookup targets an IMan Lookup table this argument will override the filter/where clause constructed internally when executing the query.
  • mustreturnvalue
    • True or False to specify the Lookup query should return one record.
    • True
      • An error will be raised if the lookup fails to return a record.
    • False
      • The function will return an empty string if the lookup fails to return a 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 database connection string used by the Lookup will be parameterised by replacing each of the numerically ordered tokens (%1, %2, %3) with the values specified in this argument.
  • customMessage
    • Optional. The message if the lookup fails i.e. the query returns multiple (or no) records. The standard message will be used if this argument is omitted or empty.

Example

See the examples listed in the Lookup Function to see how the other arguments are used.

Use the LookupWhere function against an IMan lookup table function to perform a reverse translation i.e. filtering against the LKUPRESULT field, returning the LKUPKEY field. It is not necessary to specify the IMan lookup table to query as this will be included into the query.

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

The last argument is an empty string for the dbContext argument since the Lookup is an IMan lookup so any value will be ignored.

Execute LookupWhere against a Lookup targeting an G/L or Nominal Account table with where clause filtering on the ACSEGVAL01 and ACSEGVAL02 columns.

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

The last argument is an empty string for the dbContext argument. Populate this value if the database connection string accept parameters.

Extending the example above by using the FormatMessage function.

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