Data Tools Connection

[Fernandez1]

Hi everyone,

I am having troubles setting up a test database. Here are the specifics:

I exported out to a Access database my entire Revit Model. I opened the Walls table and added a text field for "Verified". In the table, I added a value of CWF to the Verified field. Saved and closed.

Created a User DSN for CMI University and configured it to go to my CMI University Database file.

Opened Navisworks and appended my NWC from Revit. Configured my File Options -> Data Tools. Created a new Link called CMI. Selected my CMI University odbc connection. Added the following string:

SELECT * FROM Walls WHERE "NWUniqueID" = %prop("Entity Handle", "Value"); .

Added the Verified field to the fields area. Ok'ed out and checked the box for CMI. Closed and reopened.

I select any wall and I get nothing.

I have attached my test files. Any help would really be appreciated!

[Fernandez1]

Oh, and everything is 64-bit. Revit 2012, Navisworks Manage 2012, Office 2010.

[lee.mullin]

As a first step is the 64 bit Office ODBC driver installed?

There's a few other suggestions for initial troubleshooting here, if you still have problems let us know what errors you are getting.

http://beyonddesign.typepad.com/posts/2011/09/datatools-links.html

[Fernandez1]

64-bit across the board product wise. I assume they are 64-bit odbc as they weren't installed until my 64-bit version of office. And the fact that I can export using DB Link out of Revit leads me to believe they are 64-bit.

And my SQL string is SELECT * FROM Windows WHERE "WindowName" = %prop("Item", "Name");

with fields Mark and WallName.

And I'm not getting an error...I'm not getting anything. No additional tab in properties displaying the information.

[lee.mullin]

As a first step we strongly suggest you split the query up to test it piece by piece. So run the query without any WHERE clause and see what fields you get back. Check the overall connection is working first before trying to filter it down – then add in the WHERE part.

Once you can confirm that works the next thing to try would be no space in the %prop tag so there's nothing between the “Item”,”Name”.

The database looks like a joined table of some form rather than a basic table which might make it more complex to query.

There'In Revit Element ID's are Number Fields and in Navisworks they are treated as Text fields, so you will also need to use CStr() to cast a number to a string/text:

SELECT * FROM Walls WHERE CStr(Id)=%prop("Element ID","Value");

 Let us know if this gets you a bit closer.

[Fernandez1]

Hi Lee,

Thanks for trying to help out.

But, I'm getting nothing. Nothing at all to tell me I have connected in any way. No tabs, no values, nothing. I even took a step back and created a simple mdb with 1 table named tbl_Windows.

Connection String:

DBQ=C:\Data\Presentations\Autodesk University\AU2011\FM2181 - Where's the Data Ask and You Shall Receive\Datasets\CMI_University.mdb;DefaultDir=C:\Data\Presentations\Autodesk University\AU2011\FM2181 - Where's the Data Ask and You Shall Receive\Datasets;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

SQL String:

SELECT * FROM tbl_Windows WHERE "RevitID" = %prop("Element ID", "Value");

Nothing. There definitely needs to be a test database and test SQL string button.

Signed...Desperate!

[Fernandez1]

Creating a Navisworks Log File

Sometimes in Support we'll ask you to create and send us a log file. This will contain more detailed information about what is happening so we can identify the problem and potential workaround sooner.

If you do get asked to create a Log file, this is what you need to do.

Start > Run and type

"C:\Program Files\Autodesk\Navisworks Manage 2012\OptionsEditor.exe –a"

Or the equivalent depending on where you have Navisworks installed or if this is Simulate or Freedom.

Go to General> Logging

My Navisworks doesn't have this...It may help.

[Fernandez1]

Got the logging working...so, nm that part.

[Fernandez1]

But it isn't saving changes made to the Options...sigh.

[lee.mullin]

Hi Fernandez,

Have you tried what we suggested?

It looks like there's still a space in the %prop tag you posted.

Have you tried the SQL without the WHERE condition?

We would need you to break down the DataTools query into smaller parts as suggested in the Blog article.

Please let us know

a) What happens when you try "SELECT * FROM tbl_Windows"

b) If a) works, what happens when you remove the space from the %prop tag

"SELECT * FROM tbl_Windows WHERE "RevitID" = %prop("Element ID","Value");"

[lee.mullin · Accepted solution]

This case was taken into a one to one case, just a summary of the resolution for any other users who get this problem.

The SQL String is incorrect as you are checking for the Entity Handle property in Navisworks against the NWUniqueID column in the database. 

- Entity Handles are a unique identifier for AutoCAD files.  For Revit files, you’ll need to check against the Element ID property in Navisworks.

- Looking at the Walls table in your MDB file, there isn’t an NWUniqueID column.  In the table, the column is named “Id”

The type of value in the ID column of Revit Exported MDB files is numeric data.  However, %prop("Element ID","Value") in the DataTools SQL String will return text data.  This will cause an issue as your WHERE clause in the SQL statement will have a type mismatch in "ID"=%prop("Element ID","Value").  You’ll need to use CStr() to convert the numeric data from the MDB to text.

Therefore, the proper SQL Statement should be:

SELECT * FROM Walls WHERE CStr("Id")=%prop("Element ID","Value");

The next potential problem will be if you are selecting from the Scene View rather than the selection tree.  You’ll need to select the correct level in the selection tree that corresponds to the item’s Element ID that is in the MDB table.   To achieve this, you can set the selection resolution to first object.  You’ll then be able to select a wall in the scene view and the right level will be selected in the selection tree. Slide out the Select & Search Panel in the Home Tab to change the selection resolution.

From the screenshots you sent, you are also attempting to pull Window information.  The SQL string would be similar only changing the Table name from Walls to Windows:

SELECT * FROM Walls WHERE CStr("Id")=%prop("Element ID","Value");

Again, you’ll need to ensure First Object is set in the selection resolution.

Another problem exists:  You are trying to pull the Mark and Wall Name from the Window table in your MDB.  However, if you check the Design View of the Window table, you’ll find it does not have a “Wall Name” column.   This wall name column is part of the WindowWalls table.  The screenshot looks like MS Access allows you to expand a row in the table and pull information from that table automatically joined. 

In Navisworks, this will require you to create a join of the two tables in the SQL statement, which will be somewhat complicated.