Developer ToolsFree generatorPDF, Excel, Word exportSAP Business OneAll SAP Business One tools

SAP Business One Formatted Search (FMS) Query Generator

This generator writes formatted search (FMS) queries for SAP Business One marketing documents, using the dynamic $[...] references that read the field values on the open form. Choose the document, copy a query, save it in Query Manager and assign it to a field.

Fill in

Document header table

ORDR sales order, OINV A/R invoice, OPOR purchase order.

Matching row table

Item UDF to copy

Generated code

Download:

1. Last price for this business partner and item (row Unit Price)

/* SQL Server */
SELECT TOP 1 T1.Price
FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.CardCode = $[$4.0.0]
  AND T1.ItemCode = $[$38.1.0]
  AND T0.CANCELED = 'N'
ORDER BY T0.DocDate DESC, T0.DocEntry DESC

/* HANA */
SELECT T1."Price"
FROM "ORDR" T0
INNER JOIN "RDR1" T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T0."CardCode" = $[$4.0.0]
  AND T1."ItemCode" = $[$38.1.0]
  AND T0."CANCELED" = 'N'
ORDER BY T0."DocDate" DESC, T0."DocEntry" DESC
LIMIT 1

2. Available stock in the row warehouse (row UDF or free text column)

/* SQL Server */
SELECT T0.OnHand - T0.IsCommited
FROM OITW T0
WHERE T0.ItemCode = $[$38.1.0]
  AND T0.WhsCode = $[RDR1.WhsCode]

/* HANA */
SELECT T0."OnHand" - T0."IsCommited"
FROM "OITW" T0
WHERE T0."ItemCode" = $[$38.1.0]
  AND T0."WhsCode" = $[RDR1.WhsCode]

3. Customer balance and remaining credit (header UDF)

/* SQL Server */
SELECT T0.CreditLine - T0.Balance
FROM OCRD T0
WHERE T0.CardCode = $[$4.0.0]

/* HANA */
SELECT T0."CreditLine" - T0."Balance"
FROM "OCRD" T0
WHERE T0."CardCode" = $[$4.0.0]

4. Default a row UDF from the item master

/* SQL Server */
SELECT T0.U_Brand
FROM OITM T0
WHERE T0.ItemCode = $[$38.1.0]

/* HANA */
SELECT T0."U_Brand"
FROM "OITM" T0
WHERE T0."ItemCode" = $[$38.1.0]

Dynamic references used

Reference	Meaning on a marketing document
$[$4.0.0]	Header field item 4: business partner code
$[$38.1.0]	Row matrix item 38, column 1: item number
$[RDR1.WhsCode]	Current row value of the warehouse field, referenced by table and field name
$[ORDR.CardCode]	Same as $[$4.0.0], written by table and field name

Item and column IDs differ between forms and add-ons: confirm each one with View > System Information (the status bar shows item and column numbers when you hover a field). OITW.OnHand minus IsCommited ignores quantities on order (OnOrder); add them if your users expect available to promise. Save each query in Query Manager before assigning it.

Get your formatted searches reviewed

Send us your list of FMS assignments from CSHS and we will flag slow or fragile queries before your next upgrade.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to save and assign a formatted search

Paste a query into the Query Generator, execute it once (it returns nothing outside a document, which is fine), and save it in a category such as FMS. Open the target document, place the cursor in the field that should receive the value and press Alt+Shift+F2 to open the formatted search definition. Choose Search in Existing User-Defined Values according to Saved Query, select your query, and optionally tick Auto Refresh with the field that should trigger it, for example the item number for a row price. Users can also trigger the search manually with Shift+F2 or the magnifying glass in the field.

Auto Refresh When Field Changes: pick the trigger field, such as Item No.
Display Saved User-Defined Values: avoids rerunning the query for unchanged rows.
Refresh Regularly: reruns on every change and costs performance on long documents.

How the $[...] syntax works

A formatted search query can read any field on the current form. $[$item.column.0] addresses a field by its item ID and column ID, so $[$4.0.0] is the business partner code in the document header and $[$38.1.0] is the item code of the current row in the item matrix (item 38). The last number selects the format; 0 returns the value as stored. You can also write $[TABLE.Field], for example $[RDR1.WhsCode], which is easier to read and survives layout changes. Business One substitutes the values as parameters before the query runs, so you do not add quotes around them, on SQL Server or HANA.

Where formatted searches are stored

Assignments live in table CSHS, with one row per form, item and column pointing to the saved query in OUQR. That is useful when you migrate a company or audit which fields run queries: select from CSHS to list every formatted search, then join to OUQR for the query text. Copying queries between companies is possible through the Query Manager export and import, but the assignments need to be recreated or copied with care, because form and item IDs can differ between versions and localizations.

Gotchas

A header-level formatted search can only reference header fields; referencing a row column from the header returns nothing. On HANA, unquoted lower case identifiers fail, and LIMIT 1 replaces TOP 1 in most examples, although HANA also accepts TOP. Returning more than one row or column opens a selection list instead of filling the field, which is sometimes useful and often a surprise. Queries on large document tables such as INV1 need the CardCode and ItemCode filters to stay fast, so do not drop them.

Frequently Asked Questions

Which table stores formatted searches in SAP B1?

CSHS stores the formatted search assignments (form, item, column, query and refresh settings). The query text itself is in OUQR, saved through the Query Manager. Joining CSHS to OUQR lists every field that runs a query and the SQL behind it.

What do $[$4.0.0] and $[$38.1.0] mean?

They are formatted search references to fields on the open form: item 4, the business partner code in the document header, and item 38, column 1, the item number in the current document row. The last digit chooses the value format; 0 returns the stored value.

Como crear una busqueda formateada en SAP Business One HANA?

Guarde la consulta en el Query Manager con identificadores entre comillas dobles, abra el documento, situese en el campo y pulse Alt+Shift+F2. Elija la consulta guardada y, si quiere, active la actualizacion automatica cuando cambie un campo. La sintaxis $[$38.1.0] es la misma en HANA y SQL Server.

Why does my formatted search return nothing?

Usually the referenced field is empty when the query runs, the reference points to a row column from a header field, or the item or column ID is wrong for that form. Check IDs with View > System Information and run the query manually with Shift+F2 on a filled row.

If your users want to ask Business One for last prices, stock and balances in plain English instead of building more formatted searches, we can run that on your own server.

Talk to an Expert Explore More Free Tools