Community
Showing results for 
Search instead for 
Do you mean 
Reply

ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

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

ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

[ Edited ]

I am writing a reporting software that needs to know a cleared activity's History type (i.e. To-Do Done, To-Do Not Done, etc.), but also needs to know who the Organizer is (from Activity).  I've looked over the data structure for a little while, and I have not been successful in linking the two tables.  We have SQL that works with ActReader, but we need to be able to do this using ACTOLEDB2.1.  Is it possible, and has anyone been demonstratively successful, in running SQL that joins HISTORY and ACTIVITY using ACTOLEDB2.1?


Accepted Solutions
Solution
Accepted by topic author bconner26
‎09-25-2015 03:20 AM
Nickel Elite Contributor
Posts: 937
Country: USA

Re: ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

Matt - it looks like we don't have a way to join History back to the originating Activity using the OLEDB2 views. 

 

I'll support Matts statement though and highlight a logic problem that's going to manifest with assumptions about organizer field:

 

It's possible that someone other than the organizer of an activity clears an activity.  There are several different use cases there, but if there's one standard assumption to make related to clearing activities and who actually "actioned the activity" it's probably better to go with the History create user field.  

 

Which field you use on your report will depend on how a customer organizes their calendar information.  If a customer rolls activities up to record managers (who might have other users clear activities) and you want to roll up to that level for reportting then that field is sufficient for use, if an organization is more employee hierarchy based - it's probably better to go with create user and group histories created by users together.  

 

Are you building a generic report or is there a specific customer requirement in mind to create the report?

 

 

View solution in original post


All Replies
Employee
Posts: 1,163
Country: USA

Re: ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

[ Edited ]

Yes this can be done. Based on what I'm reading you'll at the very least need to use the following tables:

 

ACTIVITY, CONTACT, CONTACT_HISTORY, CONTACT_ACTIVITY, HISTORY, HISTORY_TYPE

 

I had a moment so I tossed together tossed together the following query in the editor in visual studio using the OLEDB provider. I filtered out all histories that were not of type meeting held and where the details section wasn't blank to reduced the number of records returned. I'm not sure if this is exactly the data you were looking for but if not hopefully it will get you going in the right direction

 

SELECT ACT2012Demo.dbo.ACTIVITY.ORGANIZER, ACT2012Demo.dbo.CONTACT.CONTACT,ACT2012Demo.dbo.HISTORY.DETAILS,  ACT2012Demo.dbo.HISTORY.HISTORY_TYPE,ACT2012Demo.dbo.HISTORY.REGARDING, ACT2012Demo.dbo.ACTIVITY.REGARDING AS ActivityRegarding

 

FROM  ACT2012Demo.dbo.CONTACT, ACT2012Demo.dbo.CONTACT_HISTORY,ACT2012Demo.dbo.CONTACT_ACTIVITY, ACT2012Demo.dbo.ACTIVITY, ACT2012Demo.dbo.HISTORY,ACT2012Demo.dbo.HISTORY_TYPE

 

WHERE        ACT2012Demo.dbo.CONTACT.CONTACTID = ACT2012Demo.dbo.CONTACT_HISTORY.CONTACTID AND                      ACT2012Demo.dbo.CONTACT.CONTACTID = ACT2012Demo.dbo.CONTACT_ACTIVITY.CONTACTID AND                         ACT2012Demo.dbo.CONTACT_ACTIVITY.ACTIVITYID = ACT2012Demo.dbo.ACTIVITY.ACTIVITYID AND                          ACT2012Demo.dbo.CONTACT_HISTORY.HISTORYID = ACT2012Demo.dbo.HISTORY.HISTORYID AND                          ACT2012Demo.dbo.HISTORY.HISTORYTYPEID = ACT2012Demo.dbo.HISTORY_TYPE.HISTORYTYPEID AND                          (ACT2012Demo.dbo.HISTORY_TYPE.HISTORY_TYPE = 'Meeting Held') AND

(ACT2012Demo.dbo.HISTORY.DETAILS<> '')

Matthew Wood
Act! SDK Support
Community Moderator
Nickel Elite Contributor
Posts: 937
Country: USA

Re: ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

I can confirm that OLEDB 2.1 returns history/Contact, Matt's sample is a great starting point.

 

Like ACTReader -the details in history will turn as RTF data - I believe in Sage ACT! 2012 we modified the OLEDB2 interface and we strip out the RTF meta data in these columns.

 

 

Copper Contributor
Posts: 42
Country: United States

Re: ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

I basically had SQL like this worked out, but the problem is that I need to know which Activity record is associated with which History record.  With ActReader, I did this with Accessor_Activity_Cleared and Accessor_Activity.  With ACTOLEDB2.1 and SQL like the SQL you provided, I essentially get a Cartesian Product.  I looked through the views the reporting provider seems to work with, but I didn't catch anything that would get me the unique joins I require.

 

Let me know if you have any other thoughts or ideas.

Nickel Elite Contributor
Posts: 937
Country: USA

Re: ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

ACCESSOR_ACTIVITY_CLEAREDID is available in the history view in ACTOLEDB2.1 - you should be able ot use that to join to the activity table.  It may not show as displayable via output, but it's in the view and can be used to join views.

Copper Contributor
Posts: 42
Country: United States

Re: ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

Yeah, I was looking at the views, and I did see the view ACTIVITY included the ACCESSOR_ACTIVITY_ID.  As you say, I also saw the HISTORY view included the ACCESSOR_ACTIVITY_CLEARED_ID; however, from the perspective of the tables, the only way I found I could properly join tbl_history and tbl_activity is through those ID's in TBL_ACCESSOR_ACTIVITY_CLEARED.  I found no equivalent in the views.  I even checked the ACT_COLUMNS view to see if the views included these columns beyond the ones I already listed, but I could find no other views that included them.  There just doesn't appear to be any means provided to mimic the join between History and Activity I described.

 

At this point, I'm beginning to think I'm hosed on this approach.  If you have any other ideas, let me know.  I appreciate the input.

Nickel Elite Contributor
Posts: 937
Country: USA

Re: ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

I see what you mean - we missed bringing in the right ID to link that information up correctly.  I can tell if a history came from clearing an activity witht hat information, but I can't link back to the originating activity itself.  

 

I'll talk to the engineer here - optimally I'd like to simply see the ActivityID as a field in the History view (you shouldn't need to join this through).

 

On a side note - ACT! is kind of unique here in that it will keep the old activity record once it's cleared (we do it to display the Activity with the line through it in the calendar view).  In most applicaitons the task is either an activity (not completed) OR a history (completed), you get into some odd information storage scenarios when you keep 2 copies of data representing differing states of the same information.  

 

Which leads me to this question - why do you want to link a history with the activity?  A history has all of the same contextual linking to other entities as the parent activity.  

 

 

 

 

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

Alan,

Since you are taking this to the engineers I thought I would add another history OLEDB2.1 issue.

 

Adding DISTINCT on a select statement times out.

Without the DISTINCT it works OK:

SELECT HISTORY_TYPE FROM HISTORY

 

With DISTINCT it throws the time out error below:

SELECT DISTINCT HISTORY_TYPE FROM HISTORY

---------------------------Microsoft Visual Studio---------------------------

SQL Execution Error.
Executed SQL statement: SELECT DISTINCT HISTORY_TYPE FROM HISTORY
Error Source: ACT! OleDB Provider for Reporting 2.0
Error Message: Query timeout expired

---------------------------[OK]   [Help]---------------------------

 

Thanks

-- Jim Durkin

Nickel Elite Contributor
Posts: 937
Country: USA

Re: ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

Thanks for the feedback.

 

You probably already know this but there's an option that'll give you the same results that I know does work well through the provider:

 

Select history_type from history

group by history_type

 

I suspect SQL has a sequencing issue when running some of these statements via functions, so post return features (like group by) are probably more easily handled than pre fetch features.

 

 

 

Bronze Super Contributor
Posts: 1,231
Country: USA

Re: ACTOLEDB2.1 - SQL joining HISTORY and CONTACT

Yes, I already tried Group By and that also fails

 

 

SELECT        HISTORY_TYPE
FROM            Wanxin.dbo.HISTORY
GROUP BY HISTORY_TYPE

 

---------------------------
Microsoft Visual Studio
---------------------------
SQL Execution Error.

Executed SQL statement: SELECT HISTORY_TYPE FROM Wanxin.dbo.HISTORY GROUP BY HISTORY_TYPE
Error Source: ACT! OleDB Provider for Reporting 2.0
Error Message: Query timeout expired
---------------------------
OK   Help  
---------------------------

 

 

-- Jim Durkin