GoldMine Web (GMWEB) - Spinning wheel without any error message when a user defined field has a different datatype in CONTUDEF (DATE) than in CONTACT2 (VARCHAR)

Version 1

    Details

    GoldMine Web (GMWEB) - Spinning wheel without any error message when a user defined field has a different datatype in CONTUDEF (DATE) than in CONTACT2 (VARCHAR)

    [STEPS TO PREPARE]
    1. Log on GMPE with master rights
    2. Browse to Bea Defkow
    3. Browse to Fields Tab
    4. Right Click > End User Screen
    5. Right Click > New Field
    6. New Button Field
    a. Field Name = UTEST
    b. Description = UTest
    c. Field Type = Char 10
    d. OK
    7. Place the field as desired on the screen
    8. Make a rebuild as required
    9. Log into GoldMine again and verify the field is available
    10. Enter the value Test into the field
    10. Perform an IISreset
    11. Log into GoldMine Web
    12. Browse to Bea Defkow
    13. Open the Contact view of Bea Defkow
    14. Select the End User Screen from the Custom Screens
    15. Verify that the field UTest is available and the value Test is displayed



    [STEPS TO REPRODUCE]
    1. Log on GMPE with master rights
    2. Tools > Configure > Custom Fields
    3. Browse to UTEST
    4. Properties Button
    5.  Change the Field Type to Date > OK (Rebuild is not necessary to  demonstrate as it will be even result in an error message - Failed to  copy records from CONTACT2 to GOLDMINETEMPCOPYTABLE)



    6. Perform an IISreset
    7. Log into GoldMine Web
    8. Browse to Bea Defkow
    9. Open the Contact view of Bea Defkow
    10. Select the End User Screen from the Custom Screens

    [RESULT]
    There will be only a spinning wheel and the End User Screen is never loaded




    [ADDITIONAL NOTES/DISCUSSION]
    -  This behavior appears even worse when the field UTest would be  originally be setup in the Prospect screen of Bea Defkow as this is the  Rule based first view for Bea Defkow, if this is done then the whole  section with the
    - This is primarily a  'data'  corruption with an incorrect definition of user defined fields, unfortunately there does neither appear an error message on screen nor do the GMConnect.log or the GMWEB.Log file  give any indication of an error or that there is a discrepancy between field types in  CONTUDEF and CONTACT2 column which makes it very difficult to identify  the reason for this behavior.
    - This behavior appears only  when the field has a value, browsing for example to Donald Dunst and  opening the End User Screen will display correctly, but exposes the  behavior then when trying to edit the content






    Resolution

    [CAUSE]
    - The discrepancy of 2 or more user defined fields from the user defined field definition (in CONTUDEF) and the actual current Column/Field Type in the CONTACT2 table is causing this behavior.
    - GoldMine Web is unable to convert the existing varchar value into a date field
    - While this is considered as a structural corruption within the GoldMine database and therefore not an issue within the application - neither GoldMine nor GoldMine Connect, a RM# 234743 was submitted in order to improve the logging for this behavior as there should be an error message on screen or at least in the related log files.


    [VERIFICATION/RESOLUTION]

    Compare CONTACT2 - user defined fields Data_type definition in Microsoft SQL Server vs. CONTUDEF Field_Type definition

    SELECT S1.COLUMN_NAME, S1.DATA_TYPE,  S2.FIELD_NAME, S2.FIELD_TYPE FROM INFORMATION_SCHEMA.COLUMNS AS S1  JOIN CONTUDEF S2
    ON S1.COLUMN_NAME=S2.FIELD_NAME WHERE S1.COLUMN_NAME LIKE 'U%' ORDER BY
    S1.COLUMN_NAME

    [NOTE]
    - The following values are considered matches for Type:
    C = varchar
    D = datetime
    N = float, int, smallint, etc. (SQL numeric types)


    Data types in the CONTACT2 (column) MUST match the data type definition for the specific user defined field in the CONTUDEF

    - Upon recognizing the discrepancy between the data types it needs to be determined how to resolve the corruption, which might require
    a. Relatively simple by changing the definition in CONTUDEF via
    1. Make sure to have a full running backup of the database
    2. All users are logged out and related applications and services are shut down
    3. Log into GMPE with master rights
    4. Tools > Configure > Custom Fields
    5. Highlight the identified field > Properties
    6. Change the data type to the correct and matching one
    7. As the CONTACT2 column is already available and has the correct datatype it would not be absolutely technically necessary to perform a rebuild but it might be considered
    8. Perform an IISreset
    9. Verify the behavior in GoldMine Web again

    b. it might be (very) complex and may require additional assistance by a GoldMine partner and/or setting up a new user defined field with Global replace - Exchange of values, once the definition (CONTUDEF) appears to be correct but the underlying column in CONTACT2 does have the wrong data type. This is rather considered as a professional service and GoldMine Technical support may not be able to assist to the full extend here.