BOM Table to Text File

[John Lock]

Can anyone have a LISP program that reads the Current BOM table within Solid Designer – Annotation and outputs a ASCII CSV file?
I would welcome any help.
Thanks

[fabrizio]

Yes John, I wrote such a program for a customer of mine, but
I am not authorized to send it to you... anyway, I'll try to explain
you the steps I followed.
-First of all, you have to retrieve the logical table that contains
the data you want to write to the file:
(setq ltab (sd-get-display-table-logical-table "AM-BOM-DATA-DTAB")).
"AM-BOM-DATA-DTAB" is the AM BOM display table name, and, after this call, you'll have the BOM data into the logical table ltab.
-Now you have to retrieve the fields of the logical table that are
shown into the display table: in fact the logical table usually
contains much more information that the one you see in the
display table.
Probably the global variable DOCU::*DOCU-REGISTERED-BOM-ATTRS* is a good starting point: it contains a list of lists, each
sublist represents a single registered BOM attribute in the format
(attr-type attr-title attr-visible)
-Now you have to read the contents of the logical table ltab,
according to the information you retrieved at the previous step.
To do this you can use the documented function
(sd-read-logical-table-cell)
-Finally, write the data to the file.

Hope this helps.
Fabrizio

[ChrisE]

Originally posted by John Lock
Can anyone have a LISP program that reads the Current BOM table within Solid Designer � Annotation and outputs a ASCII CSV file?
I would welcome any help.
Thanks

Are you using DDM/WM Desktop ? If so, you could define a macro behind a push button in the WM Bom editor/viewer doing the following (I supplied you with a simple version):

LET l_win (GET_CURRENT_WINDOW)
LET l_bom_ltab Awm_bed_f_get_local_bom_ltab l_win
LET l_head_ltab Awm_bed_f_get_local_head_ltab l_win
LET l_head_name (READ_LTAB l_head_ltab 1 db_default_name)
LET l_file (FIX_MESG "{1}\{2}.csv" (READ_ENV "TEMP") l_head_name)
Awm_na_m_register_shut_action (FIX_MESGQ "PURGE_FILE {1} CONFIRM" l_file)
LTAB_TO_TABSL l_bom_ltab DEL_OLD l_file
LET r (SHELL_EXECUTE "open" l_file "" "")

This will take the entire bom ltab (unformatted, with all columns) directly to Excel.

[ChrisE]

Hi John

as you are not using WM/DDM, the only way to access this table is some LISP code (as Fabrizio pointed it out)

Beats me why CoC can't provide a button for exporting the table e.g. into a file or HTML. Shouldn't be such a big deal for them.

Unfortunately, I don't have such LISP code at hand :( (at the moment)

Chris

[Wolfgang]

(defun sd-logical-table-contents-to-csv (ltab fname)
(when(sd-logical-table-p ltab)
(let (row)
(with-open-file (fo fname :direction :output)
(dotimes (i (sd-get-logical-table-number-of-rows ltab))
(setf row (sd-read-logical-table-row ltab :row i))
(format fo "~A~{,~A~}~%" (car row) (rest row)))))))


look's like lisp :)

I could not test it!, because I don't have an OSD at home !!

I'm thinking about to post some small functions during the next days...

[ChrisE]

That's the piece of code I didn't have at hand right away. Thank you, Wolfgang.

So I was curious to test his algorithm. It worked right away :). However, I enhanced it a bit :D

I put it all together into a small LISP program, that will add a new button "Print Bom" to the toolbox. Within the then called dialog you can choose the destination CSV file.

Upon pressing OK, the bom table will be read out, and written to the file of your choice.

When you are on Windows, the file will also directly be loaded into the appropriate application (like e.g. MS Excel)

Please find a sample program attached.

Have fun :D

[John Lock]

Chris Thank you for you help with the lisp program, it worked well

[Steve]

How do you "run" a lisp program like this?

Steve

[John Scheffel]

Originally posted by Steve
How do you "run" a lisp program like this?
You can run a LISP file by loading it. From the Load File browser, you can go to the folder containing the file, set the file type to LISP Source (*.lsp), pick the file and load it. On the command line you can use a command such as:
(load "filename.lsp")
provided the file is in the current folder. If not, you will need to include a path such as:
(load "C:/path/filename.lsp")
Note the use of forward slashes (UNIX syntax) rather than back slashes (Windows syntax). In SolidDesigner or Modeling the forward slash will work for either OS.

[Andy Poulsen]

Hi Steve,

In addition to the methods John mentioned, you can also just drag and drop the lisp file into your active Modeling session (this option may be easier in many cases than typing at the command line).

After the lisp file is loaded, the function will be available in your toolbox. If you want it on a toolbar, you can create a new command and bitmap for it, and add that new bitmap and command to any toolbar you wish. If you'd like more info on how to do this, feel free to drop me a line or just reply to this post...

If you want the file to be loaded each time you start Designer Modeling, just add the line already mentioned by John i.e.

(load "C:/path/filename.lsp")

to your startup file (normally pesd_customize, though with V12 it will typically be just sd_customize...)

I hope this helps!

andy

[dszostak]

For anyone who is interested in data management capabilities, even though this is a Designer Modeling thread, Model Manager has the feature of exporting BOM tables to CSV, HTML or Excel. The BOM tables come directly from assembly structures saved with MM. Once saved, they can be exported as is or manually massaged for manufacturing purposes and then rev’d for tracking.

[Steve]

Well, I tried running the above bom.lsp file, by dragging and dropping it into SD.

When I ran the program, it came back with the following error:

A display table with name "AM-BOM-DATA-DTAB" does not exist.

I'm running 11.60C.

Does anyone know a simple way to get a printed (or text file) BOM out of SD/WM?

Steve

[fabrizio]

@Steve:
just a guess: was Annotation loaded when you ran the program? If I am not wrong, the display table that is mentioned in the error msg is defined only when you load Annotation...

Fabrizio

[Andy Poulsen]

Hi Steve,

We are going to be releasing a freeware module in the near future that includes this functionality. I extracted the relevant code from our development code stream and did a quick check on it, and believe it does what you are looking for.

It's a bit different than the other solutions mentioned in that it doesn't require Annotation to be loaded just to get a BOM of items in your active session.

Since the module in question is going to be freeware, I can make this publicly available (keep in mind it's PRE-RELEASE but has been fairly well tested!). I have attached a zip file that contains a compiled version (i.e. .dll file for windows) of this function. Please take a look and see if it does what you want!

Quick Overview of AI-BOM:
AI-BOM allows you to specify which assemblies to include (i.e. either all objects or selected objects) for your Bill Of Material. It also gives you several choices for how you want the data displayed/written (including being able to get more detail on which parts are common, including pathnames). The resulting BOM can be displayed on the screen, and also can be saved to disk as either a .csv file or in a tabular .txt file that will look nice (when displayed in a fixed-width font, obviously!)

Please give it a look and let me know if this fits your needs. Also, if you think of any other features you would find useful, please pass those along as well. We'd love to hear any input you have!

Good luck!

andy

NOTE: the attachment that was here has been deleted, and an updated version has been posted in a later message in this thread. It includes versions compatible with both SolidDesigner 9 and OneSpace Designer Modeling 11+.

[May Kung]

Interesting. I just tried it on SD9.01D and I get the attached LISP error. Does it need OSD11 at a minimum to work?

[Andy Poulsen]

Hi May,

This was compiled on 11.65, and the compiler generates some version-specific code. All the functionality should work fine on 9.x though, so I'll try to get a 9.x-compatible version uploaded ASAP.

Thanks for the heads-up!

andy

[Andy Poulsen]

Hi May,

Here's an updated zip file with versions for both 11.6+ and 9.x.

The 9.x file should work on 11.6+, but may not be optimized. In short, if you have 11.6+, please use the ai-bom.dll file, and if you're using 9.x, please use the ai-bom-sd9.dll file.

Please let me know if you have any questions.

Thanks!

andy

NOTE: the attachment that was here has been deleted, and an updated version has been posted in a later message in this thread. It includes versions compatible with both SolidDesigner 9 and OneSpace Designer Modeling 11+.

[Steve]

Wow, this looks great!

My only complaint right now is that the BOM returns the part number of the top level itself in the BOM. Obviously the owning assembly is not a component of itself.

Looks good so far, though!

Steve

[Steve]

Again, this looks very good, a couple of suggestions though:

It would be nice if I could somehow get an indented BOM. Right now the whole BOM becomes "flat" - you can't tell from the listing which components are components of sub-assemblies.

Another option that would be nice would be a "top level list only" - in other words, ignore the components of sub-assemblies.

Steve

[Steve]

When running the program on my top-level assembly, I got the following error:

WARNING: Cell value NIL is not of type STRING. New value "NIL" will be taken as value.
WARNING: Cell value NIL is not of type STRING. New value "NIL" will be taken as value.

Steve

[Andy Poulsen]

Hi Steve,

Thanks for your comments and feedback! Your suggestions have been added to the enhancement/change list, and I'll post an update as they're implemented.

As far as the warnings you mentioned, I have sent you an email (to avoid cluttering the board) asking for more details to isolate the issue.

Please keep the feedback flowing! (either here or via private email if you prefer)

Thanks again!

andy

[May Kung]

I agree. It looks quite promising. In SD9, it will not show a BOM, though the Preview button does show it.

It prints out the headers for the BOM listing, but does not print the rest of the BOM.

When using the code for OSD 11.6, I figured out how to get it to print out the BOM in both TXT and CSV formats. I did notice I have to use Preview first, then print out the file. Not using Preview first results in an empty BOM file with only headers (like what happens under SD 9 no matter in which order I press the buttons).

Under both systems, I get the same error Steve mentioned upon initially starting the command.

[Andy Poulsen]

Hi May,

I just checked it again on SD9 using a simple assembly, and I didn't see any of the problems you mentioned (either the error Steve mentioned or the empty table).

If you would be willing to send me the file you're working with I could take a look at it and see if I can duplicate the problem. If not, no problem -- we'll try to figure out what to do to duplicate the problem here. If you are willing to send the file, please send it to testfiles (at) asp-inno.com.

The problem regarding needing to click "Show BOM" has already been fixed, and I'll keep you posted on the other issues as well.

(heck, when I posted an alpha release I knew I'd see some "real-world" issues!)

Thanks for your patience!

Andy

[May Kung]

I did a little testing on OSD11.6 and it seems to happen if the assembly has children that own viewsets. It doesn't happen if the parent has a viewset and the children do not.

If I delete out the viewsets from the children, the error does not come up.

If I have time later, I'll try it again under SD9 to see if the same pattern holds.

[Andy Poulsen]

Hi May,

We've seen this problem before during development of some of our other modules, though most viewsets won't cause this problem -- read on...

The underlying problem seems to be that somehow a viewset can end up being the owner of an "invisible" part or assembly i.e. you can't see it in the structure browser, but a lisp-level query of the structure will show a part or assembly owned by the viewset (which I believe should never happen...). I don't know how or why this happens, but I've seen it from several of our customers, so something nasty is happening behind the scenes (apparently with Annotation). Most viewsets don't exhibit this problem, but some definitely do, and it causes some big headaches (as you might imagine).

We have worked around that issue in our other modules, but apparently that check didn't get implemented here. I'll make sure it gets included in the next rev.

Thanks for taking the time to check on this!

andy

[Andy Poulsen]

Hello All,

Attached is an updated version with many significant changes. Thanks to everyone who provided input, both through this thread and via private email!

This version also has a utility function called "AI Check Viewset" that will allow you to check for the corruption mentioned previously (with the "invisible" part or assembly being owned by a viewset), and gives you the option to delete this invisible part or assembly. PLEASE NOTE: while no problems have been observed with these deletions, the full impact is not yet known, so please use caution!

The main BOM function has an internal check that will ignore these corruptions, so it is not necessary to run the check/clean function to get a valid BOM. The check function is just for your own information (and it would be interesting to know how many others have these internal corruptions without knowing it!).

Please let me know if you have any further questions or comments!

NOTE: This attachment was deleted since the newest release of AI Bill-of-Materials Generator can be downloaded from our website at www.asp-inno.com/prod_BOM.html (http://www.asp-inno.com/prod_BOM.html). Please take a look and let us know what you think!

Thanks!

andy

[Shaba]

NOTE: This attachment was deleted since the newest release of AI Bill-of-Materials Generator can be downloaded from our website at www.asp-inno.com/prod_BOM.html (http://www.asp-inno.com/prod_BOM.html). Please take a look and let us know what you think!

Thanks!

andy

Link doesn't work :confused:

[Andy Poulsen]

Hello,

The AI Bill of Material function has been significantly enhanced (including generating PDF files of the BOM, including images), and is now part of our AI MAXTools Suite of Products (which has a wide variety other functions as well).

For more information on the products, please visit www.ai-maxtools.com/products (http://www.ai-maxtools.com/products).

The download page can be found at:
http://www.ai-maxtools.com/products/free-trial

Please note that the AI Bill of Material function (and a few selected other functions) will work in demo mode, but all pages will be watermarked. A license from AI MAXTools is required to remove the watermark (and enable all of the unavailable functionality). You may request an evaluation license on the download page to get access to the other functions.

I would be glad to answer any questions -- just ask! :)

Thanks!

andy

[Shaba]

There is a way to edit an existing BOM table?

My goal is manual edit BOM logical table
AM-BOM-DATA-LTAB

My idea is full exporting it to excel, or txt file
With also DOCU_BOM_ENTRY_ID
Edit it with excel or notepad
And import back to annotation to use it.

There is some lisp to do this?
Thanks! :cool: