topic Re: How to use SQL to find an available rack slot within a specific bay range in FlexSim Forum

How to use SQL to find an available rack slot within a specific bay range

jon_abbott — Mon, 16 Dec 2019 19:24:31 GMT

[ FlexSim 20.0.0 ]

I am using a SQL query similar to below to find an available rack slot in a specific rack:

Storage.system.findSlot("WHERE slot.storageObject.name = 'Rack1' ORDER BY RAND()",0)

How can I adjust this to only consider a specific range of bays within that rack? For example, assume Rack1 has 30 bays. I would like to have the SQL query only consider bays 5-10 of Rack1 for returning an available slot, and I would like to use SQL to accomplish this. Thanks in advance for any help you can provide.

Re: How to use SQL to find an available rack slot within a specific bay range

tanner_p — Mon, 16 Dec 2019 19:34:45 GMT

Hi @Jon Abbott,

I think you can narrow your search using a BETWEEN statement in the code you have. Example:

Storage.system.findSlot("WHERE slot.storageObject.name = 'Rack1' AND bayNumber BETWEEN 5 AND 10 ORDER BY RAND()",0)

You would just need find how to access the appropriate column that represents the bay number.

Re: How to use SQL to find an available rack slot within a specific bay range

jon_abbott — Mon, 16 Dec 2019 19:47:49 GMT

Hi @tanner.p, thank you for the quick response. I tried this but it returns <no path>, which isn't the behavior I was expecting. I'm not using a table to represent the rack, so there is no column for bayNumber to refer to. Normally I can run the findSlot command using the Script Console and it returns a node like this:

Node: Rack1>variables/bays/29/levels/2/slots/1

I'm looking for a way to write the SQL query in a way that doesn't use a variable but instead uses the rack's own storageObject properties so that it returns a node like above but limited to a specific bay range.

Re: How to use SQL to find an available rack slot within a specific bay range

tanner_p — Mon, 16 Dec 2019 20:03:26 GMT

@Jon Abbott

Bummer that didn't work. It seemed like the easiest thing to do.

I asked a colleague for an alternative suggestion and he thinks it might be easiest to use labels on the slots. This way, if you have certain slots that you want to pick from, you can still use SQL, but you'll look for slots with a specific label. I think this should work as long as you know beforehand which slots are go and which slots are no-go.

Re: How to use SQL to find an available rack slot within a specific bay range

jon_abbott — Mon, 16 Dec 2019 20:17:39 GMT

Thanks @tanner.p. It seems redundant to add labels to each slot that indicate which bay it is, when the bay number is already a property of the slot... in the documentation, there is an entry for Storage.Object.bays... is there a way to reference this using SQL?

Re: How to use SQL to find an available rack slot within a specific bay range

anthony_johnsonT83CM — Mon, 16 Dec 2019 21:09:11 GMT

Storage.system.findSlot("WHERE slot.storageObject.name = 'Rack1' AND slot.bayID BETWEEN 5 AND 10 ORDER BY RAND()",0)

When you use the "slot." prefix, it assumes you are accessing the slot as a Storage.Slot FlexScript object, so you can access any of the members available in that interface. From the manual:

"If the slot's storage object has an Address Scheme, the slot's bay ID is formatted according to that address scheme. In other words, if the address scheme's bays are addressed with a letter, bayID will be a string, otherwise bayID will be a number. If there is no address scheme, bayID will be the bay number."

I assume you are not using an address scheme, or if you are, the bays are defined by numbers. Thus, in your case you can assume slot.bayID will give you back a number, so you can use BETWEEN in SQL.

The other, more verbose, way to access the rank of the bay would be:

Storage.system.findSlot("WHERE slot.storageObject.name = 'Rack1' AND slot.bay.as(treenode).rank BETWEEN 5 AND 10 ORDER BY RAND()",0)

This will directly access the rank of the bay, which may be useful if you expressly don't want to use bayID, e.g. if you've set up an address scheme that doesn't give you ranks.

Re: How to use SQL to find an available rack slot within a specific bay range

jon_abbott — Mon, 16 Dec 2019 21:51:22 GMT

Great, thanks for explaining how this works, @anthony.johnson. This behaves exactly how I would expect. I had consulted the manual but hadn't discovered the Storage.Slot page yet. Your explanation helps to tie it all together.