Cannot insert duplicate key row in object 'dbo.REPORTTEMP' with unique index 'REPORTTEMPREC' when printing any report with an activated Relationship

Version 1

    Details

    Cannot insert duplicate key row in object 'dbo.REPORTTEMP' with unique index 'REPORTTEMPREC' when printing any report with an activated Relationship

    [STEPS TO REPLICATE]

    - on a US Demo database
    1. Browse to Lauren Hall
    2. Browse to the Tab Relationship
    3. Right Click on the Organization header (Technology Organizations) >> New Section
    - This can be also done under any other section
    4. Provide any Section Heading for example Support Services and leave all other settings as default (recognize that the section is linked by default to the contact record Lauren Hall)

     
    5. OK
    6. Add further other random contacts to the section or leave it as is (for replication of the behavior it is not necessary to add any contacts under the new section Support Services)
    7. Highlight the Organization Technology Organizations >> Button Active


    8. Go To >> Reports >> Reports
    9. Select and Double click the (public) >> GoldMine Reports >> Contact Reports >> Contact History Report with Notes (the behavior will occur on most Contact Reports)
    10. Activate in the Please specify contacts in the following filter or group the All contact in the following filter or group which selects by default the Active Contact Filter
    11. OK
    >> RESULT:

    Database operation error:
    Cannot insert duplicate key row in object 'dbo.REPORTTEMP' with unique index 'REPORTTEMPREC'. The duplicate key value is (00000009I{{{>2R, MASTER  ).


    Resolution

    [REASON]
    - One or several contacts are twice or more times in the current activated filter, therefore the Contact record's RECID value cannot be added to the  REPORTTEMP table as the table is not allowed to have certain duplicates.
    - In the above example the contact record Lauren Hall is linked twice on the relationship tree, one time as real contact, the second time linked to the new created section header.
    - This also occurs on contact record who are added by intention several times to a relationship tree or example as member of Support Services but also as member of Professional Services

    [RESOLUTION]/[INFORMATION]

    - Whenever new Organization trees and / or sections are created, the user needs to make sure that the header is NOT linked to a contact record which is already available in the relationship tree


    - If a Contact is added by intention more than once then the only possibility is to
    a. Either only activate a sub-section where this criteria not applies
    b. Or remove the duplicate entry from the relationship tree  (even if it would be only temporarily)
    c. Or another consideration would be a combined Section

    Additional Note:
    - Potential candidates for being duplicates in certain Orgcharts/Relationships can be for example identified via the following SQL query:

    select count(*),
    'Contact Accountno: ' + ltrim(rtrim(ACCOUNTNO))+ ' Relationship Accountno: '+ ltrim(rtrim(Contact))
    from Contsupp
    where RECTYPE = 'O' and accountno <>''
    group by
    'Contact Accountno: ' + ltrim(rtrim(ACCOUNTNO))+ ' Relationship Accountno: '+ ltrim(rtrim(Contact))
    having count(*)>1