How to convert a custom/user defined field into a different custom/user defined field? For example a character field type into a numeric field type

Version 1

    Details

    - You have a user-defined field that should have been set up as numeric so that you could do comparisons such as greater than, but it was accidentally set up as a character field.
    - Is it possible to change the field to a different field type?


    Resolution

    [STEPS]

    Preparation for a new user-defined field for the 'conversion.
    GoldMine Support does not recommend to convert an existing field into a new field type as all data might be removed, therefore a new field and the feature Global Replace are needed.

    I. Create a new user-defined field with the proper data type > numeric, please keep in mind to set the field with a sufficient size
    1. Make sure that all users are logged out and all related services (for example GoldSync) or integrations (for example into Microsoft Outlook) are shut down
    2. Make sure to have a full running backup of the actual database
    3. Log in with master rights
    4. Open the Fields tab from a contact record and if applicable change via right click to the specific custom view where the field shall reside
    5. Right Click > New Field
    6. New Field button
    7.a. Set the proper desired Field Name (starting with U, no special characters, no SQL reserved words and maximum 10 characters)
    7.b. Provide a Description (this will be the used label for the field within GoldMine
    7.c. Set the desired Field Type (Numeric) + Len(gth) + Dec(imals) (please keep in mind that the set Len(gth) refers includes also the decimal separator and Dec(imals)
    7.d. OK
    8. Place the field in the desired place on the view
    9. Review and if applicable adjust the Field Properties
    10. OK
    11. A message appears that the field does not yet exist in the database and a rebuild must take place > confirm and rebuild the database
    12. After a new start of GoldMine the field should now with a prefilled 0 on all contacts be available > this should be verified for several contact records
    II. Global Replace of original wrong Character field into new Numeric field
    0. Depending on the database size and amount of users running GoldMine it is recommended to perform this action after or before business hours
    1. Make sure to have a full running backup of the actual database
    2. Log in with master rights
    3. Tools > Data Management > Global Replace
    4. Exchange the values of two fields
    5.a. Replace the value of the left field with the value of the right field
    5.b. Update this field = the new field created in I.
    5.c. With this field  = the original wrong character field > Next
    6. All Contact Records > Next > Finish
    7. Verify in the process monitor that the global replace action ended without any error
    8. Verify several contact records that the figures in both fields are the same or original blank fields the character field is blank and the numeric field = 0
    III. Clean up the database - removing the wrong character field as it is not needed anymore
    - This action is technically not necessary if you can live with both fields, but it is recommended to clean up when it is not needed anymore. It is necessary when in the end there should be a numeric field with the identical Database field name in the backend e.g. UBUDGET
    1. Make sure that all users are logged out and all related services (for example GoldSync) or integration (for example into Microsoft Outlook) are shut down
    2. Make sure to have a full running backup of the actual database
    3. Log in with master rights
    4. Open the Fields tab from a contact record and if applicable change via right click to the specific custom view where the field shall reside
    5. Right Click > Screen Design
    6. highlight the original wrong character field > Delete Key > a message appears to Delete this field? > Confirm deletion (this deletes the field only from the view)
    7. Exit (the screen designer)
    8. Tools > Configure > Custom Fields
    9. Highlight the incorrect character field > Delete > a message appears USER, Delete 'UFIELD'? > Confirm deletion
    10. Close > a message appears Changes have been made. Would you like to rebuild the database? > Yes
    11. After a restart of GoldMine, the old incorrect field should have vanished and the new correct field is available with the proper values should be available
    IV. Further possibly necessary actions
    1 If the new numeric field needs to have in the backend the exact name e.g. UBUDGET Numeric as the original field UBUDGET char, the steps in III. MUST be performed and then steps in I. and II. need to be repeated, this time the new Field Name should match the ORIGINAL field name