question

martin.j avatar image
1 Like"
martin.j asked anthony.johnson edited

Accessing Property Tables

Th new Property Tables are quite clever, but I am wondering if there is a way to import and export sets of properties by importing and exporting a Property Table?

I imagine making a Property Table showing machine properties for an array of machines. I would then export that table to excel where it can be altered, duplicated and more. I would then inport an excel table to quickly update all the machines settings and run a test with them.

How would i go about doing that, and is there a way to get/set information in the table through code, or does it have to be through SQL queries?

FlexSim 20.2.3
flexsim 20.2.3excel importsql queryexcel exportproperty table
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

2 Answers

anthony.johnson avatar image
2 Likes"
anthony.johnson answered anthony.johnson edited

I wanted to include some type of export/import mechanism for property tables when I developed them, but there were technical hurdles that I couldn't solve in time, so unfortunately it's not there now.

However, you can, with a little SQL, implement an import/export mechanism that uses global tables as a middle man.

To create and update a global table for exporting to Excel:

Table.query("SELECT Name, Prop1, Prop2, ... FROM Objects() WHERE ...").cloneTo("ObjectProps");

Then you can export that table to Excel. Then, after changes are imported from Excel:

Table.query("UPDATE Objects() AS Object INNER JOIN ObjectProps ON Object.Name = ObjectProps.Name SET Object.Prop1 = ObjectProps.Prop1, Object.Prop2 = ObjectProps.Prop2, ...");

This will do a join on the global table and the objects in the model, matching the object name, and set the object properties accordingly. Here you would need to make sure your objects are named uniquely, or you could use some other uniquely identifying attribute.

Note: Unfortunately the UPDATE clause with INNER JOIN is not implemented properly in the 21.0.1 release. We were trying to bring the syntax inline with SQL Server syntax, but ended up breaking the syntax. So you will need to wait until the 21.0.2 release to use this.

· 5
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

martin.j avatar image martin.j commented ·

@anthony.johnson So this solution only works in a yet unreleased version of Flexsim? Or are you simply making changes to the syntax for the next release?

0 Likes 0 ·
Jason Lightfoot avatar image Jason Lightfoot ♦♦ martin.j commented ·

21.0.1 is released.

0 Likes 0 ·
Lars Jacobsen avatar image Lars Jacobsen commented ·

@anthony.johnson As far as I know, the FROM clause is used for SELECT, but not for UPDATE. UPDATE replaces FROM in an UPDATE clause to my knowledge. Thus no change is needed in the FlexSim SQL syntax.

0 Likes 0 ·
Jason Lightfoot avatar image Jason Lightfoot ♦♦ Lars Jacobsen commented ·

MySQL uses UPDATE <table> INNER JOIN without FROM and with SET after the join, while SQLServer seems to want UPDATE then SET FROM INNER JOIN


1 Like 1 ·
anthony.johnson avatar image anthony.johnson ♦♦ Jason Lightfoot ♦♦ commented ·

Dang it, you guys are right. I guess when I saw SQLServer's syntax, I thought that maybe I had just been dumb when I originally implemented UPDATE with a join. But turns out MySQL uses the original syntax. And it appears that PostgreSQL has its own unique syntax for updating with a join. And, to top it off, I implemented it with the FROM statement before the SET. Oh I'm so mad at myself!

So, I guess, whereas before we were aligned with MySQL, in 21.0.1 we're aligned with nothing. So, everybody hold off on using this until I get this fixed in the next bug fix release.

0 Likes 0 ·
Lars Jacobsen avatar image
1 Like"
Lars Jacobsen answered

Currently it is not possible to use Excel import and export for a property table. The property table is basically a "view" of a selected part of the object tree. Thus you will have to use code and sql statements for export, updates and inserts. Very relevant for future development including the new Model Parameter Tables and Experimenter Scenario Table. @anthony.johnson

5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.