Using VB Script in Schedule Formulas - Help Needed

[Anonymous]

Hello, I am in need of help with a project. I am creating a floor system, and using different schedules to evaluate the system. I have all the structural members I want created and added, along with a schedule for each. The problem is very simple, but I have no coding experience. I created a formula column that takes the inches of length, and multiplies that by the unit cost, and the result is the multiplied by the quantity. No problem there it works fine. and gives a valid result for material cost.

What I need to do now is create another formula column that can give an approximate number of framing members that will be required. On the schedule you can see that there are both Joists and Beams, because they are both cut from the same 2 x 10 stock, and need to be purchased together. How can I have this formula column express that the beams are 3 ply, so the length of 2 x 10 you need is actually to be multiplied by 3, but that the joists are only 1-Ply and the length is the same as the automatic property.

So to get the approximate number of 2 x 10 x 16 pieces of lumber I need for the Joists is easy, just divide the total length in inches of all of them by 192, which would be 16'-0" in inches. But what about the Beams? The inches wood in a 3-ply beam is 3 times greater than that in a joist.

Could an if/then statement evaluate the "Style" Column, and when it sees "2 x 10 Joist", it returns the length unaltered, and when it sees "3-Ply 2 x 10 Beam" it multiplies the value by 3?

I tried several times and just cannot know what is and is not a syntax issue, or how to sort it out.

Thank you

[David_W_Koch · Accepted solution]

You could do an If Then Else statement, or, if you have a lot more that two styles, perhaps a Select Case statement, but that could get complicated, and require updating if/when you create additional styles.

I assume that you have separate Structural Member Styles for the different members (one 2x10 vs three 2x10s) and that your style-based cost property reflects that the beam costs three times what a joist costs (or more, if fastening the three together adds cost).  What I would suggest is adding a style-based property that would indicated the number of individual members required to make up that style.  It could go in the Structural Member Styles Property Set Definition if you are willing to track which out-of-the-box definitions you modify, or it could go into a custom, style-based Property Set Definition that applies to Structural Member Styles.  I would make it an integer-type manual property, with a default value of 1.  Leave that value at the default of 1 for all Structural Member Styles that have one "ply".  Set it to 2 for 2-ply styles, 3 for 3-ply styles, etc.  Then add that property is as a multiplier in your formula when calculating the rough number of members needed.

[Anonymous · Accepted solution]

David, I did exactly what you suggested about an hour after posting this, and have to say I am very pleased to have organically arrived at the same solution as you. You've helped me quite a bit in the past because you thoroughly answered someone else's questions.

I imagine you hear it often but thanks 

[Anonymous]

LOL, I accidentally gave myself a solution but didn't mean to.

[RidgewayPlumbing]

I have a similiar problem. I created a schedule which tell me all the pipe length and an ID number that I assigned to the the different sized pipe.

What I am trying to do is show One ID with the total amount for each pipe. can you help. See Screen shot.

[David_W_Koch]

You could add a quantity column to your schedule, which would then collapse all identical rows into one row, with the number of such rows indicated in the quantity column.  You could then add a formula column, and multiply the length by the quantity to get to total length of those elements.

But, you will not be able to combine elements with the same ID number but differing lengths.  The best you can do is group your elements by the ID number, and then include a subtotal for each ID number.

[RidgewayPlumbing]

Thanks for the answer. Is there any other way of getting this done in the autocad mep environment automatically ?