Skip to content
P4 Software P4 Software / logisticaHQ-en

Add these docs to Claude

Ask Claude questions about these docs. Claude reads the published pages and links you back to them. Copy this address:

https://p4-software.opendocs.page/mcp

In claude.ai or Claude Desktop: open Settings, then Connectors, choose Add custom connector and paste the address.

In Claude Code, run:

claude mcp add --transport http docs https://p4-software.opendocs.page/mcp

Handheld bin queries

Configuration: the handheld bin queries
Applies to: every LogisticaHQ Tenant (release 2.56.22 and later)Roles required: Administrators only. The page that shows these settings is the Administrator Configuration screen, and theโ€ฆ

Overview

When an operator on the Handheld (the gun) receives stock, receives a return, or moves stock directly, the gun asks LogisticaHQ for a short list of Bins where the stock could go. The list comes from a SQL query stored in the company's Configuration. There are three of them:

Setting (as listed in Configuration) Where the gun uses it
Advanced > Receiving > Putaway Query (Advanced_Receiving_Handheld_PutawayQuery) When a purchase order line is received.
Advanced > Returns > Putaway Query (Advanced_Returns_Handheld_PutawayQuery) When a returned (RMA) line is received.
Advanced > Direct move > Recommended Bins (Advanced_DirectMove_Handheld_RecommendedBins) When an operator moves stock directly and the gun recommends Bins.

Until this release those three settings were plain text boxes. They now open in the same SQL workbench the Query builder uses, with suggestions for tables, columns and parameters, ten ready-made examples, a clear sign of whether the shipped default is in use, and a Test with a product panel that runs the query exactly as the gun would and shows the Bins in the order they would be offered. A query that fails its test cannot be saved.

What you see: the setting's label and description on the left, the status line at the right of it, the editor with its toolbar below, the validity line under the editor and, under that, the Test with a product panel.

Navigation

Setup > Configuration, then open Advanced in the left list and choose Receiving, Returns or Direct move. You can also press Ctrl+K and type "putaway" or "recommended Bins", or use Find a setting at the top of the page; the result opens the exact row.

All other multi-line settings in Configuration keep their ordinary text box. Only these three names use the editor.

How the gun uses the result

Knowing this makes every message in the editor easy to read.

  1. The gun runs your query on the company's read-only copy of the database each time the operator reaches the put-away (or recommended Bins) step.
  2. The gun hands the query four parameters. The query may use any of them and nothing else:
Parameter Receiving Returns Direct move
@WarehouseId The Warehouse the operator is Receiving in. The Warehouse the operator is Receiving the return in. The Warehouse the operator is moving in.
@ProductId The product on the purchase order line. The product on the return line. The product being moved.
@PacksizeId The packsize scanned; empty for an each. The packsize scanned; empty for an each. The packsize being moved; empty for an each.
@Quantity How many units are being put away. How many units are being put away. How many units are being moved.
  1. The gun reads the first column of each row as text, in the order the rows come back, and shows each line to the operator. By convention the first column is BinCode @ ZoneCode, for example A-01-03 @ RECEIVE-A, which is what every example below Returns. Other columns are ignored.
  2. Row order is the offer order. The first row is offered first. Put an order by in your query; without one the database may return rows in any order.
  3. An empty result is allowed. The gun then asks the operator to scan a bin. That is not an error.
  4. An empty setting uses the shipped default. If the stored text is empty or blank, the gun runs the shipped default query instead, so clearing the box never leaves the gun without suggestions.

The screen, part by part

1. The row: label, description and status

  • Label (for example Putaway Query or Recommended Bins): the name of the setting. Clicking it puts the cursor in the editor.
  • Description under the label: what this setting is used for, plus two sentences: "The gun reads the first column of each row as text." and "The gun lists every row the query Returns, so keep it short; the shipped default Returns the top 10." The first part of the wording is specific to each of the three settings. The gun does not cut the list at 10 rows: it shows every row, one per line, so the "top 10" belongs to the shipped default query, not to the gun.
  • Status at the right of the same line:
  • Shipped default in use: the text equals the shipped default (ignoring line endings and spaces at the ends) or is empty.
  • Edited ยท differs from the shipped default: the text is different. The Restore the shipped default button appears next to it.
  • After a test has run in this visit to the page, the status adds ยท last tested HH:mm. It is not remembered between visits.
  • If the shipped default could not be read from the server, the Restore button and example 1 are hidden; everything else still works.
  • Restore the shipped default: puts the shipped default text in the editor. It is undoable (see "Undo chip"). Nothing is saved until you press Save.

What you see: the status line reads "Edited ยท differs from the shipped default" and the Restore the shipped default button sits right next to it.

2. The toolbar

  • Read-only badge: the query is only ever run on the read-only copy of the database. Hover for the explanation: "Queries run on a read-only copy of the database. Nothing can be changed from here."
  • Hint: "Type a table name and a dot for its columns, or @ for a parameter. Ctrl+Space lists everything."
  • Examples (10): opens the Examples menu (below).
  • Clear: empties the editor (disabled when it is already empty). An empty setting means "use the shipped default". There is no confirmation dialog: the amber chip Cleared the query appears with an Undo button that brings your text back (see "The Undo chip"). Ctrl+Z in the editor and Discard at the bottom of the page also work.
  • There is no Format button in this release.

3. The editor and its suggestions

The editor is the same one the Query builder uses.

  • Type a table name and a dot (for example Bins.) to see that table's columns. Each suggestion shows the column's data type and its table, for example nvarchar ยท Bins.
  • Type @ to see the four parameters, each with its meaning for this setting.
  • Ctrl+Space lists everything that fits at the cursor. Up/Down choose, Enter inserts, Esc closes.
  • Only table and column names that exist in the company's database are suggested.
  • Ctrl+Enter runs the test (when the Run test button is enabled).

What you see: after typing Bins.Al a small list opens under the cursor with the columns of Bins that start with those letters, each with its data type and table.

4. The side panel

  • Tables: a search box and the list of the company's tables. The arrow beside a table unfolds its columns with their types. Click a table or column name to insert it where the cursor is.
  • Parameters: the four parameters with this setting's wording.
  • Database query library: opens the user documentation page with example queries in your language.

5. The validity line

Under the editor a coloured pill tells you at once whether the text can work on the gun. It is computed in your browser as you type; the server checks again with its own, stricter read-only check, which remains the authority.

Pill Meaning
Green: Valid: one read-only SELECT, N parameters used (or "1 parameter used", "no parameters used") One SELECT (or WITH ... SELECT), nothing that changes data, only the four known parameters. N is how many of the four the text uses.
Grey: Empty: the shipped default query is used The text is empty; the gun will run the shipped default.
Red: Only a SELECT query can be saved here. Start with SELECT. The text starts with something else.
Red: Nothing can be added, changed or deleted here. Remove INSERT. (or UPDATE, DELETE, MERGE, DROP, ALTER, CREATE, TRUNCATE, EXEC, EXECUTE, DECLARE, GRANT, REVOKE, WAITFOR, BACKUP, INTO) One of those words appears in the query outside quotes and comments.
Red: Only one query is allowed. Remove everything after the first semicolon. A second statement follows a semicolon. A trailing semicolon alone is fine.
Red: The query uses @Foo, which the gun does not give. Use only @WarehouseId, @ProductId, @PacksizeId, @Quantity. A parameter other than the four. @@ROWCOUNT and similar are not parameters and are ignored.

Words inside quotes ('...'), comments (-- ... and /* ... */) and [bracketed names] are ignored by this check.

The caption at the right of the pill says Changes apply when you press Save at the bottom of the page. When the text differs from what is saved, is not empty, has no problem, and has not been tested in this visit, it says instead Not tested yet with this text. Press Run test below before you save. That is a nudge, not a block.

6. The Examples menu

Press Examples (10). The menu opens with a short introduction, three groups and ten examples, then a Closing note. Each item shows a title, a description and, for the first one, a shipped default tag. All three settings offer the same ten examples, because they use only the four parameters the gun always passes. Their SQL is listed in full at the end of this page.

  • Picking an example replaces the text in the editor. There is no confirmation dialog: instead an amber chip appears (see below) with an Undo button.
  • The example you loaded is highlighted in the menu and tagged loaded while the text is still exactly the example.
  • Example 1 is the shipped default fetched from the server; it is never a copy kept in the screen, so it cannot drift from what the gun uses when the text is empty.

What you see: the Examples menu open, with the introduction at the top, the three group headings, the ten items (title and description) and the Closing note; the first item carries the "shipped default" tag and the item you loaded carries the "loaded" tag.

7. The Undo chip

After you pick an example, press Restore the shipped default or press Clear, the status area shows an amber pill Example loaded: <title> (or Restored the shipped default, or Cleared the query) with an Undo button.

  • Undo puts back exactly the text that was there before, and the chip goes away.
  • The chip disappears as soon as you type in the editor, because Undo would then silently throw your typing away. After that, use Ctrl+Z in the editor.
  • The chip is also cleared when you save or discard.
  • Nothing is saved by loading an example: it only changes the text in the page until you press Save.

What you see: where the status line normally is, an amber pill reading "Example loaded: Top up where it already is" with an Undo button next to it.

8. Test with a product

This panel runs the query above exactly as the gun does, on the company's real stock data, so you can see which Bins a product would be offered. Nothing is saved or moved: the test is read-only (see "Protections").

Control What it does
Warehouse The Warehouse passed as @WarehouseId. The list holds the active Warehouses by code; the first is preselected.
Product A search box. Type at least two characters of the SKU or the description and wait a moment: up to eight matching active Products appear. Use Up/Down and Enter, or click, to choose; Esc closes the list. The chosen product shows as "SKU ยท Description" and is passed as @ProductId. Typing again clears the choice. If nothing matches, the list says "No product matches this search." Discontinued Products are not offered; a 3PL user only finds their own client's Products.
Packsize Each (empty, passed as @PacksizeId = NULL) or one of the chosen product's Packsizes, shown as "Name (N each)". Disabled until a product is chosen and reset to Each when the product changes.
Quantity The number of units passed as @Quantity. It must be more than zero. Default 1.
Run test Runs the test. Disabled until a Warehouse and a product are chosen, the quantity is more than zero and the query has no problem; while it runs it reads "Running...". Under the fields a note says "Choose a product to run the test." until a product is chosen.

If the editor is empty, the test runs the shipped default, because that is what the gun would run.

Administrators only. If the server refuses the first request the editor makes (the shipped defaults, which only an Administrator may read), the controls are hidden and the panel says Only administrators can test these queries. instead; Run test then stays off and a test can never fail for that reason.

What you see: a light-blue panel titled "Test with a product" with the Warehouse list, the Product search box, the Packsize list, the Quantity box and the Run test button in one row.

9. The test result

  • Success with Bins: N Bins in M ms (or 1 bin in M ms), "in the order the gun would offer them", then one numbered chip per bin. Chip 1 is marked offered first. At the right: "Result as the gun shows it: one line of text per bin."
  • Only the first 50 Bins are shown. appears when the result reached the limit of 50 rows. The gun itself reads all rows; the test stops at 50 so a big Warehouse does not slow the page.
  • No Bins: "The query returned no Bins. That is allowed: the gun then asks the operator to scan a bin." This is neutral, not an error.
  • Failure: a red box with the database's own message for a mistake in the query, for example Invalid column name 'X'., and below it "A query that fails here cannot be saved." Two kinds of failure use a fixed sentence instead of the database's text, so no server, database or login name is ever shown: when the read-only database cannot be reached the box reads "The read-only database could not be reached. Try again in a moment.", and for any other problem that is not a SQL error it reads "The query could not be run. Check that it Returns one text column and uses only the four parameters." A query the server's read-only check refuses is reported with that check's own sentence (in English), and a company without a read-only database reads "This company has no read-only database to test on."
  • Time limit: a test is stopped after 5 seconds with "The query took longer than 5 seconds and was stopped. Make it simpler or narrow the Bins it looks at."
  • A product that no longer exists (or is discontinued) is reported in the same red box.

What you see: a line such as "3 Bins in 41 ms ยท in the order the gun would offer them" and, under it, one rounded chip per bin with its position number; the first chip is highlighted and marked "offered first".

What you see: after a failed test a red box with the message and, under it, the grey note "A query that fails here cannot be saved."; the validity line and the Save button show that Save is off.

Saving

The page's Save and Discard buttons at the bottom work as for every other setting. Save sends the query trimmed of spaces at both ends.

  • A query that failed its test cannot be saved. After a failed test of the exact current text, Save is switched off and a message beside it reads Save is off for <label>: The last test of this query failed. Change the query and run the test again. Changing the text lifts the block; run the test again to check the new text.
  • A changed query with a problem in the validity line (red pill) also switches Save off, with the problem as the reason.
  • A valid query that has not been tested can be saved. The "Not tested yet" note is a reminder. Test first whenever you can: the database is the only judge of a column or table name the editor could not know.
  • An unchanged query never blocks Save, so a company whose stored query was already unusual can still save other settings on the page.
  • Other settings on the page save exactly as before; only a changed, blocked query holds the Save button.
  • Discard puts the query back to what was last saved.

Protections

The editor and the test cannot change data. Three independent locks apply:

  1. One SELECT only. The server reads the query with Microsoft's own T-SQL parser and refuses anything that is not a single SELECT over the company's own tables, including sneaky spellings, other databases, System tables and tables that hold passwords or keys.
  2. A read-only login. The test uses the company's reader connection.
  3. A transaction that is always rolled back. Even if something got through, nothing would be kept.

In addition, the test has a 5 second limit and a 50 row cap, and the server does not write the query text or its rows to any log.

Why Administrators only. The query is run for real, on real stock data, and a stored query decides what every operator is offered, so editing and testing it is limited to Administrators. This is also why the test reads the live data: only the live data tells you what the gun will really do.

Common questions

  • The gun offers nothing. Run the test with a product that should have a bin. An empty result means no row matched; loosen the conditions, or use example 3 as a fallback.
  • The order is wrong. The gun offers rows in the order they come back; add or fix the order by.
  • I want one query for all three settings. Copy the text into each; they are independent settings.
  • I cleared the box by mistake. Press Undo on the amber chip right away, or press Discard before saving. If you save an empty box, the setting uses the shipped default.
  • The query works in the test but the gun shows nothing for another product. Test with that product and packsize; many conditions depend on the product (category, client, hazmat, expiry).

The ten examples

Every example starts with a comment line listing the parameters, uses only those four parameters, and Returns BinCode @ ZoneCode as its first column. They are the same for Receiving, Returns and Direct move. Several of them rest on columns that stand in for a business idea (the bin sequence number as walking order, the maximum and minimum quantity of a bin as capacity, "allow several pallets per bin" as the mark of a pallet zone, the expiry date of the stock): run each one in the Test with a product panel with a real product before relying on it.

Empty bins first

1. Empty bins in product Zones, by category and client (shipped default)

Bins with nothing in them, in Zones that store by product, whose allowed categories and client match the product. This is the shipped default.

--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
    Bins.BinCode+' @ '+Zones.ZoneCode
from
    Bins
    join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
    join Products on Products.Id = @ProductId
    left join InventoryLocations on Bins.Id = InventoryLocations.BinId
where
    InventoryLocations.Id is null
    and Zones.ProductHandling = 'ByProduct'
    and (coalesce(Bins.AllowedCategories, Zones.AllowedCategories) is null or Products.Category in (select lower(ltrim(rtrim(value))) from string_split(coalesce(Bins.AllowedCategories, Zones.AllowedCategories), ',')))
    and (coalesce(Bins.ClientId, Zones.ClientId) is null or coalesce(Bins.ClientId, Zones.ClientId) = Products.ClientId)    
order by
    coalesce(Bins.ClientId, Zones.ClientId) desc,
    coalesce(Bins.AllowedCategories, Zones.AllowedCategories) desc,
    Zones.ZoneCode,
    Bins.BinCode

2. Empty bins in walking order

Empty bins in Zones that store by product, ordered by zone and then by the bin's sequence number, so the first offer is the next one along the aisle. The sequence number is stored as text, so "A-10" sorts before "A-2"; use zero-padded numbers such as "A-02" if you rely on this.

--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
    Bins.BinCode + ' @ ' + Zones.ZoneCode
from
    Bins
    join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
    left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
    InventoryLocations.Id is null
    and Zones.ProductHandling = 'ByProduct'
order by
    Zones.ZoneCode,
    Bins.SequenceNumber,
    Bins.BinCode

3. Any empty bin in the Warehouse

The simplest fallback: every empty bin that is not in quarantine, by zone and bin code, no category or client rules.

--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
    Bins.BinCode + ' @ ' + Zones.ZoneCode
from
    Bins
    join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
    left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
    InventoryLocations.Id is null
    and Zones.IsQuarantine = 0
    and coalesce(Bins.IsQuarantine, 0) = 0
order by
    Zones.ZoneCode,
    Bins.BinCode

Same product first

4. Top up where it already is

Bins already holding this product come first, then Empty bins in the same kind of zone, so stock stays together.

--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
    Bins.BinCode + ' @ ' + Zones.ZoneCode
from
    Bins
    join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
    left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
    Zones.ProductHandling = 'ByProduct'
    and (InventoryLocations.Id is null or InventoryLocations.ProductId = @ProductId)
group by
    Bins.BinCode, Zones.ZoneCode
order by
    max(case when InventoryLocations.ProductId = @ProductId then 1 else 0 end) desc,
    Zones.ZoneCode,
    Bins.BinCode

5. Pick Bins of this product, lowest first

The Bins dedicated to this product, those under their minimum quantity first. Returns nothing when the product has no dedicated bin.

--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
    Bins.BinCode + ' @ ' + Zones.ZoneCode
from
    Bins
    join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
    left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
    Bins.DedicatedProductId = @ProductId
group by
    Bins.BinCode, Zones.ZoneCode, Bins.MinQuantity
order by
    case when coalesce(sum(InventoryLocations.Quantity), 0) < coalesce(Bins.MinQuantity, 0) then 0 else 1 end,
    Zones.ZoneCode,
    Bins.BinCode

6. Bins with room left for this quantity

Bins that have a maximum quantity and still have room for this many units, fullest first, so partly filled Bins get finished before new ones open.

Unit note: stock in the Bins is counted in eaches, so the query turns @Quantity into eaches by multiplying it by the each count of @PacksizeId (1 when the packsize is empty). Putting away 2 cases of 12 therefore compares 24 eaches, not 2.

--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
    Bins.BinCode + ' @ ' + Zones.ZoneCode
from
    Bins
    join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
    left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
    Bins.MaxQuantity is not null
group by
    Bins.BinCode, Zones.ZoneCode, Bins.MaxQuantity
having
    Bins.MaxQuantity - coalesce(sum(InventoryLocations.Quantity), 0) >= (@Quantity * coalesce((select ProductPacksizes.EachCount from ProductPacksizes where ProductPacksizes.Id = @PacksizeId), 1))
order by
    Bins.MaxQuantity - coalesce(sum(InventoryLocations.Quantity), 0),
    Zones.ZoneCode,
    Bins.BinCode

Special handling

7. Expiry Products: beside the oldest stock

For Products that track expiry: Bins already holding this product, the one with the earliest expiry first, so picking can stay first-expiry-first-out. Returns nothing for other Products.

--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
    Bins.BinCode + ' @ ' + Zones.ZoneCode
from
    Bins
    join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
    join Products on Products.Id = @ProductId
    join InventoryLocations on InventoryLocations.BinId = Bins.Id and InventoryLocations.ProductId = Products.Id
    join InventoryLocationDetails on InventoryLocationDetails.InventoryLocationId = InventoryLocations.Id
where
    Products.IsExpiryControlled = 1
    and InventoryLocationDetails.ExpiryDate is not null
group by
    Bins.BinCode, Zones.ZoneCode
order by
    min(InventoryLocationDetails.ExpiryDate),
    Zones.ZoneCode,
    Bins.BinCode

8. Only this client's dedicated Bins

For 3PL companies: Empty bins whose client, or whose zone's client, is the product's client. Nothing shared and nothing of another client.

--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
    Bins.BinCode + ' @ ' + Zones.ZoneCode
from
    Bins
    join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
    join Products on Products.Id = @ProductId
    left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
    InventoryLocations.Id is null
    and Zones.ProductHandling = 'ByProduct'
    and coalesce(Bins.ClientId, Zones.ClientId) = Products.ClientId
order by
    Zones.ZoneCode,
    Bins.BinCode

9. Keep hazardous goods together

Hazmat Products go to Empty bins in Zones that already hold hazmat stock; other Products never go there. The first hazmat product finds no such zone, so the gun asks the operator to scan a bin.

--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
    Bins.BinCode + ' @ ' + Zones.ZoneCode
from
    Bins
    join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
    join Products on Products.Id = @ProductId
    left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
    InventoryLocations.Id is null
    and Zones.ProductHandling = 'ByProduct'
    and (
        (Products.IsHazMat = 1 and exists (select 1 from Bins HazBins join InventoryLocations HazStock on HazStock.BinId = HazBins.Id join Products HazProducts on HazProducts.Id = HazStock.ProductId where HazBins.ZoneId = Zones.Id and HazProducts.IsHazMat = 1))
        or (Products.IsHazMat = 0 and not exists (select 1 from Bins HazBins join InventoryLocations HazStock on HazStock.BinId = HazBins.Id join Products HazProducts on HazProducts.Id = HazStock.ProductId where HazBins.ZoneId = Zones.Id and HazProducts.IsHazMat = 1))
    )
order by
    Zones.ZoneCode,
    Bins.BinCode

10. Full pallets to pallet Zones, loose units to shelving

When the quantity reaches the product's largest packsize, Zones that allow several pallets per bin; otherwise the other Zones. A product with no packsize counts as loose units.

Unit note: stock in the Bins is counted in eaches, so the query turns @Quantity into eaches by multiplying it by the each count of @PacksizeId (1 when the packsize is empty). Putting away 2 cases of 12 therefore compares 24 eaches, not 2.

--Available parameters: @WarehouseId, @ProductId, @PacksizeId, @Quantity
select top 10
    Bins.BinCode + ' @ ' + Zones.ZoneCode
from
    Bins
    join Zones on Zones.Id = Bins.ZoneId and Zones.WarehouseId = @WarehouseId
    left join InventoryLocations on InventoryLocations.BinId = Bins.Id
where
    InventoryLocations.Id is null
    and Zones.ProductHandling = 'ByProduct'
    and Zones.AllowMultipleLpnsPerBin =
        case when (@Quantity * coalesce((select ProductPacksizes.EachCount from ProductPacksizes where ProductPacksizes.Id = @PacksizeId), 1)) >= coalesce((select max(ProductPacksizes.EachCount) from ProductPacksizes where ProductPacksizes.ProductId = @ProductId), 2147483647) then 1 else 0 end
order by
    Zones.ZoneCode,
    Bins.BinCode

Was this page helpful?