Fog Creek Software
Discussion Board




Knowledge Base
Documentation
Terry's Tips
Darren's Tips

Feature Request: List Sunset Articles

From experience (with a Lotus Notes Domino server that's 4gb after 4 years) I know that systems that have articles that can sunset (i.e. Do Not Publish After dates) get cluttered quickly, and several years down the road you have a slow, sluggish website (or CityDesk file) and no easy way to clean it up.

3 buttons for "Show Not Yet Sunrised" "Show Active" and "Show Sunset" would help clean up the db (you can choose the UI method for this though, this is just one option). Maybe the ability to archive all old "Sunset" articles to a file so that you can retrieve them if you want to, but they don't slow the dB down or make the file difficult to transport.

Also see my request for an orphaned file finder (qv).

When do I get my prize for most active (annoying?) member of this group? I'll take my prize in small unmarked dot releases.

Mark W
Sunday, February 10, 2002

Until Joel gets around to it, this one would be trivial to implement as an external program. Do you have Microsoft Access 2000 or 2002 handy?

Mike Gunderloy
Sunday, February 10, 2002

I have Access... probably 97 handy, but I'm inept with it. Maybe I'll open up a copy of a file and start playing around since so many people seem to have suggessted it to me for various requests I've had.

Mark W
Sunday, February 10, 2002

The CityDesk file is in Access 2000 format. Access 97 won't do you any good, alas.

Mike Gunderloy
Sunday, February 10, 2002

=( I guess I'll have to wait for Joel. Which is fine, I was never fond of 'workaround' solutions anyhow. You never know when they're gonna break down.

Mark W
Sunday, February 10, 2002

While you're waiting, try this in Excel (I'm using Excel 2002, but it should work in the last several versions as well):

1) Launch Excel and create a new workbook
2) Data > Import External Data > New Database Query
3) In the Choose Data Source dialog box, make sure the Databases tab is selected, select the "<New Data Source>" row, and click OK
4) In the Create New Data Source dialog box, name the new data source (I chose "CityDesk"). Select the Microsoft Access Driver. Click on Connect.
5) In the ODBC Microsoft Access Setup dialog box, click on Select. Use the Select Database dialog box to browse to your .cty file. Click OK. Click OK again to close the ODBC Microsoft Access Setup dialog box.
6) Now you should be back in the Create New Data Source dialog box. Do not select a default table. Click OK to close this dialog box.
7) This will return you to the Choose Data Source dialog box, with your new data source selected. Click OK to launch the Query Wizard.
8) On the Choose Columns panel of the Query Wizard, expand the tblArticle node in the Available tables and columns list. Select the ixArticle, ixSelect, sHeadline, and fDeleted columns and move them to the Columns in your query list. Similarly, move the ixSet, dtFiled, dtEffective, and dtRetire columns in the tblArticleSet table to the Columns in your query list. Click Next.
9) On the Filter Data panel of the Query Wizard, click Next.
10) On the Sort Order panel of the Query Wizard, click Next.
11) On the Finish panel of the Query Wizard, make sure the option button for Return Data to Microsoft Excel is selected, and click Finish.
12) In the Import Data dialog box, click OK.

The end result will be a spreadsheet with: two columns that you can ignore, then the article headline, a column that shows 0 if the article is still in the CityDesk tree and 1 if it's been deleted from the site, and the filing, sunrise, and sunset dates for the articles.

If you right-click anywhere in the data and choose Refresh Data, it will be updated to reflect the latest changes in CityDesk.

To sort or filter the data, highlight it all and select Data > Sort or Data > Filter.

One interesting discovery from this: CityDesk doesn't actually remove data from the database when you delete an article; it just marks the article as deleted. At some point it might be interesting to write a utility to get rid of deleted articles and then repair and compact the database. That is, if Joel doesn't beat me to it :)

Mike Gunderloy
Sunday, February 10, 2002

File->Compact Site.  Already done in the current version :-)

Michael H. Pryor
Sunday, February 10, 2002

Glad I didn't spend any time on that part of the problem then :)

Mike Gunderloy
Sunday, February 10, 2002

*  Recent Topics

*  Fog Creek Home