Why does their appear an "(oc:" before a contact name when there is an activity completed and logged into the CONTHIST table?

Version 1

    Details

    Why does their appear an "(oc:" before a contact name when there is an activity completed and logged into the CONTHIST table?






    Resolution

     

    [REASON]

    - The (oc:) information, indicates the related Contact for whom the historical activity was completed against.

    - As a GoldMine primary contact record may have also additional contact records this information needs to be kept for the specific completed activities.

    - Due to the fact that the CONTHIST table does not have a separate CONTACT column like the CAL for the Pending items the (oc: information in the REFERENCE field fills this 'gap'



    - As this information is usually not visible within the GoldMine UI (apart from the Contact information on the History tab, but not in the Reference of the activity itself) a user may need to query the back end data which will display this (oc: information.
    - Example queries on how to remove this information is part of our Knowledge  Article # 15455 - Useful SQL Queries regarding History related  information within GoldMine

     


    .....


     

    (5) List of historical activities with removing the (oc:<Contact Name>) from the history reference

     

    SELECT CONVERT(VARCHAR(X), CONTHIST.ONDATE, YYY) AS ONDATE,

    SUBSTRING(REF,1,CHARINDEX(' (OC:',REF)) AS REF,

    CONTACT1.COMPANY,CONTACT1.CONTACT

    FROM   CONTACT1,CONTHIST

    WHERE CONTACT1.ACCOUNTNO=CONTHIST.ACCOUNTNO

     

    Replace VARCHAR(X) with the desired amount of displayed characters for the date format – corresponding to Date format YYY which must be also replaced.

     

    Examples:

     

    A.   Date Format - May 13 2013

    SELECT CONVERT(VARCHAR(11), CONTHIST.ONDATE, 100) AS ONDATE,

    SUBSTRING(REF,1,CHARINDEX(' (OC:',REF)) AS REF,

    CONTACT1.COMPANY,CONTACT1.CONTACT

    FROM   CONTACT1,CONTHIST

    WHERE CONTACT1.ACCOUNTNO=CONTHIST.ACCOUNTNO

     

    B.   Date Format - 05/13/2013 (UK format)

    SELECT CONVERT(VARCHAR(10), CONTHIST.ONDATE, 103) AS ONDATE,

    SUBSTRING(REF,1,CHARINDEX(' (OC:',REF)) AS REF,

    CONTACT1.COMPANY,CONTACT1.CONTACT

    FROM   CONTACT1,CONTHIST

    WHERE CONTACT1.ACCOUNTNO=CONTHIST.ACCOUNTNO

     

    C.  Date Format - 05/13/2013 (US format)

     

    SELECT CONVERT(VARCHAR(10), CONTHIST.ONDATE, 103) AS ONDATE,

    SUBSTRING(REF,1,CHARINDEX(' (OC:',REF)) AS REF,

    CONTACT1.COMPANY,CONTACT1.CONTACT

    FROM   CONTACT1,CONTHIST

    WHERE CONTACT1.ACCOUNTNO=CONTHIST.ACCOUNTNO