A GoldMine Discussion and Support Community

ContactReview Forum Index » GoldBox » History/Email Archive Goto page Previous  1, 2, 3  Next    View previous topic :: View next topic  Posted: Fri Apr 19, 2013 1:24 pm Points: 0 DougCastell GoldMine Guru Joined: 15 Jun 2006 Posts: 1639 Location: Los Angeles, CA you could always use Tools|Data management|Delete records and then use the 'delete old history records' part of the wizard to accomplish that... Or, of course, just issue a DELETE statement in SQL Code: Delete from conthist where ondate<='01/01/2005' Pretty easy stuff. Of course, later displaying this data can be trickier -- part of why I've never been a fan of GoldBox views for this purpose -- email bodies in RFC822 are tricky to actually display. You can't just throw them up in a text box -- or even an IE window. The HTML and formatting is frequently more complex than that. So yeah, for your need here, it might be pretty easy to archive the stuff off to another SQL database, but then you have to figure a workable way to display the stuff. My solution is to leave it right where it is. GoldMine displays it just fine. I question the need to archive stuff out of the GoldMine database. Having it all there isn't hurting anything, is it? _________________ Doug Castell GoldMine Sales and Support: http://www.castellcomputers.com/ office: (310)601-4738 Posted: Mon Apr 22, 2013 9:30 am Points: 0 TPorter2 Awesome Administrator Joined: 30 Jun 2006 Posts: 334 Location: Addison, TX DougCastell wrote: you could always use Tools|Data management|Delete records and then use the 'delete old history records' part of the wizard to accomplish that... Or, of course, just issue a DELETE statement in SQL Code: Delete from conthist where ondate<='01/01/2005' Pretty easy stuff. Of course, later displaying this data can be trickier -- part of why I've never been a fan of GoldBox views for this purpose -- email bodies in RFC822 are tricky to actually display. You can't just throw them up in a text box -- or even an IE window. The HTML and formatting is frequently more complex than that. So yeah, for your need here, it might be pretty easy to archive the stuff off to another SQL database, but then you have to figure a workable way to display the stuff. My solution is to leave it right where it is. GoldMine displays it just fine. I question the need to archive stuff out of the GoldMine database. Having it all there isn't hurting anything, is it? Thanks for your help, Doug. I think having 200,000+ history activities and 150,000 emails prior to 1/1/2008 could be hurting Goldmine. I am going to try creating an archive database and using your SQL commands to insert this stuff there. Posted: Mon Apr 22, 2013 10:32 am Points: 0 DougCastell GoldMine Guru Joined: 15 Jun 2006 Posts: 1639 Location: Los Angeles, CA Quote: I think having 200,000+ history activities and 150,000 emails prior to 1/1/2008 could be hurting Goldmine. I don't agree.. I'd leave it all there. In what manner is GoldMine currently hurting? _________________ Doug Castell GoldMine Sales and Support: http://www.castellcomputers.com/ office: (310)601-4738 Posted: Wed Apr 24, 2013 9:15 am Points: 0 TPorter2 Awesome Administrator Joined: 30 Jun 2006 Posts: 334 Location: Addison, TX DougCastell wrote: Quote: I think having 200,000+ history activities and 150,000 emails prior to 1/1/2008 could be hurting Goldmine. I don't agree.. I'd leave it all there. In what manner is GoldMine currently hurting? I may end up doing this. Performance issues, we have records locked down to only bring in the first 1000 history activities, but some records have many thousands in them. Universal Search is having to go through these old history activities. I got an error message on your insert SQL query. Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.conthist' with unique index 'CNHRECID'. The statement has been terminated. I was highlighting the conthist table of the History Archive database and then running your insert query. Is that the right place? Will this work on a database with history already existing? And thanks, Doug! Posted: Wed Apr 24, 2013 10:07 am Points: 0 DougCastell GoldMine Guru Joined: 15 Jun 2006 Posts: 1639 Location: Los Angeles, CA well, you'd want to add a where clause to make sure the recid isn't already in the target table.. Code: insert into conthist select * from your_real_database.dbo.conthist where ondate<='01/01/2005' where recid not in (select recid from conthist) ...for instance _________________ Doug Castell GoldMine Sales and Support: http://www.castellcomputers.com/ office: (310)601-4738 Posted: Wed Apr 24, 2013 10:35 am Points: 0 TPorter2 Awesome Administrator Joined: 30 Jun 2006 Posts: 334 Location: Addison, TX DougCastell wrote: well, you'd want to add a where clause to make sure the recid isn't already in the target table.. Code: insert into conthist select * from your_real_database.dbo.conthist where ondate<='01/01/2005' where recid not in (select recid from conthist) ...for instance Oh I see! Should there be a database name after where recid not in? Much appreciated! Posted: Wed Apr 24, 2013 10:39 am Points: 0 DougCastell GoldMine Guru Joined: 15 Jun 2006 Posts: 1639 Location: Los Angeles, CA TPorter2 wrote: Oh I see! Should there be a database name after where recid not in? Much appreciated! Nope. My query assumes you're in the TARGET database, so "conthist" is just the archive version of conthist you're working with/refreshing from "your_real_database.conthist" _________________ Doug Castell GoldMine Sales and Support: http://www.castellcomputers.com/ office: (310)601-4738 Posted: Wed Apr 24, 2013 10:48 am Points: 0 TPorter2 Awesome Administrator Joined: 30 Jun 2006 Posts: 334 Location: Addison, TX DougCastell wrote: TPorter2 wrote: Oh I see! Should there be a database name after where recid not in? Much appreciated! Nope. My query assumes you're in the TARGET database, so "conthist" is just the archive version of conthist you're working with/refreshing from "your_real_database.conthist" Sorry, Doug, I am getting an error message near the where. I assume this is the second where they mean. Help appreciated... Posted: Wed Apr 24, 2013 10:49 am Points: 0 DougCastell GoldMine Guru Joined: 15 Jun 2006 Posts: 1639 Location: Los Angeles, CA doh, yeah.. too many where's Code: insert into conthist select * from your_real_database.dbo.conthist where ondate<='01/01/2005'  and recid not in (select recid from conthist) _________________ Doug Castell GoldMine Sales and Support: http://www.castellcomputers.com/ office: (310)601-4738 Posted: Wed Apr 24, 2013 10:55 am Points: 0 TPorter2 Awesome Administrator Joined: 30 Jun 2006 Posts: 334 Location: Addison, TX DougCastell wrote: doh, yeah.. too many where's Code: insert into conthist select * from your_real_database.dbo.conthist where ondate<='01/01/2005'  and recid not in (select recid from conthist) You are the best, Doug! It is working! Posted: Thu Apr 25, 2013 7:23 am Points: 0 BobTaylor GoldBox Guru Joined: 07 Sep 2007 Posts: 1216 Location: Jacksonville, FL Was logged out and didn't know it, never got the notice of your post. Sorry to be so long getting back. 2. OK, so you want to archive History only. However, if you archive and delete History, you will effectively orphan all the E-mail that is linked to that History. Do you really want to do that? I think you are really saying that you want to archive and delete E-mail and History; you just won't have any interest in the E-mail. Is that correct? 3. I should have put that differently. I don't mean to imply that there are issues with it; but doing the export without deletions; and then later doing the deletions; does give me a level of safety that I favor. In the unlikely event that something goes wrong with the export, at least I haven't deleted anything. 4. I've done lots of archives where each year is in its own folder; so that's fine. I think maybe you are saying that you want to archive everything; but then filter out all the E-mail History when you view the archive? That's possible, I think. But I'm just not sure what you mean by your comments that the View won't work for you. Can you explain a bit more fully? _________________ Bob Taylor Bob Taylor Consulting (904)646-9861 www.goldboxbob.com Consulting with GoldBox and GoldMine since 1997. Posted: Thu Apr 25, 2013 2:50 pm Points: 0 TPorter2 Awesome Administrator Joined: 30 Jun 2006 Posts: 334 Location: Addison, TX BobTaylor wrote: Was logged out and didn't know it, never got the notice of your post. Sorry to be so long getting back. 2. OK, so you want to archive History only. However, if you archive and delete History, you will effectively orphan all the E-mail that is linked to that History. Do you really want to do that? I think you are really saying that you want to archive and delete E-mail and History; you just won't have any interest in the E-mail. Is that correct? 3. I should have put that differently. I don't mean to imply that there are issues with it; but doing the export without deletions; and then later doing the deletions; does give me a level of safety that I favor. In the unlikely event that something goes wrong with the export, at least I haven't deleted anything. 4. I've done lots of archives where each year is in its own folder; so that's fine. I think maybe you are saying that you want to archive everything; but then filter out all the E-mail History when you view the archive? That's possible, I think. But I'm just not sure what you mean by your comments that the View won't work for you. Can you explain a bit more fully? 2. I do want to archive History and E-mail. But it is more likely the history activity we will go back to the archive database to find than the e-mail. 3. Totally agree with that concept. 4. I know little about GoldboxViews, but I know what my users will want to see is the history in the same format as it is now, and not in a different view. Thanks for your help, Bob! Posted: Thu Apr 25, 2013 3:02 pm Points: 0 DougCastell GoldMine Guru Joined: 15 Jun 2006 Posts: 1639 Location: Los Angeles, CA note that if you'd also like to archive the mailbox records, it's just a matter of pulling those whose recid appears in (select linkrecid from archivedatabase.dbo.conthist) and inserting them into archivedatabase.dbo.mailbox similarly. _________________ Doug Castell GoldMine Sales and Support: http://www.castellcomputers.com/ office: (310)601-4738 Posted: Sun Apr 28, 2013 4:24 am Points: 0 BobTaylor GoldBox Guru Joined: 07 Sep 2007 Posts: 1216 Location: Jacksonville, FL "4. I know little about GoldboxViews, but I know what my users will want to see is the history in the same format as it is now, and not in a different view. " With History (as well as E-mail), GoldBox strips the HTML tags from the record before archiving, so there is no way to preserve the native GM formatting as things stand. The text is all there, just not the formatting. You might want to check with Bill Lefkoski at Ticomix to see if there is any prospect of changing this. If you'd like, I can check with Dexter to see if DBX might eventually include a solution for your needs; but I don't think it's something that would be offered in the very near future, _________________ Bob Taylor Bob Taylor Consulting (904)646-9861 www.goldboxbob.com Consulting with GoldBox and GoldMine since 1997. Posted: Mon Apr 29, 2013 7:47 am Points: 0 Paul Laufer GoldBox Guru Joined: 27 Oct 2008 Posts: 125 BobTaylor wrote: With History (as well as E-mail), GoldBox strips the HTML tags from the record before archiving, so there is no way to preserve the native GM formatting as things stand. The text is all there, just not the formatting. Are you sure about that Bob? I thought it stored the original email content and used the StripHTML() function when displaying? I haven't used it in a while... just wondering. _________________ Paul Laufer Telesage Solutions www.tsage.com History/Email Archive   ContactReview Forum Index » GoldBox You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum All times are GMT - 7 Hours   Page 2 of 3   Goto page Previous  1, 2, 3  Next All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First  Select a forum GoldMine----------------GoldMine 8 - 2019 (Premium Edition)GoldMine 7 (Corporate Edition)GoldMine 6GoldMine LegacyGoldMine Tips and TricksGoldMine CodersFAQOff-Topic GoldMine Addons----------------GoldBoxMastermineCompliance for GoldMineCrystal ReportsQuoteWerksDetails PlusIntelliClickCompanionlinkEndUserSoftwareGoldBookPapillon TechnologyAnvilInaportOpenOffice link Vertical Markets----------------Real EstateRecruitersFinancial   Brought to you by Castell Computers, Doug Castell, Admin Powered by phpBB © 2001-2004 phpBB Group Theme created by Vjacheslav Trushkin