Topic: Storing a BOM Matrix in a Drawing.

[TR]

Note: Language C#, AutoCAD 2005

Ok, I'm trying to write an interface to an ERP system we are implementing for creating Drawing Bills of Material. I have already cracked out the code using ODBC.NET to collect data and all the nonsense required for the user interface. I have yet to figure out the best way to store our bills of material in the drawing and am looking for advice.

We do our drawings in a fashion known as tabulated drawings. This means that a single drawing can represent different items, depending on the suffix of the drawing. For example say we have a drawing of a mounting bracket for a switch. This switch bracket will always have the same dimensions but we may need to make it out of carbon steel, 304 stainless steel or 316 stainless steel. An example BOM would look like this:

Qty 03	Qty 02	Qty 01	Catalog Number	Item	Description
0	0	1	CD12345	01	Plate, 3/4" Thick, Carbon Steel
0	1	0	CD12346	01	Plate, 3/4" Thick, 304 Stainless Steel
1	0	0	CD12343	01	Plate, 3/4" Thick, 316 Stainless Steel
1	1	1	DC65432	02	Switch, Proximity, SQ-D #FAKE-1234

If this drawing number was WTK21-00001D. We would be able to request a 304 Stainless Steel version of the mounting bracket by calling for WTK2100001D02. Even if a drawing doesn't have multiple "parts"/"assemblies" on it there is always an 01 part/assembly.

Note: Drawing can have anywhere from 01 to 99 "parts"/"assemblies" on it.

I hope this was somewhat clear.

[Draftek]

So, for your example, are there 3 different dwg files or one? If one, I don't understand fully where the suffix comes into play.

Also, how often do the drawings get updated?

[TR]

Hmm..Maybe this is a better explanation.

It is a single drawing file with multiple Bills of Material on it. The suffix defines which BOM on the drawing to use. For example if we wanted to use the 304 Stainless assembly on the above BOM on another drawings BOM we would place the drawing number with the 02 suffix on the BOM. If we wanted to call the Carbon Steel Assembly we would place the drawing number with the 01 suffix on the BOM. This allows us to call drawings on other drawings the same way we would call buy-out items and such.


All drawings have an 01 suffix, even if there is only one BOM on the drawing.


The suffix 01, 02 and 03 are defined in the Qty 01, Qty 02 and Qty 03 fields.


The drawings could be updated anywhere from 6 times in one week to once every five years. It depends on if it's a job specific drawing or one of our standard drawings. It also depends on which one of the machines we build that it's used on. Some are more rapidly changing than others.

[mohnston]

Can you explain what you mean by "store our bills of material in the drawing".

Are you looking to just store data in the drawing file? Or does that data also need to be visible in the drawing?

For storing simple data in a drawing xdata is very lightweight but it doesn't display and is more difficult to get to than say block attributes or simple text.

[Draftek]

Ah..., that narrows it down then.

Where is the BOM information currently stored - blocks / attributes / text / other? You might consider ease of change if you have tons of drawings.

You could use blocks with attributes, xdata or xrecords (dictionary).

The advantage of using blocks with attributes is you can have an average user easily update the database. The disadvantage of using blocks with attributes is you can have an average user easily update the database.

xdata and xrecords would take an application for the storage part and you want to make sure you design integrity of the data if it is stored in two different locations - which I hate.

Yeah, what mark said as I was typing this....

[MickD]

Why not store/update this data in a regular db and access the drawing from there?
For example, your bracket that has more than 1 bom for different materials will have 4 boms say for the same drawing id/key, this way you don't need acad all the time.

If it definitely has to be part of the drawing xrec's would be the go.

<edit>
Or, do you need to request a certain bracket and get a drawing with the correct BOM filled out for that bracket?
Again a db would be the go and you can run your request remotely, create a routine that finds the BOM location and inserts a block with att's filling each block with a record row from the db, increment location and repeat.
By using a db you would rarely if ever have to update that drawing and would only need acad to prep the drg and print, just update the db as required. If you did have to change the drawing it's not the same bracket anymore anyway and would/should require a new db entry.

[TR]

I don't want the user to be able to edit the BOM without going through my program so a block with attributes is out of the question. I want my program to generate an uneditable block, I assume it shouldn't be to hard to accomplish.

I thought about an external database solution too but it seemed like overkill for what I want to do. I want to keep my data in the file so if it gets moved around outside our company it will still hold the information. Don't ask me why, it will just help me sleep better at night.

I was thinking of taking the BOM and converting it to json using Newtonsoft Json.NET. The example looks pretty slick and since JSON is nothing more than a text string I assume it would be fairly trivial to stuff it away somewhere in the drawing database. Looking at the examples here it seems pretty easy to store all sorts of data types.

Any thoughts?

[mohnston]

I'll offer an opinion. Warning: very opinionated opinion follows!

Let me try to understand, you say the database seems like overkill but stuffing data into the drawing somewhere with Newtonsoft.NET is an option?
You are talking crazy talk. If you have data visible on your drawing and a copy of that data stuffed, invisibly, into that same drawing you are asking for data disconnect.
The scenario you describe SCREAMS database. In fact you said you already have one. And it's hooked up. Integrate it and your drafting system. You'll be glad you did.

[MickD]

...If you have data visible on your drawing and a copy of that data stuffed, invisibly, into that same drawing you are asking for data disconnect.
...

I'm pretty sure he wants the data for each variation of the same bracket stored in the drg and when he needs a certain type material/parts, he calls up the relevant data and puts it into the drg's bom and then print.

Sorta swings and roundabouts but he's right in that at least the data is contained in one spot - but - once you get say 3 lots of data in one drg and say 8 lots in another, choosing the right data will be the tricky bit and that will probably need to be stored somewhere else anyway (else how would you know what the code no. would be for the part you want?).

[TR]

I'm only using the json string to maintain integrity inside the drawing. When drawings are done their BOM's will be pushed to our ERP database.

[It's Alive!]

Tim,
That json string looks pretty interesting, thanks for the tip, I’ll have to look at that more in depth.
One method I use for storing data is a (key . value) list , but I use bit packing in the key.
For example....

Code: [Select]

(1073741823 . value)  == (111111111111111111111111111111 . value) 

if I only use the first 15 bits as an ID number, I can then use the remaining bits to store information about the value. I.e.

Code: [Select]

(1111111111  |   11     |   111    |    111111111111111 . value)
 Qty(1-1023) |   Type   |   01-03  |            ID              

Of course you would arrange the bits to suit your needs, also you could use some combination like.

Code: [Select]

['JSON!',1,true,{ 1073741822:'value'}]
Then you just need to fire up Wrathchild and write your bit decoder 

just a thought for ya
Dan

[TR]

Dan:

You don't need to do anything like that when using JSON.

Take a look at the following code:

Code: [Select]

using System;
using System.Collections.Generic;
using System.Text;

using Newtonsoft.Json;
using Newtonsoft.Json.Converters;

namespace jsontest
{
    class bomitem
    {
        public int quantity;
        public int partnumber;
        public string catalognumber;
        public string description;
    }

    class Program
    {
        static void Main(string[] args)
        {
            bomitem bom1 = new bomitem();
            bomitem bom2 = new bomitem();
            bomitem bom3 = new bomitem();

            bom1.quantity = 4;
            bom1.partnumber = 01;
            bom1.catalognumber = "90I0200";
            bom1.description = "Meter, Red Lion #CUB4V";

            bom2.quantity = 9;
            bom2.partnumber = 02;
            bom2.catalognumber = "90X0199";
            bom2.description = "Pilot Light, 24V DC, Green, SQ-D";

            bom3.quantity = 100;
            bom3.partnumber = 03;
            bom3.catalognumber = "23M1234";
            bom3.description = "Line Reactor, 5KW, MTE Corporation";


            bomitem[] boms = new bomitem[] { bom1, bom2, bom3 };

            string output = JavaScriptConvert.SerializeObject(boms);

            Console.WriteLine(output);
            Console.ReadLine();

        }
    }
}

It outputs:

Code: [Select]

[{"quantity":4,"partnumber":1,"catalognumber":"90I0200","description":"Meter, Red Lion #CUB4V"},{"quantity":9,"partnumber":2,"catalognumber":"90X0199","description":"Pilot Light, 24V DC, Green, SQ-D"},{"quantity":100,"partnumber":3,"catalognumber":"23M1234","description":"Line Reactor, 5KW, MTE Corporation"}]
Then when I deserialize it I can convert it back into an array of bomitems.

[Twitch]

You could store your BOM info in a custom dictionary.  At first I thought about xData but i think you'd run out of room.