Skip to main content

We have customers who are distributors and they sell to contractors that market our products. Currently I had defined a relationship and made these contractors related to our distributers. I can view any contractor that is related to the distributer under the related entries tab.

I am tryign to make a report in Crystal and cannot find the related entries table or any UDf that says Relationship there is only one view I see and that is S-relationship through which I cannot really link anything as there is only one field that says "relationship". This field contains the defined relationships that I set up (i.e Distributer/contractor) but canot link this to any company as missing clinet ID etc.

In short can anyone help with this report.
Original Post

Replies sorted oldest to newest

There is no "Related Entries" table. As I said, the relations are stored in the Opportunity Link table. In ME9 eCRM SQL that table is called 'AMGR_Opportunity_Link'.

The rows corresponding to Related Entries linkages are the ones with a value of 14 in the 'Type' column. The 'Opp_Id' and 'Opp_Number' columns identify the Primary address book Entry, and the 'Client_Id' and 'Contact_Number' columns identify its Related Entry.
In both cases you need a join to AMGR_Client, so you have to include that view (or table) in your report twice. In ME9 SQL, the query that Crystal Reports would use would be something like this:

SELECT "AMGR_Client"."Name", "AMGR_Client"."First_Name", 
   "AMGR_Client_1"."Name", "AMGR_Client_1"."First_Name", 
   "AMGR_Opportunity_Link"."Type"
FROM   
   (
   "AMGR_Client" "AMGR_Client" 
   INNER JOIN "AMGR_Opportunity_Link" "AMGR_Opportunity_Link" 
      ON ("AMGR_Client"."Client_Id"="AMGR_Opportunity_Link"."Opp_Id") 
         AND ("AMGR_Client"."Contact_Number"="AMGR_Opportunity_Link"."Opp_Number")
   ) 
   INNER JOIN "AMGR_Client" "AMGR_Client_1" 
      ON ("AMGR_Opportunity_Link"."Client_Id"="AMGR_Client_1"."Client_Id") 
         AND ("AMGR_Opportunity_Link"."Contact_Number"="AMGR_Client_1"."Contact_Number")
WHERE  "AMGR_Opportunity_Link"."Type"=14

Add Reply

Post
LEGAL INFO
CONTACT US
Copyright 2007-2018 Advoco Solutions Ltd. All Rights Reserved.
×
×
×
×
Link copied to your clipboard.
×