Community
Showing results for 
Search instead for 
Do you mean 
Reply

Histories not mapping

Accepted Solution Solved
New Member
Posts: 3
Country: Australia
Accepted Solution

Histories not mapping

Using Sage ACT! Premium 2011 Version 13.0.401.0, I created a copy of a database and my goal was to get rid of all the duplicate companies - which I did. However, the histories did not export along with everything else and when I queried the database in SQL, all the history tables (I.e. TBL_HISTORY, TBL_HISTORYTYPE, TBL_HISTORYTYPE_GROUP, TBL_HISTORYTYPE_SUPERGROUP) were blank. I tried transferring the histories from the original TBL_HISTORY to the one in the new database using the below INSERT INTO statement (database names have been changed for use in this forum):

 

INSERT INTO [DB_12].[dbo].[TBL_HISTORY] (HISTORYID, HISTORYTYPEID, ISPRIVATE, STARTTIME, ENDTIME, REGARDING, DETAILS, ACCESSOR_ACTIVITY_CLEAREDID, MANAGEUSERID, CREATEUSERID, CREATEDATE, EDITUSERID, EDITDATE, NOTEID)
SELECT HISTORYID, HISTORYTYPEID, ISPRIVATE, STARTTIME, ENDTIME, REGARDING, DETAILS, ACCESSOR_ACTIVITY_CLEAREDID, MANAGEUSERID, CREATEUSERID, CREATEDATE, EDITUSERID, EDITDATE, NOTEID
FROM [DB_11].[dbo].[TBL_HISTORY];

INSERT INTO [DB_12].[dbo].[TBL_HISTORYTYPE] (HISTORYTYPEGUID, HISTORYTYPEID, ACTIVITYTYPEID, NAME, DESCRIPTION, ISACTIVE, ISCUSTOM, ISUSERRECORDABLE, ISDEFAULT, CLEARTYPENUM, CREATEUSERID, CREATEDATE, EDITUSERID, EDITDATE, HISTORYTYPEGROUPID)
SELECT HISTORYTYPEGUID, HISTORYTYPEID, ACTIVITYTYPEID, NAME, DESCRIPTION, ISACTIVE, ISCUSTOM, ISUSERRECORDABLE, ISDEFAULT, CLEARTYPENUM, CREATEUSERID, CREATEDATE, EDITUSERID, EDITDATE, HISTORYTYPEGROUPID
FROM [DB_11].[dbo].[TBL_HISTORYTYPE];

INSERT INTO [DB_12].[dbo].[TBL_HISTORYTYPE_GROUP] (HISTORYTYPEGROUPGUID, HISTORYTYPEGROUPID, NAME, HISTORYTYPESUPERGROUPID)
SELECT HISTORYTYPEGROUPGUID, HISTORYTYPEGROUPID, NAME, HISTORYTYPESUPERGROUPID
FROM [DB_11].[dbo].[TBL_HISTORYTYPE_GROUP];

INSERT INTO [DB_12].[dbo].[TBL_HISTORYTYPE_SUPERGROUP] (HISTORYTYPESUPERGROUPGUID, HISTORYTYPESUPERGROUPID, NAME)
SELECT HISTORYTYPESUPERGROUPGUID, HISTORYTYPESUPERGROUPID, NAME
FROM [DB_11].[dbo].[TBL_HISTORYTYPE_SUPERGROUP];

 

This worked! The only problem is this doesn’t populate TBL_COMPANY_HISTORY, TBL_CONTACT_HISTORY, TBL_GROUP_HISTORY, or TBL_OPPORTUNITY_HISTORY

 

I’m at my wits end! Has anyone else here had the same problem? How do I populate the above tables when all the columns are unique identifiers?

Natalie
Systems Engineer

Accepted Solutions
Solution
Accepted by topic author nataliez
‎09-25-2015 03:20 AM
New Member
Posts: 3
Country: Australia

Re: Histories not mapping

My manager helped me find the solution:

 

insert into DB_12.dbo.TBL_COMPANY_HISTORY
select H1.* from
        DB_11.dbo.TBL_COMPANY_HISTORY as H1
        inner join DB_12.dbo.TBL_HISTORY as H on H.HISTORYID = H1.HISTORYID
        inner join DB_12.dbo.TBL_COMPANY as C on C.COMPANYID = H1.COMPANYID

insert into DB_12.dbo.TBL_CONTACT_HISTORY
select H1.* from
        DB_11.dbo.TBL_CONTACT_HISTORY as H1
        inner join DB_12.dbo.TBL_HISTORY as H on H.HISTORYID = H1.HISTORYID
        inner join DB_12.dbo.TBL_CONTACT as C on C.CONTACTID = H1.CONTACTID

 

I can't believe I didn't think to use inner joins! I just hope anyone else who comes across this problem in future can find the solution here. 

 

Thank you for your time, Matt!

Natalie
Systems Engineer

View solution in original post


All Replies
Employee
Posts: 1,163
Country: USA

Re: Histories not mapping

Hello nataliez,

 

Forgive me if I'm over simplifying the situation here, but is there some reason we aren't able to import directly from one database to another? We could still purge the duplicate companies but an import (assuming the histories parent entitiy still exists) should maintain the relationship between histories and who/what they're associated with.

Matthew Wood
Act! SDK Support
Community Moderator
New Member
Posts: 3
Country: Australia

Re: Histories not mapping

[ Edited ]

Hi Matthew,

 

I exported directly from one database to another but still history records were not exported. I don't know why that was but now I'm left with a database with is virtually duplicate-free but without history records.

 

Am I right in assuming that importing data from Database1 into Database2 (for example) would render the same result as exporting from Database2 into Database1? If no, what are the differences?

Natalie
Systems Engineer
Employee
Posts: 1,163
Country: USA

Re: Histories not mapping

Yes, importing or exporting should have had the exact same results. However, it should have included histories in either case. 

 

The tables that your import fails for are the ones that actually associates a history object with a top level entity, you should have the entity ID's available to you in their respective table, the only problem would be identifying which histories belong to which contact/company etc. 

 

Also, I'm assuming your using the sysadmin for SQL to accomplish this? Otherwise Insert should work at all, the database would be read only.

Matthew Wood
Act! SDK Support
Community Moderator
Solution
Accepted by topic author nataliez
‎09-25-2015 03:20 AM
New Member
Posts: 3
Country: Australia

Re: Histories not mapping

My manager helped me find the solution:

 

insert into DB_12.dbo.TBL_COMPANY_HISTORY
select H1.* from
        DB_11.dbo.TBL_COMPANY_HISTORY as H1
        inner join DB_12.dbo.TBL_HISTORY as H on H.HISTORYID = H1.HISTORYID
        inner join DB_12.dbo.TBL_COMPANY as C on C.COMPANYID = H1.COMPANYID

insert into DB_12.dbo.TBL_CONTACT_HISTORY
select H1.* from
        DB_11.dbo.TBL_CONTACT_HISTORY as H1
        inner join DB_12.dbo.TBL_HISTORY as H on H.HISTORYID = H1.HISTORYID
        inner join DB_12.dbo.TBL_CONTACT as C on C.CONTACTID = H1.CONTACTID

 

I can't believe I didn't think to use inner joins! I just hope anyone else who comes across this problem in future can find the solution here. 

 

Thank you for your time, Matt!

Natalie
Systems Engineer