Fog Creek Software
Discussion Board




Knowledge Base
Documentation
Terry's Tips
Darren's Tips

How to do a 1 time import into a cty file...

It is "almost easy" to import new articles in a cty file if you have Access 2000 ( and are a programmer by profession...)

Here are the queries I did using Access 2000 -- do extra backups first in case you goof on the queries -- for anyone wanting to try the same.

I started by renaming the file to the .mdb extension so access knew to open it.

My [Articles] table has 4 fields
- myName - was my filename
- myKey - my keywords including "(article)"
- title - article title
- content - the article in html format

001 Add Articles to tblArticle

INSERT INTO tblArticle ( ixSet, ixLanguage, sHeadline, sAuthor, cbSize, sKeywords, fDeleted, fTranslated, ixTemplateStructure, sArticle, dtModified )
SELECT -1 AS Expr6, 1 AS Expr1, [Articles].[title], [Articles].[myName], 0 AS Expr2, [Articles].[myKey], 0 AS Expr3, 1 AS Expr4, 1 AS Expr5, [Articles].[content], Now() AS Expr7
FROM Articles;

002 Update tblArticle - fix ixSet

UPDATE tblArticle SET tblArticle.ixSet = [tblArticle].[ixArticle]
WHERE ([tblArticle].[ixSet]=-1);

003 Add Articles to tblStructure

INSERT INTO tblStructure ( ixLevel, ixOrder, ixParent, sName, iType, ixItem, fDeleted, sPublishAs )
SELECT 1 AS Expr1, -1 AS Expr2, 0 AS Expr3, [tblArticle].[sAuthor], 2 AS Expr4, [tblArticle].[ixArticle], 0 AS Expr5, [tblArticle].[sAuthor]
FROM tblArticle
WHERE (([tblArticle].[sKeywords]) Like "*(article)*");

Make sure you had "(article)" as a value in [Articles].[myKey] before doing step 001.

004 Update tblStructure - fix ixOrder

UPDATE tblStructure SET tblStructure.ixOrder = ([tblStructure].[ixStructure]-###)
WHERE ((([tblStructure].[ixOrder])=-1));

Substitute your actual number for ###.You have to look at tblStructure to find out the highest [ixOrder] and calculate the difference between first [ixStructure] for the articles inserted in step 001 and the next available [ixStructure] value. You are adding the new articles to the end of the list.

005 Add articles to tblArticleSet

INSERT INTO tblArticleSet ( ixSet, fDeleted, ixAudience, ixStructure, dtFiled, fViewSource )
SELECT [tblArticle].[ixArticle], 0 AS Expr1, 0 AS Expr2, [tblStructure].[ixStructure], Now() AS Expr3, 1 AS Expr4
FROM tblStructure INNER JOIN tblArticle ON [tblStructure].[ixItem]=[tblArticle].[ixArticle]
WHERE ((([tblStructure].[ixItem])>###));

Substitute your actual number for ###. It will be the first [ixArticle] value in tblArticle for the articles you inserted in step 001.

Rename the extension to .cty, open the file in citydesk, and magically the articles will appear .

Brad
Thursday, March 11, 2004

Step 005 correction --

Substitute your actual number for ###. It will be ONE LESS THAN the first [ixArticle] value in tblArticle for the articles you inserted in step 001.

Brad
Thursday, March 11, 2004

*  Recent Topics

*  Fog Creek Home