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

Question

question

Mike Mayer asked Jun 08 2016 at 3:42 PM Brandon Peterson edited Jun 17 2016 at 6:27 PM

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

______

Cookie preferences

Your privacy is important to us and so is an optimal experience. To help us customize information and build applications, we collect data about your use of this site.

May we collect and use your data?

Learn more about the Third Party Services we use and our Privacy Statement.

Strictly necessary – required for our site to work and to provide services to you

These cookies allow us to record your preferences or login information, respond to your requests or fulfill items in your shopping cart.

YES

Improve your experience – allows us to show you what is relevant to you

These cookies enable us to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we use to deliver information and experiences tailored to you. If you do not allow these cookies, some or all of these services may not be available for you.

YES

NO

Customize your advertising – permits us to offer targeted advertising to you

These cookies collect data about you based on your activities and interests in order to show you relevant ads and to track effectiveness. By collecting this data, the ads you see will be more tailored to your interests. If you do not allow these cookies, you will experience less targeted advertising.

YES

NO

Are you sure you want a less customized experience?

We can access your data only if you select "yes" for the categories on the previous screen. This lets us tailor our marketing so that it's more relevant for you. You can change your settings at any time by visiting our privacy statement

Your experience. Your choice.

We care about your privacy. The data we collect helps us understand how you use our products, what information you might be interested in, and what we can improve to make your engagement with Autodesk more rewarding.

May we collect and use your data to tailor your experience?

Explore the benefits of a customized experience by managing your privacy settings for this site or visit our Privacy Statement to learn more about your options.

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

Brandon Peterson answered Jun 08 2016 at 4:20 PM Ben Wilson edited Jun 08 2016 at 7:40 PM

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 5: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 6: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 7: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 7: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