Worksheet Database Functions Questions

[Jim Smith]

- Is there a way to turn off / minimize the Database Top Row? (In most cases it's more confusing than useful) & right now I place a  rectangle over it to cover it up

- Is there a way to add a blank cell for NOTES or COMMENTS Cells to the right or left of the Database worksheet that's not part of the Database? 

- If yes to above, is there a way to add a formula to a blank cell? 

Edited March 22, 2017 by Jim Smith

[Pat Stanford]

With the worksheet open for editing, go the the Worksheet View Menu and uncheck Database Headers.

 

All data in a database MUST be either object specific calculations (things like position, area, perimeter) or Record.Field data. The Record.Field data can either be PIO Parameter Records (i.e. Door.Width) or user generated records (MyRecord.MyNoteField).

 

So to put in notes, you can create a user Record and attach it to the objects that will show up in the database. If you create a Note field (whatever name you want), you can then put a formula of =MyRecord.MyField into the database header to display the data. Since record.field data is 2 way, what you type into the worksheet will be stored in the record attached to the object. If you edit the field from the Data tab of the OIP the changes will show in the database when it is recalculated.

 

I don't understand the third question, but I think the answer is no.  All formulas must go in the database header row and apply to every object in the database.

 

Of course an object that does not have MyRecord attached will not show anything in the cells referencing that data.

 

Ask more if you still have questions.

[Jim Smith]

Thanks Pat, got it.

 

As for the 3rd question; many of our contractors ask for an imperial conversion of some information in schedules and I have found that having a column in a Spreadsheet with a conversion factor referencing a cell is a fast way of achieving this,. With a Database, rather than having to add the imperial conversion to the Data of a Door, for example, could I add a formula to the Database Header Row that makes this Imperial conversion to my Door Sizes?

 

[Miguel Barrera]

Yes, you can have formulas in the database header row, which will apply to all instances. If you list a numeric value in a column, you can then use it in a formula to give you a calculated value in another column

[Jim Smith]

Thanks I will try this. On smaller projects with only a few doors we tend to use a spreadsheet for a door schedule & so it's a combination of data entry on the Spread Sheet & various formula. On bigger projects we tend to you a customized database, but is is mostly one that has had columns removed/renamed  rather than new formula added. I guess it's time to exercise that part of the brain!

[Pat Stanford]

One thing to be careful of is that Vectorworks only understands a few types of units.  It understands length and area (and maybe volume and angle). So if you have fields that are defined as Dimension, you can do things like 1' +1" -2cm + 25.4 mm and come up with the correct answer.

 

For units that VW does not understand you have to define them as Numbers and store them without any units attached.  If you create a field to store a weight, you have to enter it as 57, not 57# or 57 kg.  When the next person comes along and uses a different unit, your calculations are going to get screwy really quickly.

 

There was a $50M satellite that was lost on Mars due to different groups using different units.

 

In a worksheet where you know you have control of the base unit, you should be able to do a conversion to a single known other unit without too much trouble.

 

Note that some of the PIOs (specifically the Space Object, maybe others) try to get around this by defining two fields for things like Area. One will be a number the other will be a text field and will contain both the digits and the unit marks. You have to make sure to choose the right field if you need to do math(s).

[Jim Smith]

Thanks Pat, good to keep front of mind. All our projects are Metric, but as I say we sometimes have requests from clients & contractors for Imperial values. In a spread sheet I can add a preface or suffix to an integer that's a number. However, what we generally do is when requested for Imperial, we have a set of columns to the right with a header above the converted cells  with the title: IMPERIAL CONVERSIONS. Like in so many things, Canada is somewhere between America & Europe, when we buy butter it's generally in a 454 gram package not 500 g but bags of milk are in litres. Home Depot sells tape measures in feet & inches, metres &  mm as well as ones with both measurements. If I had to guess the ones with feet & inches on 'em are the best sellers; by a mile.