Accessing Property Tables

Question

question

martin.j asked Jan 4, '21 anthony.johnson edited Jan 11, '21

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?

Software Version:

FlexSim 20.2.3

flexsim 20.2.3 excel import sql query excel export property table

Answer 1 · 2021-01-05T19:33:49Z

anthony.johnson answered Jan 5, '21 anthony.johnson edited Jan 11, '21

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

martin.j commented · Jan 07, 2021 at 09:41 AM

@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 ·

Jason Lightfoot ♦ martin.j commented · Jan 07, 2021 at 12:16 PM

21.0.1 is released.

0 ·

Lars Christian J2 commented · Jan 07, 2021 at 12:08 PM

@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 ·

Jason Lightfoot ♦ Lars Christian J2 commented · Jan 07, 2021 at 12:54 PM

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 ·

anthony.johnson ♦♦ Jason Lightfoot ♦ commented · Jan 07, 2021 at 05:18 PM

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 ·

Answer 2 · 2021-01-05T06:51:56Z

Lars Christian J2 answered Jan 5, '21

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

question