Back to Tutorials
SQL & HANA•5 min read
VIDEO + GUIDE • FREE MEMBER

SAP Business One Formatted Search (User-Defined Values): Auto-Fill Fields with Queries

Make SAP B1 fill in fields for your users. A complete guide to Formatted Search syntax, auto-refresh triggers, and six ready-to-use queries. Watch the video walkthrough, then copy the queries.

SB1

SAP B1 Academy Editorial

Published • 5 min read

Save

Formatted Search is one of the most underrated features in SAP Business One. It lets you attach a query to almost any field on any form, so the field fills itself in automatically or offers a pick list of valid values. Since release 10.0 it appears in the menu as User-Defined Values, but most consultants still call it Formatted Search.

Used well, it removes repetitive typing, prevents data-entry mistakes, and brings information to the user that would otherwise require opening another window.

What Formatted Search Can Do

When you assign a Formatted Search to a field, you choose one of three behaviors:

  • Search in Existing User-Defined Values: show a fixed list you type in yourself, such as a list of delivery methods.
  • Get Values from Saved Query: run a query from the Query Manager and either fill the field with the result or show a selection list.
  • Fill with fixed value / no action: remove an existing Formatted Search.

With the query option you can also enable Auto Refresh. The field then recalculates automatically whenever another field you specify changes, for example when the item code or customer changes.

  1. Write and save your query in Tools → Queries → Query Manager.
  2. Open the target form (for example a Sales Order) and click into the field you want to automate.
  3. Press Alt + Shift + F2 to open the User-Defined Values setup for that field.
  4. Choose Get Values from Saved Query and select your query.
  5. Optionally tick Auto Refresh When Field Changes and choose the trigger field, plus Display Saved User-Defined Values if you want the result stored rather than re-queried.
  6. Click Update. From now on, users can press Shift + F2 in the field (or rely on auto refresh) to run it.

The magnifier icon that appears in the field tells users a Formatted Search is attached.

Free Member Content

Create a free account to unlock the full step-by-step guide & copy the production SQL script.

Join SAP Business One consultants and key users worldwide. Free forever, no credit card required.

or use a magic link