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

Question

question

Allyson asked Jan 12 2023 at 8:46 PM Jeanette F commented Jan 18 2023 at 8:56 PM

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

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?

Software Version:

FlexSim 23.0.0

sql queries

· 1

______

Cookie preferences

Your privacy is important to us and so is an optimal experience. To help us customize information and build applications, we collect data about your use of this site.

May we collect and use your data?

Learn more about the Third Party Services we use and our Privacy Statement.

Strictly necessary – required for our site to work and to provide services to you

These cookies allow us to record your preferences or login information, respond to your requests or fulfill items in your shopping cart.

YES

Improve your experience – allows us to show you what is relevant to you

These cookies enable us to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we use to deliver information and experiences tailored to you. If you do not allow these cookies, some or all of these services may not be available for you.

YES

NO

Customize your advertising – permits us to offer targeted advertising to you

These cookies collect data about you based on your activities and interests in order to show you relevant ads and to track effectiveness. By collecting this data, the ads you see will be more tailored to your interests. If you do not allow these cookies, you will experience less targeted advertising.

YES

NO

Are you sure you want a less customized experience?

We can access your data only if you select "yes" for the categories on the previous screen. This lets us tailor our marketing so that it's more relevant for you. You can change your settings at any time by visiting our privacy statement

Your experience. Your choice.

We care about your privacy. The data we collect helps us understand how you use our products, what information you might be interested in, and what we can improve to make your engagement with Autodesk more rewarding.

May we collect and use your data to tailor your experience?

Explore the benefits of a customized experience by managing your privacy settings for this site or visit our Privacy Statement to learn more about your options.

Answer 1 · 2023-01-12T21:04:41Z

Jeanette F answered Jan 12 2023 at 9:04 PM Jeanette F commented Jan 13 2023 at 8:41 PM

Hello @Allyson,

You could use a SQL query like the following

value.up == Puller.PullQueue

or

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

pull-query.fsm (32.4 KiB)

· 7

Allyson commented · Jan 13 2023 at 12:49 AM

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.

0 ·

Allyson commented · Jan 13 2023 at 2:02 AM

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!

0 ·

Jason Lightfoot ♦♦ Allyson commented · Jan 13 2023 at 1:52 PM

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).

2 ·

Allyson Jason Lightfoot ♦♦ commented · Jan 13 2023 at 7:33 PM

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.

0 ·

Show more comments

Joerg Vogel Allyson commented · Jan 13 2023 at 4:03 AM

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.

1 ·

Allyson Joerg Vogel commented · Jan 13 2023 at 7:27 PM

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.

2 ·

1673637977365.png (52.3 KiB)

question