Jump to content
Tom W.

Worksheet question from a beginner

Recommended Posts

I’ve been taking my first tentative steps into the world of worksheets. I’ve made a worksheet that reports on material quantities in walls using the COMPAREABYNAME function. In the case of timber cladding/siding I then convert the sqm total into a linear metre value based on the type of cladding/siding being used. So in the case of 200mm featheredge board (bevel edge siding) I multiply the sqm in a separate cell by 5.71 which gives me the total linear metres for the cladding. Hurrah!

 

What I was wondering was, is it possible to record this sqm-to-linear-metres factor in the wall style somewhere rather than inputting it into the spreadsheet? So that if I use a different wall style that has a different type of cladding (with a different sqm-to-linear-metres factor) I won't need to remember to alter the formula in the worksheet in order for the linear metres to be returned correctly because the formula will somehow take the value from the wall style? The worksheet returns values based on the component name 'wood siding' so as long as there's only one type of siding in the file it doesn't matter what type it is.

 

My worksheet has only spreadsheet rows. I tried creating a record format that contained the 5.71 conversion factor in it + attaching it to the walls in the drawing then inserting a database in the worksheet + managed to report the total linear metres this way, but this doesn't really gain me anything. I want my '200mm featheredge cladding' wall style to have this factor baked into it. I saw some posts from people asking if it was possible to attach records to wall styles rather than to the individual walls but they didn't get any responses so I guess not... 

 

Any advice would be great. I have only the vaguest notion of how these things work so apologies if I'm asking something stupid. Just keen to keep learning now I'm on a roll!!

 

Thanks

Share this post


Link to post

Hey @Tom W.

 

I'm impressed with your first tentative steps!

 

So this isn't my area of expertise and someone more knowledgeable will no doubt have a better idea however you might be able to "bake" the data you need into a wall style via the data tab of the edit wall style dialogue. This data becomes IFC data which you can bring into a worksheet. I struggle with IFC stuff but did find an architectural report for wall styles that you could use to copy the worksheet formulas from.

 

There is a file called "Architectural Reports.vwx" and mine for 2019 is in "C:\Program Files\Vectorworks 2019\Libraries\Defaults\Reports_Schedules"

 

image.thumb.png.58237ebc8dc71453be27b4225607e1e0.png

Share this post


Link to post

Thanks so much @Boh I think that was enough of a nudge in the right direction to set me on the right path!

I did wonder about all the IFC stuff but couldn't make head nor tail of it but after your post persevered + I think I'm getting somewhere.

I went into the IFC settings for the wall style + found this field which looked hopeful + entered my 5.71 factor against 'component 1':

1273662533_Screenshot2020-12-17at22_31_31.thumb.png.13264716dd5998b9872ebe66586a5ce1.png

Then in my spreadsheet I used the 'GETIFCPROPERTY' function + managed to call up that value!

481933477_Screenshot2020-12-17at22_30_48.thumb.png.f1a37bec146dd6015a18cfca3a1bd71b.png

That's as far as I'm going to take it tonight but I'm pretty sure that's me sorted: the worksheet's reading the conversion factor from the wall style which is what I wanted. I just need to tidy up the worksheet + put the formula in the correct cell. Cool!

Thank you

 

Share this post


Link to post

Hey @Tom W. I'm pleased it made sense to you! Finding an IFC record that is formatted for numbers (rather than text) seems to be the way to go. IFC is a bit of a mystery to me. There doesn't seem to be any good help resources on it. This might be the nudge for me to try and get to grips with it.

 

Cheers

  • Like 1

Share this post


Link to post

Hmmm thought I was out of the woods but not so lucky.

I rearranged the worksheet but I couldn't get the two cells to multiply each other:

607471917_Screenshot2020-12-18at07_09_14.thumb.png.49506e0535efbe80c499845baa84b7ea.png

1038575373_Screenshot2020-12-18at07_09_21.thumb.png.78abe9ee9a66776f04859a804fe59f06.png

I took on board what you said @Boh about 'an IFC record formatted for numbers (rather than text)' + thought that might be the issue. I realised the 'Component 1' field I'd chosen was probably for text so I picked another field - 'NetVolume' - which was for numbers + used that instead. But I still got the error message...

 

If anybody has any ideas I'd be really grateful. It's not that big a deal - I'm really quite happy inputting the values directly into the worksheet at the end of the day, and I certainly don't need to get involved in the whole IFC thing for what I do - but the fact is, against all odds, I've become a bit of a spreadsheet nerd + it would just be cool to better understand why things aren't behaving as my very limited experience would suggest it ought to... If I type 5.71 manually into a cell I can multiply it by 170.7 sq m no problem. But if that 5.71 (or 5.710000 in the most recent case) comes from the IFC data it doesn't like it.

 

Many thanks

 

Share this post


Link to post

It looks like ifc records might be set up differently. Hopefully someone who knows IFC will jump in here.

 

There are also some clever worksheet functions that can pull the numbers out of text formatted cells. @Pat Stanfordor @michaelk do you guys have any suggestions? (I’m now asking the real experts,,,)

  • Like 1

Share this post


Link to post

Pat will definitely know this, but I'm guessing that the IFC record fields are all text.  Try =VALUE(GETIFCPROPERTY((T=WALL)&(L='Design Layer-1'),BaseQuantities.NetVolume)

 

The Value function will turn a piece of text into a number when possible.

  • Like 2

Share this post


Link to post

Holy mother you cracked it  @michaelk  thank you!!

420649864_Screenshot2020-12-18at19_44_31.thumb.png.4916af035748f9f14279d658f93bcbe5.png

I'll be nominating the two of you for the forum-poster-of-the-year awards thanks so much! 👍

Tom

 

Share this post


Link to post

Nice one!

 

I “discovered” worksheets a few years ago and they are now a central part of how I both present and edit a lot of my drawing work. @michaelk has done a few videos which I found very helpful and I’m sure still would be for anyone wanting to take it further.

 

Now with BIM becoming more integral new VW tools like data tags, worksheets have become even more useful.
 

There is always more to learn and I’ve found some very helpful people on this forum.  Cheers

  • Like 1

Share this post


Link to post

I think it depends on what you are going to do with the data. If you need to do IFC exports, then IFC data is the only way to go. If you think you might want to reuse the components at some later time in an IFC export then it is probably worth it to go with IFC data.

 

If you know that you are never going to do an IFC export, then you might want to consider just using a custom record format containing the fields you need. Custom Records are actually easier to access and manipulate in worksheets and in the OIP. They are directly editable. With IFC, you get the IFC record and then you have to select one or more Psets (I think of them as sub records) and then you get to the fields. So you can't use a standard Record.Field formula to access the data in a worksheet column. You have to use the more complicated IFC data access functions. IFC data is also much harder to use as Criteria than just a Record.Field.

 

So, as with almost every answer regarding Vectorworks, the answer is another question. "What are you trying to do and why?"

  • Like 1

Share this post


Link to post

Hi @Pat Stanford thanks v much for your comments when you say to use a custom record format what exactly does this mean? I saw the term somewhere else + wasn't sure what it was referring to or whether it was actually anything different to a normal record format, which is kind of 'custom' by definition, but which you can only attach to instances of a wall rather than the definition. Can a custom record be attached to a style? so that every instance of that wall in the drawing contains that data.

 

This is all I was wanting to do so the whole IFC thing wasn't really relevant in terms of looking to export data to anyone else, I just want to record data inherent to a wall for my own use. If I create a symbol + can attach a record with all sorts of data about that symbol to it but in the case of a wall style, it seems I only have the option of the IFC data entry. I'd be interested in attaching data to the wall about the different materials involved (cost, manufacturer, supplier, stock sizes, lead in times, etc) + perhaps notes regarding the wall construction (the procedure involved, guidelines for certain products, etc) - but perhaps this is precisely what the IFC format is for? But the thing with the IFC format is that (as far as I could tell) you are restricted to the format provided + can't create your own fields. So in my case I just wanted to add a 'Sqm to linear m conversion rate' field + a note explaining that this related to a certain size/type of siding.

 

Any thoughts be most welcome Pat thank you.

 

 

6 hours ago, Boh said:

I “discovered” worksheets a few years ago and they are now a central part of how I both present and edit a lot of my drawing work. @michaelk has done a few videos which I found very helpful and I’m sure still would be for anyone wanting to take it further.

 

Now with BIM becoming more integral new VW tools like data tags, worksheets have become even more useful.
 

There is always more to learn and I’ve found some very helpful people on this forum.  Cheers

 

Well said @Boh. This forum has been such an amazing source of info. I've seen some of Michael's Santa Barbara videos but not the worksheet ones I'll have to check them out. Jon Pickup I thought was very good on worksheets too. I wish I'd taken the plunge + learnt to use VW properly earlier (been a user since 2008 but only in a v basic 2D way: one step up from pen on paper). 9 months ago I thought perhaps it was time to sit down + finally learn how to do 3D + here I am still at it, not got out my seat yet! But it's been such an enjoyable + satisfying experience. It's an amazingly powerful tool

  • Like 1

Share this post


Link to post

Tom, I did 3 webinars for VW - probably 6 years ago, so there have been some improvements :-).  But, other than improved UI, the basics are the same.  I haven't checked to see if they got moved to the Univeristy, but I suspect they are too old.  Send me a PM if you're interested in watching them.  And you're always welcome to join our user group meetings!  Or if you want to swim in the big pond, check out Pat's Los Angeles user group meetings.

 

Pat was talking about adding a record format to the wall.  Image being able to tack a spreadsheet full of information to any object.  That object carries that spreadsheet around with it and you can use a worksheet database to ask any object about what information is tacked on to it.

 

That's exactly what is happening with door and window schedules (except that spreadsheet of information is actually built in.  It's used to create the door or window). But you can add any additional information that is useful or amusing to you to anything in the drawing.

 

I just checked the University.  VW now has an improved webinar featuring someone whose name I didn't catch, but he has a very pleasing British accent.  So already much improved over my old webinars.

 

https://university.vectorworks.net/mod/page/view.php?id=631

 

Check that out and post back with any questions.

Share this post


Link to post

Thanks @michaelk  much appreciated

 

Does Pat have videos online somewhere?

 

So when you say attach a record format to 'the wall' you mean to a wall, in the drawing, rather than to the wall definition, the wall style? So I'd need to use select similar tool + select all the instances of that wall then manually add the record. I did actually try that + mentioned it in my first post but it was as much if not more work than just entering the data manually in the worksheet.

 

You mention doors + windows + this is exactly what I'm talking about: you can enter data in the data tab of a door/window style + it's baked into the definition. But I think these fields are record fields rather than IFC fields. With a wall style you only have the IFC option. With a door or window you have 10 user fields where you can enter your own data. And you can also attach your own record to a door/window style. Neither of which you can do with a wall style. This is the crux of the matter

 

Thanks for the VW university video I will take a proper look. That is Mark Mullany I believe - I remember he did an excellent one on title blocks so I've got high hopes for it! I am a bit of a connoisseur when it comes to VW training videos...🤪

Share this post


Link to post
9 hours ago, Tom W. said:

Does Pat have videos online somewhere?

 

If he does, he's keeping them secret.  The greater tragedy is that years ago he did a podcast with Dan Jansenson - an architect who literally wrote the book on Renderworks.  But his professional triumphs keep him too busy for such things these days.

 

You are correct about wall styles.  There are no convenient user fields in which to store data like doors and windows.  However, I was able to attach a custom record format to a wall style through the IFC settings.  Right about here is where my understanding of IFC data runs out.  Let me play around with it to see what the magic incantation is to make it appear in a worksheet.

Share this post


Link to post

Ha ha!

 

20 minutes ago, michaelk said:

I was able to attach a custom record format to a wall style through the IFC settings

Yes I realise now I actually did that too but I didn't take it any further. I wasn't fully aware of what I was doing. I attached a custom record to one of the instances of the wall in the drawing, then when I went into the IFC settings for the wall style it was there when I opened the 'Manage Custom Property Sets...' dialog + I could check it + add it to one of the data sets. My record is 'featheredge cladding':

1773689782_Screenshot2020-12-19at17_33_26.thumb.png.92c26601b163a02712123205af88d6f7.png

 

So now thinking about it now this is kinda what I was after: it means I can find that record when I do a worksheet + report on it. Cool! But feels a bit of an arcane way to attach a record to a wall style: draw the wall in the document, attach the record to the wall, then find the record in the IFC settings... What happens if I then remove the record from the wall in the drawing? Will it still be there in the IFC data? Is the record actually contained within the wall style as part of the resource?

 

So if you could just find all that out + let me know that'd be great...! 😁 I'm joking. I will play around this with some more but yes please if you can shed any more light on the matter that would be amazing. Thanks for your input so far

 

Share this post


Link to post

Ok I've played around some more. I was wrong: if you have a record format in the file it's available for selection as a custom property set. It doesn't have to be attached to a wall object in the drawing first.

 

But what I can't seem to do is get the data I enter in my record fields in the IFC Data dialog appear in the OIP for each instance of that wall in the drawing. I get the fields showing up but they're empty. If I type in something in the 'Description' pane of the Data tab for the style then this entry appears in the OIP each time I draw that wall, but not anything I type in the 'string value for the selected data set' box in the IFC Data dialog for any of the data sets. Perhaps to do with the adjustable-parameter-slider symbols next to them but I can't change these to by style.

 

So that's probably enough of a diversion into IFC for me so unless you or @Pat Stanford can come up with anything more. I changed my material take-off worksheet so that there's a row for each + every different component so the sqm-to-linear-mitre formula is specific in each case + there's no need for the worksheet to read any data from the wall style. But still be interested to know if you find a way of attaching data to a wall style.

 

Thanks for all your help been quite an introduction to worksheets 🤪

 

 

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.


 

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...