Start a project

SAP Business One formatted searches (FMS): $[$38.1.0] syntax, 7 practical examples and auto-refresh

By Zain Abbas, Kodenova · Updated

Short answerA formatted search (FMS) is a saved query attached to a field in SAP Business One that fills the field or shows a pick list. Inside the query you read values from the open form with $[$Item.Column.Type], for example $[$38.1.0] for the item code on a document line. You attach it with Alt+Shift+F2 and choose whether it runs on demand, when another field changes, or every time.

What a formatted search is

A formatted search, usually called FMS, is a saved query linked to one field on one form. When it runs, SAP Business One either writes the result into the field or shows a list for the user to pick from.

Users love them because they remove typing. Developers like them because no add-on is needed. You can fill a line UDF with the item’s foreign name, show stock in the line warehouse, or offer a dropdown from a user-defined table.

If you want to skip the syntax work, our formatted search builder writes the query for you.

The syntax

There are two ways to read values from the open form.

FormExampleWhen to use
$[$Item.Column.Type]$[$38.1.0]Reads the field by its position on the form. Works across similar documents.
$[TABLE.Field]$[OINV.CardCode]Reads by table and field name. Only works on forms based on that table.

In the first form:

  • Item is the form item index. On sales and purchasing documents, 38 is the lines matrix and 4 is the customer or vendor code.
  • Column is the matrix column. Use 0 for header fields.
  • Type controls how the value comes back:
    • 0 returns the value as is. Use it for codes, text and document numbers.
    • NUMBER returns a number without the currency or unit. Use it for maths.
    • DATE returns a date you can add days to.

SAP’s own guide recommends the index form, because $[$4.0.0] works on any marketing document, while $[OINV.CardCode] only works on A/R invoices.

User-defined fields use their name as the column: $[$38.U_Discount.NUMBER].

How to find item and column IDs

  1. In SAP Business One, open View > System Information.
  2. Open the form and hover over the field.
  3. Read the status bar at the bottom. It shows the item, the column and, for database fields, the table and field name.

Check every ID on your own version and form. Column numbers can differ between document types and localisations. Our SAP B1 table reference helps with the table and field names.

  1. Save your query in the Query Manager, ideally in a category called “FMS”.
  2. Open the target form and click into the field.
  3. Press Alt+Shift+F2. The formatted search window opens.
  4. Choose Search in Existing User-Defined Values according to Saved Query and select your query.
  5. Decide when it runs:
    • No auto refresh: the user presses Shift+F2 or clicks the magnifier.
    • Auto Refresh When Field Changes: pick the trigger field, for example the item code. The query runs when the user leaves that field.
    • Display Saved Values: keeps the value stored on the document instead of re-running the query every time the document is opened.
  6. Click Update and test on a new document.

SAP’s guide also lists a “Refresh Regularly” option. It warns to use it sparingly because it can slow forms and change saved documents when they are browsed.

7 practical examples

All examples use SQL Server syntax. The HANA notes come after.

1. Item field on a line. Foreign name of the line item into a line UDF.

SELECT T0.FrgnName
FROM OITM T0
WHERE T0.ItemCode = $[$38.1.0]

2. Business partner field on the header. Available credit for the selected customer.

SELECT T0.CreditLine - T0.Balance
FROM OCRD T0
WHERE T0.CardCode = $[$4.0.0]

3. Stock in the line warehouse. Column 24 is the warehouse on many document forms. Confirm it with System Information.

SELECT T0.OnHand
FROM OITW T0
WHERE T0.ItemCode = $[$38.1.0]
  AND T0.WhsCode = $[$38.24.0]

4. Last price sold to this customer. Useful as a check next to the price column.

SELECT TOP 1 T1.Price
FROM OINV T0
INNER JOIN INV1 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

5. Line weight. Sales weight per unit times the line quantity (column 11).

SELECT T0.SWeight1 * $[$38.11.NUMBER]
FROM OITM T0
WHERE T0.ItemCode = $[$38.1.0]

6. Dropdown from a user-defined table. Returning two columns gives the user a list to choose from.

SELECT T0.Code, T0.Name
FROM [@REGIONS] T0
ORDER BY T0.Name

7. Today’s date. Fills a date UDF when the document is created.

SELECT GETDATE()

HANA differences

On SAP Business One for HANA, the logic stays the same but the SQL changes:

  • Put table and column names in double quotes: T0."ItemCode", "@REGIONS".
  • UDF names inside the variable also need quotes: $[$38."U_Discount".NUMBER].
  • A SELECT without a table needs FROM DUMMY.
  • Replace SQL Server functions: GETDATE() becomes CURRENT_DATE, ISNULL becomes IFNULL.

Example 7 on HANA:

SELECT CURRENT_DATE FROM DUMMY

Example 1 on HANA:

SELECT T0."FrgnName"
FROM OITM T0
WHERE T0."ItemCode" = $[$38.1.0]

If you are moving many queries, paste them into our SQL to HANA converter and then test each one.

Performance tips

  • Line-level searches multiply. A search that auto-refreshes on the item code runs once per line. On a 200-line order, that is 200 queries. Add three such searches and users will feel it.
  • Filter on keys. Query by ItemCode, CardCode, DocEntry. Avoid LIKE '%x%' and functions on columns in the WHERE clause.
  • Avoid heavy joins on document tables. Example 4 scans invoice history. Fine for a few hundred thousand lines, slow for millions. Consider an index or a summary table.
  • Prefer on-demand for rare needs. If users need a value only now and then, leave auto refresh off and let them press Shift+F2.
  • Use Display Saved Values for anything that should not change after the document is saved.

When to use something else

A formatted search fills fields. It does not enforce rules. A user can overwrite the value, and imports through DI API or Service Layer do not run it.

  • Use the transaction notification stored procedure (SBO_SP_TransactionNotification) when a rule must always hold, for example “no invoice without a cost centre” or “price cannot be below cost”. It blocks the save, whatever the source.
  • Use an add-on when you need new screens, buttons, calls to external APIs, or logic that depends on many fields at once.
  • Keep FMS for defaults, lookups and pick lists that help the user type less.

Ready to build one? Open the formatted search builder, pick your form and field, and copy the query. If your documents are slow or you need rules that FMS cannot enforce, contact us and we will look at your setup.

Sources

Related services

FAQ

Frequently asked questions

What does $[$38.1.0] mean in SAP Business One?

It reads a value from the open form. 38 is the item index of the document lines matrix, 1 is the column (Item No.) and 0 is the type, which returns the plain value. So $[$38.1.0] is the item code on the current line.

What is the difference between 0, NUMBER and DATE in a formatted search?

0 returns the field value as it is, which suits codes and text. NUMBER returns a numeric value without the currency or unit, so you can calculate with it. DATE returns a date you can use in date arithmetic.

How do I find the item and column number of a field?

Turn on View > System Information, then hover over the field. The status bar shows the item number, the column and, for database fields, the table and field name.

Do formatted searches work on SAP Business One for HANA?

Yes, but the SQL must follow HANA rules. Put table and column names in double quotes, use FROM DUMMY when you select a value without a table, and replace SQL Server functions such as GETDATE() with HANA ones such as CURRENT_DATE.

Why are my documents slow after adding formatted searches?

Each formatted search runs a query. One that auto-refreshes on every line, or is set to refresh regularly, runs many times per document. Keep line-level searches few, index-friendly and on demand where possible.

Ready to get started?

Tell us what you need. You get a clear technical answer, a fixed scope and a price, usually within 24 hours.

Start a project Chat on WhatsApp