Community
Showing results for 
Search instead for 
Do you mean 
Reply

How do I link Opportunity and Product_Service using OLEDB?

Accepted Solution Solved
Copper Contributor
Posts: 20
Country: United States
Accepted Solution

How do I link Opportunity and Product_Service using OLEDB?

I’m trying to use Power Pivot in Excel 2010 to create a report that summarizes Product sales by Opportunity.  Using a SQL query, I would link TBL_OPPORTUNITY with TBL_PRODUCT_SERVICE using the table TBL_OPPORTUNITY_PRODUCT_SERVICE as the latter includes GUIDs that link Products to Opportunities.

 

OLEDB dosent want to include GUIDs in dbo.CONTACT and dbo.OPPORTUNITY and when I try to load dbo.OPPORTUNITY PRODUCT SERVICE I get a message saying “No columns that Microsoft Excel can use were returned with the query.

 

Why is OPPORTUNITY PRODUCT SERVICE in OLE if it can’t be used?

 

Any ideas on how I can link products and Opportunities using  OLEDB?


Accepted Solutions
Solution
Accepted by topic author 0gravity@tampabay.rr.com
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 20
Country: United States

Re: How do I link Opportunity and Product_Service using OLEDB?

Found Vivec's youtube video for ACT! and Power Pivot. This video has a great demonstration of how to setup OLEDB connection to ACT! views.

 

For anyone who is interested in doing amazing things using Power Pivot for Excel 2010, Vivek has a brilliant illustration of how to create the dynamic link to ACT! data using ACT! OLEDB 2 in one of his youtube videos (URL Below)

 

http://www.youtube.com/watch?v=xGMMcu44k3M&feature=BFa&list=ULmYnxPsu5HG4

 

The previous link Vivec posted was for ACT! Opportunities with Power Pivot which uses existing connections but does not illustrate how to create them.

 

Cheers! Vivec You Rock!

JB

View solution in original post


All Replies
Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: How do I link Opportunity and Product_Service using OLEDB?

Hi

Sorry I can't reproduce your problem I'm getting the ID field. Your are sure you do not have the contactid field in dbo.contact?

You'll need to join the contact ->Opportunity via the contact_opportunity and the opportunity to the product_service via the opportunity_product_service.

 

I've just done that to check and it all works fine. Maybe you need to rebuild your oledb objects?

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Employee
Posts: 1,163
Country: USA

Re: How do I link Opportunity and Product_Service using OLEDB?

I, like Vivek am also getting the ID field. I also agree that rebuilding the oledb objects is a good place to start.

 

Start -> Run -> actdiag

Go to the Databases -> Database List

Right click on your database -> Database Rebuild -> Rebuild oledbe objectv2

Matthew Wood
Act! SDK Support
Community Moderator
Copper Contributor
Posts: 20
Country: United States

Re: How do I link Opportunity and Product_Service using OLEDB?

I've tried it on 2 different computers (Win 7 & Win 2008r2 server) with brand new databases and get the same on both: no CONTACTID field.  Have a look at how I'm doing it from screenclipping below.

 

How are you doing it differently that you're able to get CONTACTID field?

 

oledbpic.jpg

Silver Super Contributor
Posts: 2,328
Country: USA

Re: How do I link Opportunity and Product_Service using OLEDB?

I've seen the problem before.  It might be an Excel version issue.  It may be working in Excel 2010 and not in earlier versions.  It seems to work on my Excel 2010 but it didn't work with earlier versions.  The issue was displaying a GUID I think.

 

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: How do I link Opportunity and Product_Service using OLEDB?

I had originally tested this in visual studio and no in excel. I was connecting to the same provider and the same database so I (mistakingly it appears) assumed I'd get the same results.

 

In excel 2010 I have no ContactID, however in VS, every field, bar none is visible. I'm not sure right now why I'm seeing different results given that the connection string is identical.

Matthew Wood
Act! SDK Support
Community Moderator
Copper Contributor
Posts: 20
Country: United States

Re: How do I link Opportunity and Product_Service using OLEDB?

That is precisely the problem (Excel can't display the GUID).  There's even a problem in T-SQL with getting a string (nvchar) to GUID.  You need to use CAST in order to use SELECT CAST and specify format of GUID that SQL uses for uniqueidentifier.

 

I can exclude that (in my case).  The results shown in screen clippings above are in Excel 2007 but I first discover the problem working with Power Pivot in Excel 2010 so I know that with Excel 2010 an most recent updates, the GUID does not display.

 

Will someone who is able to get CONTACTID in Excel please post a screen clipping of the field?  A solution would be better but I've got to see it to believe it.

 

Thanks

Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: How do I link Opportunity and Product_Service using OLEDB?

I had made a ~15min youtube vid where I build a simple Opps dashboard using PowerPivot (PP) and PP was able to auto-detect the relationships: http://www.youtube.com/watch?v=QAGJEuwcCCk

 

Like matthew and Stan have said, if you try and import the OLEDB data directly into Excel, Excel strips out the ID fields (it doesn't seem to like the GUID data type directly for some reason). You can workaround this by selecting SQL in the connection properties and manually creating the select stmt and doing the joins. When you do it in this manner you can explicitly select the contactID or anyother ID field. I've attached a pbg file to show what I mean:

ExcelConnectinProperties.png

 

Are you having a problem with PP or Excel or both?

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Copper Contributor
Posts: 20
Country: United States

Re: How do I link Opportunity and Product_Service using OLEDB?

Nice video Vivec!  No problem with PP (if I could only get the GUIDs into Excel).  Your data sources clearly had GUIDs.

 

Your prevoious response was not clear as to how to open the dbo.CONTACT table in ACT! (couldnt find a file attachment either as I believed you mentioned) 

 

I tried various methods of

 

Get External Data  > From Other Sources > From SQL Server

 

None were successful except that below. (Step 6) I pasted the connection string from an existing ACT OLEDB 2 connection replacing the SQL OLE connection string; then selected SQL (instead of table) in Connection Type and entered a query for the Command Text. 

 

It ran and populated table in Excel but still no GUIDs.  What am I doing wrong?

 

 

OLEDB pic4-1.jpg

Solution
Accepted by topic author 0gravity@tampabay.rr.com
‎09-25-2015 03:20 AM
Copper Contributor
Posts: 20
Country: United States

Re: How do I link Opportunity and Product_Service using OLEDB?

Found Vivec's youtube video for ACT! and Power Pivot. This video has a great demonstration of how to setup OLEDB connection to ACT! views.

 

For anyone who is interested in doing amazing things using Power Pivot for Excel 2010, Vivek has a brilliant illustration of how to create the dynamic link to ACT! data using ACT! OLEDB 2 in one of his youtube videos (URL Below)

 

http://www.youtube.com/watch?v=xGMMcu44k3M&feature=BFa&list=ULmYnxPsu5HG4

 

The previous link Vivec posted was for ACT! Opportunities with Power Pivot which uses existing connections but does not illustrate how to create them.

 

Cheers! Vivec You Rock!

JB