Community
Showing results for 
Search instead for 
Do you mean 
Reply

SQL for Opportunity attached to contact with more than 1 address

Copper Super Contributor
Posts: 122
Country: United Kingdom

SQL for Opportunity attached to contact with more than 1 address

I've noticed with some of the SQL queries we run against Act database that where an opportunity is attached to a contact that has a main address AND a home address, that we often get the opportunity doubling up - one for each of the addresses. Is there something we can use to just extract the MAIN address from the contact address tables so we don't duplicate up our opportunities? I searched the forums but couldn't see anything obvious - we are using Act 2010 Thank you Andy Kent
Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk
Copper Super Contributor
Posts: 122
Country: United Kingdom

Re: SQL for Opportunity attached to contact with more than 1 address

I think I solved this but will sanity check this here I think I use TYPEID in the tbl_address table and ensure this refers to the correct item in the tbl_picklistitem - in this case referencing the main/work type address Thanks Andy
Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk
Copper Super Contributor
Posts: 122
Country: United Kingdom

Re: SQL for Opportunity attached to contact with more than 1 address

It would be useful if anyone has any similar suggestions on how to handle opportunities in Act 2010 that have been attached to MULTIPLE contacts. ie retrive the opportunity ONCE and show a main contact against it? Is this possible? Thanks Andy
Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk
Silver Super Contributor
Posts: 2,328
Country: USA

Re: SQL for Opportunity attached to contact with more than 1 address

The easiest way is to start with the opportunity and then join the other tables.

 

Stan


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!
Employee
Posts: 1,163
Country: USA

Re: SQL for Opportunity attached to contact with more than 1 address

2010 was the first version where Opportunities became a top level entity and that allowed for one-to-many relationships to exist between opportunities and contacts, so there technically isn't a "main" contact other than the record manager or the record creator, but I assume those aren't what you're looking for. 

 

It's certainly possible to create a query that would show each opportunity only a single time, but the problem would be do you want to show all the contacts associated with the opportunity or whichever would come first (for example) in the list of associated contacts. 

 

What specifically is the goal of this query? That might help others or myself give some more specific advice.

Matthew Wood
Act! SDK Support
Community Moderator
Copper Super Contributor
Posts: 122
Country: United Kingdom

Re: SQL for Opportunity attached to contact with more than 1 address

Thanks as always for speedy and helpful responses Stan - the queries work just fine it was just returning multiple rows for opportunities where the contact had more than 1 address in tbl_Address. I solved this by restricting the TYPEID so that's working great now - thank you Matthew - We use this as part of MI collected in Excel, and this particular query draws out opportunities with other data collected from other tables, in this case it includes an address so it's helpful for finance to see contact/address As it's useful in Act Opportunities to attach multiple contacts to an opp so you can track/see visibility of this no matter which contact you were looking at, I don't want to restrict the sales team to only being able to tag an opp with a single contact. I guess I could sort the contacts in the query by something and just use the first in the result of that sort order? OR I could have an ID/Status of primary opp contact and filter on that but that becomes onerous admin wise Is there a way to pull out the FIRST contact that was created/tagged against an opp? Thanks again for all your help Andy Kent
Andy Kent
Angel Solutions (UK and NZ)
www.angelsolutions.co.uk
Silver Super Contributor
Posts: 2,328
Country: USA

Re: SQL for Opportunity attached to contact with more than 1 address

I haven't done it but you could probably just use a DISTINCT modifier on the query on the Opportunity ID.

 

Stan


If you would like to get more out of ACT! you can find an ACT! Certified Consultant near you by going to:www.act.com/acc.
-------------------------------------------------------------------------------------
Stan Smith
ACT! Certified Consultant
ADS Programming Services, Inc.
(205) 222-1661
www.adsprogramming.com
www.actwebhosting.com
Click Here to Purchase Act!
Employee
Posts: 1,163
Country: USA

Re: SQL for Opportunity attached to contact with more than 1 address

The Opportunity table in the OLEDB has a Contact column. Based on some very isolated testing this appears to be the first contact alphabetically of those associated with the opportunity, there's also a column for this contacts ID so should be able to attach this contacts address to the qeury.

Matthew Wood
Act! SDK Support
Community Moderator