Jump to content
Developer Wiki and Function Reference Links Read more... ×
Floyd

Create and export custom Reports - Vectorscript or SDK?

Recommended Posts

Hi,

I find it hard to figure out a starting point for a little tool I need. Especially, I'm not sure if I need to dig into Vectorscript or if I'd have to use the SDK/C++ for this:

I regularly need to make calculations based on symbol types and counts from VW drawings and import reports into a SQL database. What I currently do is, that I manually create a bunch of worksheets (List "objects with a record", but as the symbols have different records, I need to set up at least 6-8 sheets each time) and then export them as CSV files that I adjust manually and import them to our SQL database.

This is too time-consuming and fault-likely and I'd like to automate at least most of the tasks involved. I can do the conversions I do manually (field names, factors...) by a little php script on the SQL-Server, but I need to automate the Vectorworks side. Ideally, I'd have eventually have a Button or menu option to click that exports all Objects from my drawings that have records attached including their layer and class information (ideally XYZ positions, too) and save them in one or more CSV files. If the files could be uploaded to the SQL-Server via FTP or maybe HTTP-POST or any other way, that would be even better. I could sort out everything else on the server easily.

I do know php and some python. I've also used C++ some 15 years ago, but hopefully would be able to get some c++ code working if I have the right entry points/examples.

Can anybody point me to a good example/starting point for this? 

Thanks

Frank

Share this post


Link to post

Well I don't think you can use VS to connect to an external SQL server. You probably can with python or c++.

Besides that, from what you describe getting the information you need should be fairly simple for both options, without the need of worksheets.

If automating the SQL injection would be too much, you could at least streamline your export to match the structure you require, even with plain vectorscript.

Share this post


Link to post

In vectroscript, you can create tab delimited text files with any of the information you describe.

 

Open(filename);

PutFile(prompt, defaultfilename, filename)

Write

Writeln

I'm sure there are Python calls to those commands

 

 

Share this post


Link to post
4 hours ago, Miguel Barrera said:

You can also use ODBC to connect to databases either from VW directly or programmatically with vectorscript. You can find a connection example at

 

http://developer.vectorworks.net/index.php/VS:DBDocAddConn 

 

and all routines at

 

http://developer.vectorworks.net/index.php/VS:Function_Reference#ODBC

 

I've been trying for more than a decade to get Vectorworks and Filemaker to talk using ODBC.  Not with Vectorscript, but just through their built-in options.  I was successful like one time many years ago for a brief trial on one file.  It amazes me how difficult it is to get that working.

 

 

As for the OP, exporting all of your info should be a pretty simple script.  I have a large number of scripts which import information out in tab-separated files for import into Filemaker.  They all use the calls Sam Jones specified above:

  • Open(filename)
  • PutFile;
  • Write;
  • WriteLn;
  • Tab(1);

A quick example of just the write lines:

        Write(GetRField(h,'LED Ribbon','ID'));Tab(1);
        IF GetRField(h,'LED Ribbon','Location')='' THEN WRITE ('-') ELSE Write(GetRField(h,'LED Ribbon','Location'));Tab(1);
        IF GetRField(h,'LED Ribbon','Position')='' THEN WRITE ('-') ELSE Write(GetRField(h,'LED Ribbon','Position'));Tab(1);

 

These are all using the same record format, but you could use any number and mixture of them.

Share this post


Link to post

Thanks for the feedback.

Sounds like I'll  have to get familiar with Vectorscript. Is this the only page with examples: http://www.vectorworks.net/support/custom/vscript/example?

I have only had a quick look at it, I was looking for a simple example like "go through all objects of a document and count them" or such, I have not seen any information on such loops yet? Is there some other site/community with such basic examples/Howtos?

Frank

 

Share this post


Link to post

You can find additional indo and more examples on http://www.vectorlab.info

 

I would recommend you pyodbc (or pypyodbc*) for the sql connection. It is easy to use, more stable and performs better than the vs. functions.

 

* Almost totally same usage as pyodbc ( can be seen as a re-implementation of pyodbc in pure Python )

 

Edited by Patrick Winkler

Share this post


Link to post

Developer.vectorworks.net

 

Make sure you read the Vectorscript Language Guide as it has all the information on loops.

 

The Vectorscript Function Reference and the Vectorscript Appendix (both also at the developer site, but also in the VWHelp:Script Reference folder in your Applications folder) are also required information. The Function Reference has sample code for most of the functions, but not a lot on how to combine them together.

 

Since the Python implementation is just to call the Vectorscript functions, these are needed for Python also.

Share this post


Link to post

Vectorscript? Why? It's (year) 2017...VW is supposed to "connect" to an external DB without fuss. Particularly on the Mac OS and particularly with FM Pro, it should be a snap. One should NOT have to be a VS or DB Geek to set it up and make it work, and yet the instructions are not clear & simple. I've looked but never seen a simple VW file example w/ associated DB. 

Share this post


Link to post

Just wanted to say thanks again for the input. Having looked into this I see roughly "how to do it" and will hopefully have some time to try to get this running in the next 2-3 weeks, but I agree with Don, it should be easier to automate such tasks and documentation is really really bad... especially the lack of good examples to get you started.

Share this post


Link to post
5 hours ago, Don Seidel said:

 

Vectorscript? Why? It's (year) 2017...VW is supposed to "connect" to an external DB without fuss. Particularly on the Mac OS and particularly with FM Pro, it should be a snap. One should NOT have to be a VS or DB Geek to set it up and make it work, and yet the instructions are not clear & simple. I've looked but never seen a simple VW file example w/ associated DB. 

 

OK, that would be nice.  Where have you seen this.  The "this" being an easy way for an end user to set up a custom configured connection between two separately created and marketed programs to seemlessly share information.  I'm sure this is the future of personal computing, but I haven't seen it or heard of it.  I would love to check out any examples you can point me to.

Currently, VW seems to provide the most easily accessible tools for creating such a connection between CAD data and database data. Easily? No. More easily than other CAD programs?  I think so, but my familiarity is not that great.  Show me.

Share this post


Link to post
33 minutes ago, Sam Jones said:

OK, that would be nice.  Where have you seen this.  The "this" being an easy way for an end user to set up a custom configured connection between two separately created and marketed programs to seemlessly share information.  I'm sure this is the future of personal computing, but I haven't seen it or heard of it.  I would love to check out any examples you can point me to.

Currently, VW seems to provide the most easily accessible tools for creating such a connection between CAD data and database data. Easily? No. More easily than other CAD programs?  I think so, but my familiarity is not that great.  Show me.

I could give some examples, but I'm not here to advertise other software nor should we start such a discussion :)

 

While it is possible with Vectorworks, it's not easy to say the least. I don't think the instructions are hard, but the method is, certainly for people with less computer skills. 

I spend a lot of time in getting an ODBC connection work once and have not touched it since. It could be easier now, I can't tell.

 

That being said, Don. Give it a go, you will find several resources on the web or on this forum to get you started. People will help you if you end up stuck.

It will cost you some time, but will save you much more in the end.

Vectorscript or Python might be scary at first, but your investment of time will be earned back big time. If that is not your thing, let someone else code it for you.

 

That is what I like about this software, you can make it your own. 

Share this post


Link to post

While a Filemaker user for some 20+ years, I enjoyed the fact you could get an awful lot accomplished without fancy scripting/programming. My point about it being year 2017 is that ODBC is supposed to be the magic key to link to different DB's. As long as you have assigned fields in each DB to sync, the process should be just so simple. There's only 3 options for crying' out loud; force data one direction, the opposite, or sync (with most recent entry as conflict resolution).

 

Yes of course the programming is more involved that that, but why on earth can't the interface be just so simple? For example: I take a Window plug-in and insert it into a wall, with options I choose. It's done. Next task. There's no desire or need for me to learn the programming language to make that happen.

Share this post


Link to post

The window plug-in is part of Vectorworks and the orange in an apples to oranges comparison.  The trick is constructing a user interface that will allow one to construct a custom connection with any other program that supports making such a connection.  This problem is unique and not appropriate to compare with plug-in object programming, such as the window object.

 

ODBC has never been easy.  In fact getting ODBC to work is a hell of lot harder than scripting.  This has been the case with every program I have tried to hook up to Filemaker using ODBC.  The production company PRG Hamburg got ODBC to work but only after scouring Europe.  They found one guy, in Switzerland, who they paid to construct the connection.  When you don't have control of both sides of the action, it's going to be exceptionally tough to create the user interface.

 

All that being said, if VW could solve the problem of creating an easy user interface for constructing a live connection with an outside database, it would be @#$%& fabulous.

 

Hippocode, ok, don't advertise.  Send me some examples privately, they will reshape the terrain I'm working in.

 

Sam 

sjones@autoplotvw.com

 

Share this post


Link to post

 

8 hours ago, Don Seidel said:

Vectorscript? Why? It's (year) 2017...VW is supposed to "connect" to an external DB without fuss. Particularly on the Mac OS and particularly with FM Pro, it should be a snap. One should NOT have to be a VS or DB Geek to set it up and make it work, and yet the instructions are not clear & simple. I've looked but never seen a simple VW file example w/ associated DB. 

 

Don, you can do a lot more with the database through programming than just a simple one to one relationship as it is in the VW interface (link a row to an object with a common key field). You could for example, import a database or spreadsheet into a VW worksheet by choosing the file from a folder dialog or do searches to get a particular data, etc.

 

I think that those that have not worked with ODBC, do not know that the first step is to connect the database to the ODBC driver, which translates the original format to a SQL database. This is done in the OS and not in VW. In windows you use the ODBC Data Source Administrator (odbcad32.exe) to make an ODBC alias. The alias then can be connected to VW through the "Manage Databases..." menu command. You can't make it simpler than that due to all the different databases around.

 

I have done some research and found that the first step can be done in python with the following code:

import pyodbc

# Specifying the ODBC driver, server name, database, etc. directly
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')

# Using a DSN, but providing a password as well
cnxn = pyodbc.connect('DSN=test;PWD=password')

# Create a cursor from the connection
cursor = cnxn.cursor()

so it is feasible to do all the connections within Vectorworks to access the data.

Share this post


Link to post
On 11/9/2017 at 5:19 PM, Don Seidel said:

While a Filemaker user for some 20+ years, I enjoyed the fact you could get an awful lot accomplished without fancy scripting/programming. My point about it being year 2017 is that ODBC is supposed to be the magic key to link to different DB's. As long as you have assigned fields in each DB to sync, the process should be just so simple. There's only 3 options for crying' out loud; force data one direction, the opposite, or sync (with most recent entry as conflict resolution).

 

Yes of course the programming is more involved that that, but why on earth can't the interface be just so simple? For example: I take a Window plug-in and insert it into a wall, with options I choose. It's done. Next task. There's no desire or need for me to learn the programming language to make that happen.

 

I have been a Vectorworks and Filemaker user since around 2004.  I write complex scripts in Vectorscript and entire multi-table, relational, script-driven databases in Filemaker.  I still, after more than a decade of trying, cannot get VW and FM to talk to each other over ODBC.  Every few months, I get the desire to try again, and every single time, it's an abject failure.  Something that should be fairly simple (especially for someone with a fairly deep understanding of scripting) and would change the way I develop my workflow, but I just can not get it to work.  

 

If someone has managed to get VW and FM to talk over ODBC on a mac, I would love to hear from you!!  

 

Obviously, it can work because LightWrite and Vectorworks do it.  

Share this post


Link to post

Have you managed to get ODBC to work with any 2 programs?  Perhaps Miguel Barrera is a place to start, but I think ODBC is just too hard, and it isn't all VW's fault.

 

I'm sure someone at VW has gotten ODBC to work, but the LightWright / Vectorworks  connection is not an example.  John McKernon, the author of Lightweight and VW collaborated to hard code a connection that uses XML files to pass information; no ODBC involved at all.

 

Share this post


Link to post

Sorry I cannot help you with your Mac. The last time  I owned a Mac was in the early 2000's. I have developed a VS plugin that searches for information contained in an excel file through a Windows ODBC driver and manager app, which are included in the Windows OS.

 

For the Mac, if there is no ODBC manager included in the OS, you can get one from http://www.odbcmanager.net/ 

There is also a guide to install and configure a filemaker ODBC connection here (Chapters 3 & 4):  https://fmhelp.filemaker.com/docs/16/en/fm16_odbc_jdbc_guide.pdf

Share this post


Link to post

I have not actually tired very hard to make it work, but from what I recall, Filemaker is supposed to have very poor OBDC capabilities. Getting Filemaker to work will probably be a problem.  Apparently there are other databases that work much better. MySQL and Postgres SQL come to mind.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×