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.