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.
from [pfm].[Person] p
left join [pfm].[PointOfContact] pc
left join [pfm].[Contact] cc
- (538.71 KiB) Downloaded 48 times
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.
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?
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