How to perform global table lookup using multiple columns

Question

question

Nicholas M asked Sep 9, '21 Jason Lightfoot edited Mar 26, '24

How to perform global table lookup using multiple columns

Essentially the title of the question; I have a global table containing the process times of every part number that could possibly run through the facility being modeled. The flow items follow a schedule imported from Excel into the ProcessFlow, thus I created a Custom Code block to change the process time of each machine according to the part number label attached to each token. I've managed to get that bit working, but the added layer of complexity is that there are different steps that correspond to different processing times. Is there some way to have the Global Table lookup first search column 1 to find the correct part number, then search column 2 to match the correct step sequence to then return the correct processing time in column 4?

I cannot post my actual data due to security but it is very similar to this:

Part Number	Step Sequence	Setup Time	Process Time
100	1	43	62
100	2	46	82
100	3	21	19
101	1	13	45
101	2	26	39
102	1	15	29
102	2	23	87
102	3	14	63
102	4	28	78

Currently, the code that works with just looking up using column 1 is this:

ProcessFlow:

3D Model:

Software Version:

FlexSim 21.2.2

global lookup table

1631213648632.png (25.6 KiB)

1631213689924.png (40.4 KiB)

1631213826046.png (109.7 KiB)

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 · 2021-09-09T20:26:08Z

Matthew Gillespie answered Sep 9, '21 Jason Lightfoot edited Feb 2, '24

You can use the Table.query() method and write an SQL query to find the row that has the correct part and step number:

int part = 102;
int step = 3;
string queryString = "SELECT [Process Time] FROM RoutingTimes WHERE [Part Number] = " + part + " AND [Step Sequence] = " + step;

Table result = Table.query(queryString);
return result[1][1];

Alternatively, assuming your data is always nicely ordered like in your example above, you could use the Table.getRowByKey() method to find the the first row for the part number and then use the step number to go down the correct number of rows:

int part = 102;
int step = 3;
Table table = Table("RoutingTimes");
int row = table.getRowByKey(part) + step - 1;
return table[row][4];

MultiColumnLookup.fsm

multicolumnlookup.fsm (25.6 KiB)

· 10

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 M commented · Sep 10, 2021 at 10:46 AM

That solution is perfect thank you!

1 ·

Howard commented · Jan 30 at 08:31 PM

Hi Matthew,

Can you explain what are the function of the "+" before/ after the part and step?

string queryString = "SELECT [Process Time] FROM RoutingTimes WHERE [Part Number] = " + part + " AND [Step Sequence] = " + step;

0 ·

Regan Blackett ♦ Howard commented · Jan 30 at 11:35 PM

@Howard the + sign means to concatenate the values of the variables "part" and "step" at those points in the SQL query. Based on the values show in Matthew's code, where part = 102 and step = 3, when parsed the query would read:

 "SELECT [Process Time] FROM RoutingTimes WHERE [Part Number] = 102 AND [Step Sequence] = 3";

0 ·

Howard Regan Blackett ♦ commented · Jan 31 at 12:32 AM

Thank you Regan.

0 ·

Show more comments

Answer 2 · 2024-02-06T03:41:05Z

Howard answered Feb 6, '24 Jason Lightfoot edited Mar 26, '24

So I go ahead and set Global Variables for part, stepseq, stepno and add the SQL code in the Global Macros.

I updated the data so it looks more correct for each process steps.

I hardcoded the "part", "stepseq" in the Queue and "step no" on each of the processor and just load different part in the source. Also, I put in the code to pull from the Global Macros on each processor.

The attached model is giving an error for label issue.multicolumnlookup_V1.fsm

1707179127813.png (3.6 KiB)

multicolumnlookup-v1.fsm (37.7 KiB)

· 12

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.

Howard commented · Feb 06 at 06:43 PM

@Jason Lightfoot Please guide me through my current model and let me know what the issue is preventing me from pulling the data on each machine. Big thanks.

0 ·

Jason Lightfoot ♦♦ Howard commented · Feb 07 at 12:45 AM

Here's your model altered to:

1) Put the partSeq on the processors

2) Use templates from one of the processors

3) Use a pull to only pull the items for which you have specified a process time.

4) Fixed the process time expression and added the Setup Time expression

5) Changes to use one queue and set the opseq based on the port by which an item exits.

6) Changes to generate 20 parts

7) Change the queue entry to randomly generate the part number and color the item based on that.

I still think there's a mismatch in what you've explained (or what I think you've said) and the data that defines the operations. Usually it's just a question of looking up the part and operation number to see what time it takes on that machine. I would concentrate on having that work first and then look at branching to alternative operation sequences and timings.

You still have _1_3 and _1_4 in your data for which there are no processors. You'll also see that the model stops when a part finishes in the first process and cannot move to the second since there is no map entry for it on the that processor.

multicolumnlookup-v1_jl.fsm

0 ·

multicolumnlookup-v1-jl.fsm (35.8 KiB)

Howard Jason Lightfoot ♦♦ commented · Feb 26 at 07:06 PM

Hi Jason,

I have added Seq 1's step #3 and #4 processors but then the box doesn't go to Seq 1 now. The data set is updated to reflect to the latest process map. (see attached file)multicolumnlookup-v1-jl 20240226.fsm

Please see the processor map for what I am trying to do.

Can you explain how did you assign the Seq 0 and Seq 1 to the item?

What is the blue line that connect all processors to processor _0_1?

0 ·

process-flow.png (14.0 KiB)

multicolumnlookup-v1-jl-20240226.fsm (36.4 KiB)

Show more comments

question