article

Commands

The following are commands that are used with Lists. Refer to the command documentation for more information on using these commands.

• listpull
• listpush
• listentries
• listbackorders
• listremove

Query Syntax

In a pull request you may include an SQL query that filters and prioritizes what you want to pull from the List. The query usually will include a WHERE, and/or ORDER BY clause. In some special cases you may use the SELECT clause. It has a special meaning that will be explained later in this topic.

WHERE queueSize < 5 ORDER BY age ASC

This reference documents only information specific to FlexSim's SQL implementation. Refer to commonly available documentation for more general information on the language.

Using List Fields

By default, when you reference a field in a WHERE or ORDER BY clause, it will refer to one of the defined fields on the List.

ORDER BY pushTime

Here, the ORDER BY clause will prioritize by the List's pushTime field in ascending order (in SQL the default is ascending), assuming the pushTime field is a defined field on the List.

Accessing Labels

If a field that you define in your SQL query is not a defined field on the List, then the query will retrieve the label value of the same name on the entry's value object.

WHERE SKU = "05692AQD"

In this example, if SKU has not been defined as a field on the List, and let's say it is an Item List, the query will retrieve the label named "SKU" on the items that have been added to the List, and compare it against "05692AQD".

Puller and Value Table Specifiers

You can also access labels on the puller object using a keyword "Puller" table specifier, followed by a dot.

WHERE step = Puller.step

Here the step field (either a defined List field or a label on the value object) is compared with the step label on the puller object. Here we use "table specifier" in reference to SQL's syntax for accessing a database table. You might conceptualize the List as a database table with the name "Value", and you are kind of doing an inner join with a table with one row in it called Puller, where the labels on the puller object are like fields of the Puller table.

By default if you leave a table specifier out, it assumes it is a defined field on the List or a label on the value object. However you can use a keyword "Value" table specifier explicitly for better readability.

WHERE Value.step = Puller.step

Here you can either use lower case value or upper case Value, and lower case puller or upper case Puller, according to your preference. Here at FlexSim we usually capitalize the table and lower-camel-case the fields.

Using FlexScript Commands in SQL Queries

You can also use FlexScript Commands in your SQL queries.

ORDER BY round(distance / 100) ASC, age DESC

This ORDER BY clause takes the distance field and categorizes it into distance ranges 100 units wide, so that a distance of 130 would be tied with a distance of 95 (round(130 / 100) == round(95 / 100), but would get higher priority than a distance of 200 (round(130 / 100) < round(200 / 100)). Ties would then use the second priority clause and be prioritized by highest age.

ORDER BY getitemtype(value) DESC

This ORDER BY clause gets the item type on the entry value object. Note that this ORDER BY clause would be exactly the same as if you were to use the standard itemType field on an Item List. This example just shows a different way of doing it.

Using the SELECT clause

When pulling from lists with a query, the SELECT clause takes on a special meaning. It allows you to pull things from the list using fluid-like request quantities, instead of discrete entries on the list. Take for example the following listpull() command usage.

listpull("DoughBins", "SELECT kg", 4.5, 4.5)

And assume that the entries on the DoughBins list look like the following when pulled.

Here, instead of trying to pull a number of discrete entries from the list, the listpull() command is trying to pull 4.5 total kg units from the list, no matter how many discrete entries it requires. In this case, the pull operation will pull the full 3.7 kg from Bin1672. Next, it will pull the remaining 0.8 kg from Bin3105. The listpull() command will then return back an array consisting of both Bin1672 and Bin3105.

Overflow Management

This example naturally leads to the question of how the list manages left over amounts, namely the remaining 5.4 kg on Bin3105. And what does the list do about removing entries from the list? Obviously it should remove Bin1672 from the list because it has pulled its full kg amount. But what should it do about Bin3105, which technically has some "overflow" that hasn't been pulled off the list. Should it remove Bin3105 from the list or leave it on? The answer to that depends on the nature of the fields that you are querying in the SELECT clause.

Trackable Overflow

The list will track overflow amounts if the field defined in the SELECT clause meets one of the following requirements.

1. The clause references a dynamiclabel field.
2. The clause references a label that is not explicitly defined as a field on the list.
3. The clause references a non-dynamic field.

If one of these requirements is met, the list will track the values of the fields, decreasing them until they are 0, at which point it will remove the entries from the list. If labels are used (options 1 or 2), then as the pull operation finds valid entries to pull, it will decrement the value of the label by the amount that is pulled. So in this example, the kg label on Bin1672 will be decremented to 0 and Bin1672 will thus be removed from the list. Then the kg label on Bin3105 will be decremented to 5.4, and since it is still greater than 0, it will remain on the list. Subsequent pull operations will pull from the list with Bin3105's kg quantity properly decreased.

When non-dynamic fields are used (option 3), the list stores its own cached value for the field, so the pull operation will decrement its own cached value as quantities are pulled from the list. Admittedly this makes the term "non-dynamic" a bit of a misnomer because the values do change while on the list. In this case, think of the fields instead as "cached" or "non-reevaluated" fields.

Untrackable Overflow

SELECT clauses where the list does not manage overflow amounts include the following:

1. The clause references a dynamic non-label field.
2. The clause is an SQL expression that does not reduce to a single field, i.e. SELECT length * width.

When SELECT clauses with untrackable overflow are used, any entries containing overflow amounts will be removed from the list when they are returned. This means that in the above example, Bin3105 will be removed from the list, even though it technically has 5.4 kg left over.

SELECT Clause Constraints

In list pull operations, the SELECT clause should reference only one "column". In other words, it should not be a comma-separated list of columns like normal SQL queries. Additional columns will be ignored.

Using Lists in the query() Command

You can also integrate lists in sql queries in the query() command. Just include the List as a table named in the FROM clause, and then access its fields normally. Note that here you should not use puller-dependent fields, and for now you can only access the default 0 partition.