How to Create a Formatted Search (FMS) in SAP Business One

Open the Sales Order window and identify the missing information

1. Launch SAP Business One.
2. In the left navigation pane, expand Sales - A/R.
3. Click Sales Order to open the Sales Order window.
4. Review the form. The customer phone number isn't displayed by default.

Open User-Defined Fields – Management

1. Close the Sales Order window.
2. In the top menu, click Tools.
3. Select User-Defined Fields – Management.
4. In the User-Defined Fields – Management window, expand Marketing Documents.
5. Expand Title under Marketing Documents.

Add a new user-defined field

Click Add at the bottom of the window. In the Field Data dialog, enter the following values:

• Title: PhoneNo
• Description: Phone No
• Type: Alphanumeric
• Length: 15
• Structure: leave as Regular
• Validation: leave as None

Don't select Set Default Value for Field or Mandatory Field.

Confirm and add the field

Review the field settings to make sure they match:

• Title: PhoneNo
• Description: Phone No
• Type: Alphanumeric
• Length: 15

Click Add to create the user-defined field.

You have now created a user-defined field for the customer phone number in the Sales Order module. Next, you assign a formatted search query to this field so it displays the phone number for the selected customer.

Verify the UDF placement and technical name

1. Open the Sales Order window.
2. On the right side of the form, find the Phone No field under the Demo UDFs section.
3. Hover your mouse over the Phone No field.
4. Check the bottom left corner of the screen. The system information displays the UDF technical name, for example U_PhoneNo.

Find the item number of the Customer field

1. In the Sales Order window, hover over the Customer field.
2. Check the system information bar at the bottom. It displays:

Form=139 Item=4 Pane=0 Variable=1 ORDR,CardCode

3. Note that the item number is 4. This number matches the dynamic reference you use in your query.

Check the table and field names in Business Partner Master Data

If you're unsure about the table or field names, open Business Partner Master Data from the left navigation pane under Business Partners.

• The Telephone 1 field corresponds to Phone1 in the OCRD table.
• The Code field corresponds to the business partner's unique identifier (CardCode).

Open the Query Generator

1. Close the Sales Order window if it's open.
2. In the top menu, click Tools.
3. Hover over Queries.
4. Select Query Generator from the submenu.

Open the query editor

1. In the Query Generator window, click Execute at the bottom left.
2. Click the pencil icon to open the query editor, where you write a custom SQL query.

Write the query with a dynamic condition

In the Query Preview window, make sure your SQL statement uses a dynamic reference for the customer code:

SELECT OCRD."Phone1" FROM OCRD WHERE OCRD."CardCode" = $[$4.0.0]

The placeholder $[$4.0.0] is built as follows:

• 4 is the item number of the Customer field in the Sales Order form. You saw it in the system information bar in Step 6.
• The first 0 refers to the column index. CardCode is the first column, so the index is 0.
• The second 0 refers to the data type. Alphanumeric is also indexed at 0 in SAP Business One.

This reference makes the query pull the customer code from the active Sales Order form.

Save the query

1. In the Query Preview window, click Save.
2. In the Save Query dialog, select the appropriate query category, for example General.
3. Choose the query you want to replace, for example fms_phone. If you're creating a new query, enter a new name instead.
4. Click Save to store the query.

Open User-Defined Values – Setup

1. Open the SAP Business One application and go to the main workspace.
2. In the top menu, select Tools.
3. Hover over Customization Tools.
4. Select User-Defined Values – Setup. The shortcut is Shift + Alt + F2.

Link the saved query to the field

In the User-Defined Values – Setup dialog, configure the formatted search:

1. Choose Search in Existing User-Defined Values according to Saved Query.
2. Enter fms_phone in the field provided.
3. Click Open Saved Query to select or review the saved query.
4. Make sure Auto Refresh When Field Changes is selected.
5. In the dropdown below it, select Customer/Vendor Code as the field that triggers the refresh.
6. Choose Refresh Regularly so the values update as needed.
7. Click OK to save the setup.

Test the formatted search

Once setup is complete, the Phone No UDF fills in automatically with the phone number of the customer you select in the Sales Order form.

1. In the Sales Order form, click the Customer field.
2. In the List of Business Partners dialog, select a customer, for example BP Code C20000, Name Maxi-Teq.
3. Click Choose.
4. Check that the Phone No field in the Demo UDFs panel updates with the phone number of the selected customer.

Repeat with another customer and review the form

1. Select a different customer, for example BP Code C23900, Name Parameter Technology.
2. Confirm that the phone number updates to match the new customer.
3. Review the Sales Order form. Check that the relevant fields (Customer, Name, Contact Person, and so on) and the Phone No UDF are populated correctly.