| | |
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? |
|
|
|
|
| | |
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? |
|
|
|
|
| | |
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 |
|
|
|
|
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" |
|
|
|
|
| | |
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) |
|
|
|
|
|
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. |
|
|
|
|
| | |
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. |
|
|
|
|
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
|
|
|
Brought to you by Castell Computers, Doug Castell, Admin
Powered by phpBB © 2001-2004 phpBB Group Theme created by Vjacheslav Trushkin
|