Contact Search Center (CSC) returns incorrect data when using AND operator and Contact (primary or additional) as further criteria

Version 1

    Details

    Contact Search Center (CSC) returns incorrect data when using AND operator and Contact (primary or additional) as further criteria


    [STEPS TO REPRODUCE]
    Using the Demo database:
    1. Open Contact Search Center
    2. Select "Company" begins with 'st'
    3. Select + to add additional criteria to search select 'AND' operator and select "Contact(primary or additional)" begins with 'm'

    [EXPECTED RESULT]
    Only St. James Medical Center with a contact of Morley Williams should be returned

    [ACTUAL RESULT]


    Note: the search will return contacts based upon search against Contact(primary or additional) and not against Company AND Contact. Appears the secondary operator overrides the combined search criteria.

     


    Resolution

    [CAUSE]   
    RM 203205

    [WORKAROUND]


    BUILD A TEMPLATE QUERY:
    1.  As Master user, Tools>>SQL Query
    2.  Copy the following query into the top window

    SELECT TOP 500 c1.ACCOUNTNO,c1.STATUS,c1.OWNER,c1.COMPANY,c1.CONTACT,c1.PHONE1,c1.STATE,c1.ZIP,c1.CITY,c1.KEY1,c1.KEY2,c1.KEY3,c1.KEY4,c1.KEY5,c1.LASTNAME,cs.CONTACT as NAMEADDITIONAL,isnull(cs.RECID, c1.RECID) as RECID,c1.U_CONTACT,c1.RECID as C1RECID FROM GOLDMINE.DBO.CONTACT1 c1 left outer join GOLDMINE.DBO.CONTSUPP cs on ( (cs.ACCOUNTNO = c1.ACCOUNTNO) AND (cs.RECTYPE='C') ) WHERE ( c1.U_COMPANY LIKE '<company>' and ((UPPER(cs.CONTACT) LIKE '<contact>') OR (UPPER(c1.CONTACT) LIKE '<contact>'))) ORDER BY c1.U_CONTACT asc,isnull(cs.RECID, c1.RECID)

    3.  Change the database names (highlighted in red above) to the actual name of your GoldMine database
    4.  Click Save
    5.  Give the query a name
    6.  Click OK

    USING THE QUERY:
    1.  Tools>>SQL Query
    2.  Pull-down on your username, and select MASTER
    3.  Pull-down in the down arrow next to the username, and select the query name
    4.  Replace <company> (highlighted in green) with the company search term
    5.  Replace both <contact> (highlighted in red) with the contact search term
    6.  Click Query
    7.  Do Not save the query, the template portion will be overwritten