trashcan Posted August 20, 2022 Share Posted August 20, 2022 2.1 through 2.4 are calculated from Records. I want to add a column that is Extras Spares and then a sum Column that calculates the total. Column 3, 4 are manually added as they are spares not on the plot. I want to manually put in spare #s in column C that are calculated from records, but it won't let me. Is there a way to do this? Also the =SUM isn't working for total units for 2.1 through 2.4. It's calculating as "1" Help? thx Quote Link to comment
0 michaelk Posted August 20, 2022 Share Posted August 20, 2022 Just guessing that column B is =COUNT? So it is D is returning the value 1 (the count) + 0. In column D check Sum Values in the little pulldown. Is column C returning the value of a record? 2 Quote Link to comment
0 Mark Aceto Posted August 20, 2022 Share Posted August 20, 2022 in column C, he wants to manually add (or auto calc a percentage) spares 1 Quote Link to comment
0 Mark Aceto Posted August 20, 2022 Share Posted August 20, 2022 (edited) taking what michael said, how's this: Edited August 20, 2022 by Mark Aceto 1 Quote Link to comment
0 Mark Aceto Posted August 20, 2022 Share Posted August 20, 2022 1 hour ago, trashcan said: I want to manually put in spare #s in column C that are calculated from records, but it won't let me. Is there a way to do this? the reason it won't let you manually enter spares in those rows of column C is because they're a database, so you either have to auto calc every row in column C (as in my screenshots) or add "dumb" spreadsheet rows below the database (which would quickly get annoying) 1 Quote Link to comment
0 Mark Aceto Posted August 20, 2022 Share Posted August 20, 2022 @michaelk is there a way to reference an external spreadsheet or database (like filemaker)? 1 Quote Link to comment
0 michaelk Posted August 20, 2022 Share Posted August 20, 2022 @Mark Aceto you're right that a database is tricky. But any non database cell can also count objects. Instead of having a database with a criteria and then using the database header to call the count function, you can use a regular spread sheet cell, call the count function and put a criteria on the count function. I'll attach an example. Projector Inventory.vwx 2 Quote Link to comment
0 michaelk Posted August 20, 2022 Share Posted August 20, 2022 1 hour ago, Mark Aceto said: @michaelk is there a way to reference an external spreadsheet or database (like filemaker)? Yes* Vectorworks can communicate with external databases that use Open Database Connectivity and SQL standards. The way I understand it that does include FileMaker Pro. I have tried and failed to make it work :-). I think the only person I've ever met who understands it is John McKernon. I'm pretty sure that's the root of Lightwright. Sounds like a good user group topic 🙂 . 3 Quote Link to comment
0 Mark Aceto Posted August 20, 2022 Share Posted August 20, 2022 47 minutes ago, michaelk said: Sounds like a good user group topic 🙂 . For context, there are 2 hopes: I would like to take the worksheets out of VW where I can do so much more (pivot tables), so much faster (formatting), and then reference them back into VW This would also help me manage a larger library of worksheets If nothing else, I might just export them, play with them, and then bring them back in However, it seems like the database header row formulas get lost in the plot export The shop wants to streamline shop orders across multiple projects I caveated the dream of a magic bullet shooting for the moon but there's probably a happy medium that will at least improve quality of life for them 1 Quote Link to comment
0 trashcan Posted August 21, 2022 Author Share Posted August 21, 2022 You guys are the best! @michaelk Do a manual spreadsheet rather than a report w/ database rows works perfectly. I have fixed number of spares, rather than a percentage, so instead of referencing a percentage cell, I'm just putting in a number. All of my objects that I'm counting are symbols so that works great. I don't understand the "'Don''t Count These'" in the formula and the "L" - my stuff still calcs properly with this, but wondering what this is. Quote Link to comment
0 michaelk Posted August 21, 2022 Share Posted August 21, 2022 Typing in a number is how I do it with lighting instrument inventories too. But if it's something like tile for a shower or steel for the framing of a flat I'll do some percentage of waste to get total quantity of material needed. I was riffing of of Mark's idea of a changeable percentage of spares. There is no way for a worksheet to "see" a symbol that is in the resource manager, but not inserted into the drawing. To get the image of a projector that isn't being used you need a dummy insertion. Hence a layer of all symbols used in the inventory in a "Don't Count These" layer. What I find helpful when making these kind of inventory worksheets is to create a temporary database of objects in the layer "Don't Count These". In the database header put =S to get the symbol names. Then you can copy and paste them into the criteria when making the real inventory. When getting a count, exclude that layer. 4 Quote Link to comment
0 trashcan Posted August 21, 2022 Author Share Posted August 21, 2022 @Mark Aceto aha - so "Don't Count These" is a Layer - "L"= Layer Quote Link to comment
0 michaelk Posted August 21, 2022 Share Posted August 21, 2022 It's the old school way, before autocomplete or pulldowns with choices in the database headers. Just like you could wake me at 4:00 am and ask me for all the German dative prepositions, you could also ask me for the list of database header abbreviations. =L Layer =C Class =T Type =PON Parametric object name =S Symbol etc. That list has disappeared from the Help pages (or I can't find it) so those may be in the process of being deprecated. Not sure. 3 Quote Link to comment
0 Tom W. Posted August 21, 2022 Share Posted August 21, 2022 19 minutes ago, michaelk said: That list has disappeared from the Help pages (or I can't find it) Here?: 21 minutes ago, michaelk said: =PON Parametric object name I always wondered what PON stood for!!! Duh... 2 Quote Link to comment
0 Sam Jones Posted August 21, 2022 Share Posted August 21, 2022 19 hours ago, michaelk said: Vectorworks can communicate with external databases that use Open Database Connectivity and SQL standards. The way I understand it that does include FileMaker Pro. I have tried and failed to make it work :-). I think the only person I've ever met who understands it is John McKernon. I'm pretty sure that's the root of Lightwright. Sounds like a good user group topic 🙂 . I'm not sure even John understands ODBC. He might, but Lightwright uses an XML file to communicate with VW. XML files are blazingly fast, but require coding both from LW and VW to work. The only person I know of that has come close to making ODBC work is Carlotta who is on the Architecture and Vectorscript forums, and she considered it too fragile to use. In the absences of getting ODBC working you are left with using scripting to communicate with external flat file databases via tab delimited exports or writing to XML files. Both require the external database to write to a file as well as VW. If somebody figures this out, it would fulfill a dream I've had for 25 years. 1 Quote Link to comment
0 michaelk Posted August 21, 2022 Share Posted August 21, 2022 I didn't know that John was using XML. When I've looked into it it was the getting data back into VW that made my brain hurt. Quote Link to comment
0 michaelk Posted August 21, 2022 Share Posted August 21, 2022 @Tom W. There it is! 1 Quote Link to comment
0 Mark Aceto Posted August 21, 2022 Share Posted August 21, 2022 1 hour ago, michaelk said: =PON Parametric object name Now I finally know what Pon De Floor means. Here's the link to the help file (for bookmarking): https://developer.vectorworks.net/index.php/Search_Criteria_Format Quote Link to comment
0 Mark Aceto Posted August 21, 2022 Share Posted August 21, 2022 @michaelk and @Sam Jones I thought there was an option to reference an external spreadsheet on import... Quote Link to comment
0 michaelk Posted August 21, 2022 Share Posted August 21, 2022 You can import a spread sheet and you can reference another worksheet in the drawing. And I bet Sam knows how to reference worksheets in other drawings. Quote Link to comment
0 Mark Aceto Posted August 21, 2022 Share Posted August 21, 2022 3 hours ago, trashcan said: @michaelk Do a manual spreadsheet rather than a report w/ database rows works perfectly. @michaelkis there a risk of user error with this method? I always think of VW database rows as dumb pivot tables. For example, if I add a new object that needs to be counted, the DB will automatically find it, recalc, and expand the number of rows. However, for a spreadsheet (not a database), do I have to manually add that object? 1 Quote Link to comment
0 Mark Aceto Posted August 21, 2022 Share Posted August 21, 2022 2 minutes ago, michaelk said: You can import a spread sheet and you can reference another worksheet in the drawing. And I bet Sam knows how to reference worksheets in other drawings. The goal is to take the worksheet editing and formatting out of VW. Also curious how VW manages XLSX tabs because I typically use Excel/Sheets as a "database". First tab is always "Entry", last tab is "Validation", and then I'll create a bunch of tabs in between that are pivot tables rearranging the data. Quote Link to comment
0 Mark Aceto Posted August 21, 2022 Share Posted August 21, 2022 21 hours ago, michaelk said: Projector Inventory.vwx 2.96 MB · 2 downloads @trashcansave these scripts Quote Link to comment
0 michaelk Posted August 21, 2022 Share Posted August 21, 2022 I'm expecting that soon someone will tell me how =RunScriptEdit is really supposed to work and I'll make better ones 🙂 1 Quote Link to comment
0 michaelk Posted August 21, 2022 Share Posted August 21, 2022 2 hours ago, Mark Aceto said: @michaelkis there a risk of user error with this method? I always think of VW database rows as dumb pivot tables. For example, if I add a new object that needs to be counted, the DB will automatically find it, recalc, and expand the number of rows. However, for a spreadsheet (not a database), do I have to manually add that object? Yes. All these issues are what drove me to create EL Dooro and EL Window 🙂 1 Quote Link to comment
0 Mark Aceto Posted August 21, 2022 Share Posted August 21, 2022 44 minutes ago, michaelk said: EL Dooro Officially submitting a VE for “El Doorado”. 1 Quote Link to comment
Question
trashcan
2.1 through 2.4 are calculated from Records. I want to add a column that is Extras Spares and then a sum Column that calculates the total.
Column 3, 4 are manually added as they are spares not on the plot.
I want to manually put in spare #s in column C that are calculated from records, but it won't let me. Is there a way to do this?
Also the =SUM isn't working for total units for 2.1 through 2.4. It's calculating as "1"
Help? thx
Link to comment
39 answers to this question
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.