Home Search

DriveWorks Solo 19
DWVLookup

Send Feedback

DWVLookup

Returns a value from a table corresponding to a match found in the specified column.

Syntax

DWVLookup( [Value], [Table Name], [Lookup Column], [Result Column Index], [MatchType] )

Where:

Value is the value to be found.

Table Name is the table within which the value is to be found.

Lookup Column is the number of the column containing the data to be searched for.

Result Column Index is the number of the column containing the data to be returned.

MatchType defines whether the match has to be exact (False) or the nearest found (True).

Text matching is not case sensitive (even when using FALSE for an exact match).
Data

Ensure the data (in the Value field and also in the Table being searched) can be matched.

Spaces and non-printable characters are common causes of data mismatch.

If the function returns a #VALUE! error, a data mismatch could be the cause.

See #VALUE! below for more information.

Examples

Rule

Meaning
DWVLookup(CarColourComboBoxReturn, Vehicles, 3, 2, FALSE)The function looks in the Vehicles table for a match, in the third column of the table, to the value returned from the CarColourComboBox.

If a match is found, the function then looks across to the second column and returns the value in the same row as the match found.

The user has also specified that the match needs to be exact (denoted by the FALSE term).

In this case, the Car Color selected in the combo box is "Blue", so the function returns "Golf".

Rule

Meaning
DWVLookup
  (
    CarColourComboBoxReturn,
    Vehicles,
    3,
    2,
    FALSE
  )
The function looks in the Vehicles table for a match, in the third column of the table, to the value returned from the CarColourComboBox.

If a match is found, the function then looks across to the second column and returns the value in the same row as the match found.

The user has also specified that the match needs to be exact (denoted by the FALSE term).

In this case, the Car Color selected in the combo box is "Blue", so the function returns "Golf".

Example Outcomes

Value

Table Name

Lookup Column

Result Column Index

MatchType

Outcome

"Blue"Vehicles32False (Exact)"Golf"
"White"Vehicles31False (Exact)"Renault"
26People32True (Nearest)"Dave Sharp"
42People34False (Exact)Female

Example Data

Vehicles Table

MakeModelColor
VolkswagenGolfBlue
FordEscortRed
RenaultKangooWhite
Mazda2Black

People Table

Member IDNameAgeGender
42Dave Sharp25Male
44Joe Bloggs37Male
96Sandra Shield42Female
107Thomas Knight21Male
251Isabelle Jones56Female

Error Results

#VALUE!

This error often occurs when the value to be found does not exist in the table.

Using the Vehicles Table (above) as the table the value is to be found in, examples of this include:

  • Value (to be found) = "Tesla"

    "Tesla" does not exist in the table.

    to resolve:

    Modify the table to include the missing value.

  • Value (to be found) = "Volkwagon"

    "Volkwagon" is spelled incorrectly and does not exist in the table.

    Similarly the entry in the table could have the incorrect spelling.

    to resolve:

    Ensure spellings are correct in the value filed and table values.

  • Value (to be found) = "Ford "

    "Ford " has a space at the end, where the table value does not.

    Similarly the entry in the table could have the extra space.

    To resolve:

    Ensure trailing spaces are removed.

    Consider using the Trim function to remove whitespace characters.

    The Trim functions will also remove additional spaces in between words.

    Additional spaces can be difficult to view, also other non printable characters (such as carriage returns) will not be displayed in the table.

    Finding and cleaning offending characters using a third party editor is recommended.

    Notepad++ allows all characters to be shown.

    Paste the data into Notepad++ and select View>Show Symbol>Show All Characters

    Remove all non printable characters and copy the data back into DriveWorks.

DriveWorks Tech Tips Portal

RGB Hex Conversion

This Tech Tip demonstrates how to convert RGB values into Hex values the ColorHex function.

View all Tech Tips

Table Functions

A selection of available functionality when pulling data from a DriveWorks Table onto your form.

View all Tech Tips

DriveWorks Tech Tips Portal is available to DriveWorks Pro customers with an active subscription and support contract.

Tech Tips provide cut down projects that highlight specific functionality for faster and more effective learning.

To access the portal:

  1. Go to https://my.driveworks.co.uk/learn/tech-tips/
  2. Login with your DriveWorks Pro account credentials, if asked
  3. Click the Visit Tech Tips Portal button for access

The portal provides a search facility, start typing the name of the Tech Tip (as indicated above) to display the Tip you require.