Jump to content
Sign in to follow this  
BLINK Design

WORKSHEETS!

Recommended Posts

Considering that few people use these puppies, there are few responses to questions. This ought to be an indication to VW that there is a level of complexity that needs further simplification. I purchased INFO EDITOR to help, and it does indeed solve several problems but the following questions remain. Please keep in mind that most VW users are USERS and not programmers and the language of code and spreadsheet syntax is very difficult to negotiate.

DATABASE HEADERS - when these are open and a single item is selected, a small formula is evident such as =Door(UserFld1). This tells VW where to look for associative information. Previously (pre v. 12.0) VW used Records and now schedules are composed of Plug-in objects so records cause spreadhseets and INFO EDITOR TO NOT WORK properly (MAC). For those that use WINDOOR, these must be ungrouped and the ID LABEL tool utilized because Windoor objects defaults to records!

The only way therefore to add an object to a spreadsheet (in my world) is to get the formula correct and to use the ID Label Tool to create a marker that the spreadhseet will recognize. Adding individual data ought to take minutes but it takes hours at times.... Argggh!

FORMULAS - where are these published please? The User guide is useless for people who do not speak that language.

INFO EDITOR - There ought to be a search command that allows the selection of a header to locate the corresponding column. I have found myself searching for corresponding formulas and never figuring out which column to enter the data. There are at least 100 of these.

ADDING DATA - the User guide suggests that a single item can be added to a spreadhseet, but I have found that User Fields that are not prestructured, do not recognize data period.

Whatever ignorance is discovered in these questions are precisely the questions. Exporting to EXCEL is not an option because the format is lost.

Thanks for any input.

Share this post


Link to post

Blink,

There is very little information available for worksheets...too bad because they are extremely powerful.

Go to the VW help...

Retrieving Object and Record Information

Worksheet Functions

Entering Formulas

Cell References

Operators

That's it!

Record formats are the way to go as they can be set up with your default data file and picked up with each new project...plus you can pass them between projects and retain the formulas. Right now I have my jobs set up to calculate over 190 materials with less than 20 record formats. I also use formulas in my worksheets that call data from walls windows and doors.

The worksheet part of VW is limited in its function but can provide very detailed results if you are patient with it being cranky. Query all of the staff at VW and for that matter the user base and you will find very little...however there are some users out there who have been working with worksheets for a very long time...I have foung these guys to also utilize script to enhance the worksheet functionality.

Keep your chin up and plug away...if you are stuck on finding some info post it here and see if anyone has encounterd the animal...but don't hold your breath...you are in sparsly populated territory.

Pete A.

Share this post


Link to post

There is a very good guide (by Julian Carr) to worksheets in Janis Kent's The Works Manual 8.5. She no longer seems to publish new books, but that might still be available from Improbability Press.

Share this post


Link to post

Thanks to both Pete and Petri for responses.

Re Pete's comment, when I purchased INFO EDITOR, I learned from the author that VW 12 no longer utilizes records for spreadsheets (THIS OUGHT TO HAVE BEEN IN BOLD PRINT VW!!!) and further, that INFO EDITOR does not work when an item has a record on the Mac. I ought to get credit for that one as it took me four hours to learn it and let the author know. The reason for no records has something to do with preference sets. Apparently when you migrate a preference set from an earlier version, record data became uncontrollably convoluted so VW abandoned this approach. You will note that windows and doors in VW 12 are plug-ins editable from the OBJECT INFO PALETTE.

I am also wondering how to (easily) create an association between an ID LABEL and a spreadhseet column for example using a USER DEFINED label. This has something to do with the formulas you can read in the DATABASE HEADER in any spreadsheet!?

Share this post


Link to post

I'm not sure what you mean by saying that "VW 12 no longer utilizes records for spreadsheets".

Share this post


Link to post

I believe that all you need to have with your user defined label is an index field to identify the specific type lablel.

As far as Records go I think Blink may be right as you do not seem to be able to associate any other Record with the ID Label Tool other than the Default Equipment Record which is also applied automatically when to attach an ID Label to a window, wall or door.

Now that is not the case for record formats which can still be attached to any object to enhance the data associated with the object.

Pete A.

Share this post


Link to post

I will second Blink's frustration with the worksheet in VW 12.5

I have spent the better part of two weeks trying to get this thing to just create a simple Bill of Materials and I finally ended up exporting what limited data I was able to create into an Excel document (separate by comas) and that enabled me to at least import the thing into a program that has some help features. You're right, if you're not an Alpha-Geek, there's no way you're going to figure this out on your own. I've heard the reference to that earlier release book by Janis Kent is supposed to be the only known written record of how to navigate this thing. Good luck and maybe someday the good folks at VW will spend some time imparting some of their well guarded knowledge on this topic.

Share this post


Link to post

Has either of Messrs Blink & Mac bothered to read the VW help on the subject?

Now, if all you need are Excel-functions (and being the expert on those, could Mr. Mac describe his favourite methods of querying an Excel table with the so-called database functions - I certainly could learn from the master), they are pretty easy when an equivalent exists. VW's worksheets do not have too many statistical or financial functions, but they can add, subtract, multiply and divide. I think they are perfect!

Share this post


Link to post

I'll continue a bit, despite public demand.

The problematic worksheet functionality referred to seems to relate to database queries, a.k.a. reports, and also to the way PIOs work.

Databases are simply not simple. One can't expect a VW help file to teach one how to query a database & how to construct a query with ANDs and ORs. (Especially if one does not even read the Help.)

A plug-in object is a program. It uses its defined "parameter record" for user input and occasionally for output, too. It cannot guess that a user has attached another record to the object, not to speak of guessing what values should be read or written to which field of which record.

However, one can attach one's own records to PIO instances and query on & report them.

But of course I don't know what all the fuss is about.

Share this post


Link to post

I'll say this much...

With a worksheet you can calculate the length of a hip or valley with very simple formula. If you really want to get into this worksheet thing it would be nice for some kind of information telling what data parameters are held in objects that can easily be reported in a worksheet. Right now there is only microscopic amounts of info available when you export to script and that info really doesn't give the field data names to try a collect.

I think everyones frustration is born from having been exposed to excellent spreadsheet and database programs and then wanting to have VW worksheets do the same.

Pete A

Share this post


Link to post

Petri, you have an attitude and it's not appreciated. The words 'have I bothered' is judgemental which makes your comments undesirable and useless. The reality of resolving an accurate spreadsheet is indirect and not too clear and problems that arise have no clear answer. If you could not speak Greek and I placed the Greek alphabet in front of you, you would still not be able to speak Greek right?

With that said, a VW tech told me that VW 12 abandoned records for database use because of migration issues. The English cannot be more clear than that. Records conflict with INFO EDITOR on the MAC as well and when deselected, it works!

A record has an associated text file and a PIO utilizes a macro. That's apples and oranges to a computer processor! The current spreadhseets read the database data when "INCLUDE ON SPREADHSEET" is selected and offers an ID LABEL option. You can also create an ID LABEL separately which it will read. When it does not, I don't know why. Do you?

My request to VW is for the publication of the DATABASE HEADER formulas. The techs know them by heart and can tell you when you ask them, but this is not so good at 3AM with a deadline in the morning.

Lastly, EXCEL spreadhseets destroy both the format as well the smart spreadsheet capabilities. With the intention to use VW properly, it is preferable to know how to make it work without having to export in frustration!

Share this post


Link to post
My request to VW is for the publication of the DATABASE HEADER formulas. The techs know them by heart and can tell you when you ask them, but this is not so good at 3AM with a deadline in the morning.

Obviously you have not noticed that section called "Retrieving Object and Record Information" in the VW help lists them. This and section "Worksheet Functions" are the comprehensive documentation of the Unpblished Secret Codes.

Obviously they could make it even clearer that the criteria are also "database header functions" and that functions can also be used as "database header functions". Perusing at least one of the predefined reports supplied with the program might have revealed that.

If you would not have "an attitude" but would ask (in a polite and professional tone) a question, you might even get answers.

I still don't understand what you or the NNA tech mean by "abandoning records".

Share this post


Link to post
A record has an associated text file and a PIO utilizes a macro. That's apples and oranges to a computer processor!

I have no idea what you are talking about. I have never seen any "associated text files" for VWs database records. The data are definitely stored in the VW file.

Computer programs, such as macros and scripts, are very good in processing text and numbers (even text files), but of course data is a different concept than a program.

It may well be that some of NNAs earlier PIOs used an "explicit" data record and pushed data into that: I seem to recall "Window Record", "Door Record" and so on. Now the same information can be retrived without them.

What exactly it is that you can't get done?

Share this post


Link to post

Petri, you are not helpful at all. I am NOT REFERRING to WORKSHEET FUNCTIONS that calculate various object(s) data, I am referring to the formulas that generate associations with specific object data such as door width or sill height. You seem motivated to impress us with what you know and to harass rather than contribute. I will get my answers elsewhere. The questions are clear and my comments are sourced. What I do not know is qualified in the very first post. Please stop botherng me.

Share this post


Link to post

Pete, what is an index label and how do you create one of these creatures? Somehow I think we are inventing words to describe our experiences. By index label, do you mean the identification label that results when you use the ID LABEL Tool, or similarly, a window or door dialogue entry? If so and according to VW techs, this is NOT a RECORD by the way.

Share this post


Link to post

my essential manual has an introduction to worksheets. It?s not everything that you need to know but for those that want to get started it?s good. I believe that worksheets are much under used in VectorWorks, so I cover them in my essential course and in my Essential manual.

For more info look at:

http://www.archoncad.co.nz

Share this post


Link to post
Please stop botherng me.

Dear me, dear me...

What's wrong with formulae of the automatic reports, such as

=(Window.Elevation)

which, if elevation is set to be "Sill of Window"

reports sill height.

Why doesn't

=(Door.DoorWidth)

work for you to get the width of a door?

You seem motivated to keep everyone guessing about your needs.

Share this post


Link to post

Blink,

Object Data information in a drawing has some information carried as a parameter which can be retrieved with a formula from in a worksheet. That data...for example, roof area, is the surface of the 2D poly which underlies the 3D model. The 3D surface data is not available or held as parameter with the object, however with a formula can be calculated with the formula =1/COS(DEG2RAD(ANGLE))*(AREA/144) to give you total (US)square feet. But just retrieving this info to a worksheet does not provide me with any reasonable data to create an understandable report. So what I do is attach a record format(see below) to each roof plane that provides info to help sort and sum-up the surface data.

Records1.jpg

Now the data makes sense to the real world but most important it is reliable and accountable back to the model.

Records2.jpg

You can see where I have made use of a user defined fields to create an index, catagory and description for sorting the data. With the record format I can also introduce additional numbers to assist in finding data that is not available as a parameter of an object...like the length of a roof hip in order to find the amount of hip cap shingles to order...

...by placing a 2D line to represent the hip...attaching a record format with field data then using a complex formula =SQRT((('013 RHVS'.'C HEIGHT')*('013 RHVS'.'C HEIGHT'))+((HEIGHT)*(HEIGHT))+((WIDTH)*(WIDTH))) the 3D length can be calculated and the line is now identified and accountable in the drawing.

I hope that I have explained without boring you on how I use the word INDEX....which if I were to try and sort windows and doors I would do the same....index them by type, catagory, location....and so on with an attaced ID LABEL user fields.

Pete A.

Share this post


Link to post

Pete,

thank you for this HOW TO and your time. Though I will resist the complex formulas in favor of straightforward PIO links for window and door schedules, my real experience was that 'no reasonable data to create an understandable report,' appeared or no data at all resulted due to an inaccurate formula.

Several simple questions....

1. Is the default (FIELD NAME) in the EDIT REPORT DIALOGUE the default header in the spreadsheet? I am experiencing a mental disconnect between the actual object, the desired data to be displayed and the speadhseet result, and more specifically, WHERE to enter the data to achieve the desired result. Then the formulas kick in and it drives me to drink!

2. Creating a new FIELD NAME enables current data (PIO data for example) to be displayed in a corresponding column (such as a USER FIELD) yes?

3. Have you used INFO EDITOR? Tracking down the corresponding data requires visual recognition from one of the say 100 columns. No search is available and further, the columns use the DATABASE HEADER name rather than a FIELD NAME which if incorrect, finding the corresponding column header is pure guessing to get it right in some instances.

INFO EDITOR is excellent, besides this one issue, and works in both directions (updating INFO EDITOR updates object data directly), unlike standard VW spreadhseets, which must be updated at the source and recalculated.

Share this post


Link to post

Petri, are you listening to yourself? Do you realize that your delivery is wholly self-serving and wreaks of someone starving for some sort of recognition, apparently any sort?

Stop bothering me is a clear request that matches my needs that you clearly understood, yet you persist. Get the picture? Some advice (for free), try bungy jumping or pierce your nipples, stalk a celebrity or if you are an architect or designer, design something cool and enter it into a competition! This will be undoubtedly be far more rewarding than your current approach that begs for specific questions that match your no doubt mental superiority so that you can further grace us all with your shining genius. On the other hand, maybe you live on the dark side and need pain instead, or maybe you ought to put your Mom on line so we could have a little chat? I'm here for you my friend. Best wishes...

Share this post


Link to post

Well, that is not a particularly nice way of saying thanks after I have

- told you where the worksheet functions are documented; and

- given you the exact formulae for retrieving the information you wanted.

Self-serving? Hardly. I knew these things already.

Share this post


Link to post

Speaking of formulas, does anyone use the wall area functions and are you getting accurate numbers returned? I just recently started to try my hand at the worksheets and double checked the results by actually measuring the surface area of a wall and found the results displayed in the worksheet to be incorrect. I talked to a tech at VW who was discovering the same inaccuracies. For example, a wall measuring 8 feet high by 10 feet long returns an area of 98 s.f.

The tech said he would check with the engineers and get back to me.

Maybe the waste factor has already been added in...

Share this post


Link to post

Interesting, Michael. There was recently a lengthy Junior Highschool debate in the VectorScript section regarding volumes: "THAT VECTORSCRIPT REPORTS WRONG VOLUMES". The proponent claimed that worksheets get the volumes right, VectorScript gets them wrong. The opponent found both of them reporting correct volumes.

With areas, the situation seems to be rather strange: the thickness of the wall affects both gross & net areas in worksheets. It seems that half of the area of edges is added to the "actual" face area. Or something: I get an area of either 120 m2 or 98 m2 for a 1 m thick wall of 10 x 10 metres. The average height of it is, according to NNA, 12 metres...

(What is "actual"! It's different for the carpenter, plasterer and painter and for the last two, depends on whether the wall is internal or external.)

With areas, the situation looks really weird in VectorScript, too, and these oddities may have the same fundamental reason as those in worksheets.

Share this post


Link to post

Blink, these forums require a little juice... more often than not... Petri is da pushermon : )

Needless to say, giving authoritative & definitive helpful responses to detailed questions

is never easy and often requires considerable work retrieving resources,

then double checking the reply by duplicating the exact scenario in the post.

I wanted to chime in but was too occupied ... thankfully Panthony had some quality time

to dedicate to this important topic. wink.gif

Share this post


Link to post

In reference to the area calculations for walls.

Here is what I found....documented as a known and confirmed bug as discussed with engineering at VW.

When I am working with formulas for surface area in building any estimate calculating system I prefer to use a small wall section which I can calculate in my head the quantites....so I used an 8' high by 10' foot long 2" x 4" wall. When I called for the surface area in a worksheet I was floored to find out that the SF was slightly higher than 80 which lead me down a path of depression in trying to figure out what I was doing wrong.

But all of my anxiety was put to rest when I found that if you apply textures to a wall with the selection of "USE LEFT TEXTURE FOR ENTIRE WALL" VW calculates one side edge and one top edge in the overall surface area.

Baffled.

Check to make sure that you are not applying your texture using the Wall Style Texture Check Box of "Use Left Texture For Entire Wall".

Hope this helps someone from throwing thier computer out of a window.

Pete A.

Share this post


Link to post

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×
×
  • Create New...