Jump to content
Developer Wiki and Function Reference Links ×

How to input data from custom dialog to database(excel) and worksheet using


Tn_Pink

Recommended Posts

Good day.

I am a first timer in using vectorscript.

About dialog to database (excel);

Question:

How to input data from custom dialog to database(excel) and worksheet using vectorscript?

Please kindly help me solve this.

Thank you very much in advance.

You can export DATA in vectorworks ; - delimited so excel can read it. You can do this from a worksheet within vectorworks or you could code your plugin to do so. In that case you basicly export a text file.

You could also export to XML which I find more attractive.Excel can also read/write XML but sadly only static. This means that after importing the XML structure in EXCEL you can't change it(adding / removing columns) unless you delete your work in excel.

This biggest difference between these 2 options is that in option 1 you completely rewrite the whole document each time you export. With XML you can just edit/add a specific element instead of doing it all over and over again. If its a database I'd go with XML. If its data you want to work with in EXCEL go text style.

Edited by hippothamus
Link to comment

There is also 2 other alternatives that you may want to consider.

The one that will give you more flexibility and two way communication is ODBC but it is also the most complicated to set up.

The other one is creating a *.csv file, also a text file, that can be read directly by excel. This last one was the only way to set up databases some time ago and I still use this method to read from data tables.

Link to comment

Thanks hippothamus.

Yes, I need to make it into a code or script to be able to input the data to database.

One more additional info,

I need to use ODBC.

But based on the function reference(ODBC) of vectorscript,

I cannot connect the record field to database column.

Can you please help on this?

Thanks in advance.

Link to comment
  • Vectorworks, Inc Employee

Use can use the following script as example, but have in your mind the following:

- There might be some problems with excel driver. For Excel driver the user interface is recommended;

- Excel driver on Windows is read only;

- If you want to update table you have to drop the table and insert it again. That's in theory, I haven't do that ever. See DBSQLExecute for more details.

I used Filemaker database for the sample, feel free to use any DSN. So:

DSN = "AccTest FM"

username = "admin"

password = ""

In the database I have a table called "testrecord" which is:

__________

| id | name |

| ---|----------|

| 1 | rect 1 |

| 2 | rect 2 |

| 3 | rect 3 |

In my test file I created the following record:

Record name = "TestRecord"

[Field] "ID" of type Text

[Field] "Name" of type Text

I created 3 rectangles and attached the record.

Now create the following script. The blocks are parts from one script, you can copy-paste them directly. I tested it, it works. I spitted it to make notes in the code.

- declare the variables. {$DEBUG} is optional, the directive turns on the debug module. To use that turn on the Vectorworks option "Run script in developer mode"

{$DEBUG}
PROCEDURE Test;
VAR
databases, query : DYNARRAY [] OF CHAR;	
DSN, recordName : DYNARRAY [] OF CHAR;
Tables : DYNARRAY [] OF CHAR;
User : DYNARRAY [] OF CHAR;
Pass : DYNARRAY [] OF CHAR;
res, exist : BOOLEAN;

i, cnt : LONGINT;
hCurrentLayer, hCurrentObject, hRecord  : HANDLE;
fieldValue : DYNARRAY [] OF CHAR;

- First we have to establish a connection to the DSN. Check if the DSN is already connected and add it as new connection only if necessary. AlertDialogs are optional, I use them for test purposes.

BEGIN
databases	:= '';

{1. Connect-------------------------------------------------------------------------------------------------}
DSN := 'AccTest FM';
if DBDocGetDB(databases) then begin
 {check if the DSN exist}
 if Pos(DSN, databases) = 0 then
  exist := false
 else
  exist := true
 end
else
 exist := false;
if NOT exist then begin
 User := 'admin';
 Pass := '';
 res := DBDocAddConn( DSN, User, Pass );
end;
res := DBDocGetTables( DSN, Tables );
{just check the connection}
AlrtDialog( concat(DSN, Chr(13), 'succeeded = ', res, Chr(13), 'Tables:  ', Tables ) );

- Second set the format connection. That could be verified from "Record Format Connection" dialog.

{2. Set format connection-------------------------------------------------------------------------------------------------}
recordName := 'TestRecord';
res := DBSetFormatConn(recordName, DSN, 'testrecord');

- Third set the format fields connections. That links each field to columns. The integer value in the end is the Link type of the connection. See here for more details.

{3. Set format field connection-------------------------------------------------------------------------------------------------}
res := DBSetFormatFieldConn(recordName , 'ID', 'id', 1);
res := DBSetFormatFieldConn(recordName , 'Name', 'name', 0);

- Select the objects which I want to connect.

{4. Select only objects with record attached---------------------------------------------------------------------- }
DSelectAll;
SelectObj(INSYMBOL & INVIEWPORT & (R IN [recordName ]));

- Finally connect the objects to particular row. Queries are written manually.

{5.Link each object to a table row}
fieldValue := '';
hCurrentLayer := FLayer; { first layer }
while (hCurrentLayer <> NIL) do { loop through all the layers }
begin
 hCurrentObject := FSObject(hCurrentLayer); { first selected object in layer }
 while hCurrentObject <> NIL do { loop through all the objects }
 begin
  {find the record}
  cnt := NumRecords(hCurrentObject );
  for i := 1 to cnt do begin
   hRecord := GetRecord(hCurrentObject , i);
   if hRecord <> NIL then begin
    if GetName(hRecord) = recordName then begin
     {prepare queries}

- Get field values from the record format.

     fieldValue := GetRField(hCurrentObject, recordName, 'id');
     query := Concat('SELECT "id", "name" FROM "testrecord" WHERE "id"=', fieldValue, '');
     res := DBObjSQLSetRead(hRecord, query);
     res := DBObjSQLGetRead(hRecord, query); AlrtDialog(query);

- Get field values from the record format. %%Name%% means that Vectorworks will replace that with the value from the record. You can use it in WHERE clause also.

     query := Concat('UPDATE "testrecord" SET "name"=%%Name%% WHERE "id"=', fieldValue, '');
     res := DBObjSQLSetWrite(hRecord, query);
     res := DBObjSQLGetWrite(hRecord, query); AlrtDialog(query);

    end;
   end;
  end;
  hCurrentObject := NextSObj(hCurrentObject); { next selected object }
 end;
 hCurrentLayer := NextLayer(hCurrentLayer); { next layer }
end;

END;

RUN(Test);

  • Like 1
Link to comment

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...