SoftPro Developer Forum

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.

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.

That worked perfectly!

Thank you!