Query SQL from Custom Code at Pull from List

Question

question

Borja Lorenzo asked Jan 22, '25 Jeanette F commented Jan 30, '25

Query SQL from Custom Code at Pull from List

I am trying to build an AMR management logic. The flow starts with a Source Event that listens to the input event in the AMRs list.

They move to a pull from list activity from a task list. In addition to the distance label expression, there is the Zone label that is given by the token that provided the input. This label specifies a zone as a concept with a string. I have given the pull the label Container token.Container.

My question is about the next activity of the pull from the AMR list (specific to TE). In this list, there is also a Zone label with the same criteria as the previous one. The values of this label can be BL and ZI.

The selection criteria for the AMR should be as follows:

If the token that does the pull has its value token.Container.Zone=ZI, the AMR that should attend to this query are those that have ZI. If there are no AMRs with the ZI label, this task will be attended by one with the ZB label, prioritizing by distance in both groups.

If the token that does the pull has its value token.Container.Zone=BL, the AMR that should attend to this query are those that are closest, only prioritizing distance.

I have prepared this query but it gives me an error.

I have created a Custom Code in the query field of the pull from list of AMR to try to convert everything to a string. This field could only be passed text or we could resolve the query in the code and pass the selected value (value=AMR).

I have to share it as an image because the website's security blocks it even when I insert it as a code block.

When I run the code, I receive the following in the output console as the result of the debug print:

In the System Console, I receive:

time: 0.000000 exception: Exception caught in start() of activity Desarrollo lógica|código extracción en ZI/Pull from List in process flow "ProcessFlow". Continuing throw...

time: 0.000000 exception: Exception caught in Executive::processeventinlist().

In the Compiler Console, I receive this:

syntax error, unexpected exists

Invalid WHERE statement

Thank you in advance for your help.

Software Version:

FlexSim 24.1.1

list sql query pullfromlist amr

1737541226817.png (117.0 KiB)

1737541272087.png (37.8 KiB)

1737541295258.png (61.4 KiB)

1737541305926.png (25.9 KiB)

1737541418299.png (60.7 KiB)

1737541441656.png (53.9 KiB)

1737541461707.png (72.3 KiB)

1737541470952.png (9.6 KiB)

1737541920638.png (10.4 KiB)

· 1

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.

Jeanette F ♦♦ commented · Jan 30 at 10:13 PM

Hi @Borja Lorenzo, was one of Felix Möhlmann's or Oriol Font's answers helpful? If so, please click the "Accept" button at the bottom of the one that best answers your question. Or if you still have questions, add a comment and we'll continue the conversation.

If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always comment back to reopen your question.

0 ·

Answer 1 · 2025-01-22T11:22:22Z

Felix Möhlmann answered Jan 22, '25

FROM and LIMIT are already given by other settings in the Pull from List activity and should not be part of the query. I also have doubts that the nested query works in the context of this activity.

There isn't a need to put all of the cases into a single query. There are two alternative approaches that I both find easier to follow and setup.

1) Write different cases in the code to begin with and return a query that is tailored to the given "Zona" label on the container.

if(token.Contenedor.Zone == "ZI") {
    return "WHERE Zona = 'ZI' OR Zone = 'ZB' ORDER BY (Zona = 'ZI') DESC, distance ASC";
}
else {
    return "ORDER BY distance ASC";
}

(To add code to a post, select it and mark it as code. It then gets 'encapsulated' somehow, so it doesn't trip any security checks).

2) Use list expressions. For example, here you could define an expression "IsValid".

The query "WHERE IsValid > 0 ORDER BY IsValid DESC, distance ASC" would then satisfy your needs.

Note: The default "distance" field doesn't work in this case, as it tries to calculate the straight-line distance between the value and the puller. Since the puller is a token, this doesn't work and using the straight-line distance likely doesn't make much sense in an AGV network anyway. You can use the following instead. It returns the distance the value (AMR) would need to move to reach the object the container is currently in. (Requires that object to be connected to the network, if a network is used)

1737544347138.png (425 B)

1737544371050.png (425 B)

1737544569688.png (33.6 KiB)

1737544815661.png (22.3 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 2 · 2025-01-22T10:55:50Z

Oriol Font answered Jan 22, '25

Exist syntax doesn't exist in FlexSim, you can chaeck all the query sintax at the end of this page: SQL Queries.

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.

question