How to get name of global table most recently populated by MTEI?

Question

question

Mike Mayer asked Jun 8, '16 Brandon Peterson edited Jun 17, '16

How to get name of global table most recently populated by MTEI?

I am able to import Excel sheets into global tables no problem.

I would like to then use "Post Import Code" to scan the newly populated global table for numeric strings that contain a percent sign.

If a percent sign is in an Excel cell as a result of being pasted (like, someone pasting 3.141% into Excel), MTEI will import it as a flush-left string "3.141%" versus it being a fractional value 0.03141 (which would then have correctly shown up up in the global table as a true number, flush right).

Interestingly, if the data in the Excel cell is originally 0.3141 and AFTERWARDS you do a Format Cells in Excel to display it in the Excel cell as a percent 3.141%, then FlexSim still brings it in correctly as 0.3141.

This difference in how the original Excel cell's data was created (pasted, versus Format Cell), has tripped us up numerous times. So, we thought we'd just do some simple data cleansing on the FlexSim side by scanning a just-imported global table for 1) a percent sign character "%" suffix on any string in a global table cell, then 2) determine if everything to its left is either a numeral, with or without the decimal point. In which case, we can assume with some confidence that it's actually a percentage value (like 3.141%) even though represented as a string inside the FlexSim global table.

When these percent values are discovered, we can then (I think) do some simple string-to-number conversion in FlexScript (in the Post Import Code) and re-write the fractional percent value back into the same global table cell as truly numeric 0.03141, i.e., the true fractional percent value.

The other solution of course, is to ban our own use of percent signs in the source Excel sheet. However, there's no guarantee that someone might do it anyway in the source Excel sheet (because in this application, the input Excel sheets for MTEI are created by humans). Thus, some light data cleansing is in order - we just need the name of the table that was just populated by the MTEI so we can scan it for percent symbols. I searched the Forum and the FlexSim manual, but didn't see a way the table's nodename is getting passed to the OnImport code. Note that we will likely only import one table at a time (Post Import Code I believe is executed after 1 or more tables is loaded).

Thanks, Mike - Goodyear

Software Version:

FlexSim 7.7.4

excel post import code

Answer 1 · 2016-06-08T16:20:31Z

Brandon Peterson answered Jun 8, '16 Ben Wilson edited Jun 8, '16

Mike,

The Post Import Code is only executed once at the end and there are no references to the tables that were updated passed into the function.

This means that you will have to look through all of the possible tables each time the import is executed. Here is some sample code that you can put into the Post Import Code that will loop through the tables that the data is placed into if you don't want to manually code the reference to each one in the Post Import Code:

#define EXCEL_TABLE_LOCATION_RANK 3
#define EXCEL_TABLE_NAME_RANK4
table = getvarnode(ownerobject(c), "importtable");
for(int i1 = 1; i1 <= gettablerows(table); i1++){
	treenode row = rank(table, i1);
	string tablelocation = gets(rank(row, EXCEL_TABLE_LOCATION_RANK));
	string tablename = gets(rank(row, EXCEL_TABLE_NAME_RANK));
	treenode tableNode = node(concat(tablelocation, strlen(apchar(tablename))>0?"/":"", tablename), model());

	/*Insert your code to evaluate your table here*/
}

Good Luck,

Brandon

· 4

Mike Mayer commented · Jun 08, 2016 at 05:55 PM

Thanks @Brandon Peterson, that's pretty neat.

Your code works well for retrieving whatever's stored in the tree inside the node

node("/Tools/ExcelAuto>variables/importtable/Row_10/Flexsim_Table_Name", model());

However, in all of my 13 imported global tables, the "Flexsim_Table_Name" is showing the word "data", which is what is being returned by the code as well (versus it being the actual global table name, like Customer_Address for example). I could use the "Excel_Sheet_Name" node variable, which contains the right info, and which I just happen to use as the global table name as well, for consistency. But it is strange that for each of the 13 imported tables, the Flexsim_Table_Name is showing up in the tree view as simply the text "data" instead of the table's actual name.

- Mike

0 ·

Brandon Peterson ♦ Mike Mayer commented · Jun 08, 2016 at 06:49 PM

Mike,

Global tables store their data in a variable called "data" in the global table object. That is why you are seeing "data" for the name. In the code that I posted you will see that there are two components (location and name) used to get a pointer to the table. For global tables the location portion would look more like what you expected.

Using the code I posted above you can get and set the table values by using the variable "tableNode" as the table reference in the different functions. For example the following code would set all of the values from each table to 999 if placed after the Insert comment in the code above:

for(int row = 1; row <= gettablerows(tableNode); row++){
	for(int col = 1; col <= gettablecols(tableNode); col++){
		settablenum(tableNode, row, col, 999);
	}
}

I hope this helps,

Brandon

4 ·

Mike Mayer commented · Jun 08, 2016 at 07:34 PM

Thank you Brandon, that does help! I can see why it was giving me "data" before. -Mike

0 ·

Ben Wilson ♦♦ Mike Mayer commented · Jun 08, 2016 at 07:40 PM

@Mike Mayer, I converted the above into a comment. If you're responding to someone's answer or comment, you can comment on the answer or reply to the comment.

Please reserve an official 'Answer' only for an answer that addresses the post's topmost question. Conversations are meant to happen using comments.

See Answers Best Practices for tips on using a Q&A site. Its a bit different from an old-school forum.

Thanks!

0 ·

question