Modifying the NavisWorks SQL String for AutoPLANT Database

[jgarza0422]

Hello has anyone been able to successfully modify the SQL String to pull modified tables, columns or even new relationships that have been added to AutoPLANT into NavisWorks 2011? I am adding new columns to the Piping table "Test123". The new column exists in SQL database. Now here is the fun part...in the Navisworks SQL String (I just added where is pulls all the data from the piping table) "SELECT * FROM PIPING WHERE COMP_ID=%intprop("LcOpDwgComponentAttrib","LcOaSceneBaseUserName")" this is supposed to be pulling all the columns from the Piping table. For some reason it’s not displaying my new column “Test123”. Also we added a new relationship to AutoPLANT IsoSht. I am also trying to modify the SQL String in Navisworks to pull the data from the IsoSht table and guess what...nothing comes across into NavisWorks. Here is the SQL statements for the IsoSht "SELECT IsoSht.NAME AS IsoSht_NAME FROM IsoSht INNER JOIN RELATIONSHIPINSTANCE ON RELATIONSHIPINSTANCE.ID1 = IsoSht.ID WHERE RELATIONSHIPINSTANCE.ID2 = %intprop("LcOpDwgComponentAttrib","LcOaSceneBaseUserName") AND RELATIONSHIPINSTANCE.RELATIONSHIPTYPE = 1002".

If any has been able to successfully pull any custom data in NavisWorks 2011 please advice.

Thanks,
Jgarza0422

[awerning]

What versions of AutoCAD and AutoPLANT are you using? (I see you said Navisworks 2011...but didn't want to assume on the rest)

[jgarza0422]

AutoPlant V8i SS2 and autocad 2011

[grinch2000cs]

This is what i have done to pull intelligent info out of an autoplant model. The last 7 lines were stuff that i added to the piping table in order to get intelligent info on our iso's. This was because we don't link 2D to 3D so the process table was not used. I hope this helps.


<dblinkname>
<name internal="LcOdpDBDatabaseReadLink">%PROJECT_NAME_HERE%</name>
</dblinkname>
<dblinksql sql="SELECT TAG_REG.TAG_NO FROM TAG_REG INNER JOIN RELATIONSHIPINSTANCE ON RELATIONSHIPINSTANCE.ID1 = TAG_REG.KEYTAG WHERE RELATIONSHIPINSTANCE.ID2 = %intprop(&quot;LcOpDwgComponentAttrib&quot;,&quot;LcOaSceneBaseUs erName&quot;) AND RELATIONSHIPINSTANCE.RELATIONSHIPTYPE = 2;
NAVISWORKS_MAGIC_NEXT_SQL
SELECT DOC_REG.DOC_IDCODE, DOC_REG.DOC_NAME, DOC_REG.DOC_DESC FROM DOC_REG INNER JOIN RELATIONSHIPINSTANCE ON RELATIONSHIPINSTANCE.ID1 = DOC_REG.DOC_ID WHERE RELATIONSHIPINSTANCE.ID2 = %intprop(&quot;LcOpDwgComponentAttrib&quot;,&quot;LcOaSceneBaseUs erName&quot;) AND RELATIONSHIPINSTANCE.RELATIONSHIPTYPE = 3;
NAVISWORKS_MAGIC_NEXT_SQL
SELECT AREA3D.NAME AS AREA_NAME FROM AREA3D INNER JOIN RELATIONSHIPINSTANCE ON RELATIONSHIPINSTANCE.ID1 = AREA3D.ID WHERE RELATIONSHIPINSTANCE.ID2 = %intprop(&quot;LcOpDwgComponentAttrib&quot;,&quot;LcOaSceneBaseUs erName&quot;) AND RELATIONSHIPINSTANCE.RELATIONSHIPTYPE = 18;
NAVISWORKS_MAGIC_NEXT_SQL
SELECT SERVICE3D.NAME AS SERVICE_NAME FROM SERVICE3D INNER JOIN RELATIONSHIPINSTANCE ON RELATIONSHIPINSTANCE.ID1 = SERVICE3D.ID WHERE RELATIONSHIPINSTANCE.ID2 = %intprop(&quot;LcOpDwgComponentAttrib&quot;,&quot;LcOaSceneBaseUs erName&quot;) AND RELATIONSHIPINSTANCE.RELATIONSHIPTYPE = 77;
NAVISWORKS_MAGIC_NEXT_SQL
SELECT UNIT3D.NAME AS UNIT_NAME FROM UNIT3D INNER JOIN RELATIONSHIPINSTANCE ON RELATIONSHIPINSTANCE.ID1 = UNIT3D.ID WHERE RELATIONSHIPINSTANCE.ID2 = %intprop(&quot;LcOpDwgComponentAttrib&quot;,&quot;LcOaSceneBaseUs erName&quot;) AND RELATIONSHIPINSTANCE.RELATIONSHIPTYPE = 20;
NAVISWORKS_MAGIC_NEXT_SQL
SELECT * FROM EQUIP3D WHERE COMP_ID=%intprop(&quot;LcOpDwgComponentAttrib&quot;,&quot;LcOaSce neBaseUserName&quot;);
NAVISWORKS_MAGIC_NEXT_SQL
SELECT * FROM PIPING WHERE COMP_ID=%intprop(&quot;LcOpDwgComponentAttrib&quot;,&quot;LcOaSce neBaseUserName&quot;);
NAVISWORKS_MAGIC_NEXT_SQL
SELECT * FROM NOZZLE3D WHERE COMP_ID=%intprop(&quot;LcOpDwgComponentAttrib&quot;,&quot;LcOaSce neBaseUserName&quot;);"/>
<dbfieldmapping field="MODULE" display="Module"/>
<dbfieldmapping field="CLASS" display="Class"/>
<dbfieldmapping field="EQUIP_NO" display="Equipment Number"/>
<dbfieldmapping field="TAG" display="Tag"/>
<dbfieldmapping field="SHORT_DESC" display="Short Description"/>
<dbfieldmapping field="LONG_DESC" display="Long Description"/>
<dbfieldmapping field="ITEM" display="Item"/>
<dbfieldmapping field="DRAWING" display="Drawing"/>
<dbfieldmapping field="SHEET" display="Sheet"/>
<dbfieldmapping field="REV_NUMBER" display="Revision Number"/>
<dbfieldmapping field="INSUL_SPEC" display="Insulation Specification"/>
<dbfieldmapping field="INSUL_THK" display="Insulation Thickness"/>
<dbfieldmapping field="MAT_STAT" display="Material Status"/>
<dbfieldmapping field="DATE_TIME" display="Date &amp; Time"/>
<dbfieldmapping field="DES_PRES" display="Desired Pressure"/>
<dbfieldmapping field="DES_TEMP" display="Desired Temperature"/>
<dbfieldmapping field="VENDOR" display="Vendor"/>
<dbfieldmapping field="MATL_IDENT" display="MATL_IDENT"/>
<dbfieldmapping field="POSX" display="Position X"/>
<dbfieldmapping field="POSY" display="Position Y"/>
<dbfieldmapping field="POSZ" display="Position Z"/>
<dbfieldmapping field="WEIGHT_DRY" display="Dry Weight"/>
<dbfieldmapping field="VERSION" display="Version"/>
<dbfieldmapping field="USER_NAME" display="Username"/>
<dbfieldmapping field="BOM" display="Bill Of Materials"/>
<dbfieldmapping field="NOTE1" display="Note 1"/>
<dbfieldmapping field="NOTE2" display="Note 2"/>
<dbfieldmapping field="NOTE3" display="Note 3"/>
<dbfieldmapping field="NOTE4" display="Note 4"/>
<dbfieldmapping field="QUANTITY" display="Quantity"/>
<dbfieldmapping field="NSPEC" display="Inspection"/>
<dbfieldmapping field="ISO_NAME" display="ISO Name"/>
<dbfieldmapping field="OWNER" display="Owner"/>
<dbfieldmapping field="DWG_PARAM" display="DWG Parameter"/>
<dbfieldmapping field="COMP_LEN" display="Component Length"/>
<dbfieldmapping field="NPS_MAIN" display="NPS_MAIN"/>
<dbfieldmapping field="WT_MAIN" display="WT_MAIN"/>
<dbfieldmapping field="PROJECTION" display="Projection"/>
<dbfieldmapping field="DIRX" display="Direction X"/>
<dbfieldmapping field="DIRY" display="Direction Y"/>
<dbfieldmapping field="DIRZ" display="Direction Z"/>
<dbfieldmapping field="CMP_RATING" display="Component Rating"/>
<dbfieldmapping field="CMP_FACING" display="Component Facing"/>
<dbfieldmapping field="EC" display="EC"/>
<dbfieldmapping field="SORT_CODE" display="Sort Code"/>
<dbfieldmapping field="SHOP_FLD" display="Shop Field"/>
<dbfieldmapping field="PIECE_MARK" display="Piece Mark"/>
<dbfieldmapping field="RUN_SIZE" display="Run Size"/>
<dbfieldmapping field="MAIN_SIZE" display="Main Size"/>
<dbfieldmapping field="BRAN_SIZE" display="BRAN_SIZE"/>
<dbfieldmapping field="FACING1" display="Facing"/>
<dbfieldmapping field="SCHEDULE" display="Schedule"/>
<dbfieldmapping field="PIPE_OD_M" display="Main Outside Diameter"/>
<dbfieldmapping field="RATING" display="Rating"/>
<dbfieldmapping field="MANU_FACT" display="Manufacturer"/>
<dbfieldmapping field="GTYPE" display="Fitting Angle Type"/>
<dbfieldmapping field="STYPE" display="Fitting Type"/>
<dbfieldmapping field="MATERIAL" display="Material"/>
<dbfieldmapping field="MAT_GRADE" display="Material Grade"/>
<dbfieldmapping field="WEIGHT_TOT" display="Total Weight"/>
<dbfieldmapping field="PIPE_LEN" display="Pipe Length"/>
<dbfieldmapping field="CUT_LENGTH" display="Cut Length"/>
<dbfieldmapping field="EXCESS_LEN" display="Excess Length"/>
<dbfieldmapping field="COMP_LAYER" display="Component Layer"/>
<dbfieldmapping field="ELEVATION" display="Elevation"/>
<dbfieldmapping field="INSTHICK" display="Insulation Thickness"/>
<dbfieldmapping field="INSULATION" display="Insulation"/>
<dbfieldmapping field="TAGNUMBER" display="Tag Number"/>
<dbfieldmapping field="MAT_MARK" display="Material Mark"/>
<dbfieldmapping field="SPEC" display="Specification"/>
<dbfieldmapping field="EXISTING" display="Existing"/>
<dbfieldmapping field="CATALOG" display="Catalogue"/>
<dbfieldmapping field="CTR_END_M" display="Center To End Main"/>
<dbfieldmapping field="CTR_END_R" display="Center To End Run"/>
<dbfieldmapping field="CTR_END_B" display="Main branch centre line to EndSpec3 connect point"/>
<dbfieldmapping field="QTY_PERSET" display="Quantity Per Set"/>
<dbfieldmapping field="BOLT_DIA" display="Bolt Diameter"/>
<dbfieldmapping field="BOLT_LEN" display="Bolt Length"/>
<dbfieldmapping field="PAINT_CODE" display="Paint Code"/>
<dbfieldmapping field="BOM_DESCR" display="Bill of Materials Description"/>
<dbfieldmapping field="PROPERTY" display="Property"/>
<dbfieldmapping field="ANNMARK" display="Annotation Mark"/>
<dbfieldmapping field="TRACING" display="Tracing"/>
<dbfieldmapping field="TAG_NO" display="Line Number"/>
<dbfieldmapping field="DOC_NAME" display="Document Name"/>
<dbfieldmapping field="DOC_IDCODE" display="Document ID"/>
<dbfieldmapping field="DOC_DESC" display="Document Description"/>
<dbfieldmapping field="AREA_NAME" display="Area"/>
<dbfieldmapping field="SERVICE_NAME" display="Service"/>
<dbfieldmapping field="UNIT_NAME" display="Unit"/>
<dbfieldmapping field="DESIGN_PRESS" display="Design Pressure"/>
<dbfieldmapping field="DESIGN_TEMP" display="Design Temperature"/>
<dbfieldmapping field="OPER_PRESS" display="Operating Pressure"/>
<dbfieldmapping field="OPER_TEMP" display="Operating Temperature"/>
<dbfieldmapping field="PID_NO" display="PID Number"/>
<dbfieldmapping field="TEST_PRESS" display="Test Pressure"/>
<dbfieldmapping field="PIPE_CODE" display="Pipe Code"/>
</dbreadlink>

[jgarza0422]

What type of connection are you using.My connection does not havve the "<dblinkname>" listed in my SQL String. We use the AutoPLANT Project Connection String.

[grinch2000cs]

Here is how i set it up, see attachment.

[jgarza0422]

Thanks for the help. I think i found out why my fields where not coming in now. After the chages where made to the XML file, i had to reimport it. I thiught just becasue i had it path at start up that it would update. Now i know this is not true. thanks for the help guys.

[grinch2000cs]

Glad to help. If you need more help you know where to come, the best plant design community out there.