We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

Person Address in SQL View

Hi There,

I am trying to build a view in SQL 2005 to SuperOffice 6.3 that shows the firstname, lastname and private address (not the company address - the person address) of all of the 'persons' in superoffice.
 
I need to see the person in the view (first name and last name) even if they do not have a private address.

SDK is telling me:

"A person’s private address will have Address type Person 16384 + Subtype private 3 = 16387 as atype_idx in the address table"

I can get the persons with 'Company Address' but not private address.

Any help much appreciated.

Thanks,

Andy

Re: Person Address in SQL View

Hi Andy,

Just a tip while I (or someone else) go off and create this query from scratch: it's generally faster if you submit/include the SQL you have already have so as to have something to build from.

Best regards.

By: Tony Yates 10 Feb 2011

Re: Person Address in SQL View

Hi Andy,

This seems to be working for me:

SELECT PN.person_id, PN.FirstName, PN.LastName, AD.address1, AD.address2, AD.address3
FROM CRM5.PERSON AS PN
LEFT OUTER JOIN CRM5.ADDRESS as AD ON AD.owner_id = PN.person_id
WHERE AD.atype_idx=16387 OR AD.atype_idx IS NULL

Hope this helps.

By: Tony Yates 10 Feb 2011

Re: Person Address in SQL View

Thanks Anthony - works perfectly.

I will post my 'work in progress' in future.

Much Appreciated.

Andy

By: Andy Chaney 10 Feb 2011