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?
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:
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.
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
Try the attached! The properties you want are:
You'll still need to add a column to the report that uses these variables. As an example formula:
fil_title + "-" + sym_columnmark + "-" + sym_rowmark
Tim, you're the man!
That went through without any "could not be bound" or "unexpected non-boolean" errors, and I see the info I need to write a formula. It seems weird that the JOINs you added weren't automatically generated by SWE like every other variable I've dealt with... but that just gives me more of an excuse to learn more about SQL.
Beautiful man! I had somebody asking after this same idea just yesterday!
First off thank you for the effort into looking into this.
I attempted to add the feature, and it seems (i think even with yours) that it is now counting every symbol placed instead of each unit.
Ex. I have a motor drive that has 10 symbols for it. in the bom report it shows QTY 10 rather than QTY 1.
I am guessing it has to do with this line:
LEFT JOIN tew_symbol ON tew_symbol.sym_com_id = tew_component.com_id
I have attached my original query as well as the updated one.
Could some one with more SQL knowledge take a look at this for me.
Thank you!
I can confirm that I see the same thing - shoot. What's happening is SWE determines the QTY value by counting the rows returned by the query, and by joining to the symbols table (in which there are multiple rows referencing the same component) the query is returning "duplicate" results for the same component, thereby impacting the reported quantity. I'll play with this some more and see if I can find a solution.
Okay, I think I've found a workaround. What I had to do was add another UNION statement to the query whose sole purpose is to report on the symbol cross references. The addition is a carbon copy of the original, however it sets eltcount to 0 so as not to affect the reported QTY. Because the first part of the query is set to report NULL for the value of the cross reference values (fil_title, sym_rowmark, sym_columnmark), you'll need to add a condition to your column formula to ignore NULL values for one or all of the properties. I used the fil_title (drawing mark) because if this is blank the other two values are guaranteed to be blank.
IIF (LEN(fil_title) == 0, "", fil_title + "-" + sym_columnmark + sym_rowmark)
Let me know if this gets you closer Matt!
You guys may be going about this slightly the wrong way. We have a component list we generate instead of a proper BOM. It starts out with the Component tag, and then gives you all of the information related to it after that. It's super helpful that way, instead of trying to search by part number and finding all of the places that part shows up. We still generate a BOM, but just use that for internal purposes, unless the customer requests it.
What do you do when you have a terminal block w/ 50 terminal slices?
Or for the pins in a connector. Say for instance we have 5 x 10 Circuit Receptacles and 5 x 10 Circuit Plugs. How would you display the Pins and Sockets for those Connectors?
It does seem like an interesting approach though.
Terminal blocks are so dumb they aren't worth displaying in the component list, so I just filter them out entirely. I have a separate internal report that nicely displays terminal blocks, which we give to our panel builders, but I do sometimes give that one to the customer as well if they want it. A connector with a bunch of pins does show up in the list. Typically our worst case would be like a 25 pin connector, but it doesn't occur too often. What happens very often is the pins are close to each other on the schematic, and we tell the cross reference not to show duplicates, so it will say go to sheet 22.C1, 22.C2, 22.C3, 22.C4, 22.C5, and 22.C6 to find the all 25 pins. If we cared a ton about people being able to look up the exact pin number of all of our connectors, and just to them in the schematic that way, we'd create a list identical to the terminal block list showing how to get there.
That's really cool. Did you start from a standard report or did you create that from scratch?
This is like 5 years old, so I don't remember exactly, but it looks like we took the standard BOM report, and then tweaked it a ton. It starts out the same way as the one Greg posted earlier, except it adds in the component tables.
FROM (((((((((tew_location
INNER JOIN (tew_buildofmaterial
INNER JOIN (tew_component
ON tew_buildofmaterial.bom_objectid=tew_component.com_id)
ON tew_location.loc_id=tew_component.com_loc_id)
And interestingly, that code was from our BOM report, where the inner joins exclude out anything that doesn't have a part number, or isn't a component (part numbers assigned to locations maybe?)
The code I use for the terminal block list uses full outer joins, so that every terminal block will show up, and if it is missing a part number, it is immediately obvious.
FROM ((((((((((tew_location
LEFT JOIN (tew_buildofmaterial
FULL OUTER JOIN (tew_component
ON tew_buildofmaterial.bom_objectid=tew_component.com_id)
ON tew_location.loc_id=tew_component.com_loc_id)
Awesome Ill look into this.
Thanks a ton.
Everyone!
Im actually in a situation right now where SWE has completely destroyed my terminal drawings. I used the grid style drawings, but they completely broke that in 2018. It doesn't iterate through the terminal block correctly any more.
I attached the same terminal block drawn in 2017 and 2018. I circled where the main differences are. I think I have 5 or 6 SPRs open right now unfortunately.
Figuring out a new way to do the drawings with out using their broken terminal generation drawings would be awesome.
We never trusted any of their terminal block stuff to begin with, so we don't use their terminal block editor at all.
I just wanted to give an update. I liked what Daniel was saying about the "Component BOM" I used a mix of BOM report and the SQL query that you helped create and made a Component BOM report to add to the the project Books.
I think it came out nicely.
It shows the Tag of the Component and then each part that is associated with it as well as the QTY of each part. And shows the page locations of where that component is drawn.
Thanks again for your help guys this turned out awesome!
Yeah, that looks great. Works out really well from the way you have so many parts per component.
I wanted to post this just for completeness. Maybe this will give some others ideas of good reports to create.
I also modified the Component BOM that i created above to make one for TBs as well. This way when the techs are pulling parts for a TB the have an easy way to quickly gather them for each TB.
(Edit pay no attention to the non uniform implementation of Wago )
That's not a bad idea actually. The only reason we're having this discussion is because of the QTY field. Using a separate report that shifts focus from parts to components would simplify things
Try the attached! The properties you want are:
You'll still need to add a column to the report that uses these variables. As an example formula:
fil_title + "-" + sym_columnmark + "-" + sym_rowmark