• TechNotes
• Using SQL Queries within ArchiCAD

TechTip of the Month Contest Winner of January 2004:

KarlOttenstein's tip on using SQL Queries within ArchiCAD!

"(...)
Someone asked me privately if there was a way to extract all of the layer settings for every layer combination in order to document all of the Layer combinations in an external program such as Excel or Access."

This tip is an example of using one of ArchiCAD's lesser-known capabilities, namely SQL queries from inside the Calculate menu group:

1. First, you must enable SQL in the Calculate menu - it is disabled by default. For ArchiCAD 8.1 users, open Options/Preferences/Imaging and Calculation... and for "Calculate menu appearance" choose "Full with SQL" (pop-up menu at the bottom of the dialog) as shown in Screenshot 1, below. For ArchiCAD 9 and Archicad 10 users, it is likely that you will need to open your Work Environment settings and add SQL to the menu of your choice. You'll now have a new menu option similar to that in Screenshot 2.
   

2. Choose Query... and paste in the following (below 'Type your SQL query here:'):
   SELECT FLC.NAME AS COMBINATION, LAYERS.NAME AS LAYER_NAME,
   LAYERS.LOCKED, LAYERS.VISIBLE
   FROM FLAT (LAYERCOMBINATIONS, LAYER_STATUS) AS FLC
   INNER JOIN LAYERS ON (FLC.LAYER_STATUS.LAYER_ID = LAYERS.ID)
   ORDER BY FLC.NAME, LAYERS.NAME.

3. Click the Execute button. The result will appear in your web browser as an alphabetized table. (see screenshot below)
   
   You can save the HTML and/or paste it into Excel, etc. for further manipulation to achieve your documentation goals.

Hint: By choosing Show Data Structure... from the Calculate/SQL menu displays all of the tables and fields that can be viewed. In fact, it opens an XML file in your browser showing all of the fields and tables... all you have to learn is how to use SQL ;)

CategorySetup