Boh Posted August 29, 2018 Share Posted August 29, 2018 Hi I'm trying to create a worksheet that lists the area and volume of earthworks for a project. I have used the existing/proposed site models to get general cut / fill volumes however I don't want to model the dtm to reflect the earthworks required for carparking, foundations, piles etc. Instead I have created space objects with the correct area & heights of these elements. The worksheet uses the space objects criteria to bring the heights, areas, volumes into the worksheet. It looks good except the "totals" aren't summing the values for the volumes, instead just summing the number of entries. Any suggestions?? Quote Link to comment
Pat Stanford Posted August 29, 2018 Share Posted August 29, 2018 That probably means that the area and volume are defined as Strings instead of numbers. I don't have time to check the record format right now. Take a close look and see if there is an alternate version (maybe something like 11_Volume) that is formatted as a number/dimension instead of text. Quote Link to comment
Boh Posted August 29, 2018 Author Share Posted August 29, 2018 Thanks Pat. I don't think there is one for volume. I found another one for area which worked as a number rather than text but not one for volume. Will double check again tomorrow. is there any way to edit record format of a PIO like this? Quote Link to comment
Pat Stanford Posted August 29, 2018 Share Posted August 29, 2018 Space.Volume and Space.GrossVolume should return numbers. Space.11_Volume returns a string. No there is not a good way to edit the record format. You can go to Tools:PlugIns:PlugIn Manager and view the record format and even change the field names and defaults, but the next time NNA updates the program and edits the space object you will lose all your changes and have to make them again. If you need more stuff, you are better off attaching a second Custom Record format. This thread has a script that will show you all of the fields in a PIO record and their types. I linked to the latest version, but you probably should scan the thread to see how to use the script. Quote Link to comment
Boh Posted August 30, 2018 Author Share Posted August 30, 2018 Thanks Pat. Indeed, in the create report dialogue there is an alternative option : "Gross Volume Number" which provides the record "'Space'.'Gross Volume'. So I now have a nice Earthworks Calcs Spreadsheet: Excellent!! I tried importing the PIO worksheet script you linked to however had some issues. Have posted a comment in that. Quote Link to comment
reGenerate Design Posted December 27, 2019 Share Posted December 27, 2019 Hi Boh, I'm working on a similar application but I don't quite know how to make my worksheet recognize cut and fill values as numbers and not strings (as it seems it cannot use strings for making calculations. I see in your worksheet above that your cut and fill values are adding up in a totals row. I have used the "Create Report" option in Tools to generate a database row that shows only site models and provides some field information about the site model (row 7 in my worksheet below). When I try to do calculations on these site model values, I get unexpected results. How are you pulling your cut and fill values in your worksheet? Thanks so much , Adrian Quote Link to comment
Pat Stanford Posted December 27, 2019 Share Posted December 27, 2019 Many of the VW objects have multiple fields where values are stored in one as a formatted string and another where it is stored as a number. Send me a file with an object and your worksheet and I will try to find it for you. Or you can just use the =Value() function to convert the string to a number you can use in a calculation. Something like: =Value('DTM6"."Net C&F Volume"). Or you may have to leave the basic formula to get the value in a cell and add a new column with a formula of =Value(B7) Do you have the cell formatted to display the units? or is that actually part of the string coming from the object? If it is coming from the object you would have to do something like: =VALUE(SUBSTRING(B7, ' ', 1)). Where ' ' is a space to indicate the end of the number portion and 1 indicates that you want the first substring. Ask again if this is not clear enough. Quote Link to comment
reGenerate Design Posted December 27, 2019 Share Posted December 27, 2019 Hi Pat, Thanks so much for your response and taking the time to get back to me. I've tried the suggestion you gave, but it's still not seeming to work. I've attached the file as you requested, and truly grateful for you looking at this, and hopefully this thread will help others as well. Reeves site model retaining wall.vwx Quote Link to comment
Pat Stanford Posted December 27, 2019 Share Posted December 27, 2019 @reGenerate Design I don't have VW2015 set up to be able to test, but the following worked to get the text value as a workable number in VW2020. =VALUE(SUBSTRING(('DTM6'.'Net C&F Volume'), ' ', 1)) If you then format the cell at Dimension Volume it will convert to whatever volume unit you have set in Document preferences and show the units while still being stored as a number. It is possible that SubString (and maybe even Value) were not added to worksheets until after VW2015. In which case you are out of luck until you upgrade. Try and insert a function into a cell from the menu and see if SubString and Value show in the list. Quote Link to comment
reGenerate Design Posted December 28, 2019 Share Posted December 28, 2019 Hi Pat, thanks again. When I enter that formula, my worksheet just returns #VALUE! (as in the attached image). I'm not very knowledgeable of using formulae in VW worksheets yet, so maybe I'm missing something in the syntax. Thanks so much for your help again. Quote Link to comment
Pat Stanford Posted December 28, 2019 Share Posted December 28, 2019 That most likely means that the functions were not available in 2015. There were a lot of enhancements to worksheets around VW2018 (17?). It looks like you will have to manually type in the numbers or update VW. Sorry. One last option. In the Worksheet Function list is there an entry for RunScript? If so I can probably give you a script that will return the value. If no RunScript you are truly stuck. Quote Link to comment
Peter Vandewalle Posted December 29, 2019 Share Posted December 29, 2019 I think “value” was available way before 2015. “Substring” most likely wasn’t. Quote Link to comment
reGenerate Design Posted December 29, 2019 Share Posted December 29, 2019 Thanks so much to you both for your responses. Quote Link to comment
Recommended Posts
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.