topic Re: Candidate "placeholder" reference for use in a SQL query? in FlexSim Forum

Candidate "placeholder" reference for use in a SQL query?

allysonfrancis — Thu, 12 Jan 2023 20:46:06 GMT

[ FlexSim 23.0.0 ]

My goal is to query an item list for all flow items that are located in a specific parent node.

If I knew the exact flow item in question, I could check this condition by:

if(flowItem.up == parentNode){ //... }

However, I am searching a list of candidates, so I don't know the specific flow item yet. I have seen elsewhere that some search protocols allow something like "candidate.up == parentNode" to allow a function to be executed on candidates during the search, but I can't find anything like this for FlexSim's SQL search functionality. Is there a way?

Re: Candidate "placeholder" reference for use in a SQL query?

Jeanette_Fullmer — Thu, 12 Jan 2023 21:04:41 GMT

Hello @Allyson,

You could use a SQL query like the following

value.up == Puller.PullQueue

Queue == Puller.PullQueue // where Queue is defined in the advanced properties of the list

Pull Query.fsm

Please let me know if you need further assistance with this question

Re: Candidate "placeholder" reference for use in a SQL query?

allysonfrancis — Fri, 13 Jan 2023 00:49:54 GMT

Thanks Jeanette,

So is "value" the wildcard expression that represents a List entry? I tried it in my query and it doesn't work, so I'm trying to make sure I understand what you're suggesting and that my question was clear.

Basically I don't know how to use a FlexScript method in a query without a known object to pass in as the parameter for the method. I'm trying to have the search operation substitute all List objects into the expression and return any which satisfy the condition. So I need to figure out what the wildcard expression is that would enable that to happen, or if there even is one.

Put differently, I understand the right side of the condition equation (how to reference the parent node via the puller parameter) but not the left side of the condition equation (how to compose a query using a FlexScript method that requires the List entry objects as one of the parameters).

Let me know if that doesn't make sense! Thanks again.

Re: Candidate "placeholder" reference for use in a SQL query?

allysonfrancis — Fri, 13 Jan 2023 02:02:18 GMT

Thanks again Jeanette, with some fiddling around I was able to achieve what I need by entering expressions into the List property fields. I figured out that "value" is indeed a wildcard but only in the expressions fields of the List, not in the query string. So I had to reconfigure everything to reference those List expressions, but now it's sorted. Appreciate the nudge in the right direction!

Re: Candidate "placeholder" reference for use in a SQL query?

joerg_vogel_HsH — Fri, 13 Jan 2023 04:03:40 GMT

You get it wrong. The keyword is Puller. You can set in a Process Flow a Reference to be parsed into a query clause. I strongly recommend to read more in the manual about lists. And there is a section about SQL in reference manual under Advanced User Reference

“value“ is used as the testing element in sql source code to read the pushed items in your list. A query source code tests against all entries if a matching set of conditions get fulfilled as true and put those into an internal result table.

Your list has got entries of pushed items. You can push other values onto a list like references of objects, a simple number, a string or a character. And while you query a list to pull from it, you get exactly the value back that you pushed before.

Later you can use a SELECT clause to return field values instead of the pushed value in a query.

The testing conditional clause value.up == Puller.PullQueue assumes that you set for the keyword Puller a reference to the item and that on this item exists a label called PullQueue with a label value that points to the reference of 3D object into your model. This reference is tested against an “up” property of your pushed value of your list. An “up“ property gets the container of the item.
Jeanette suggested to add a field into your list to replace a query clause value.up by an easier clause that reads the name of this field instead. This suggestion does not reduce any writing of source code. It moves the source code from a query string to a field on a list. But the query string looks later more readable because it is shorter. And you can take a look onto your list entries and find a direct reference to the container of your items then.

Re: Candidate "placeholder" reference for use in a SQL query?

jason_lightfoot_adsk — Fri, 13 Jan 2023 13:52:32 GMT

Another thing to consider is partitioning by the item's location which then means you don't need to access puller.PullQueue you can just put that expression in the partition of the pull from list activity (if you have pushed to the correct partition).

Re: Candidate "placeholder" reference for use in a SQL query?

allysonfrancis — Fri, 13 Jan 2023 19:27:54 GMT

Thanks Joerg, perhaps my issue was not clear. As I mentioned above, I understand the right side of the condition equation (i.e. where I would be referencing the parent node via the puller parameter), as this is well documented in the FlexSim manual. The issue I was having was figuring out the left side of the equation, i.e. where the ".up" method would need to be evaluated on each List entry in order to check the condition. I did not know the wildcard term to use to indicate that I wanted the ".up" method to be checked on each entry, as I couldn't find it in any of the FlexSim documentation. But in digging into the List advanced properties (thanks to Jeanette's prompting) and seeing the example expression fields that appear on items lists by default, I was able to infer that "value" is the term used in the expression field as the wildcard that represents any entry. Or as you put it, "the testing element in sql source code to read the pushed items in your list".

Logically this would mean I should also be able to use "value.up" directly in my query string in my FlexScript code, because as you said, this would just be moving the same source code from a field on a list to the query string, but ultimately the executed code would be the same. However when I tried it, FlexSim threw errors, but the exact same query worked just fine when I entered it in the expression field. So rather than try to debug why it would not work directly in my query string, I'm just using expression field, as I know it works. This may be a bug or it may be user error, but either way I didn't want to lose half a day trying to figure it out.

I've also had some issues using "nullvar" in comparisons, both in sql queries and in logical if statements. Sometimes it works directly and sometimes it doesn't, which I assume is due to a type casting issue. So I get around that by declaring a new Object variable initialized with a value of "nullvar", and then use that for the comparison instead. However since I was limited to one puller parameter, I couldn't pass my null object into the query and had to use the expression fields as a workaround for this as well.

Ultimately I went from this (where neither clause worked):

Object parentNode = Model.find("ToteHolding");
String query = "WHERE value.up = puller AND BatchToken = nullvar";
List.PullResult pulled = List("Totes").pull(query,1,1,parentNode,nullvar,LIST_DO_NOT_REMOVE);

to this (which does work):

Object parentNode = Model.find("ToteHolding");
String query = "WHERE ParentNode = puller AND Allocated = 0";
List.PullResult pulled = List("Totes").pull(query,1,1,parentNode,nullvar,LIST_DO_NOT_REMOVE);

Just wanted to explain with better detail and (hopefully) more clarity exactly what I was trying to solve and how I solved it, in case other users find it helpful.

Re: Candidate "placeholder" reference for use in a SQL query?

allysonfrancis — Fri, 13 Jan 2023 19:33:34 GMT

Interesting point! Is the partition dynamic though? I would be keeping the items on the list while they move through the facility so if the partition is only set when the item is pushed, this wouldn't work. But possibly a good option for another use case.

Re: Candidate "placeholder" reference for use in a SQL query?

Jeanette_Fullmer — Fri, 13 Jan 2023 20:41:56 GMT

From a test I did with the following model it does not look like the Partition is Dynamic.

Pull Query_1.fsm

Re: Candidate "placeholder" reference for use in a SQL query?

Jeanette_Fullmer — Wed, 18 Jan 2023 20:56:18 GMT

Hi @Allyson, was Jeanette F's answer helpful? If so, please click the "Accept" button at the bottom of their answer. 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 unaccept and comment back to reopen your question.