Using Worksheets and Databases
VectorWorks gives you a wide range of options when using and creating worksheets. You can create an independent worksheet. An interactive worksheet works with the drawing as it's being created, updating along the way. An independent worksheet does not directly use information from the drawing.
To create an independent worksheet
This opens the Resources palette.
The Create Resource dialog box appears.
3. Click the Worksheet radio button.
The Create Worksheet dialog box appears.
5. Enter a Name for the worksheet.
6. Enter the number of Rows for the worksheet.
7. Enter the number of Columns for the worksheet.
Note: You can modify the number of columns and rows later, if desired.
Your new blank worksheet appears in the Drawing Area.
You can now assign names and functions to your columns, as well as select whether you want individual rows to be spreadsheet or database rows. The worksheet can be resized, moved, and closed as desired.
In VectorWorks worksheets, rows are automatically set up as spreadsheet rows. These can be changed as needed.
To change a spreadsheet row into a database row or vice versa
A pulldown menu appears with Spreadsheet or Database options.
2. Select either Database or Spreadsheet.
The row is changed to the selection.
If you change a spreadsheet row into a database row, the Database Criteria dialog box appears. Enter the desired search criteria and click OK.
If you change a database header row into a spreadsheet row, any subrows and the information in them is removed. The information in the database header row is carried over into the spreadsheet row.
You can select cells by clicking in individual cells, use the arrow keys to move around from cell to cell, or use the Enter and Tab keys in conjunction with the Shift key. The Enter, or Return, key moves you to the next cell in that column, while the Shift-Enter key combination moves you up one cell in that column. The Tab key moves you to the next cell in that row and the Shift-Tab key combination moves you to the previous cell.
You can select individual cells, a group of cells, entire rows or columns, or the entire worksheet. Simply clicking in a cell makes it the active cell. You can click-drag across a range of cells to select a group or click in one corner and Shift-click in the opposite corner to select a range. To select an entire column or row, click on the heading cell. Simply click-drag across the heading cells to select multiple rows or columns. You select the entire worksheet by clicking on the square in the upper left corner of the worksheet.
To enter data, you must select a cell or range of cells that the information will be applied to. There are two types of information you can enter into a worksheet: formulas or constant values. All information is entered via the Worksheet Entry Palette, which appears at the top of the screen overlaid on the Data Display Bar.
Constant values are typed in the Worksheet Entry Palette and can consist of text or numbers, usually in the form of dates or times. They do not change unless you change them or they are the result of a formula.
To enter a number as a constant value, click in the desired cell and enter the number in the Worksheet Entry Palette. To enter a negative number, place a negative sign (-) before the number. Do not include dollar signs or commas in a number entry or VectorWorks will consider the entry as text. Cells can be formatted with commas or dollar signs, as needed.
In a VectorWorks worksheet, text is anything that is not a formula, number, date, or time. Text can be entered into a cell as the result of a formula with each cell holding up to 255 characters. These characters can be any character, letter, or number that can be produced by the font in use at the time. However, to keep VectorWorks from treating numbers entered as text in a cell as a number cell, enclose the number in single quotation marks, e.g., '40'. If you need to enter text as part of a formula and the text is more than a single word, you must also enclose it in single quotation marks, as VectorWorks doesn't recognize the spaces between words in a formula.
Either of the worksheet types can be included in your drawing as a graphic object. For example, if you've generated a parts list for an object in the drawing and you want to include this list with the drawing, you could place the worksheet on to the drawing as an object. You can move the worksheet to any location on the drawing. However, you can not resize it, nor can it be edited as an object. You can only edit it when its window is open.
To include a worksheet as an object on a drawing
The Resources palette appears.
2. Select the desired worksheet.
The worksheet must be a part of the current drawing.
If the worksheet is part of another drawing, you must first import it to the current drawing. See "Worksheet Functions" .
3. Click the On Dwg check box on the Resources palette.
4. Close the worksheet window.
The worksheet is now included in the drawing as a graphic object. You can now move the worksheet like any other graphic object and place it where you want on the drawing.
To edit a worksheet included as a drawing's object
The Resources palette appears.
2. Select the desired worksheet.
The worksheet is opened in a window and available for editing. The space on the drawing where the worksheet was is now outlined and marked with an X. Closing the worksheet will return it to the drawing as a graphic object.
When entering cell references in formulas, you can refer to cells inside the current worksheet, known as internal references, or to cells in another worksheet, known as external references. Internal references can be made by clicking on the cell when creating the formula or by typing the cell address in the Worksheet Entry palette. External references must be typed and must include the full path name for the worksheet. The syntax for entering an external reference into a formula is [worksheet name:cell/range address]. An external reference is updated when you select Recalculate from the worksheet menu.
The Worksheet Entry palette appears, listing the cell's address in the left.
2. Click to place the cursor in and activate the Worksheet Entry palette.
All formulas must begin with an equals sign.
A formula can consist of functions, operators, cell references, and values. These may be typed in or entered via the mouse. The Paste Function and Paste Criteria menu items can assist formula creation.
The following syntax is used in formulas
When you finish entering the formula, click the Check Mark box.
To cancel an entry, click the X Mark box. The cell is returned to its previous state.
A report is an interactive worksheet which integrates the worksheet with the items in your drawing. As you change items in your drawing, the interactive worksheet is updated. You can create an interactive worksheet in your drawing file at any time. However, it usually is easier if you first create record formats, as well as part or all of your drawing.
To create a worksheet from record formats or symbols
The Create Report dialog box appears.
2. Enter a name for the worksheet in the Title text box.
3. From the List all pulldown menu, select the objects that will comprise the worksheet.
The choices here are Objects With A Record and Symbols.
4. Select the name of the record format you want to include in the worksheet from the Listing Objects With Record pulldown.
5. Select the columns to include in the worksheet.
The names of all Possible Columns are included in the left-hand list. Names of included columns are grayed.
The name of the selected column(s) appears in the right-hand Worksheet Columns list.
Click the Add All button to add all of the possible column names to the worksheet.
7. If desired, change the order of the Worksheet Columns.
Select the name of a column and click the Up or Down button to change its position in the list and worksheet.
8. Click the Summarize items with same checkbox.
This will summarize items that have the same entries in a selected field.
9. Select the name of the summarized field from the pulldown list.
The Create Report Options dialog box appears.
11. Choose a worksheet option.
Options include creating a New worksheet or Append to existing worksheet. The default is New worksheet.
You can also choose to search inside symbol instances to include information from them into your worksheet by clicking the Search in symbols checkbox.
This returns you to the Create Report dialog box.
This returns you to the Drawing Area and displays the worksheet, which can be resized and moved as desired.
The worksheet now contains a subrow for each object or symbol in your drawing. It also displays the column information that you specified. The first data row shows the total value for the column.
Creating record formats in your drawing file is an important step to creating meaningful database rows in your worksheets. You can attach record formats, which store a wide range of data, to any object (including symbols) in your drawing, such as price or part number data.
Note: Data that is an intrinsic part of your object (such as its width or length) can be edited with the Object Info palette.
VectorWorks' flexibility lets you create the object or the record format first. Either way, any data you attach to an object becomes a permanent part of it, staying with the object even when you import or cut-and-paste it into another drawing. This doesn't mean that you can't change the values of the data, though. For directions on editing data linked to an object, see "Editing Record Formats" .
You can create as many record formats as you need in a drawing file, each attached to whichever objects you desire. Many formats can be created for a single object.
To create a single record format
The Resources palette appears.
The Create Resource dialog box appears.
3. Click the Record Format radio button.
The Create Record Format dialog box appears.
5. Enter the Name of the Record Format.
The Edit Field dialog box appears.
7. Enter a Name for the field.
There are four choices here: Integer, Boolean, Text, and Number.
Select the Integer radio button to use whole numbers ranging from -32,768 to 32,767.
Note: Using Integer requires less memory than Number.
Select the Boolean radio button to use a data value of either true (1) or false (0).
Select the Text radio button to enter a string of characters, such as a word or a sentence.
Select the Number radio button to use numbers outside the range for Integer, fractions or decimals, or to specify a number format. The following table defines the fields available under Format.
Note: If you select Number, you must also select a number format.
9. Enter the data value in the Default text box.
For Integer and Number formats, type the numerical value you want to assign to the record.
For Booleans, type the desired values, usually 1 (true) or 0 (false).
For Text, type up to 256 characters.
10. For each additional field you want to add, repeat Steps 6 through 9.
This returns you to the Create Record Format dialog box.
This returns you to the Drawing Area and the worksheet.
The Data tab of the Object Info palette displays all records contained in the current drawing.
Note: To save the record format(s) you create, be sure to save your document before closing it.
Attaching Record Formats to Objects
To attach record formats to symbols in the symbol library
The Resources palette is displayed.
The Attach Record dialog box appears.
4. Select the record to attach.
Multiple records can be attached to a symbol.
The record is attached to the symbol. To attach multiple records to the same symbol, select each of the records and click the Attach checkbox for each record.
Note: You can select multiple records to attach by pressing the Control (windows) or Option (Macintosh) key and clicking on each record.
The attached record(s) will be included with the symbol each time the symbol is used in the drawing or imported into another drawing.
To attach or unattach record formats to previously placed symbols
2. Select Object Info from the Palette menu.
The Object Info palette is displayed.
3. Select the Data tab of the Object Info palette.
The Object Info palette repopulates to show a list of all record formats in the drawing. An X in the box indicates the record format is attached to the symbol.
4. Click the check box next to the name of the desired record format(s).
If unattaching a record format, a dialog box asks you to confirm the deletion of the record format from the object.
If attaching a record format, an X appears in the box and the record is attached to that instance of the object.
To attach record formats to other types of objects
The Object Info palette is displayed.
The Object Info palette repopulates to display a list of all attached record formats in the drawing.
3. Enter a name for the selected object, if desired.
Note: Names of objects are limited to 20 characters.
4. Click the check box next to the desired record format(s) in the list.
An X in the box indicates the record format is attached to the object.
If unattaching a record format, a dialog box asks you to confirm the deletion of the record format from the object.
If attaching a record format, an X appears in the box and the record is attached to that instance of the object.
Linking Text to Record Formats
The Link Text to Record command gives you a way to link symbols in your drawings with text that is part of a record format. It is particularly useful if you want to label symbols in a drawing with unique information. In order to use this command, you must have symbols and record formats already created in your drawing file.
Make sure the text is in the font and style you want. What you type is not important at this point. You may also want to create and assign a class.
3. Place it next to an unrotated symbol.
Make sure you place it exactly where you want the record format information to appear.
4. Select the symbol and text or block.
5. Select Link Text to Record from the Organize menu.
The Choose Field text box appears.
6. Select the record format(s) from the Formats list.
7. Select the field(s) in the format from the Fields list to apply the text.
VectorWorks will attach the record field information you selected to your symbol, placing it as you designated with your dummy text.
Note: If you edit the record format after placing symbols linked to that record, the link is broken and the text reverts to the default value.
The Object Info palette can be used for a wide variety of things, among them viewing and editing record details for objects via the Data tab. When you select this tab in the Object Info palette, VectorWorks displays a listing of any records attached to the object(s) that you have selected along with detailed information about the records. You can also use the Object Info palette to make changes to record settings. However, if you select multiple objects that have different records attached to them, the Data tab will list all the records attached to your selected objects, but you will not be able to tell which records are attached to which objects.
Note: You can resize the three lists by selecting and dragging the bars between them.
To view record information for a selected object
Select Object Info from the Palette menu.
The Object Info palette appears.
The Object Info palette repopulates with the appropriate information.
Information for the object is displayed in four text boxes.
Once you've created a record format in your drawing, you can edit its field values or layout at any time. The Object Info palette lets you edit the field values for existing objects or the default field values for new objects. The Resources palette allows you to edit the layout of a record format, changing any existing objects as well as new objects.
To change the field values for a record format for existing objects
The Object Info palette is displayed.
Note: The ChangeSymDefRec script lets you easily attach a particular record format to a folder of symbols in one step. This script can be found in the Goodies folder in the Design and Drafting Toolkit.
2. Select the Data tab on the Object Info palette.
The Object Info palette repopulates to display information on the record format(s) attached to the selected object(s).
3. Click the name of the record field you want to change in the Field List box.
4. Change the data in the Edit Field box.
VectorWorks changes the record format data for all selected objects. It does not, however, change the default data. Therefore, any new object you create will still use the original (default) record format. Directions for changing the format for new objects follows.
To change the default field values for a record format for new objects
5. Make sure no objects are selected in the drawing.
6. Select the Object Info from the Palette menu.
The Object Info palette appears.
7. Select the Data tab on the Object Info palette.
The Object Name field displays *DEFAULTS*. This indicates that no objects are selected and you are about to edit the default values.
8. Select the record to edit from the Record List box.
To change the layout of a record format for new and existing objects
The Resources palette is displayed.
2. Select the name of the current drawing file from the pulldown menu.
3. Select the name of the record format you want to edit.
The Edit Record Format dialog box appears.
5. Click the name of the field you want to edit.
The Edit Field dialog box appears.
7. Make the desired changes to the field type.
The changes are made and you're returned to the Edit Record Format dialog box.
Note: You may also add new fields by clicking New and remove unwanted fields by clicking Remove.
When finished editing all the fields, this returns you to the Drawing Area.
Functions are calculation tools that allow you to make decisions, take actions, and return values automatically for VectorWorks spreadsheet worksheets. All functions that begin with a capital letter use search criteria. Lower case functions, known as worksheet functions, require a number value or a cell range. Worksheet functions take a value or values, perform some action on them, and return a value or values. The argument for all trigonometry functions must be in radians. The following table alphabetically lists all of the worksheet functions available in VectorWorks.
Some aspects of VectorWorks' worksheets vary from the standard used by Excel. While executed differently, the same things can usually be done. An example is the Fill Right/Fill Down feature of Excel. This feature allows you to repeat the same value or formula over a range of cells. In VectorWorks, you can do the same thing by selecting the cell with the information you want to repeat and copying the cell to the clipboard. Then, select the cells you want to place the information and paste it in. The formula or value is repeated in each of the selected cells.
Search criteria are used within a function to find a specific type of object within a file. For example, you could use search criteria to find specifically-sized doors, walls of a certain color, or objects costing less than a certain amount of money.
Criteria is placed in the formula when you select the Paste Criteria item from the Worksheet menu. If an object is selected in your drawing when you select this command, the paste attributes dialog appears. This dialog allows you to choose which of the selected object's attributes you want to include in the function. You can click the custom dialog to perform a database criteria custom selection. If no objects are selected in the drawing, the Database Criteria window appears allowing to do a database criteria custom selection.
The following search criteria codes can be manually entered and used in a VectorWorks formula. These same codes are placed in a formula, with proper parentheses and syntax, by the Database Criteria dialog box.
VectorWorks uses the following operators. If the operator can be created with a special key combination, it is shown.
Performs basic mathematical operations. They combine numeric values and produce numeric results.
Compares two values and produces the logical value TRUE or FALSE.
Operator | Description |
---|---|
= | Equal |
<> or ![]() | Not Equal |
< | Less than |
<= or ![]() | Less than or equal to |
> | Greater than |
>= or ![]() | Greater than or equal to |
Combines references to two cells into a single joint reference. The operator is... or Range.
The following special words and characters are reserved for use in formulas.
This section provides a few tips and tricks to enable you to make better use of VectorWorks' Worksheets function.
Adding Default Values While Editing a Symbol
You can add default values while editing a symbol. While in Edit Symbol mode, the Data tab of the Object Info palette displays Symbol Defaults in the Object Name text box. You can select the field you want to have a default value and enter that value in the Field List text box. Once you exit the symbol, the values are applied. This will change the default from this point. Symbols placed prior to this point will not be affected.
Using VectorWorks as a Database
You can use VectorWorks as a database for symbols and information associated with them. Several different text fields are applied to the symbol definitions. Each text field is assigned a class and is linked to a field of the record attached to the symbol. Using the Custom Visibility tool, these classes are either hidden or visible, allowing for different information to be shown with each symbol, depending on which classes are shown.
Importing a Table from Microsoft Word
The following procedure is an example of how to import a table from Microsoft WordTM into a VectorWorks worksheet.
To import a Microsoft Word table into a worksheet
You must select the entire table. The import will not work with only a portion of the table selected.
2. Select Convert Table to Text from the Table menu.
The Convert Table to Text dialog box appears.
3. In the dialog box, click Tabs for the separation identifier.
If the file consists of only the table, select Save As from the File menu. In the Save As dialog box that appears, select Text Only With Line Breaks from the Save as type pulldown menu, and click OK.
If the table is part of a larger file, copy the table to the Clipboard. Select New from the File menu, and paste the table text into the new document. Save the file as above.
7. Select Resources from the Palette menu.
The Resources palette appears.
The Create Resource dialog box appears.
10. Create a new worksheet with the same number of rows and columns as the table in Microsoft Word.
11. Select the Import Worksheet item from the Import option on the File menu.
The Select Worksheet File dialog box appears.
12. Select the worksheet file to import.
VectorWorks' worksheet files can be exported and read by spreadsheet programs, such as Excel, as well as by some word processing programs. Because many of these programs have different format requirements, VectorWorks gives you a selection of formats to choose from when you export files as a worksheet. These include comma delimited, tab delimited, merge, DIF, and SYLK. Using the tab delimited format, for example, creates a file that can be opened as a table in Word. In addition, VectorWorks allows you to export all rows or just selected rows of a worksheet.