LOOKUP.INI evaluates values for a sequence of fields or sequence in fields (several lookup instructions) unexpectedly - best practise to create such instructions or how are such sequences setup correctly?

Version 1

    Details

    LOOKUP.INI evaluates values for a sequence of fields or sequence in fields (several lookup instructions) unexpectedly - best practice to create such instructions or how are such sequences set up correctly?

    [SCENARIO]

    a LOOKUP.INI shall evaluate certain NAICS Codes (the following example is shortened). NAICS Codes are based on the North American Industry Classification System (NAICS) and is used by businesses and governments to classify and measure economic activity in Canada, Mexico, and the United States. (related also SIC codes - further information see http://siccode.com/en/)

    e.g.
    11: Agriculture, Forestry, Fishing and Hunting
    21: Mining, Quarrying, and Oil and Gas Extraction
    22: Utilities
    23: Construction
    etc.

    Sub categories of 11: Agriculture, Forestry, Fishing and Hunting
    111: Crop Production
    112: Animal Production and Aquaculture
    113: Forestry and Logging
    etc.

    Sub categories of 111: Crop Production
    1111: Oilseed and Grain Farming
    1112: Vegetable and Melon Farming
    1113: Fruit and Tree Nut Farming
    etc.

    Sub categories of 1113: Fruit and Tree Nut Farming
    11131: Orange Groves
    11132: Citrus (except Orange) Groves
    11133: Noncitrus Fruit and Tree Nut Farming

    Sub categories of 11133: Noncitrus Fruit and Tree Nut Farming
    111331: Apple Orchards
    111332: Grape Vineyards
    111333: Strawberry Farming
    111334: Berry (except Strawberry) Farming
    111335: Tree Nut Farming


    For certain NAICS codes (categories and sub categories), different CAMs should be assigned whereby a user may enter only the main category or any sub category into the field UNAICS e.g.
    11=Jessica
    21=Jessica
    22=Jessica
    23=Jessica

    111=John
    112=Heidi
    113=Barbie

    1111=Paul
    1112=Peter
    1113=Ralph

    11131=Ralph
    11132=Ralph
    11133=Thomas

    111331=Sue
    111332=Michael
    111333=Sue
    111335=Sue

    The intention is that in case the final NAICS is not assigned to a CAM here e.g. 111334: Berry (except Strawberry) Farming the CAM should be set the CAM of the parent category here e.g. 11133=Thomas


    [STEPS TO PREPARE]
    - UNAICS and UREP are 2 userdefined character fields which need to be available for steps to reproduce
    - the following LOOKUP.INI was setup and is available in the GoldMine shared folder

    [AutoUpdate]
    UNAICS=UREP

    [UREP]
    Lookup1=left(contact2->UNAICS,6)
    111331=Sue
    111332=Michael
    111333=Sue
    111335=Sue
    Lookup2=left(contact2->UNAICS,2)
    11=Jessica
    21=Jessica
    22=Jessica
    23=Jessica
    Lookup3=left(contact2->UNAICS,4)
    1111=Paul
    1112=Peter
    1113=Ralph
    Lookup4=left(contact2->UNAICS,5)
    11131=Ralph
    11132=Ralph
    11133=Thomas
    Lookup5=left(contact2->UNAICS,3)
    111=John
    112=Heidi
    113=Barbie
    Overwrite=1


    [STEPS TO REPRODUCE]
    1. Browse to Art Bardtoll
    2. enter in UNAICS the value 11
    >> RESULT 1: as expected the UREP field shows Jessica
    3. enter in UNAICS the value 1111
    >> RESULT 2: as expected the UREP field shows Paul
    4. enter in UNAICS the value 11132
    >> RESULT 3: as expected the UREP field shows Ralph
    5. enter in UNAICS the value 111332
    >> RESULT 4: as expected the UREP field shows Michael
    6. enter in UNAICS the value 111334
    >> RESULT 5: intended and expected the UREP field would be Thomas as he is the CAM of the parent category but instead the assigned CAM is Jessica


    Why does the LOOKUP.INI apply the CAM for the main category instead of the parent category of 11133: Noncitrus Fruit and Tree Nut Farming

    What is the best practice to create such instructions or how are such sequences set up correctly?


    Resolution


    [CAUSE]
    When 111334 is entered the LOOKUP.INI doesn't find a full matching value  and therefore the LOOKUP.INI will evaluate again and applies the first partly matching value which in this scenario is lookup2 (Left 2 characters=11 >> UREP=Jessica). As soon as the first 'match' is found the LOOKUP.INI will not evaluate any further as it would not even be possible to say which one is more matching than the other and therefore the LOOKUP.INI stops any further evaluation at this point.



    [RESOLUTION]
    the lookup sequence need to respect the desired order how values should be assigned by the LOOKUP.INI. Usually in such scenarios this means that the lookup sequence needs to go subsequently from the longest possible string to the smallest string.

    Lookup1=left(contact2->UNAICS,6)
    ...
    Lookup2=left(contact2->UNAICS,5)
    ...
    Lookup3=left(contact2->UNAICS,4)
    ...
    Lookup4=left(contact2->UNAICS,3)
    ...
    Lookup5=left(contact2->UNAICS,2)
    ...

    the final LOOKUP.INI for the above scenario looks like


    [AutoUpdate]
    UNAICS=UREP

    [UREP]
    Lookup1=left(contact2->UNAICS,6)
    111331=Sue
    111332=Michael
    111333=Sue
    111335=Sue
    Lookup2=left(contact2->UNAICS,5)
    11131=Ralph
    11132=Ralph
    11133=Thomas
    Lookup3=left(contact2->UNAICS,4)
    1111=Paul
    1112=Peter
    1113=Ralph
    Lookup4=left(contact2->UNAICS,3)
    111=John
    112=Heidi
    113=Barbie
    Lookup5=left(contact2->UNAICS,2)
    11=Jessica
    21=Jessica
    22=Jessica
    23=Jessica
    Overwrite=1



    [STEPS TO REPRODUCE AND VERIFY]
    1. Browse to Art Bardtoll
    2. enter in UNAICS the value 11
    >> RESULT 1: as expected the UREP field shows Jessica
    3. enter in UNAICS the value 1111
    >> RESULT 2: as expected the UREP field shows Paul
    4. enter in UNAICS the value 11132
    >> RESULT 3: as expected the UREP field shows Ralph
    5. enter in UNAICS the value 111332
    >> RESULT 4: as expected the UREP field shows Michael
    6. enter in UNAICS the value 111334
    >> RESULT 5: as expected the UREP field shows now Thomas as CAM for the parent category