Import Data from database into Customs Arrival Table

Question

question

Nicholas B asked May 15 2018 at 4:37 PM Cliff King edited May 23 2018 at 6:07 PM

Import Data from database into Customs Arrival Table

So far I have loaded all of my data from an Access Database into global tables. The only thing I still have left is to load my arrivals by way of a database. I know I have to create a pointer, but I don't know how to point to the schedule of the arrivals module. Or is there a way I can manipulate the custom arrivals table through code.

Software Version:

FlexSim HC 5.3.4

database schedule source generate patients

______

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 · 2018-05-17T16:21:46Z

Matthew Gillespie answered May 17 2018 at 4:21 PM Matthew Gillespie commented May 17 2018 at 5:11 PM

The experimenter has a variable that updates the Custom Arrivals table from a Global Table. It uses code like this to copy the data to the Arrival Object. Also, see the attached model. importcustomarrivalstable.fsm

treenode obj = PatientArrivals1;	
treenode globaltable = reftable("GlobalTable1");
 
treenode table = var(obj, VAR_CustomArrivalsTable);
int needfullmatch = 0;
 
int tablecols = gettablecols(globaltable);
if (gettablecols(table) == 5) // There's sometimes an extra, hidden column for CustomArrivals
	tablecols++;
 
settablesize(table, gettablerows(globaltable), gettablecols(table));
 
int globalprecision = getnodenum(node("/1/environment/settings/precision",maintree()));
for (int row = 1; row <= gettablerows(globaltable); row++)
{
	for (int col = 1; col <= gettablecols(table); col++)
	{
		treenode src = gettablecell(globaltable, row, col);
		treenode target = gettablecell(table, row, col);
		int datatype = getdatatype(src);
		nodeadddata(target, datatype);
		if (datatype == DATATYPE_NUMBER)
			set(target, get(src));
		else if (datatype == DATATYPE_STRING)
			sets(target, gets(src));
			
		if (col == 1 || col == 2)
		{
			assertsubnode(target, "minutes", DATATYPE_NUMBER);
			set(first(target), timestringtominutes(gets(target)));
		}
		
		if (col == 3 || col == 4)
		{
			if (getdatatype(target) == DATATYPE_NUMBER)
			{
				double temp = get(target);
				nodeadddata(target, DATATYPE_STRING);
				sets(target, numtostring(temp, 0, globalprecision));
			}
		}
		else if (col == 5) // Update the "Scheduled" column in Custom Arrivals
		{
			nodeadddata(target, DATATYPE_NUMBER);
			set(target, 0);
		}		
	}
}

importcustomarrivalstable.fsm (49.9 KiB)

· 2

Nicholas B commented · May 17 2018 at 5:06 PM

Thank you! I'm going try this and report back. Essentially I can use treenode table that you've defined and read write as needed? Can I do this during a run or only on run start?

0 ·

Matthew Gillespie ♦♦ Nicholas B commented · May 17 2018 at 5:11 PM

Essentially I can use treenode table that you've defined and read write as needed?

I'm not sure what you're asking. This code just copies data from a global table to the Custom Arrivals Table node of an Arrivals object and formats the data appropriately. The only things you need to change in the code are the first two lines which define the Arrivals object and the global table.

Can I do this during a run or only on run start?

Why do you want to do this during a run? I don't think that would really work. The Arrivals object was designed with the idea that you define all the arrivals and then run the model.

0 ·

Answer 2 · 2018-05-16T18:27:25Z

Jeff Nordgren answered May 16 2018 at 6:27 PM Nicholas B commented May 17 2018 at 3:39 PM

@Nicholas B

I'm not sure that an import from an Access database or not. Probably not, without a lot of manual coding and such. But if you can save your data to an Excel database, there is a way to import that straight into a Source using the Excel interface (see below).

Using the Excel interface would also be easier than trying to write the code to have a Global Table imported into a Source. The Excel interface does all the "hard" work for you. You can also use the Excel interface to import into Global Tables. So I would suggest that you send your data from Access to Excel and then to the import into FlexSim.

Thanks.

user-manual.png (74.4 KiB)

excel-interface.png (65.3 KiB)

· 1

Answer 3 · 2018-05-17T20:56:30Z

Cliff King answered May 17 2018 at 8:56 PM Nicholas B commented May 18 2018 at 8:41 PM

It just so happens I was working on a model where I wanted to dynamically update the Custom Arrivals Table with a daily quantity of patients listed in a Global Table for each of 30 days of simulation. You might wonder, why didn't I just import the daily quantity information into the Custom Arrivals Table directly to begin with. The answer is that the quantity values written in the Global Table may change based on the state of the model during the simulation run. In other words, I wanted to adjust the number of "New Patient" arrivals in my clinic based on congestion caused by return appointments made by some of the previous new patient arrivals.

In the attached model I dynamically create patient arrivals by starting out with just a single row in the Custom Arrivals Table, and then updating the "Number of Arrivals" column of the row using User Events (see toolbox). The trick is that I must update the Custom Arrivals Table just before the software reads the table to create the next block of arrivals at the start of each "Repeat Interval" (every 1440 minutes in my model).

This may not be what you want to do exactly, but at least it will give you some ideas plus the syntax for referencing the Custom Arrivals Table in the tree.

arrivals-generated-dynamically-from-global-table.fsm

arrivals-generated-dynamically-from-global-table.fsm (63.8 KiB)

· 3

Nicholas B commented · May 17 2018 at 10:04 PM

Cliff that is exactly what I am doing. I want to dynamically update my arrivals based on what is going on in my OR during a run. I want control over my arrivals more so than creating an arrival on run start and uploading them. Thanks for the example and I will implement and report back.

0 ·

Cliff King Nicholas B commented · May 17 2018 at 10:08 PM

Oh good! I would love to hear more about what you're able to learn with your simulation project. I'd be glad to lend assistance where I can!

0 ·

Nicholas B commented · May 18 2018 at 8:41 PM

Yes this is exactly what I needed. I knew that their was some way to access the custom arrivals table so that I can change the data at my whim. Now I can query my data on start by loading from a Database and update as needed. This completely eliminates my need for Excel to link to Flexsim, which is exactly what I wanted.

Now my model loads in seconds compared to minutes!

0 ·

question