Point of Contact Table

Discussions related to custom development with Select.
Post Reply
ngillet
Posts: 8
Joined: Tue Apr 05, 2016 4:32 pm

Point of Contact Table

Post by ngillet » Wed Oct 02, 2019 1:07 pm

Hello,

I am looking for some clarification to provide to me developer who is working on our website integration app with SP Select. Below is what the team has sent me and attached is examples of what they are seeing from the Point of Contact tables. Thank you in Advance for any light you can shed on this.

While implementing the logic for adding the selected contact types to an order, we would like to know more on the SoftPro SelectDB behavior for Contact & Person.
We observed PointOfContact table is used to map for Contact and Person, however it is observed that NULL value is getting set here and hence the interconnection between Contact & Person is not getting fetch successfully.

we are using the below query. Here [pfm].[PointOfContact] table is used to map the tables [pfm].[Person] and [pfm].[Contact]. But mapping data are missing in the table [pfm].[PointOfContact]. Please find the attached document containing the details of the same.

select p.RootId#,pc.RootId#,p.email,
p.Id#,pc.Id#,pc.ContactId$,cc.PreferredEmail,cc.ContactType,
p.LastId#,pc.LastId#,p.Email
from [pfm].[Person] p
left join [pfm].[PointOfContact] pc
on p.RootId#=pc.RootId#
and p.Id#=pc.PersonId$
left join [pfm].[Contact] cc
on pc.RootId#=cc.RootId#
and pc.ContactId$=cc.Id#
--and p.Id#=pc.Id#
--and p.LastId#=pc.LastId#
where p.rootid#=-2147483620
Attachments
PointOfContact.docx
(538.71 KiB) Downloaded 46 times

BobRichards
Posts: 956
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Point of Contact Table

Post by BobRichards » Mon Oct 07, 2019 2:05 pm

What are you trying to achieve? Is there a reason you cannot use the Select API to obtain this information?

The question as it stands is beyond the scope of what can be handled in the DevForum. For assistance, please contact the Custom Development Group by calling the SoftPro main number at 800-848-0143 or Custom Development - SoftPro.
Bob Richards, Software Developer, SoftPro

ngillet
Posts: 8
Joined: Tue Apr 05, 2016 4:32 pm

Re: Point of Contact Table

Post by ngillet » Tue Oct 08, 2019 3:15 pm

We are trying to get the Contact/Person information from SoftPro db which had added with in a specified time frame. So in this case we are not expecting a complete list of Contact/Person from the SoftPro rather a filtering criteria on time when this is getting added/updated and that is why we are not able to make use of their API directly.

We need the below information from SoftPro side
- What is the relationship between Contact Table & Person Table ?
- How do access Persons from the information from Contact table ( any foreign key available)
- We could see there is "Point of Contact" table, is there any interconnection with this table, if so what is it?

Lisa Ennis
Posts: 80
Joined: Thu Sep 11, 2008 1:57 pm

Re: Point of Contact Table

Post by Lisa Ennis » Tue Oct 08, 2019 7:48 pm

The PointOfContact table inherits from the pfm.CDF table. It contains the data related to the contacts on Page 5 of the CDF and I don't believe you need to use it here. I have sent you an email directly, if you will email me, I have detailed database documentation that I can provide to you that explains the table inheritance. The file is too large to attach here.

To briefly explain, these are the most used contact related tables and joins.
pfm.Order as O
INNER JOIN pfm.Contact AS C ON O.RootId#=C.RootId# AND C.Id# BETWEEN O.Id# and O.LastId#
INNER JOIN zref.ContactType AS zCT ON C.ContactType=zCT.ID
LEFT JOIN pfm.Principal AS PL ON C.RootId#=PL.RootId# AND C.Id#=PL.Id#
LEFT JOIN pfm.Person AS P ON C.RootId# = P.RootId# AND P.Id# BETWEEN C.Id# AND C.LastId#
LEFT JOIN pfm.PrincipalPerson AS PP ON P.RootId#=PP.RootId# AND P.Id#=PP.Id#
LEFT JOIN core.SecurityIdentity AS MSI ON C.MarketingRepID=MSI.ID
LEFT JOIN pfm.AgentBroker AS A ON C.RootId#=A.RootId# AND C.Id#=A.Id#
LEFT JOIN pfm.Contact AS AA ON C.RootId#=AA.RootId# AND C.RepresentsId$=AA.Id#
LEFT JOIN zref.ContactType AS zCA ON AA.ContactType=zCA.ID
LEFT JOIN zref.[State] AS zS WITH ON C.Address#StateID = zS.ID
Lisa Ennis
Report Developer
SoftPro

Post Reply