question

Nicholas M avatar image
0 Likes"
Nicholas M asked Jason Lightfoot edited

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 NumberStep SequenceSetup TimeProcess Time
10014362
10024682
10032119
10111345
10122639
10211529
10222387
10231463
10242878


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

1631213648632.png


ProcessFlow:

1631213689924.png


3D Model:

1631213826046.png


FlexSim 21.2.2
global lookup table
1631213648632.png (25.6 KiB)
1631213689924.png (40.4 KiB)
1631213826046.png (109.7 KiB)
5 |100000

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

2 Answers

Matthew Gillespie avatar image
0 Likes"
Matthew Gillespie answered Jason Lightfoot edited Best Answer

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

  1. int part = 102;
  2. int step = 3;
  3. string queryString = "SELECT [Process Time] FROM RoutingTimes WHERE [Part Number] = " + part + " AND [Step Sequence] = " + step;
  4.  
  5. Table result = Table.query(queryString);
  6. 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:

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

MultiColumnLookup.fsm



multicolumnlookup.fsm (25.6 KiB)
· 10
5 |100000

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

Howard avatar image
0 Likes"
Howard answered Jason Lightfoot edited

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

1707179127813.png

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

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