Import Format Macro Example: Creating Drop Down (Lookup) Entries

Scenario: a drop-down (lookup) key-value along with the description are included in an import file.  There are data scenarios were in the drop-down (lookup) table:

1)      The key (code) already exists and we would use the key-value as it was passed in.

2)      The description exists;  however, the key (code) does not and we would use the key value of the found description.

3)      Neither the key nor description exists and the entry would be added to the drop-down (lookup) table and then use the key value that was passed in (created).

Sample macro before replacing placeholders

-- test if code exists
IF NOT EXISTS (Select MajorCode From tbl_LUMajor Where MajorCode = '[%MajorCode%]')

    BEGIN
        -- test if description exists
        Declare @code varchar(4)
        Select @code = MajorCode From tbl_LUMajor Where Description = '[%MajorDescription%]'
        IF @code is null
            Begin
                -- insert code and description into lookup table
                Insert INTO tbl_LUMajor (MajorCode, Description, CurrAbreb) Values ('[%MajorCode%]', '[%MajorDescription%]', SubString('[%MajorDescription%]',1,15))
                Select '[%MajorCode%]'
            End
        Else
            Select @code
    END
Else --return value
    Select '[%MajorCode%]'

Example:

  • [%MajorCode%] = x
  • [%MajorDescription%] = y

Note: additional text was added to the returned value to demonstrate the scenarios:

-- test if code exists
IF NOT EXISTS (Select MajorCode From tbl_LUMajor Where MajorCode = 'x')
    BEGIN
        -- test if description exists
        Declare @code varchar(4)
        Select @code = MajorCode From tbl_LUMajor Where Description = 'y'
        IF @code is null
            -- insert code and description into lookup table
            Begin
                Insert INTO tbl_LUMajor (MajorCode, Description, CurrAbreb) Values ('x', 'y', SubString('y',1,15))
                Select 'x new'
            End
        Else
            Select @code + ' from desc.'
    END
Else
    Select 'x exists'

The macro returns “x new” because both the code and description did not exist (#3):

The value is created in the drop down (lookup) table.

The next time the macro runs “x exists” with the same code (#1):

If the description exists; but, not the key ) (#2):

The macro would then return “XYZ”

By using macros to test and create lookup values in this manner gives you the flexibility to change the rules as needed without requiring a new plugin or release to address manipulating import data.

Related Posts

%d bloggers like this: