Custom Numbers In Calculated Worksheet

[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

[michaelk]

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?

[Mark Aceto]

in column C, he wants to manually add (or auto calc a percentage) spares

 

 

[Mark Aceto]

taking what michael said, how's this:

 

 

 

Edited August 20, 2022 by Mark Aceto

[Mark Aceto]

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)

[Mark Aceto]

@michaelk is there a way to reference an external spreadsheet or database (like filemaker)?

[michaelk]

@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

[michaelk]

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 🙂 .

[Mark Aceto]

47 minutes ago, michaelk said:

Sounds like a good user group topic 🙂 .

 

For context, there are 2 hopes:

1. 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
2. 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

[trashcan]

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.

 

[michaelk]

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.

[trashcan]

@Mark Aceto aha - so "Don't Count These" is a Layer - "L"= Layer

[michaelk]

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.

[Tom W.]

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...

[Sam Jones]

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.

[michaelk]

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.

 

 

[michaelk]

@Tom W.

There it is!

 

[Mark Aceto]

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

[Mark Aceto]

@michaelk and @Sam Jones I thought there was an option to reference an external spreadsheet on import... 

[michaelk]

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.

[Mark Aceto]

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?

[Mark Aceto]

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.

[Mark Aceto]

21 hours ago, michaelk said:

Projector Inventory.vwx 2.96 MB · 2 downloads

 

@trashcansave these scripts

[michaelk]

I'm expecting that soon someone will tell me how =RunScriptEdit is really supposed to work and I'll make better ones 🙂

[michaelk]

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 🙂 

[Mark Aceto]

44 minutes ago, michaelk said:

EL Dooro

Officially submitting a VE for “El Doorado”.