Useful SQL Queries - Getting the appropriate user group names when ownership should be changed via Global Replace feature or setup via the LOOKUP.INI

Version 1

    Details

    Useful SQL Queries - Getting the appropriate user group names when ownership should be changed via Global Replace feature or setup via the LOOKUP.INI

    - When using Territory Realignment feature within GoldMine, the ownership can be set by a drop down selection if applicable and the GoldMine Administrator does not need to take care what specific value the user groups do have. Therefore this is the recommended way to re-assign records to different users or user groups.

    - Using the Global Replace feature as an alternative possibility, for example when
    a. more than 2 additional fields need to be changed during this re-assignment
    b. other fields need to be changed using advanced options (for example by dBase expressions - one possibility is when the STATUS field should keep the existing 'Phone Format' and / or Contact Alert status of records which might be different for each contact record)

    Or

    - Using for example the LOOKUP.INI in order to (re-)assign ownership by default when creating new or changing existing contact records

    >> require to use the correct back end value for the certain user groups

    While the related article # 13472 describes where the user group values are stored, for the Global Replace or the LOOKUP.INI a further criteria needs to be considered as the required values are a combination of the group counter value and part of the user group name

     

     


    Resolution

    - User groups are stored in the LOOKUP table and have a group number (lookupsupp) and a groupname (entry)
    - the Ownership is stored as a combination (to make it unique) of the group number and the groupname.
    - As the OWNER field has a length of 8 characters the user group name will be shortened if applicable

    the following SQL query provides the name which needs to be applied as expression into the dBase expression when performaing a global replace or setting up a LOOKUP.INI


    select left(ltrim(rtrim(lookupsupp))+ltrim(rtrim(lower(entry))),8) from lookup where fieldname ='MSPUSERGRPV'


    - the result appears for example as