This tutorial is good for TurboCAD Professional versions 4 through 9 and TurboSketch.
Create a custom database toolbar/buttons or custom SEKEs (there are none standard), especially for Edit Object Data.
Reports must be run in model space (in v7 you can run the report from floating model space) (this does not apply to v4 or TurboSketch).
This tutorial shows how to create a bill of materials both with and without resorting to an outside program such as Excel, Lotus 123, Corel QuattroPro, or Access - but the use of these programs can help increase your productivity and create more professional looking documents.
The TurboCAD database cannot be linked with outside databases.
When creating new reports, once you have finished the report, hit OK all the way out to your drawing. If you hit Cancel or the Escape button on your keyboard, your report will not be saved.
For AutoCAD users: the TurboCAD database uses "object data" not "block attributes" and has no way of extracting block attribute information for use in the database.
Once you have created new database fields TurboCAD saves them under the ...\TurboCAD\Program\Database\ directory in the TCDB.INI, but a template drawing is necessary for creating new drawings using your custom database setup. Reports (once created) are saved in your ...;\TurboCAD\Programs\Database\ folder as reports.rpt. If you share your file with another user, you can send a copy of your reports.rpt along with the your file (but make sure they back up theirs).
OLE users: whether linking or embedding the table back into TurboCAD, the OLE file should be co-located with the TCW file.
If you have a comment or question about this material, send an email to the author.
In its most basic form, a Bill of Materials (BOM) is little more than a list of parts needed to build something: how much wood and how many nails will it take to build a house? With the advent of modern corporations, it is hard to find a BOM that is anything less than a full arsenal of information, listing such things as:
quantities · supplying vendors
descriptions · date ordered
part numbers · expected date of arrival
prices · vendor phone numbers
This list is by no means exhaustive, and you will find TurboCAD's database is capable of doing much to simplify your needs. (Click image for larger version.)
Open a new drawing or template (or an existing drawing to which you would like to add a database) and go to the pull-down menu Tools|Database|DefineFields...
Here is the Define Fields dialog box and an explanation of the columns:
- Field Name
- a title for the fields
- Data Type
- the kind of information that will be stored
- Visibility
- whether the field will be visible for editing
- Editable
- whether the field will be editable
- Description
- memo about the data field, only seen in this dialog box
What you see so far are fields that TurboCAD must have in order to create a drawing. Our goal is to use this database to store a little extra information along side the drawn objects' information — for this tutorial, we do not want to see all of the basic fields, so uncheck the Visibility box for all of the default fields.
Next, ask yourself: what am I going to draw, and what kind of information would be usefully kept with the drawing? Think about what you want to end up with - you will need at least two fields, one for quantities and one for a part number, name, or description. Also, what kind of information will each field be? For example: Description will be Text, while price would be Currency.
For this tutorial, I chose the following fields:
- Callout
- will correspond to a leader callout pointing at the part; integer
- Description
- of the part; text
- Quantity
- of parts; integer *
- Units
- such as "each" or "pair" or "linear foot"; text
- Price
- of each part; currency
- Price subtotal
- same as the price **; currency
- Supplier
- for the supplying vendor name; text
- Part number
- either your company's, or the supplier's; text
* This tutorial uses integer Units, such as how many nuts or bolts "each". If you are working with wood, you may want to make the Data Type "Double". The "Double" Data Type will allow you to enter decimal values. There is no provision for fractional input.
** The Price and the Price Subtotal are the same because I am setting up the database to show both the price per unit and the subtotals in the final BOM. If you are using an external application such as Excel or Lotus 123, then only one column is necessary.
Once you have created your fields, you will want to create a template drawing that includes your database fields' setup.
Populating means to fill out the individual cells or objects with information.
Begin creating your drawing/model. As you create an object, it will have empty cells to be populated. Use the pull down menu Tools|Database|EditObjectData to populate each object.
Mini-tutorial on blocks:If you are going to use a complex object more than once, then make a block out of it. Blocks are stored definitions of complex objects or groups of objects. When you insert a block from the Block Palette you are actually only inserting a reference to the block definition. Therefore, if the block takes up 100K of definition and you use it three times, instead of increasing the drawing size by 300K it will only use a few bytes to store the coordinates, scale, and rotation of the block reference.
In relation to the database, a new block insertion will not carry the edited database attributes with it, so you will have to enter the BOM information after you have inserted the block. Every time you insert the block you will have to re-enter the data, but you can make your job easier by copying already populated block references (which in turn copies the data).
For the curious - if you define the database information before creating the block, or even if you Edit Contents on the block, inserted references will never carry that data. Why? In the "eyes" of the database interpreter, you are inserting a new entity - a block reference is an entity unto itself, that refers to the graphical definition in the file's block library.
Here is the dialog box you will see when entering data:
If you select several items in your drawing before hitting Edit Object Data, you will be entering the same information for all of them - so if you insert a dozen block references and forget to populate them, simply select them and populate them all just once.In addition, you can hit the "Show All" button on the Edit Object Data dialog box and edit them individually. I do not recommend this particular procedure, because the only way to distinguish one object from the next is the ObjectID.
The Show All dialog box:
Once you have completed your drawing and you are satisfied that you have entered information for all of your objects, you are ready to extract the information.
In order to extract information from the database, TurboCAD provides us with a Database Report tool. Tools|Database|Reports... will display the following dialog box:
Since no reports have previously been created, the Available Reports box is empty. Hit the Create button to get...
...the Report Create dialog box:
Every report must have a title, so name this one BOM. The Comments field is good for leaving reminders to yourself about the various reports you might create. We will be creating a "Summary" report (under Report Type) - the summary report type allows access to some additional functions that we will use to create our BOM.
Next, we want to select which fields we want to have included in our report. The ObjectID is a required field for the Detail Report Type, but is optional in Summary Report Type. We do not need it, so on the right side under Report Fields select ObjectID and hit the Remove button.
Now select each of the fields we created earlier from the Available Fields and hit the Add button to move them to the Report Fields. Below is a complete Report tab:
Now select the Advanced Report tab.
On the Advanced Report tab, you can adjust the Report Header Name (middle column) and the Function. The Function describes how TurboCAD will handle the information in each column. Text fields cannot be manipulated, so they register N/A but numerical fields have the following choices:
SUM provides a total from all of the values entered in that field
AVG provides as average of all of the values entered in that field
MIN returns the smallest value entered in that field
MAX returns the greatest value entered in that field
COUNT returns the total number of items within sorted fields
For this tutorial, we want the Quantity to be COUNT and the Price Subtotal to be SUM, all other fields are to be None. Notice we are not assigning any function to Price - this is because we want to have one column that displays the per-unit price and one column with the subtotaled price for each part. Again, spreadsheet users can eliminate a field and calculate this in the spreadsheet.
We now have a completed report. Hit the OK button and you will be returned to the Reports dialog box. Hit the OK button on the Reports dialog box to save your report. If you hit Cancel or the Escape button on your keyboard, you will lose your report.
When you reopen the Reports dialog box, you will see the title of the report you just created in the Available Reports section. Select the report title and hit the Report...
...button.
Note: if you drafted in model space and use viewports to show the drawing in paper space, then you must run your report in model space. If you drafted entirely in paper space, you will have to run the report in paper space.
You will be presented with the Summary Report dialog box as seen below -- to see the rest of the information scroll to the right. If you see information you would like to change, it can be changed in this dialog box, but it will not be saved back to the drawing (remember this is a report of the data -- not the data itself, such as you were working with in the Edit Object Data dialog box).
Hitting the Σ button will sum the columns with numerical data. For this tutorial, only the Price Subtotal is meaningful.
Now you need to get the information back into the drawing. You can do this the easy way or you can do this the hard way.
The easiest but sloppiest way is to simply copy and paste - select the information, hit the Copy button on the dialog box, hit the Close button and paste into paper space. You will notice that everything has lost its formatting, as the paste provides only a tab-delimited entity, and TurboCAD does not recognize Tabs. So the quick and sloppy method is of limited usefulness.
If you do not have any outside spreadsheet or database application, then you must resort to the "hard" option. Hit the Save button on the Summary Report dialog box, and save the report as a Tabbed Text (*.txt) file.
Open this file using WordPad (not NotePad) and make any editing changes you are interested in, erase the Tabs and replace them with spaces. Due to formatting with space characters, it is important that you use a monospace font. Cut and paste your resulting information back in to TurboCAD. In order to draw a table around the text, select the Line tool and draw a line across the top of the text box using vertex snaps (notice that although there is no visible box around the text, it is nevertheless treated as a rectangle entity, and you can snap to the Vertex, Midpoint, Near, or Divide snaps). Next, hit F7 on your keyboard to select the line that you just drew, then select the Fit Linear Copy tool. Enter the number of Sets in the Inspector bar: you will need the number of items in your BOM plus one, then choose the Midpoint of the bottom of the text box. Now create the vertical edges of your text box with the Line tool, again using vertex snaps. For the vertical table separators you will use the Nearest snap to begin your line and the J SEKE (perpendicular) to finish your line.
The best option is to use a spreadsheet, database, word processing, or any OLE compliant client application that will support tables. You simply cut and paste the Summary Report directly into the application, edit it to your liking, add the table gridlines, then cut and paste the table back into your drawing. It is also possible to save the file from OLE applications and insert it as a linked or embedded object from the Insert menu.
Users of MS Access or Excel get some additional benefits: they can save the Summary Report as XLS or MDB and then insert and edit the OLE object all without leaving TurboCAD (the outside applications are invoked "transparently" within TurboCAD).
A final note for OLE users: whether linking or embedding the table back into TurboCAD, it is wise to co-locate the OLE file with the TCW file. For linked objects, this is the easiest way to prevent broken links due to directory changes and file moves; for embedded objects, this will keep a reference object in case the object gets mistakenly deleted or the file becomes corrupt.