03-02-2012 05:25 AM
How can I pull through the MANAGEUSERID field using an OLEDB connection so I can track which user enters a Call or Visit ect - with ODBC this is a field in the History table.
Hope someone can help.
03-02-2012 06:56 AM
To get that you're going to need to join the History, Contact_History and the User tables. The History table will give you the record manager, who will also be an act contact, their contactID and their UserID should match, the field names between the OLEDB and the ODBC will be a little bit different.
03-02-2012 07:04 AM
What would the join text be for this? I have found example join below but don't really know how to adapt it :
SELECT CONTACT.*, HISTORY.*
FROM HISTORY INNER JOIN
CONTACT_HISTORY ON HISTORY.HISTORYID = CONTACT_HISTORY.HISTORYID INNER JOIN
CONTACT ON CONTACT_HISTORY.CONTACTID = CONTACT.CONTACTID
Sorry, this is all new to me I'm afraid!
03-02-2012 07:07 AM
This is what I used, it was built by the VS query editor so I'm certain it can be improved upon:
SELECT ACT2011Demo.dbo.HISTORY.RECORD_MANAGER, ACT2011Demo.dbo."USER".CONTACTID, ACT2011Demo.dbo."USER".USERID
FROM ACT2011Demo.dbo.HISTORY, ACT2011Demo.dbo.CONTACT_HISTORY, ACT2011Demo.dbo."USER"
WHERE ACT2011Demo.dbo.HISTORY.HISTORYID = ACT2011Demo.dbo.CONTACT_HISTORY.HISTORYID AND
ACT2011Demo.dbo.CONTACT_HISTORY.CONTACTID = ACT2011Demo.dbo."USER".CONTACTID
03-05-2012 06:56 AM
Thanks for that - that seems to only pull through the Record Manager. is this what you were expecting? This is just what is set against the contact or company in ACT not necessarily who put the entry onto the system.
the other fields that we need are (from ODBC) Historytype name (call attempted, call completed etc), endtime (time entry was entered), and CONTACT full name - can these be all pulled through together?
03-06-2012 09:23 AM
The record manager Id and the manageuserid should be the same. The creator would be the record creator.
Too add those columns you should just need to add those to your select statement.