Jump to content

Worksheet pushing cell value to "User Field 2" in an object / other worksheet


Recommended Posts

Good afternoon folks,

 

I'm wondering if something is possible. Using the worksheet created by @Wes Gardner (THANK YOU!) I've been able to have VW figure out the occupancy loads using the space tool. Under "User Field 1" is the # of people per sq m (Canada) which I've added to the Space OIP for ease of input.

 

What would be great is if after the # of occupants is calculated in the worksheet, if that number could be pushed to "User Field 2" in the Additional Info section of the object. The reason for this is to allow the data tags used in the plans to show the occupancy for that one room. In the attached screenshot, I'd like to take the value from column H and put them into the "Additional Info 02" and have the data tag pull from that field. 

 

Maybe this isn't the best way to have the data tag display this info? If any of you know of a better way to do it, I'm all ears!

 

The other half of this post is this. Can I have a worksheet pull a value from another worksheet? I'm very happy for the occupancy calculator worksheet that Wes posted but I'd like to make a simpler version to put onto the finished plans. One with three columns (Room number, name and occupancy load) and doesn't have all the columns needed for the calculations. Is there anyway to do this as well?

 

Thanks in advance for all the help!

 

- Dylan

Screen Shot 2021-10-08 at 12.14.32.jpg

Screen Shot 2021-10-08 at 12.25.23.jpg

Link to comment

 

On 10/8/2021 at 10:28 AM, Dylan said:

 

What would be great is if after the # of occupants is calculated in the worksheet, if that number could be pushed to "User Field 2" in the Additional Info section of the object. The reason for this is to allow the data tags used in the plans to show the occupancy for that one room. In the attached screenshot, I'd like to take the value from column H and put them into the "Additional Info 02" and have the data tag pull from that field. 

 

Yes there is a way to do it.  Maybe 2 or three ways.

 

The easiest way is to use a worksheet script to do the calculations and set the value for user field 2.

 

You could also run virtually the same script outside the worksheet, but then you would have a two step process:  1) run the script and 2) update the worksheet

 

With the script called from the database header in the worksheet all you have to do is update the worksheet.

 

In the attached file column E in the database is =RUNSCRIPT('Number Of Occupants in User 2').  There is a script palette in the resource manager called Space Worksheet Script.  In that palette is a script called Number Of Occupants in User 2.  It does the math you were doing in the worksheet and plugs the result into user field 2.  You can right click on the script in the resource manager and choose Edit if you want to see how it works.

 

Just make sure that that script is in the resource manager for the document and you can put the RUNSCRIPT in the database header and Bob's your uncle.

 

 

 

 

On 10/8/2021 at 10:28 AM, Dylan said:

The other half of this post is this. Can I have a worksheet pull a value from another worksheet? I'm very happy for the occupancy calculator worksheet that Wes posted but I'd like to make a simpler version to put onto the finished plans. One with three columns (Room number, name and occupancy load) and doesn't have all the columns needed for the calculations. Is there anyway to do this as well?

 

You can pull the values from other worksheet spreadsheet cells, but not other database cells.  In the attached example you can see a common use case - prices.  You can have one worksheet that is just a price list and you can call those values in other worksheets.

 

What you can't do is get a value from a database row (x.1 x.2, etc) because those rows are dynamic.  Adding a new space will add another row.  So you can't get the value from B4.10.  There may or may not be 10 space objects that meet the criteria to be in the database.

 

 

 

Your signature says you're on 2020.  I don't think data tags in 2020 (or even 2022?) could convert the text value in user field 1 to a numeric value to do the division and I'm not sure what would happen with a division by 0 situation in 2020 or 2022 🙂.

 

 

I'll attach an example file in 2022, 2021, and 2020.  

 

Let me know if you need the math adjusted.  It looks like you're doing this:

 

net area / occupancy load (user field 1)

round the result

if the result is less than 1 make it 1.

 

Occupancy Example v2020.vwx Occupancy Example v2021.vwx Occupancy Example.vwx

  • Like 1
Link to comment

Good morning @michaelk

 

That is a fantastic explanation! I just got a chance to apply it to they project file and it works beautifully! Thank you!

 

By writing the Occupancy Capacity to "Additional Info 2", I can call that info out on a different "presentation" worksheet for the info/notes sheet. It also lets me call it from a data tag in the plans. Works very well!

 

It's also great to see how to pull information from a non-database cell from a different worksheet. That (especially for pricing) will be very useful! 

 

Now it look into what the script means (I have a bit of coding experience) so I can properly understand how you created it. 

 

Thanks again Michael! It's so nice to have folks like yourself on the forums. I really appreciate the help!

 

Have a great day,

- Dylan

Link to comment

@Dylan Glad it worked.

 

If you do look into the script (if you have any coding experience you'll find it very simple) keep in mind there are two languages you can use for scripting in VW.  The one I use is based on PASCAL.  All the cool kids are using Python.

 

If you were using 2022 I would have also tried the Data Manager.  If it's not possible yet to do this with the Data Manager I suspect it may be in a year or two.

  • Like 1
Link to comment

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.

×
×
  • Create New...