Virtual Building - Material List, by Karl Ottenstein

[Djordje]

Glad the quickie property script exploration was useful, Geoff.

I agree with David as far as using the Interactive Schedule for things such as the window schedule example that you give ... and for most day-to-day work other than BOM.

This thread started with a question about a getting "studs, plates, blocking, header info, etc." as a BOM which is quite different than a schedule of parts (doors, windows, etc.).

ArchiCAD provides tools for both ... and I suppose the thing I would say in favor of having both tools (Calculate and I.S.) is that they work the same on both platforms, Mac and PC ... so I give credit to GS for that.

Generating a BOM involves what are called 1-to-many mappings in the database world. We take one wall object (in the simplest case), and want to generate multiple lines/components from it as illustrated in the screenshots that I posted for studs, plates, wall wrap, sheathing, etc.

Sometimes a BOM involves 1-to-1 mappings or even many-to-1 mappings as in a window or door schedule where we consolidate similar parts into a total quantity of those parts.

OK. Now here's the scoop.

The Interactive Schedule produces listings for 1-to-1 and many-to-1 mappings ... and indeed, those can be quickly exported to Excel for further formatting there (and OLE insertion into PM). Personally, I use all of the options available for I.S. - sometimes I place a graphic schedule in a workspace and get it into PM via a view, sometimes I export to an external program for cleanup.

But, what about 1-to-many mappings? How do I get a component list for my wall, floor, roof, etc.? Let's not even talk composites where the issue become quite significant - let's stick with the stud wall of the originally posted example that was responding to muttlieb's question.

Well, we saw that property scripts handle 1-to-many quite easily - with code the spits out the desired components. There is a certain level of complexity involved (coding, and the fact that there isn't a good booklet available on how to do this in practice) ... but as mentioned above, once done, it will work on PC and Mac.

Can this be done with the I.S. and Excel and would it be easier? Yes and no IMHO.

Excel alone:

The same formulas / code / logic that determine the component counts in the GDL property script must be in the external application. Can this be in Excel in a way that you could generate a BOM in Excel alone? Yes (because in theory anything can be computed in Excel) ... but you wouldn't want to do it as it would involve more complex Visual Basic programming to produce the BOM in appropriate rows/cols of a new spreadsheet than the GDL in the original property script....a level of Visual Basic skill beyond typical Excel users.
(You would be taking a single line exported from the I.S. that gives the wall type, fill type, area, length and height and computing all components from that information so that they appear on multiple lines in a BOM sheet.)

Excel with Access:

Short of something custom/expensive such as Timberline which I haven't had the opportunity to use, the combination of Microsoft Excel and Microsoft Access provide the most powerful solution on the PC side. Export the I.S. data as Excel data that is linked as tables into an Access database. Within Access, various materials and specifications tables, appropriate queries, custom Visual Basic and powerful reporting tools could let you generate anything your heart desires. As the model changes, simply re-export your excel tables ... and re-generate the Access reports.

Again, this requires a skill set that most users lack (why should an architect/designer know these things after all!), so for the majority of people, I still think that property scripts are the way to go.

I'll make one other comment so that I can cross-post this response onto two other related threads. The ODBC driver available from the GS developer center provides a way for a Microsoft Access database application to query the PLN file directly - bypassing the need to export Excel sheets/tables as in the scenario above. This of course leads to the ideal scenario, but requires the most technical skills.

[MS Access is only available for Windows. I have no experience with Mac database applications; presumably you could do similar things with FileMaker Pro 6.]

To repeat myself - these last complex alternatives are only things I would consider for 1-to-many mappings (BOM) and when (a) you do not need cross-platform support and (b) you have the necessary skills set. Otherwise, use property scripts. For window, door, furniture, etc. schedules, just use the Interactive Schedule and either place the schedule, or take it into Excel for further cleanup/annotation.

HTH,
Karl