question

Kyle P avatar image
1 Like"
Kyle P asked Matthew Gillespie edited

Order by statement ignored in Backorder query

I am pulling task sequences from a task sequence list, with task executors perpetually pulling from the TSlist. This is achieved by putting the "Pull from Task Sequence List" code in the "OnResourceAvailable" trigger.

Everything seems to work fine, until a backorder is needed to be filled. I have a dynamic variable checking a dummy location and toggling to 1 if the location is onshift and 0 if the location is offshift.

On offshift, the backorder looks as follows:

Then, before the reevaluation event when the dummy location comes onshift, we would expect the task executor to pull from the top of the following query, but it actually pulls the highlighted line:

And running the query without the ORDER BY statement, yields this table:

(ts7249 would have been on top except I ran the query after it was sent to the task executor)

If this is working as intended, it would help for me to understand the sequence/actions the backorder query takes to get its result. Any insight on this would be greatly appreciated.

FlexSim 18.0.7
listtask executertask sequencebackorder
xx74r.png (34.3 KiB)
ufulv.png (126.1 KiB)
xdjmo.png (39.4 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.

Kyle P avatar image Kyle P commented ·

ts-backorder-example.fsm

I have attached a model highlighting the issue with a good example occurring around Jan 23, 2017 at 7:30am model time.

0 Likes 0 ·
Matthew Gillespie avatar image Matthew Gillespie ♦♦ Kyle P commented ·

Can you give some more details about what we're looking for at 7:30? What task sequence is being pulled that you don't think should be?

0 Likes 0 ·
Kyle P avatar image Kyle P Matthew Gillespie ♦♦ commented ·

Here is me manually querying the table just before the backorder reevaluates the list. This shows that the next task sequence should be for the crane to unload the train. But that is not the task sequence it executes when the backorder is re-evaluated. This screen shot was taken at January 23, 7:45am.

@Matthew Gillespie Thank you for your reply.

0 Likes 0 ·
lqryr.png (92.4 KiB)

1 Answer

Matthew Gillespie avatar image
3 Likes"
Matthew Gillespie answered Kyle P commented

This is working as intended

That's the short answer. When back orders are reevaluated the entries are considered one at a time starting at the top. The back orders are sorted according to the Back Order Queue Strategy and then the best back order that wants the entry gets fulfilled. Notice, that the back orders are being sorted, not the entries. And the back orders can be sorted in a different order for each entry if the queue strategy sorts based off some characteristic of the entry (like the distance field). This behavior works for the majority of models, except for situations like yours where you suddenly make a bunch of entries available at once.

The only way to get around this is previous to 18.2 is to control the list more tightly. Either don't push entries on the list until they're actually ready to be pulled, or find a way to not create a back order.

18.2 lets you choose the behavior you want

You're not the first person to run into this issue and in 18.2 we added a "Sort Back Orders Before Evaluating Entries" checkbox to the List properties. Checking this box will get you the behavior you want. Instead of considering each entry, the back orders are sorted once and then each back order is considered. If you can update to 18.2, I would recommend it and then you just need to check the box.

Here's the documentation of that checkbox in 18.2 (note that leaving the box unchecked is how the list worked before 18.2):

Sort Back Orders Before Evaluating Entries
This setting determines how back orders will be sorted when they are reevaluated for all entries on the list. The default is not checked. Here the list will iterate through each entry on the list, sort back orders associated with that specific entry, then match the highest priority back order to that entry. Alternately, when this box is checked, the list will sort the back orders once, then iterate through each back order and fulfill that back order based on its pull query.
Leave this Box Unchecked If the Back Order Queue Strategy Relies on Entry-Dependent Fields
When this box is checked you are not allowed to include anything in the back order queue strategy that accesses data associated with a list entry. Since the list sorts back orders first for all entries, there is no single entry to compare back orders against.
As an example, let's say you are simulating a list of nurses, and back orders for nurses represent the patients who are requesting them. When one or more nurses become available, they may want to sort the requesting patients based on the distance from the nurse to the patient. Such a back order queue strategy might be: ORDER BY distance. In this case the distance field needs both a nurse (an entry) and a patient (a back order) in order to calculate the distance between the two. Here you want the list to go through each newly available nurse, and sort back orders (the requesting patients) by the distance from the nurse to the patient. This desired functionality is enabled when this checkbox is unchecked.
Check this Box If the Pull Query ORDER BY Clause Should Still Be Used When Multiple Entries Become Available at the Same Time
When this box is checked, the list will sort the set of back orders once, then go through each back order and attempt to fulfill it based on its pull query. This scenario allows for the back order's pull query to still be in effect when multiple entries become available at the same time. In going through each back order, the list will evaluate the back order against all of the available entries, meaning the back order pull query's ORDER BY clause will take effect, and available entries will be sorted accordingly.
· 1
5 |100000

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

Kyle P avatar image Kyle P commented ·

Top quality answer. Thank you for looking into this. I will get with my IT to get my FlexSim version current.

0 Likes 0 ·