21 Replies Latest reply on Aug 9, 2018 1:10 PM by Daniel Myers

    How to add symbol coordinates on a sheet to a BOM report?

    Greg Bosma

      Origin-destination arrows automatically generate coordinates for their counterparts on a drawing. For example, an arrow will have an attribute 02 - (5B) to show that it leads to page 2, column 5, row B.

       

      I would like to add these coordinates in the BOM report to locate components' associated symbols (e.g. show that part # 9034-00230 is at 02-(8H) and 03-(10P)). What variable do I need to do that?

        • Re: How to add symbol coordinates on a sheet to a BOM report?
          Tim Pulaski

          This should be possible, however I do not think any of the default reports offer this so a modification of the SQL query they are based on will be required. The data you want is stored in the tew_symbol table, specifically:

          • TABLE: tew_symbol
            • Page ID: sym_fil_id
            • Column Mark: sym_columnmark
            • Row Mark: sym_rowmark

           

          You'll need to join tew_component.com_id to this table on the column tew_symbol.sym_com_id to be able to assign them to a column in your report.

            • Re: How to add symbol coordinates on a sheet to a BOM report?
              Greg Bosma

              I couldn't seem to make the query work... here it is:

               

              SELECT

              tew_component_1.com_tag AS tagParent

              , tew_component.com_tag AS com_tag

              , tew_component.com_tagpath AS com_tagpath

              , tew_component.com_loc_id AS com_loc_id

              , tew_buildofmaterial.bom_reference AS bom_reference

              , tew_buildofmaterial.bom_manufacturer AS bom_manufacturer

              , tew_buildofmaterial.bom_value1 AS bom_value1

              , tew_buildofmaterial.bom_value2 AS bom_value2

              ,tew_buildofmaterial.bom_value3 AS bom_value3

              ,tew_buildofmaterial.bom_value4 AS bom_value4

              ,tew_buildofmaterial.bom_value5 AS bom_value5

              ,tew_buildofmaterial.bom_value6 AS bom_value6

              ,tew_buildofmaterial.bom_partname AS bom_partname

              ,tew_buildofmaterial.bom_orderno AS bom_orderno

                 ,tew_buildofmaterial.bom_implantx AS bom_implantx

                    ,tew_buildofmaterial.bom_implanty AS bom_implanty

                    ,tew_buildofmaterial.bom_implantz AS bom_implantz

                    ,tew_buildofmaterial.bom_length AS bom_length

              ,tew_buildofmaterial.bom_articlename AS bom_articlename

              , tew_location.loc_text AS loc_text

              , tew_translatedtext.tra_0 AS tra_0

              , tew_translatedtext.tra_1 AS tra_1

              , tew_translatedtext.tra_2 AS tra_2

              , tew_translatedtext.tra_3 AS tra_3

              , tew_translatedtext.tra_4 AS tra_4

              , tew_translatedtext.tra_5 AS tra_5

              , tew_translatedtext.tra_6 AS tra_6

              , tew_translatedtext.tra_7 AS tra_7

              , tew_translatedtext.tra_8 AS tra_8

              , tew_translatedtext.tra_9 AS tra_9

              , tew_translatedtext.tra_10 AS tra_10

              , tew_translatedtext.tra_11 AS tra_11

              , tew_translatedtext.tra_12 AS tra_12

              , tew_translatedtext.tra_13 AS tra_13

              , tew_translatedtext.tra_14 AS tra_14

              , tew_userdata.use_data0 AS use_data_0

              , tew_userdata.use_data1 AS use_data_1

              , tew_userdata.use_data2 AS use_data_2

              , tew_userdata.use_data3 AS use_data_3

              , tew_userdata.use_data4 AS use_data_4

              , tew_userdata.use_data5 AS use_data_5

              , tew_userdata.use_data6 AS use_data_6

              , tew_userdata.use_data7 AS use_data_7

              , tew_userdata.use_data8 AS use_data_8

              , tew_userdata.use_data9 AS use_data_9

              , tew_userdata.use_data10 AS use_data_10

              , tew_userdata.use_data11 AS use_data_11

              , tew_userdata.use_data12 AS use_data_12

              , tew_userdata.use_data13 AS use_data_13

              , tew_userdata.use_data14 AS use_data_14

              , tew_userdata.use_data15 AS use_data_15

              , tew_userdata.use_data16 AS use_data_16

              , tew_userdata.use_data17 AS use_data_17

              , tew_userdata.use_data18 AS use_data_18

              , tew_userdata.use_data19 AS use_data_19

              , tew_translatedtext_1.tra_0 AS tra1_0

              , tew_translatedtext_1.tra_1 AS tra1_1

              , tew_translatedtext_1.tra_2 AS tra1_2

              , tew_translatedtext_1.tra_3 AS tra1_3

              , tew_translatedtext_1.tra_4 AS tra1_4

              , tew_translatedtext_1.tra_5 AS tra1_5

              , tew_translatedtext_1.tra_6 AS tra1_6

              , tew_translatedtext_1.tra_7 AS tra1_7

              , tew_translatedtext_1.tra_8 AS tra1_8

              , tew_translatedtext_1.tra_9 AS tra1_9

              , tew_translatedtext_1.tra_10 AS tra1_10

              , tew_translatedtext_1.tra_11 AS tra1_11

              , tew_translatedtext_1.tra_12 AS tra1_12

              , tew_translatedtext_1.tra_13 AS tra1_13

              , tew_translatedtext_1.tra_14 AS tra1_14

              , tew_userdata_1.use_data0 AS use_data1_0

              , tew_userdata_1.use_data1 AS use_data1_1

              , tew_userdata_1.use_data2 AS use_data1_2

              , tew_userdata_1.use_data3 AS use_data1_3

              , tew_userdata_1.use_data4 AS use_data1_4

              , tew_userdata_1.use_data5 AS use_data1_5

              , tew_userdata_1.use_data6 AS use_data1_6

              , tew_userdata_1.use_data7 AS use_data1_7

              , tew_userdata_1.use_data8 AS use_data1_8

              , tew_userdata_1.use_data9 AS use_data1_9

              , tew_userdata_1.use_data10 AS use_data1_10

              , tew_userdata_1.use_data11 AS use_data1_11

              , tew_userdata_1.use_data12 AS use_data1_12

              , tew_userdata_1.use_data13 AS use_data1_13

              , tew_userdata_1.use_data14 AS use_data1_14

              , tew_userdata_1.use_data15 AS use_data1_15

              , tew_userdata_1.use_data16 AS use_data1_16

              , tew_userdata_1.use_data17 AS use_data1_17

              , tew_userdata_1.use_data18 AS use_data1_18

              , tew_userdata_1.use_data19 AS use_data1_19

              , tew_bundle.bun_tag AS bun_tag

              , 1 AS %ELEMENT_COUNT%

              , tew_component.com_type AS type

              , tew_component.com_tagroot AS tagroot

              FROM  (((tew_location

              INNER JOIN (tew_buildofmaterial

              INNER JOIN (tew_component

              LEFT JOIN tew_component AS tew_component_1

              ON tew_component.com_com_id = tew_component_1.com_id)

              ON tew_buildofmaterial.bom_objectid = tew_component.com_id)

              ON tew_location.loc_id = tew_component.com_loc_id)

              LEFT JOIN tew_translatedtext ON (tew_buildofmaterial.bom_id = tew_translatedtext.tra_objectid AND tew_translatedtext.tra_strobjectid =]]bom[[ AND tew_translatedtext.tra_lan_strid = ]]%PROJECT_LNG_CODE%[[))

              LEFT JOIN tew_userdata ON (tew_buildofmaterial.bom_id =  tew_userdata.use_objectid AND tew_userdata.use_objectno = -1)

              LEFT JOIN tew_translatedtext AS tew_translatedtext_1 ON (tew_buildofmaterial.bom_objectid = tew_translatedtext_1.tra_objectid AND tew_translatedtext_1.tra_strobjectid =]]com[[ AND tew_translatedtext_1.tra_lan_strid = ]]%PROJECT_LNG_CODE%[[)

              LEFT JOIN tew_userdata AS tew_userdata_1 ON (tew_buildofmaterial.bom_objectid =  tew_userdata_1.use_objectid AND tew_userdata_1.use_objectno = -1)

              LEFT JOIN tew_bundle  ON (tew_bundle.bun_id =  tew_location.loc_bun_id))

              WHERE ((tew_location.loc_bun_id = %CUR_BUNDLE_ID%)) AND bom_manufacturer != ]]REF ONLY[[

              UNION All SELECT

              ]][[  AS tagParent

              , tew_location.loc_text AS com_tag

                    , tew_location.loc_tagpath AS com_tagpath

              , tew_location.loc_id AS com_loc_id

              , tew_buildofmaterial.bom_reference AS bom_reference

              , tew_buildofmaterial.bom_manufacturer AS bom_manufacturer

              , tew_buildofmaterial.bom_value1 AS bom_value1

              , tew_buildofmaterial.bom_value2 AS bom_value2

              ,tew_buildofmaterial.bom_value3 AS bom_value3

              ,tew_buildofmaterial.bom_value4 AS bom_value4

              ,tew_buildofmaterial.bom_value5 AS bom_value5

              ,tew_buildofmaterial.bom_value6 AS bom_value6

              ,tew_buildofmaterial.bom_partname AS bom_partname

              ,tew_buildofmaterial.bom_orderno AS bom_orderno

              ,tew_buildofmaterial.bom_implantx AS bom_implantx

                    ,tew_buildofmaterial.bom_implanty AS bom_implanty

                    ,tew_buildofmaterial.bom_implantz AS bom_implantz

                    ,tew_buildofmaterial.bom_length AS bom_length

              ,tew_buildofmaterial.bom_articlename AS bom_articlename

              , tew_location.loc_text AS loc_text

              , tew_translatedtext.tra_0 AS tra_0

              , tew_translatedtext.tra_1 AS tra_1

              , tew_translatedtext.tra_2 AS tra_2

              , tew_translatedtext.tra_3 AS tra_3

              , tew_translatedtext.tra_4 AS tra_4

              , tew_translatedtext.tra_5 AS tra_5

              , tew_translatedtext.tra_6 AS tra_6

              , tew_translatedtext.tra_7 AS tra_7

              , tew_translatedtext.tra_8 AS tra_8

              , tew_translatedtext.tra_9 AS tra_9

              , tew_translatedtext.tra_10 AS tra_10

              , tew_translatedtext.tra_11 AS tra_11

              , tew_translatedtext.tra_12 AS tra_12

              , tew_translatedtext.tra_13 AS tra_13

              , tew_translatedtext.tra_14 AS tra_14

              , tew_userdata.use_data0 AS use_data_0

              , tew_userdata.use_data1 AS use_data_1

              , tew_userdata.use_data2 AS use_data_2

              , tew_userdata.use_data3 AS use_data_3

              , tew_userdata.use_data4 AS use_data_4

              , tew_userdata.use_data5 AS use_data_5

              , tew_userdata.use_data6 AS use_data_6

              , tew_userdata.use_data7 AS use_data_7

              , tew_userdata.use_data8 AS use_data_8

              , tew_userdata.use_data9 AS use_data_9

              , tew_userdata.use_data10 AS use_data_10

              , tew_userdata.use_data11 AS use_data_11

              , tew_userdata.use_data12 AS use_data_12

              , tew_userdata.use_data13 AS use_data_13

              , tew_userdata.use_data14 AS use_data_14

              , tew_userdata.use_data15 AS use_data_15

              , tew_userdata.use_data16 AS use_data_16

              , tew_userdata.use_data17 AS use_data_17

              , tew_userdata.use_data18 AS use_data_18

              , tew_userdata.use_data19 AS use_data_19

              , tew_translatedtext_1.tra_0 AS tra1_0

              , tew_translatedtext_1.tra_1 AS tra1_1

              , tew_translatedtext_1.tra_2 AS tra1_2

              , tew_translatedtext_1.tra_3 AS tra1_3

              , tew_translatedtext_1.tra_4 AS tra1_4

              , tew_translatedtext_1.tra_5 AS tra1_5

              , tew_translatedtext_1.tra_6 AS tra1_6

              , tew_translatedtext_1.tra_7 AS tra1_7

              , tew_translatedtext_1.tra_8 AS tra1_8

              , tew_translatedtext_1.tra_9 AS tra1_9

              , tew_translatedtext_1.tra_10 AS tra1_10

              , tew_translatedtext_1.tra_11 AS tra1_11

              , tew_translatedtext_1.tra_12 AS tra1_12

              , tew_translatedtext_1.tra_13 AS tra1_13

              , tew_translatedtext_1.tra_14 AS tra1_14

              , tew_userdata_1.use_data0 AS use_data1_0

              , tew_userdata_1.use_data1 AS use_data1_1

              , tew_userdata_1.use_data2 AS use_data1_2

              , tew_userdata_1.use_data3 AS use_data1_3

              , tew_userdata_1.use_data4 AS use_data1_4

              , tew_userdata_1.use_data5 AS use_data1_5

              , tew_userdata_1.use_data6 AS use_data1_6

              , tew_userdata_1.use_data7 AS use_data1_7

              , tew_userdata_1.use_data8 AS use_data1_8

              , tew_userdata_1.use_data9 AS use_data1_9

              , tew_userdata_1.use_data10 AS use_data1_10

              , tew_userdata_1.use_data11 AS use_data1_11

              , tew_userdata_1.use_data12 AS use_data1_12

              , tew_userdata_1.use_data13 AS use_data1_13

              , tew_userdata_1.use_data14 AS use_data1_14

              , tew_userdata_1.use_data15 AS use_data1_15

              , tew_userdata_1.use_data16 AS use_data1_16

              , tew_userdata_1.use_data17 AS use_data1_17

              , tew_userdata_1.use_data18 AS use_data1_18

              , tew_userdata_1.use_data19 AS use_data1_19

              , tew_bundle.bun_tag AS bun_tag

              ,1 AS %ELEMENT_COUNT%

              ,  -1 AS type

              , tew_location.loc_tagroot AS tagroot

              FROM (((tew_location

              INNER JOIN tew_buildofmaterial

              ON tew_location.loc_id = tew_buildofmaterial.bom_objectid)

              LEFT JOIN tew_translatedtext ON (tew_buildofmaterial.bom_id = tew_translatedtext.tra_objectid AND tew_translatedtext.tra_strobjectid =  ]]bom[[ AND tew_translatedtext.tra_lan_strid =   ]]%PROJECT_LNG_CODE%[[ ))

              LEFT JOIN tew_userdata ON (tew_buildofmaterial.bom_id =  tew_userdata.use_objectid AND tew_userdata.use_objectno = -1)

              LEFT JOIN tew_translatedtext AS tew_translatedtext_1 ON (tew_buildofmaterial.bom_objectid = tew_translatedtext_1.tra_objectid AND tew_translatedtext_1.tra_strobjectid =]]loc[[ AND tew_translatedtext_1.tra_lan_strid = ]]%PROJECT_LNG_CODE%[[)

              LEFT JOIN tew_userdata AS tew_userdata_1 ON (tew_buildofmaterial.bom_objectid =  tew_userdata_1.use_objectid AND tew_userdata_1.use_objectno = -1)

              LEFT JOIN tew_bundle  ON (tew_bundle.bun_id =  tew_location.loc_bun_id))

              WHERE ((tew_location.loc_bun_id =  %CUR_BUNDLE_ID%))

                                                  ORDER BY com_loc_id,bom_reference,tra_0