navisworks datatools SQL string %prop('category','property')

[Anonymous]

Hi all, 

Under the cateroy "DWF-Pipe", i have two properties, for example, "LineNumber" and "Spec"

In my excel file, there is a field "name" which is the concatenation of "LineNumber" and "Spec"

However, i don't find any solution to write it in my SQL string.

The SQL string would be like:

SELECT * FROM entity_data WHERE "name"=concat(%prop("DWF - Pipe","LineNumber"),%prop("DWF - Pipe","Spec"))

I would like to be able to write something like that to:

SELECT * FROM entity_data WHERE "name" like %prop("DWF - Pipe","LineNumber")

But it seems that we can only match "name" to a property wich the exact same name.

Has any one faced the same issue?

[Holger.Kloecker]

I am afraid what you are trying to do is not possible. 

The "Name" should always match to a property using the equal (=) operator. 

Typically this expression would include a Navisworks tag which fetches model data to participate in the condition);

SELECT weight FROM entity_data WHERE name=%prop('Item','Name');

This assumes a table called entity_data with two fields, weight and Name, where Name is the same as the Name of Items in the Navisworks model

Also see here:

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

[Anonymous]

You have to writte:

SELECT * FROM entity_data WHERE "name"= %join(%prop("DWF - Pipe","LineNumber"),%prop("DWF - Pipe","Spec"))

[austin.dumas]

What about using an AND operator to match 2 fields?  I tried this and it doesn't seem to like it. The reason I need to use 2 fields is because some of the reference file element in the model I am opening have the same Element_ID.  meaning I have to match Element_ID AND reference file name.  Thoughts?  This is what I write and it isn't working...

SELECT * from [Equipment$] where "Element_ID"=%prop("Element ID", "Value") AND "DGN"=%prop("Item", "Source File");

[Remy_MAURCOT]

Hi, guys,
I'm trying to connect to a sql database on a personal server but I can't. Do you have any leads I can study to get the right information in?

[austin.dumas]

Hi Remy,

Without knowing what you have tried so far, here is the query (SQL string) we use in DataTools to link to an SQL database (in DataTools, ODBC Driver = SQL Server). Note that you'll have to create a connection to every table housing data.  It can't be done in a single connection. In the example below, I am showing a single connection to one table in the database...

SELECT * FROM [H355025DataMart].[dbo].[NV_EQUIPMENT] WHERE "Mstn_ID" = %prop("Element ID", "Value") AND "Mstn_Filename" = %prop("Item", "Source File")

where;

H355025DataMart = SQL database

NV_EQUIPMENT = is the applicable table to pull from 

Mstn_ID = the column in the table with the unique identifier to match against Element ID value in Navisworks

Mstn_Filename = the column in the table with the filename to match against Item Source File in Navisworks

In our case we have a second connection to a table housing piping data. Subsitute NV_EQUIPMENT with NV_PIPING and the rest of the SQL query is the same.

Hope that helps.

Cheers,

Austin.