Need to generate a custom report on Related Entries

newbeeNew Member

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.

1/31/062:45 PM

❤️ 0

Original Post

Replies sorted oldest to newest

gordNew Member

Related Entries linkages are stored in the Opportunity_Link table, Type=14.

1/31/063:59 PM

❤️ 0

newbeeNew Member

quote:
=14

So I set the Type id in that table to 14 and then choose description, how do I get to the related entries defined.

2/1/069:50 PM

❤️ 0

newbeeNew Member

Why I cannot see the Related Entries Table in Crystal?

2/1/0610:00 PM

❤️ 0

gordNew Member

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.

2/1/0610:42 PM

❤️ 0

newbeeNew Member

quote:
Reply

Hey thanks man, this is valuable information.

Now I have to figure out how to convert the opp_id to a compnay name and client_id to a company name.

have you done this before do you have an easy way.

2/2/067:50 PM

❤️ 0

gordNew Member

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

2/2/069:58 PM

❤️ 0

newbeeNew Member

Thanks man.

2/3/068:39 PM

❤️ 0

Skip to main content

Add Reply

Membership Required

Remove From Your Block List

Manage Follow Preferences

Block

Please wait...