SAP B1 User-Defined Fields (UDF) & Formatted Searches: Complete Guide

What Are UDFs and Why Do They Matter?

Out of the box, SAP Business One covers the most common business fields — item codes, quantities, prices, customer names, addresses. But every business has data that doesn't fit neatly into standard fields. A trading company might need a "Port of Origin" field on Purchase Orders. A service company might need a "Technician ID" field on Sales Orders. A manufacturer might need a "Batch Expiry Date" field on inventory documents.

User-Defined Fields (UDFs) solve this. They let you add custom fields to almost any SAP B1 form — without modifying the database schema directly or developing a custom Add-On. These fields appear inside standard SAP B1 documents, store data in the company database, and can be used in queries, Crystal Reports, Formatted Searches, and integrations.

UDF Data Types

When creating a UDF, you choose its data type. Here are the available options and when to use each:

• Alphanumeric: Free-text field. Use for notes, codes, references. Define max length carefully — SAP B1 stores this in the underlying SQL column.
• Numeric: Integer values. Use for counts, quantities, ranking numbers.
• Decimal: Decimal numbers. Use for percentages, rates, custom pricing fields.
• Date: Date picker. Use for custom date fields like warranty expiry, next review date, shipping deadline.
• Memo: Long free-text (up to 254 characters). Use for extended notes or instructions.

Creating a UDF Step by Step

To add a User-Defined Field in SAP Business One:

1. Go to Tools → Customization Tools → User-Defined Fields — Management
2. In the left tree, navigate to the object you want to extend — for example, Marketing Documents → Rows to add a field to Sales Order line items
3. Click the Add button
4. Enter a Field Name (alphanumeric, no spaces — this becomes the column name in the database with a U_ prefix)
5. Enter a Description (this is the label users will see on screen)
6. Choose the Type and set any validation options
7. Click Update to save — SAP B1 immediately adds the column to the underlying database table

UDF Valid Values (Dropdown Lists)

A UDF can be restricted to a predefined list of values, turning it into a dropdown. This is extremely useful for fields like "Shipment Method", "Priority Level", or "Region Code" where you want consistent, controlled input.

To add valid values to a UDF, in the UDF Management screen:

1. Select your UDF and click Valid Values
2. Add each allowed value and its description
3. SAP B1 will render this UDF as a dropdown when the user opens the document

User-Defined Tables (UDTs)

While UDFs add fields to existing SAP B1 objects, User-Defined Tables (UDTs) let you create entirely new database tables to store custom data that has no standard SAP B1 object.

Common use cases for UDTs:

• A custom "Freight Rates" table that Formatted Searches query to auto-calculate shipping costs
• A "Sales Territory" table that maps Business Partners to regional managers
• A "Product Certificates" table linking items to their compliance documents
• A lookup table for custom approval routing rules

Creating a UDT

Go to Tools → Customization Tools → User-Defined Tables — Management. Click Add, give the table a name (SAP B1 adds a @ prefix in the database, e.g., @ZK_FREIGHTRATES), and define its columns using the same UDF types. Once created, the table appears as a custom object in SAP B1 and can be accessed via the Service Layer, SQL queries, Crystal Reports, and Formatted Searches.

Formatted Searches (FMS) — SAP B1's Most purpose-built Tool

Formatted Searches (FMS) are SQL queries attached to a specific field in a SAP B1 form. When the user clicks on that field (or when a trigger fires), SAP B1 runs the SQL query and either:

• Auto-fills the field with the query result (Auto Refresh)
• Displays a list for the user to choose from (Choose From List / FMS)

Formatted Searches are the backbone of SAP B1 automation. They can pull data from anywhere in the database — standard tables, UDTs, even the current document's own fields — and use it to populate other fields automatically.

Practical FMS Examples

Example 1: Auto-fill a UDF with the Item's last purchase price

SELECT MAX(T1.Price)
FROM   OPCH T0
       INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
WHERE  T1.ItemCode = $[$38.1.0]
AND    T0.DocStatus = 'C'

The $[$38.1.0] is a SAP B1 system variable that reads the current row's Item Code from the document. This FMS auto-fills a UDF called "Last Purchase Price" whenever a user enters an item on a Purchase Order.

Example 2: Auto-fill Sales Order "Region" UDF from Business Partner

SELECT T0.U_ZK_Region
FROM   OCRD T0
WHERE  T0.CardCode = $[$4.0.0]

$[$4.0.0] reads the current document's Customer Code. This FMS fires when the user selects a customer, automatically filling the "Region" UDF from the Business Partner's custom field.

FMS Trigger Types

When attaching an FMS to a field, you choose how it triggers:

• Regular (Manual): The user must press Shift + F2 to run the FMS.
• Auto Refresh — When Field Changes: The FMS fires automatically whenever a specified "trigger field" changes value. This is the most purpose-built option — e.g., run when Item Code changes.
• Auto Refresh — When Form Opens: Runs once when the document is first opened or created. Useful for setting default values.

UDF & FMS Best Practices

• Document every UDF and FMS — maintain a spreadsheet listing: field name, table, purpose, data type, FMS SQL (if any), and who requested it. This is critical when multiple people manage the system over time.
• Use ISNULL() in FMS queries — if your SQL can return NULL, the FMS will clear the field. Wrap results: SELECT ISNULL(T0.U_ZK_Region, '') FROM ...
• Test FMS in a sandbox first — a broken FMS SQL can cause SAP B1 to throw errors when users open documents. Always validate in a test company before applying to production.
• Use UDTs as lookup sources — instead of hard-coding values in FMS SQL, store them in a UDT. This lets business users update the lookup data without IT involvement.
• Keep FMS SQL simple — complex subqueries and multi-table joins slow down form loading. If logic is very complex, consider a stored procedure or Boyum B1UP Script instead.

Using UDFs in Integrations & Reports

UDFs are fully accessible through the SAP B1 Service Layer — they appear automatically in the JSON response of any GET request to the document containing them, using their U_ prefixed name. You can read and write UDF values just like standard fields in any API call.

In Crystal Reports, UDF columns appear in the database field list for their respective tables (e.g., ORDR.U_ZK_Region for a Sales Order header UDF). Simply drag them into your report layout like any other field.