bc Posted April 11, 2018 Share Posted April 11, 2018 (edited) Pat, SEE ATTACHED IMAGE My earlier posting on the community board led me to wonder: Is there a function I could put in the Number column of the Window Schedule (or wherever) that would allow the Window Schedule to include the doors listed in the Exterior Door Schedule? My architect likes to include them here because they are French and to be purchased from the mfg who makes the windows. Also for title 24, etc they can be overlooked by consultants. Would desperately appreciate your suggestions and time. The how to is just as needed as the what, I might add. It seems that the criteria for the Widths and Heights, etc also needs to be changed? Or is it best to just start out with a new worksheet newly created from scratch? Way beyond my ken, it would seem. Thanks Bradley Chase Edited April 11, 2018 by bc Quote Link to comment
TKA Posted April 11, 2018 Share Posted April 11, 2018 i have grappled with this issue. It seems to me like there is no way to mix windows and doors in a single spreadsheet. To go around it I just used window objects and named them as doors (D prefix as opposed to W) - sliding window as sliding door and bi-parting casement for swinging doors. This is exactly to have, like you said, all windows and doors by one manufacturer in one spreadsheet. The only issue I noticed is the sill details which are not compatible. But so far on almost all the projects we have in the office this is what we are doing and it works fine. In reality the whole division between window and door is an artificial one, given the current design trends what is needed is something like an opening type - there are just too many different types of door window solid panel items that just dont want to molded into traditional typology. Quote Link to comment
michaelk Posted April 11, 2018 Share Posted April 11, 2018 There are a couple ways to do it. One easy. One not. There is no limit to the number of databases you can have on a worksheet. So you can just start the window DB right under the door DB and use criteria to make sure you only select the doors you want. OR You could use IF/THEN/ELSE statements in the DB header to select from both windows and doors. I highly recommend the first option. Let me know if you need examples. Quote Link to comment
bc Posted April 11, 2018 Author Share Posted April 11, 2018 (edited) I totally need examples. Like the old John Muir Volkswagen Repair Manuals (A Step-by-Step Guide for the Complete Idiot). Seriously. The videos don't get me there. I have no idea how anyone knows what these functions are or the syntax or where to find them or anything. I would like to know how to. I have played with appending my window schedule with a door schedule and the schedule was appended but included every door in the file and doesn't let one delete rows. Thanks for your troubles. bc Edited April 11, 2018 by bc typo Quote Link to comment
Popular Post Pat Stanford Posted April 12, 2018 Popular Post Share Posted April 12, 2018 Let's see if this helps. A lot of explanation of a lot of concepts here. Ask again if some of this does not make sense. 1. The database section of a worksheet contains two primary parts. First is the Database Criteria. This is what controls what items get shown as subrows in the database. The second if the formulas that go into the Database Header Row. By appropriately combining these two parts you can do just about anything. The question is, is it worth the time to do it versus the inconvenience of living with the simple separated version. 2. The Database Criteria from the worksheet row header popout menu. Right click on any Row Header and you will get a number of options. The ones we are most interested in are the Database,Set Criteria, Edit Criteria, and Create/Edit Report options. The Database option converts the row to a Database row and opens the Criteria Editor. Set Criteria will delete any criteria that already exists and let you start from scratch. Use carefully. Edit Criteria will let you edit whatever criteria already exists. Create Report/Edit Report Let you not only set the criteria but also choose fields that you want to display. It is possible to create (and to need) criteria that are more complicated than the Criteria Editor can handle. In this case it is possible to manually edit the criteria in the formula bar, but that is beyond the scope of this post. In the example file I have used the Criteria Editor to put in criteria to show all doors and windows that have their respective "On Schedule" check boxes clicked. 3. Most of what you want to display in a schedule is data that is stored in a record. There may be some data you want to pull in using worksheet functions, but primarily, especially for doors and windows, you are going to be using data in the 'RecordName'.'FieldName' format. Notice that the two parts are surrounded by single quote marks. Technically these are not required unless there is a space in the name, but if you just get in the habit of putting them in you will go wrong less often. 4. Doors and Windows in VW are PlugIn Objects (PIOs). Every (most) PIO has what is called a Parameter Record. The Parameter Record has a record name that is the name of the PIO. So 'Door' for doors and 'Window' for windows. These two different PIOs share some field names, but since the data is a record.field combination, they are completely different data stores. It is also possible to create Custom Records with whatever fields you want. These are accessed similarly, but you use the names you have chosen for the Record and Field. The Record name must not conflict with any object in the drawing (i.e. since there is already a Door object, you can't name your record 'Door'. Try something like "MyDoorRecord" or "DoorRec" or "Oscar"). Once you have a custom record attached to an object everything else about accessing it through a database is the same as accessing Parameter Records. 5. The =IF function in the worksheet is your friend. The format is =IF('Test Condition', 'Test True Result', 'Test False Result'). In words this equates to "IF the Test Condition is true (THEN) display the Test True Result otherwise (ELSE) display the Test False Result. This is often referred to as an IF/THEN/ELSE function. So for this combined schedule example, I chose to use a 'Test Condition' of 'Door'.'IDLabel'<>'' The <> is worksheet speak for "Not Equal To". Following the <> are two single quotes ('') with no space between them. The back to back single quotes represent a text value of nothing. This means that when I use this Test Condition in an If function as long as there IS data (any characters) in the 'Door'.'IDLabel' field, then the result will be whatever is in the Test True Result. If there are NO data in the Test Condition (meaning there is no 'Door' record present or that the door has nothing in the 'IDLabel' field) then the Test False Result will be displayed. Look at the formula in cell E4. This shows the following formula: =IF('Door'.'IDLabel'<>'', 'Door'.'Config', 'Window'.'SashOperation') The Test Condition is there being data in the 'Door'.'IDLabel' field. If there is then it will display data in the 'Door'.'Config' field. If there is not data in the 'Door'.'IDLabel' field, it will display the data from the 'Window'.'SashOperation' field instead. You don't have to have a direct correlation between the different record formats, just that you want them to display in the same column. 6. If you need to be extra careful, you can nest IF statements so you can do multiple tests. So a statement like =IF('Test Condition 1', 'Test True Result 1',IF('Test Condition 2','Test True Result 2','Test False Result 2')) In words this would be IF Test 1 THEN True Result 1 ELSE IF Test2 THEN True Result 2 ELSE False Result 2. I have used this type of formula in cells C4 and D4 to check that if it does not have a door record that it does have a Window record and if not to return an error. In the sample file, the cased opening, while marked as being On Schedule, it does not have any data in the 'Door'.'IDLabel' field. The formula in C4 is: =IF('Door'.'IDLabel'<>'', 'Door'.'Height', IF('Window'.'IDLabel'<>'', 'Window'.'Height', 'ERROR')) Since the Cased Opening on the right of the wall does not have any data in the 'Door'.'IDLabel' field, it displays as 'ERROR' as I specified in the second ELSE portion of the function. For the other columns, it will display whatever data is stored in the Parameter Record. This double check is a good way to make sure you only have the objects you want displayed. 7. In Column A, I have combined the data from two different fields of the same record. Since this data is of type text you do this using the CONCAT (short for Concatenate) function. I first used an IF so I can enter separate formulas for Doors and Windows. I used the simple single record check format for this column. For both the True Result and False Results I used a Concat to combine the data from the 'IDPrefix' and 'IDLabel' fields into a single cell. The formula is: =IF('Door'.'IDLabel'<>'', CONCAT('Door'.'IDPrefix', 'Door'.'IDLabel'), CONCAT('Window'.'IDPrefix', 'Window'.'IDLabel')) 8. Database cells that contain nothing but a single ='Record'.'Field' function have editable data. That means if you edit the data in the worksheet, that data will automatically be transferred and stored in the Record of the object. This can be a great way to edit objects in a drawing without having to go to the OIP for each one. And if you need editable fields in a database, this is the ONLY way to do so. There is no way to enter data in a database cell unless it is linked to a Record.Field. If you object have User Fields (like Doors and Windows do) you can use those. If not, you can create a Custom Record and use that instead. This is often a good reason for keeping separate columns for similar data from different Records. Take a look a cell H4 in the worksheet. The formula is: ='Window'.'UserFld9' Since this is a single ='Record'.'Field' function, I can type into the database cell to change the value. Take a look at Cell H4.2 below compared to the above screenshot. That change was accomplished by selecting and typing in the cell. Now try and do the same in cell G4.1 You will not be able to type anything into the cell. Take a look at the formula in G4. =IF('Door'.'IDLabel'<>'', 'Door'.'UserFld9', 'N/A') Since there is the =IF function in the cell you can not manually change the data. Similarly look at the formula in cell F4: =IF('Door'.'IDLabel'<>'', 'Door'.'UserFld9', 'Window'.'UserFld9') The formula pulls data from both the Door Record and the Window Record and displays it in the same column. But, since this has an IF function you can't type into the cell. In either of the above cases, you can go to the object and edit the data in the OIP and when you recalculate the worksheet you will get the updated data. Or your could keep a column in this (or any other database) with the ='Record'.'Field' type of function to be editable. If it is in a different worksheet you probably have to Recalculate the worksheet to get the change to show. 9. There are a couple of ways to figure out the Record.Field combinations that you need. The most built in is the Create/Edit Report where you can scroll through the list and pick what you need. Or the advanced version of that which is create a new worksheet and have it create columns for every field (use the >> button to move all the fields over at once). That way you can see not only the names, but also the data that is showing. Or you can use the script I wrote that will let you place and instance of (almost) any PIO on the drawing (not in a wall), run the script and get a new worksheet that lists every field of the PIO and what the data type is. The following thread should take you directly to the latest (as of April 11, 2018) version of the script, but you probably want to skim through the entire thread for more information on how it works. 10. With all of the above as background, it is actually as simple as ABC to create a combined Door/Window Schedule. A. Create a database with criteria that will return every Door and Window in the drawing (or only the ones On Schedule, or only the ones on a certain Layer or Class, etc.) B. Determine what Field(s) you are going to use to determine if you want to pull data from the Door or Window record. C. Use IF functions to check on the type (Record.Field) of the object and return the correct Record.Field value to the cell If this is something that you will only use once, it is probably not worth the time. But, if this is something you can put in your template and reuse on every project the couple of hours it might take you to get this set up are probably worth it. Start simple. Set the criteria and put in just enough columns to identify the different objects/object types. Then add columns using the IF statements one or a few at a time so you can get all of the kinks worked out in the formulas. You can copy/paste the formulas by selecting the text in the formula bar. Once you select the data and copy, you need to hit ESCAPE to exit the field before you try to click into a different cell. If you don't you will still be editing the first formula instead of going somewhere else to paste the formula. Then click back into the formula bar to edit the pasted formula. I guess that is probably the short version. Ask again if you have any questions. My sample file is attached. Combined Schedule.vwx 4 2 Quote Link to comment
bc Posted April 12, 2018 Author Share Posted April 12, 2018 Wow Pat! That was heroic! It's going to take tomorrow for me to fathom and get to back to my work computer to realize. Yes I do plan on reusing this....and it will be worth the effort. Thank you. Bradley Quote Link to comment
bc Posted April 12, 2018 Author Share Posted April 12, 2018 (edited) Pat, I am working with a cannibalization of the script for the combined numbers. I have inserted "Height" where there used to be IDPrefix', 'Door'.'IDLabel and "Elevation" where there used to be IDPrefix', 'Window'.'IDLabel Here's your Function for the NO. Column A: =IF('Door'.'IDLabel'<>'', CONCAT('Door'.'IDPrefix', 'Door'.'IDLabel'), CONCAT('Window'.'IDPrefix', 'Window'.'IDLabel')) Here's my function for Column F: =IF('Door'.'IDLabel'<>'', CONCAT('Door'.'Height'), CONCAT('Window'.'Elevation')) This seems to work but the results are decimals to the nth place: I have selected the Column F and specified it for Dimension but no joy. Any thoughts? Thanks Edited April 12, 2018 by bc Quote Link to comment
Pat Stanford Posted April 12, 2018 Share Posted April 12, 2018 CONCAT is the text field equivalent to addition. It will only work with things that are of type Text. Both the Door.Height and Window.Elevation fields are already numbers. And it also looks like you only want to put one of them into the cell at a time, so you don't need the Concat at all. Try: =IF('Door'.'IDLabel'<>'', 'Door'.'Height', 'Window'.'Elevation') Ask again when you need to. Quote Link to comment
bc Posted April 12, 2018 Author Share Posted April 12, 2018 OK I get the CONCAT thing thanks. I pasted your formula and switched the column back to General but got this: I also tried to remove the spaces after commas....not there yet. I also tried thhis: =IF('Door'.'IDLabel'<>'', 'Door'.('Height'), 'Window'. ('Elevation')) for the heck of it. Any ideas? Thanks Quote Link to comment
Pat Stanford Posted April 12, 2018 Share Posted April 12, 2018 I just copied and pasted my formula into my worksheet and it worked fine. Possibly you got an extra carriage return/line end? Try and copy it again being careful to copy just from the = to the last ) Or try and type it in rather than paste. Quote Link to comment
bc Posted April 12, 2018 Author Share Posted April 12, 2018 OK that worked and provided decimals and good-to-go after I switched it to Dimensions. Or should I spell that "Dimentians" ? Thanks . This is above and beyond. Now I am going to attempt to adjust the notes columns and see what kind of trouble I can get into. Quote Link to comment
michaelk Posted April 12, 2018 Share Posted April 12, 2018 Just getting back to my computer and reading the Pat's novel :-) I agree with everything he said. But I do want to point out that there is another easier way. See example attached. While it lacks the ability to interleave windows and doors by ID number, it doesn't require IF/THEN/ELSE statements and the parenthesis salad that goes with them. More_worksheet_ideas.vwx Quote Link to comment
Pat Stanford Posted April 12, 2018 Share Posted April 12, 2018 Thanks Michael, I like that I am now The Pat. I knew you were a good dude. And Michael is completely correct, if you just want them to print together without necessarily having the two groups intermingled, putting different databases (with equivalent fields in each column) in sequential rows is a much easier way to accomplish the task. I just had some time and a bee in my bonnet about teaching more about worksheets function and saw this thread as a good starting point to put a bunch of information down in a single spot. Quote Link to comment
michaelk Posted April 12, 2018 Share Posted April 12, 2018 Not sure if my typo was an extra "the" or not capitalizing it: "The Pat" Quote Link to comment
bc Posted April 13, 2018 Author Share Posted April 13, 2018 Well he's got it down pat, I know that for sure, so for me he's Down Pat. As far as having side-by-side columns for the IDs, isn't that what comes out of the box with Doors and Window Schedules? AT any rate, I will be doing more study of this this weekend when I return. Thanks you guys. Quote Link to comment
bc Posted April 13, 2018 Author Share Posted April 13, 2018 (edited) OK so I have got a schedule that works. I duplicated it and began exploring the option of having individual Door and Window, height and width columns such that things could read a bit more separately and also with the "IF" mandate one cannot edit the fields. See what I have done below: Notice the columns are displaying the data correctly for the respective Door/Window except for one thing. In the Door columns it shows 0" where there are windows. Likewise the Window columns show 0" where there are doors. One could say this is logical because there isn't present the criteria need to display otherwise. BUT I want the cells that display 0" to display nothing, or maybe a dash (-). So isn't it logical that wherever the Door column searched and found a Window it would not return anything? Can I get this schedule to NOT show the 0"? Current Cell functions: Door Width =(Door.Width) Door Height =(Door.Height) Window Width =(Window.Width) Window Height =(Window.Height) Thanks Bradley Edited April 13, 2018 by bc Quote Link to comment
michaelk Posted April 13, 2018 Share Posted April 13, 2018 Something like =IF(((PON='Door')), (Door.Height), ('-')) or if you just wanted the height of the opening =IF(((PON='Door')), (Door.Height), (Window.Height)) Quote Link to comment
Pat Stanford Posted April 13, 2018 Share Posted April 13, 2018 12 minutes ago, bc said: So isn't it logical that wherever the Door column searched and found a Window it would not return anything? Can I get this schedule to NOT show the 0"? Michael is right, the only way to get them to display something other than zero is to use some form of IF which then eliminates your editabilty. Is it illogical? Maybe, but it is the way it works. You have asked for the value of a number field in a record that is not attached to the object. Since it is a number the value that indicated that nothing is there happens to be zero. Would different functionality be better, probably. Is it going to happen soon, probably not. Perhaps you need two versions of your worksheet. One with the IF functions to mask the missing data and one without the IF functions to be editable. Quote Link to comment
michaelk Posted April 13, 2018 Share Posted April 13, 2018 4 minutes ago, Pat Stanford said: Perhaps you need two versions of your worksheet. One with the IF functions to mask the missing data and one without the IF functions to be editable. YES. Pro tip: One version of worksheet for presentation and one for data management. Quote Link to comment
bc Posted April 13, 2018 Author Share Posted April 13, 2018 11 minutes ago, Pat Stanford said: Is it illogical? Maybe, but it is the way it works. You have asked for the value of a number field in a record that is not attached to the object. Since it is a number the value that indicated that nothing is there happens to be zero. Would different functionality be better, probably. Is it going to happen soon, probably not. OK , I just thought that it was weird for a Door data column to be indicating ANYTHING in a Widow Row. So that's the way it works. Actually, I suppose it's no weirder than me combining the schedules in the first place! Thanks again. The movie continues..... Quote Link to comment
jah011 Posted August 28, 2018 Share Posted August 28, 2018 Wow guys , that is some serious Spreadsheet elaboration. I believe my question is super simple to everything that i going on in this tread, so I hope I will slip trough... I have 2D door symbols, like total amateur, all types have different names (i.e. 1.01 in 30cm wall etc) and I manage to count them, but I would like to know on which level they are. Actually I set search criteria to Level but It would be awesome to actually have it written in one of the cells. Thanks Jan Quote Link to comment
Pat Stanford Posted August 28, 2018 Share Posted August 28, 2018 Put a formal of '=L' in one of the database headers and it will show you the layer. '=C' will give you the class. Quote Link to comment
jah011 Posted August 28, 2018 Share Posted August 28, 2018 Awesome! Thank you very much! And while we are at it.... Is there a list I could find with all search criteria? Quote Link to comment
Pat Stanford Posted August 28, 2018 Share Posted August 28, 2018 Not a really good list. I guess someone should make one. There are three ways to bring data into a worksheet. You can use the normal worksheet functions. You can use the Record.Field syntax. Both of these are available to choose from the database header row. The third way is the use the function that are part of the Criteria. You can use the Insert Criteria menu item to get something close that will show you the "abbreviations" for a given criteria. They are listed in the Vectorscript Appendix also. 1 Quote Link to comment
mgries Posted October 19, 2018 Share Posted October 19, 2018 Pat, This is an amazing thread! I have another door/window worksheet issue maybe you can shed light on... Is there anyway to edit the width and height of doors directly from a worksheet? I don't understand why these 2 fields must be "read only". Why can't they behave like all the other Record.Field columns? Thanks, Matt 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.