SAP B1 return default contact in sql script

[former_member391531]

SAP B1 9.2 PL7

Hi, I want to have a fms (formatted search) that returns just the main default contact setup on a BP.

This script gives me 4 contacts but I only want the default contact so I can pre-fill the email address field on some forms.

SELECT T0.[CardCode], T0.[Position], T0.[Name], T0.[E_MailL] FROM OCPR T0

WHERE T0.[CardCode] ='C207102'

Can anyone advise how to just get the default contact on one line from a script?

(I've tried using SINGLE but can't get it working as I need)

Thanks, Eoin.

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try below query to get default contact person name,

SELECT T0.[CardCode], T0.[CardName], T1.[Name] FROM OCRD T0 INNER JOIN OCPR T1 ON T0.[CardCode] = T1.[CardCode] and T1.[Name] = T0.[CntctPrsn] ORDER BY T0.[CardCode]

Regards,

Nagarajan

[former_member391531 · Accepted solution]

Thank you very much, That's great. I see now the default contact is stored in the OCRD table rather than the OCPR.