[Tip] Export layer combination data

[Karl Ottenstein]

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 combos in an external program such as Excel or Access.

I talked about a method to do this briefly over a year ago when 8.0 first came out.

First, you must enable SQL in the Calculate menu - it is disabled by default. Open Preferences and for "Calculate menu appearance" choose "Full with SQL". You'll now have a new menu option as shown in the attached screenshot. Choosing 'Show Data Structure...' displays all of the tables and fields that can be viewed (I'm hoping there are more that are undocumented).

Choose Query and paste in the following:

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

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

The critical part of the code above is thanks to Akos Pfemeter who in 2002 explained the key part of getting the JOIN to work via FLAT.

Note that this displays the visible and locked status of every layer for every layer combination, something not possible via Attribute Manager's Print to File command. However, it does not display layer intersection priority numbers or wireframe display status - neither of these fields is documented, but I'm hoping that they are implemented and we just need someone from GS to tell us what they are.

Karl

[Anonymous]

Fantastic!

Thanks Karl!

BTW, Is there any way to query this data directly from MS Access or another DB program so that changes to the layer list can automatically be updated in reports?

[Karl Ottenstein]

Eric wrote:
Fantastic!

Thanks Karl!

BTW, Is there any way to query this data directly from MS Access or another DB program so that changes to the layer list can automatically be updated in reports?

You're welcome. 😉

The GS ODBC driver, available by registering as a developer last time I looked, should in principal allow the query to be made directly from Access. Thus, the report would always be up-to-date... and formatted the way you want to boot. I have had mixed results with this ODBC driver to date, but it is good to know that GS is working on it!

Karl

[Anonymous]

Now, the question is why doesn't this work on the Mac version. I repeated the same process and when I click "execute" nothing happens.

Any ideas?

[Karl Ottenstein]

Eric wrote:
Now, the question is why doesn't this work on the Mac version. I repeated the same process and when I click "execute" nothing happens.

Any ideas?

Well, nobody has responded in a year, and Frank Beister reminded me of this message...

Since I now have a Mac to test on, I see that there is a bug in the SQL functionality on the Mac. I don't know the full extent, but for me (AC 9 build 1812), doing just a SELECT * FROM LAYERS does nothing on the Mac, but a SELECT * FROM PENS works. I'll report this.

Karl

[Anonymous]

(AC 9 build 1812 usa full)

I am new to ArchiCAD. I'm trying to find out more about using the SQL and ODBC features.

The menu screen that you've shown in the first post of this topic is not part of the prefs in my version. I guess things have changed. Can someone point me at some more recent documentation about using the SQL features.

My head is spinning with trying to learn advanced features on my third day, but I think im in the right direction. If I can get some answers, I hope to post frequently in this section.

[Ben Odonnell]

If you are using the US version of ArchiCAD please see this thread http://archicad-talk.graphisoft.com/viewtopic.php?t=5520 about changing your WE to get all of the options to show in ArchiCAD.

I could show you but the SWE version of ArchiCAD, based on the INT version, the We options are different, although producing the same results.

HTH.
Cheers.
Ben

[Karl Ottenstein]

PotentialTech wrote:
(AC 9 build 1812 usa full)

I am new to ArchiCAD. I'm trying to find out more about using the SQL and ODBC features.

The menu screen that you've shown in the first post of this topic is not part of the prefs in my version. I guess things have changed. Can someone point me at some more recent documentation about using the SQL features.

My head is spinning with trying to learn advanced features on my third day, but I think im in the right direction. If I can get some answers, I hope to post frequently in this section.

As Ben notes, the menu items are not added via the Work Environment... you'll find the two SQL commands available for dragging onto your customized Calculate menu.

By the way, ODBC is pretty stable compared to my note of a year ago, below. Be sure to look at the table design to see what each table really contains, as the contents do not necessarily match the (out of date) documentation.

The ODBC documentation contains the definition of the Graphisoft version of SQL. It is a download from the developer area of the GS web site.

Both ODBC and SQL are one-way streets (read-only). For 2-way interaction with ArchiCAD data, one needs to use the API.

All of these things are pretty deep for a guy 3 days into the product, so I assume that you're a database programmer and not an architect? 😉

Karl

[Anonymous]

Now I see...

The full featured menu is much more agreeable. I'll get the hang of this yet.

Karl, you are correct, and it looks like I now have a nice sized project making some customized quoting software. I'm actually from a civil engineering background, so I'm multichallenged. My client loves his Mac, so I'm really hoping the ODBC will be cross platform some time soon?

I've got tight deadlines and even tighter budget, so He'll have to live with a windows only for version 1.0, but at least we can still view and edit projects on a Mac.

Thank God I don't have to code in C++. Automatic memory management is the way to go! Now, on to the SQL and logic statements!!

[Karl Ottenstein]

Good luck with the project. 😉

I have not heard any rumors of a Mac ODBC implementation. Note also that SQL queries for ODBC look different than those in the Calculate SQL dialog because the table/schema structures as slightly different. So, you cannot copy/paste SQL from one to the other and expect it to work in all cases. (It will work in some cases, of course.)

Finally, SQL and ODBC provide read-only access to project data. If you want two-way access, you need to jump into C++ and the ArchiCAD API.

If you have any other questions about ODBC (or the API), please ask them on the Developer area of this forum. Any questions about SQL as it applies to the Calculate menu belong here though.

Cheers,
Karl