When reviewing opportunities in GoldMine Mobile (GMME) not all open opportunities for the user are displayed compared to GoldMine Premium Edition.

Version 1

    Details

    [ISSUE]
    When reviewing opportunities in GoldMine Mobile (GMME) not all open opportunities for the user are displayed compared to GoldMine Premium Edition. This usually happens on older opportunities which were created in older versions of GoldMine and / or Microsoft SQL


    [STEPS TO REPRODUCE]
    1. in GoldMine Premium Edition log in as the testing user
    2. Go To >> Opportunities
    3. Make sure to set the testing user as Manager
    4. in the Show drop down to Active or Open
    5. in Mobile (GMME) log in as the testing user
    6. Tap Opportunities
    7. My Active Opportunities
    8. Verify the amount of available active opportunities compared to GMPE

    [ACTUAL RESULT]
    in GoldMine Mobile less opportunities are available as in GoldMine Premium Edition


    Resolution

    [CAUSE]
    An open opportunity is stored in the Database within the RECTYPE field of the OPMGR table. The value is 'O  ' (really 2 blanks after the O). A {SPACE} (blank)  character / trailing space is interpreted by Microsoft SQL Server depending on the ANSI  PADDING ON or OFF setting for the database the Microsoft SQL Server differently. This leads in the underlying query '....WHERE ([opmgr_op].[RECTYPE] LIKE 'O %'  OR [opmgr_op].[RECTYPE] LIKE 'OZ%') AND (([opmgr_op].[RECTYPE] LIKE 'O  %'  to an 'incorrect' or undesired result as a value 'O' and a value 'O  ' is not considered as the same value.


    - Since Microsoft SQL Server 2005 and higher the  default setting is always ANSI PADDING ON, so when you installed GoldMine for the first time on Microsoft SQL 2005 and higher you should not be impacted unless a DBA actively changed this setting
    - Microsoft SQL  Server 2000 and lower had a default setting of ANSI PADDING OFF for the  created databases. This means that usually only GoldMine environments  upgraded from this versions of Microsoft SQL Server are impacted.


    [RESOLUTION]
    Please keep in mind that the following workflow only changes the impacted and for this GoldMine feature necessary fields. It is highly recommended that your Microsoft SQL Server DBA considers to convert/verify the whole database to ANSI PADDING ON if applicable.

    The following workflow is provided only as-is without any warranty of any kind. Please make sure to have a full running backup of the GoldMine database before adjusting the columns and also keep in mind that this change is not sync aware and it is necessary to apply the same steps on any remote sites or undocked user's GoldMine systems if applicable


    1. In the SQL Server Management Studio browse to the OPMGR table and further to the Indexes
    for each index name starting make sure to have either a documentation for them, otherwise
    a. Right click on the index >> Script Indexes >> Create To >> New Query Editor Window
    b. Repeat a. for every index

    2. Delete all indexes via
    a. Right Click >> Delete >> OK
    b. Repeat for all available Indexes in the OPMGR table

    3. In the SQL Server Management Studio > Change the OPMGR>RECTYPE column to char(3) via the following query applied to the GoldMine database

    ALTER TABLE OPMGR ALTER COLUMN RECTYPE char(3)

    4. Within GoldMine > Re-index the OPMGR table via
    4.a. With master rights
    4.b. Tools > Databases > Maintain Databases
    4.c. Individual Files
    4.d. Select OPMGR
    4.e. Only re-index the table (so leave everything else as default)


    5. Delete all indexes via
    a. Right Click >> Delete >> OK
    b. Repeat for all available Indexes in the OPMGR table


    6. in the SQL Server Management Studio > Change the OPMGR>RECTYPE column back to varchar(3) via the following query applied to the GoldMine database

    ALTER TABLE OPMGR ALTER COLUMN RECTYPE varchar(3)

    7. Within GoldMine > Re-index the OPMGR table via
    7.a. With master rights
    7.b. Tools > Databases > Maintain Databases
    7.c. Individual Files
    7.d. Select OPMGR
    7.e. Only re-index the table (so leave everything else as default)

    8. Re-apply any 'user defined' indexes which were not already recreated by GoldMine, as these indicate that your system was using user defined indexes e.g. via executing one query after the other from step 1.a. and 1.b.