question

James Riebau avatar image
0 Likes"
James Riebau asked James Riebau commented

Create a variable rate for process time in global table??

I'm working on a model of case erectors sending cases on conveyors to case packers. I have a total of 16 case erectors for 22 case packers. Different case types can go to multiple different case packers. I'm struggling to get the case erectors to send out cases at a variable rate based on how many case erectors and case packers are currently using a particular case. I have the data on global tables, but I'd like to add the current rate to it, that can change over time.


I know how to do the calculation in excel but I have no idea how to do it in FlexSim, or if its even possible. I exported the tables to excel and added lines (highlighted green and blue) that I'd like to add to the global table. I'm clueless if there is some sort of COUNTIFS equivalent in FlexSim. Can someone help me, or let me know if it's possible to get the same result in FlexSim that I did in excel?

EXPORT_MIL_Case_Automation.xlsx1677596456266.png

FlexSim 23.0.0
processtimeglobal tables
5 |100000

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

1 Answer

Kavika F avatar image
0 Likes"
Kavika F answered James Riebau commented

Hey @James Riebau, there are a few ways you could try to implement the COUNTIFS function in FlexSim for your model.

One way would be to write a custom User Command that performs the COUNTIFS functionality; you can then call that in the Global Table.

1677624037001.png

I wrote a simple one that like this:

/**Custom Code*/
string tableName = param(1);
Variant criteria_row1 = param(2);
Variant criteria1 = param(3);
Variant criteria_row2 = param(4);
Variant criteria2 = param(5);

Table table = Table(tableName);

Array firstCriteria = [];
for (int i = 1; i <= table.numCols; i++) {
  if (table[criteria_row1][i] == criteria1)
  firstCriteria.push(i);
}

int total = 0;
for (int i = 1; i <= firstCriteria.length; i++) {
  if (table[criteria_row2][firstCriteria[i]] == criteria2)
  total++;
}

return total;

It takes in a tableName, two criteria rows, and two criteria (similar to Excel's COUNTIFS function). It will loop over the criteria rows found in the specified table and match the values in the row against the criteria. Call this function in each of the CEmatchCase and CPmatchCase rows and update the colNum (see image above) so it pulls the right values.


Another possibility is to transpose your data and use a Table Query. Here's an example:

1677624628852.png

Table t = Table("CaseErectors1");
int rowNum = 2;
return Table.query("SELECT COUNT(*) FROM (SELECT * FROM CaseErectors1 WHERE Side = $1 AND CaseTyp = $2)", t[rowNum]["Side"], t[rowNum]["CaseTyp"])[1][1];

This query will Select every row in the CaseErectors1 table where the Side is equal to the rowNumber's Side and CaseTyp is equal to the rowNumber's CaseTyp. Then it will count the rows returned. The Table.query() will return a table, so I extract the count from the first cell.

I didn't fill out the CPmatchCase row/column equations, but they are similar to the CEmatchCase row/column. You can perform your equations like normal in the Rate rows/columns using FlexScript.


Here's the model I used to experiment with these things. update_rates.fsm


1677624037001.png (28.4 KiB)
1677624628852.png (38.6 KiB)
update-rates.fsm (28.6 KiB)
· 3
5 |100000

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

James Riebau avatar image James Riebau commented ·

Alright I think I'm on the right track now. I ended up copying the custom user command above. To get the values in the table I wrote them to a different line from process flow. I'm sure there is a better way, but it works for me. Thanks @Kavika F and @Jason Lightfoot for the help.

1 Like 1 ·
James Riebau avatar image James Riebau commented ·

Hi @Kavika F, thanks for your response. I'm not sure I understand how to see the results. I tried to use the Table.query method, transposed my data, and copied in the code with table names updated. All I see is the code in the cell, not a result.

It seems like the same thing in your example model. I tried to press execute script and the model crashes. I'm probably missing something basic, because of my lack of coding knowledge.

0 Likes 0 ·
Jason Lightfoot avatar image Jason Lightfoot ♦ James Riebau commented ·

Here's an approach to create the table using a single query:

Table.query("SELECT a.CaseErector, a.Side, a.CaseTyp, a.Quantity, a.Remaining,\
            SUM(1) OVER (Partition BY a.Side, a.CaseTyp) AS CEmatchCase, c.CPmatchCase,\
             5*c.CPMatchCase AS CPRateNeeded, 60/(5*c.CPmatchCase)*CEmatchCase AS CECurrentRate \
              FROM CaseErectors1 a ,(SELECT Side,CaseTyp, COUNT(*) AS CPmatchCase FROM CasePackers1 GROUP BY Side,CaseTyp) c \
               WHERE a.Side=c.Side AND a.CaseTyp=c.CaseTyp" ).cloneTo("Temp")

It relies on transposing the CasePackers table (done with tableTranspose() user command in the attached model).

update-rates_jl_transpose.fsm

At the moment the script writes the result to the Temp table - you could also use it to update or overwrite you CaseErector table. You'd need to update it whenever you allocate a CasePacker/Erector to a different Side or CaseTyp.

0 Likes 0 ·