Cameron Custom Closed Order Report

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
cgriffin
Posts: 22
Joined: Mon Nov 17, 2014 6:14 pm

Cameron Custom Closed Order Report

Post by cgriffin »

Good morning,

I am assisting a colleague with writing a Custom Cameron Closed order report in MSSQL. The issue we are encountering is that we are looking for the Marketing Source, and it is only selected in the GUI on the Buyer contact. However, when we query the database we get back a person from the Buyer and Seller, and they both results in the database reflect them as Buyer. We are expecting one record and receiving two back. It appears to be an issue with the pfm.person table, when I say issue, most likely with the query oppose to Cameron. Any and all assistance would be greatly appreciated.

Here is the query:

SELECT t3.Number, t3.RootID, pfm.Contact.ContactType, pfm.Contact.Code@, pfm.Contact.IsMarketingSource, pfm.Contact.LookupCode, pfm.Contact.MarketingRepID, pfm.Person.FullName, pfm.Person.FirstName,
pfm.Person.LastName, zref.ContactType.Code, zref.ContactType.Description, pfm.Contact.MarketingRep2ID, pfm.Contact.MarketingRep3ID
FROM pfm.Contact INNER JOIN
pfm.Person ON pfm.Contact.RootId# = pfm.Person.RootId#
INNER JOIN zref.ContactType ON pfm.Contact.Code = zref.ContactType.Code
INNER JOIN [pf].[order] t3 on t3.RootID = pfm.contact.RootId#
where pfm.contact.RootId# = '-2147477513' and IsMarketingSource = 1

Thank you,
Colin.
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: Cameron Custom Closed Order Report

Post by Lisa Ennis »

Additional join criteria is needed when joining to the pfm.Person table. When joining to tables in an object's heirachy, you will need to use a BETWEEN statement where the Id# is between the Id# and LastId# of the table it inherits from. Hope this helps.

Code: Select all

SELECT 
    t3.Number, 
    t3.RootID, 
    pfm.Contact.ContactType, 
    pfm.Contact.Code@, 
    pfm.Contact.IsMarketingSource, 
    pfm.Contact.LookupCode, 
    pfm.Contact.MarketingRepID, 
    pfm.Person.FullName, 
    pfm.Person.FirstName, 
    pfm.Person.LastName, 
    zref.ContactType.Code, 
    zref.ContactType.Description, 
    pfm.Contact.MarketingRep2ID, 
    pfm.Contact.MarketingRep3ID
FROM 
     pfm.Contact 
     INNER JOIN pfm.Person ON pfm.Contact.RootId# = pfm.Person.RootId# 
                            AND pfm.Person.Id# BETWEEN pfm.Contact.Id# AND pfm.Contact.LastId# 
                            AND pfm.Person.IsMainPerson=1
     INNER JOIN zref.ContactType ON pfm.Contact.Code = zref.ContactType.Code
     INNER JOIN [pf].[order] t3 on t3.RootID = pfm.contact.RootId#
WHERE
     pfm.contact.RootId# = '-2147477513' and IsMarketingSource = 1
Lisa Ennis
Senior Report Developer
SoftPro
cgriffin
Posts: 22
Joined: Mon Nov 17, 2014 6:14 pm

Re: Cameron Custom Closed Order Report

Post by cgriffin »

That worked perfectly! :D

Thank you!
Post Reply