Import Data from database into Customs Arrival Table

Question

question

Nicholas B asked May 15, '18 Cliff King edited May 23, '18

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

5 |100000

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

Answer 1 · 2018-05-17T16:21:46Z

Matthew Gillespie answered May 17, '18 Matthew Gillespie commented May 17, '18

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

5 |100000

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

Nicholas B commented · May 17, 2018 at 05: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 05: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, '18 Nicholas B commented May 17, '18

@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

5 |100000

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

Nicholas B commented · May 17, 2018 at 11:37 AM

Thanks for the reply. All my data is in Access and I don't want to have to link it to excel and than import it into FlexSim. The excel interface is slow when you have a lot of data. I am looking for the code to import data directly into a source. I just need help with finding the pointer to the source. How that is done so I can load the source on run start or even manipulate it during the run.

0 ·

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

Cliff King answered May 17, '18 Nicholas B commented May 18, '18

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

5 |100000

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

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 08: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