Community
Showing results for 
Search instead for 
Do you mean 
Reply

OLE / SQL Query

Accepted Solution Solved
Copper Contributor
Posts: 36
Country: United_Kingdom
Accepted Solution

OLE / SQL Query

Can anyone show me which table and field I need to use to see all the uncleared activities in the the database?


Accepted Solutions
Solution
Accepted by topic author brad
‎09-25-2015 03:20 AM
Employee
Posts: 1,163
Country: USA

Re: OLE / SQL Query

If you're using any Act version later than v12, then the activity table in the OLEDB 2.0 reporting provider defintely contains an Is_Cleared column, I just verified this in the demo.

 

If needed, here is the KB covering how to setup your OLEDB connection in excel, in versions 2007/2010 you'll go to the data tab and choose From Other Sources -> From Data Connection Wizard, you'll choose Other/Advanced as your data source and in the subsequent  list choose ACT! OLE DB Provider for Reporting 2.0.

 

The only reason I can imagine it may not be there would be if we're using the 1.0 oledb provider.

Matthew Wood
Act! SDK Support
Community Moderator

View solution in original post


All Replies
Employee
Posts: 1,163
Country: USA

Re: OLE / SQL Query

If you don't need any of the associated contact information associated with the activity, you should only need ACTIVITY, and the column IsCleared. If you wish to also include the contact information you'll need the CONTACT table as well as CONTACT_ACTIVITY to associate the contact id with the activity id.

Matthew Wood
Act! SDK Support
Community Moderator
Copper Contributor
Posts: 36
Country: United_Kingdom

Re: OLE / SQL Query

Thanks so all i need it the TBL_ACTIVITY is it?

Employee
Posts: 1,163
Country: USA

Re: OLE / SQL Query

That's correct, but with one correction. The column name in OLEDB provider is Is_Cleared, rather than IsCleared.

Matthew Wood
Act! SDK Support
Community Moderator
Copper Contributor
Posts: 36
Country: United_Kingdom

Re: OLE / SQL Query

i cannot see Is_Cleared in TBL_ACTIVITY...!?

Can someone explain!!! All i need is a conection in excel that will so me a list of un cleared activities!!?
Solution
Accepted by topic author brad
‎09-25-2015 03:20 AM
Employee
Posts: 1,163
Country: USA

Re: OLE / SQL Query

If you're using any Act version later than v12, then the activity table in the OLEDB 2.0 reporting provider defintely contains an Is_Cleared column, I just verified this in the demo.

 

If needed, here is the KB covering how to setup your OLEDB connection in excel, in versions 2007/2010 you'll go to the data tab and choose From Other Sources -> From Data Connection Wizard, you'll choose Other/Advanced as your data source and in the subsequent  list choose ACT! OLE DB Provider for Reporting 2.0.

 

The only reason I can imagine it may not be there would be if we're using the 1.0 oledb provider.

Matthew Wood
Act! SDK Support
Community Moderator
Copper Contributor
Posts: 36
Country: United_Kingdom

Re: OLE / SQL Query

sorry i didn't mean to accept as solution!!!!  No doubt that it is though!

 

I have been using the ACT Reader utility ,  and then building the queries and reports in excel using MS Query is this not the best way?

 

thanks for your help

Employee
Posts: 1,163
Country: USA

Re: OLE / SQL Query

I personally prefer the OLE provider rather than the ODBC connection whenever it's available and fortunately with excel it is. Regardless,  I just checked with the ODBC connection and the Activity view should contain a column for Is_Cleared, it'll be near the end of the list once the Activity view is selected.

 

Which data access method you choose is really your choice, this white paper should clear up any questions you have between the various data access methods.

Matthew Wood
Act! SDK Support
Community Moderator
New Member
Posts: 8
Country: USA

Re: OLE / SQL Query

I want to know how I can insert new activity and what is the related tables.

 

1- I try to add a new record to [dbo].[TBL_ACTIVITY] 

 

INSERT INTO [dbo].[TBL_ACTIVITY] ([ACTIVITYID],[ACTIVITYTYPEID],[REGARDING],[STARTTIME] ,[ENDTIME] ,[ORIGINALTIME],[ISTIMELESS],[LOCATION],[ISPRIVATE],[ORGANIZEUSERID],[DETAILS],[MASTERACTIVITYID],[ISEVENT],[SOURCENUM] ,[RECURPERIOD],[RECURENDDATE],[ISRECURENDLESS],[ISDELETED],[TZ_BIAS],[TZ_DAYLIGHT_BIAS],[TZ_STANDARD_BIAS],[TZ_DAYLIGHT_MONTH],[TZ_DAYLIGHT_DOW],[TZ_DAYLIGHT_DAY],[TZ_DAYLIGHT_HOUR],[TZ_DAYLIGHT_MINUTE],[TZ_STANDARD_MONTH],[TZ_STANDARD_DOW],[TZ_STANDARD_DAY], [TZ_STANDARD_HOUR], [TZ_STANDARD_MINUTE], [CREATEUSERID], [CREATEDATE] , [ISINVITATIONSENT]) VALUES (data);

 

2 - I add a new record  activityid and contactid to [dbo].[TBL_CONTACT_ACTIVITY]

 

 

I want to know another required table should I add record for new activity to be appeared on ACT Task list

 

Thanks in advanced

 

Mukhtar  

Employee
Posts: 1,163
Country: USA

Re: OLE / SQL Query

OLEDB is read-only. So an insert statement isn't going to work.

Matthew Wood
Act! SDK Support
Community Moderator