I would like a report form follow above, but function topic doesn't have in Column configuration see picture below.
Can I add function column in BOM report?
So that report below needs to be customized a bit to make it look like the above one. First you need to be merging on Mark, not part number. I think there actually is a report named: "ReferenceByTag_Metric" Bill Of Materials sorted by Mark. Use that as your starting point.
Then comes the hard part. You're going to have to activate expert mode to edit the SQL and figure out how to join the tew_function table, and then alias the tew_function.fun_text field. But this is just the function name =F1. If what you really want is the function description, you'll have to figure out how to join the tew_translatedtext table and get description field out.
Working through all of that is not easy, at all. There's lots of trial and error because some SQL commands work, others don't. And then there's syntax issues that it won't help you with much other than telling you there's an error near "(". Which is not even slightly helpful because there's like 40 of those in the file.
I'd recommend contacting your VAR. The one I use, Hawkridge, is great with writing these reports, and the price is way worth it compared to amount it time it would take to learn to do this small customization. If you do foresee needing to heavily customize a lot of reports, then maybe it's worth learning.
Thanks so much, Daniel Myers
I try it follow your suggestion, I found the errors that you say about syntax.
That is not easy because it's not tell me an error position.
What should I do?
So look at your From statement. it is joining tew_location table onto the tew_buildofmaterial table. And then doing a few more joins after that. No where does tew_function get bound. You'll need to add a ( before the (((((((tew_function. Then add a line break before the WHERE. Then try adding the code LEFT JOIN tew_function ON (tew_function.fun_id = tew_buildofmaterial.bom_objectid))
If this doesn't work, and you still can't get it, really consider having your VAR customize everything for you.
If you want to learn SQL, I recommend going to this site:
It was really helpful for me when I was trying to figure out the same stuff you are working on.
Looking at your first picture, it looks like your going to have to join the following tables
tew_component, tew_buildofmaterial, tew_function, tew_bundle, tew_symbol (to get your page information)
Your also going to have to join the tew_translatedtext table to the component, buildofmaterial, and function tables to get the description.
When you join the same table more than once, you have to join it as an alias, using the AS syntax
I encourage you to go through that tutorial (its broken down by command so you don't have to go through the whole thing, just the parts you need)
Retrieving data ...