LOOKUP.INI does not work for further met conditions when several levels of evaluation are used in combination with iif conditions

Version 1

    Details

    LOOKUP.INI does not work for further met conditions when several levels of evaluation are used in combination with iif conditions

    - The following LOOKUP.INI was put into place

    [AutoUpdate]
    NewRecord=Key5,Key2
    Key1=Key2

    [Key5]
    lookup1=Contact1->CREATEBY
    MASTER=Milpitas
    PATRICKS=Colorado Springs
    LENAH=Munich
    Overwrite=0

    [Key2]
    lookup1=Contact1->Key5
    Milpitas=&iif(Contact1->Key1="Prospect",("HEL"+str(counter("HEL001",1,1),6,0,"0")),"")
    Colorado Springs=&iif(Contact1->Key1="Partner",("TIM"+str(counter("TIM001",1,1),6,0,"0")),"")
    Munich=&iif(Contact1->Key1="Prospect",("ARM"+str(counter("ARM001",1,1),6,0,"0")),"")
    Munich=&iif(Contact1->Key1="Partner",("AGN"+str(counter("AGN001",1,1),6,0,"0")),"")
    Overwrite=0



    [STEPS TO REPRODUCE]

    1. Login as MASTER
    2. Create a new contact record
    3. enter any company name and/or contact + enter into KEY1=Prospect

    4. OK
    5. Verify the KEY5 and KEY2 field
    >> RESULT: both fields were correctly evaluated by the LOOKUP.INI as CREATBY=MASTER >> KEY5=Milpitas AND KEY5=Milipitas >> KEY2=HEL000001



    6. Login as PATRICKS
    7. Create a new contact record
    8. enter any company name and contact + enter into KEY1=Partner

    9. OK
    10. Verify the KEY5 and KEY2 field
    >> RESULT: both fields were correctly evaluated by the LOOKUP.INI as  CREATBY=PATRICKS >> KEY5=Colorado Springs AND KEY5=Colorado Springs >>  KEY2=TIM000001



    11. Login as LENAH
    12. Create a new contact record
    13. enter any company name and contact + enter into KEY1=Prospect

    14. OK
    15. Verify the KEY5 and KEY2 field
    >> RESULT: both fields were correctly evaluated by the LOOKUP.INI as  CREATBY=LENAH >> KEY5=Munich AND KEY5=Munich >>  KEY2=ARM000001



    16. Still logged in as LENAH
    17. Create a new contact record
    18. enter any company name and contact + enter into KEY1=Partner

    19. OK
    20. Verify the KEY5 and KEY2 field
    >> RESULT:ONLY the KEY5 field was correctly evaluated by the LOOKUP.INI as  CREATBY=LENAH >> KEY5=Munich. the KEY2 field remains blank, whereby a value AGN000001 is expected
     


    Resolution

     

    [DISCLAIMER]
    The LOOKUP.INI is one of the most complex features within GoldMine and
    setting up and troubleshooting the LOOKUP.INI is outside the usual GoldMine Support boundaries. It might be necessary to discuss further with a partner providing professional service and any recommendation and information from the below article is provided only as-is without any warranty of any kind

    [CAUSE]
    - The reason for this behavior is that the original condition KEY5 = Munich was already met and the expression KEY1=Prospect was not met and in the iif-condition there is no false evaluation what should be done if the value is not Prospect. Talking more in a picturesque way, the LOOKUP.INI found the right door (with the sign Munich) and went through it seeing now 2 further doors one with a sign Prospect and one with a sign Nothing more to do - Exit (which would have been the false evaluation). So the LOOKUP.INI literally had nothing more to do and took the EXIT at this point.


    [RESOLUTION]
    - While there is definitely the possibility to make this existing condition more complex by adding a false statement and adding further iif-condition(s) into the false statement, this might get to complex to be easily to be managed. Handling one false condition might be ok but when it is required to evaluate even further false conditions like if KEY1 = Prospect then KEY2=xyz, else (
    if KEY1 = Partner then KEY2=abc, else (if KEY1 = Supplier then KEY2=def, else (if KEY1 = Employee then KEY2=ghi, else  ...))) the LOOKUP.INI setup and maintenance
    a. may require extended skills or experience
    b. may require quickly replacement statements as the length of a statement (1 line) is limited to 255 characters (see also related Knowledge Article # 19066 :  Replacement Statements in a LOOKUP.INI
    c. might be hard to understand for any reviewer
    d. may lead more likely to undesired results

    A different approach is to take out one level of evaluation and put both conditions in the first place when the initial string is evaluated. It may look like a slight larger effort in the first place but for many users or administrators this might be manageable easier and in the end better to understand....
    >> The intention is to combine the KEY5 and KEY1 value in the first place and evaluate the concatenated string instead of the single strings in 2 different levels. The LOOKUP.INI would then look like this
     

    [AutoUpdate]
    NewRecord=Key5,Key2
    Key1=Key2

    [Key5]
    lookup1=Contact1->CREATEBY
    MASTER=Milpitas
    PATRICKS=Colorado Springs
    LENAH=Munich
    Overwrite=0

    [Key2]
    lookup1=&(rtrim(contact1->key5)+trim(contact1->key1))
    MilpitasProspect=&("HEL"+str(counter("HEL001",1,1),6,0,"0"))
    Colorado SpringsPartner=&("TIM"+str(counter("TIM001",1,1),6,0,"0"))
    MunichProspect=&("ARM"+str(counter("ARM001",1,1),6,0,"0"))
    MunichPartner=&("AGN"+str(counter("AGN001",1,1),6,0,"0"))
    Overwrite=0

    Additional Notes:
    - It is strongly recommended to make a backup of the current LOOKUP.INI when any changes are made.
    - It is also recommended to verify the LOOKUP.INI either in a test environment first or at least on test contact records.

    [STEPS TO VERIFY]
    1. Login as MASTER
    2. Create a new contact record
    3. enter any company name and/or contact + enter into KEY1=Prospect

    4. OK
    5. Verify the KEY5 and KEY2 field
    >> RESULT: both fields were correctly evaluated by the LOOKUP.INI as  CREATEBY=MASTER >> KEY5=Milpitas AND KEY5=Milipitas >>  KEY2=HEL000002



    6. Login as PATRICKS
    7. Create a new contact record
    8. enter any company name and contact + enter into KEY1=Partner

    9. OK
    10. Verify the KEY5 and KEY2 field
    >> RESULT: both fields were correctly evaluated by the LOOKUP.INI as  CREATEBY=PATRICKS >> KEY5=Colorado Springs AND KEY5=Colorado Springs >>  KEY2=TIM000002



    11. Login as LENAH
    12. Create a new contact record
    13. enter any company name and contact + enter into KEY1=Prospect

    14. OK
    15. Verify the KEY5 and KEY2 field
    >> RESULT: both fields were correctly evaluated by the LOOKUP.INI as  CREATEBY=LENAH >> KEY5=Munich AND KEY5=Munich >>  KEY2=ARM000002



    16. Still logged in as LENAH
    17. Create a new contact record
    18. enter any company name and contact + enter into KEY1=Partner

    19. OK
    20. Verify the KEY5 and KEY2 field
    >> RESULT: both fields were correctly evaluated by the LOOKUP.INI as  CREATEBY=LENAH >> KEY5=Munich AND KEY5=Munich >>  KEY2=AGN000001