Create a Formatted Search

You may have heard your SAP Business One consultant say the words “formatted search”, and it probably went through one ear and out the other because it sounds kind of technical or perhaps too complex to setup. The truth is, formatted searches aren’t complicated at all, so if you can understand the concept of formatted searches, and when and how they can be used, you can really take your SAP Business One system to another level!

What is a Formatted Search?

In very simple terms, it means you can insert specific information into a field. In geek-speak a formatted search function enables you to enter values, originated by a pre-defined search process, into any field in the system (including user-defined fields).

You can use the formatted search function to:

• Automatically enter values into fields from various objects in the system.
• Enter values into fields using a pre-defined list.
• Automatically enter values into fields with pre-defined queries (user queries).
• Create dependencies between fields in the system (e.g. the value in field X influences the value in field Y).
• Display internal fields that aren’t exposed on windows. These are fields that aren’t displayed anywhere in the system, but they can be queried. For example, some of these fields are: User Signature, Creation Date, Open Checks Balance (for Business Partners), etc.

Why Use a Formatted Search?

Formatted searches are extremely for a few reasons:

• They save you time from having to search for information located on other screens
• They’re especially handy if you want to automatically display information based on conditions and calculations (e.g. you could automatically calculate the annual maintenance for a specific item within a UDF on a document row)
• They reduce effort and possibility of human error.

Practical Example

Let’s say you want to automatically display the item’s Foreign Name onto the row of your Sales Order. The Foreign Name field exists in the Item Master Data screen, but it’s not a field that’s inherently available to display on document rows. You can use a formatted search to bring in this data from the Item Master Data onto the Sales Order row.

For example, you sell Christmas trees and every time you enter the Christmas tree item code on the row of a Sales Order, you also want the French description “l'arbre de Noël” to appear on the row.

Here is how to accomplish this using a formatted search:

1. Create a User-defined Field (UDF) on the row called Foreign Name.
   
   
2. Save the following query in the Query Manager. This will be called in your formatted search:
   SELECT T0.[FrgnName]
   FROM OITM T0
   WHERE $[$38.1.0] = T0.[ItemCode]
   (This query basically grabs the Foreign Name from the Item Master Data window of the respective item code that you entered on your sales order)
3. Place your cursor into the Foreign Name UDF you created on the Sales Order row and press Alt + Shift + F2 on your keyboard.
4. The User-defined Values – Setup window will appear. Click on Search in Existing User-defined Values according to Saved Query radio button and select the Foreign Name query you just saved.
   
   
5. Click on the Auto Refresh box and select Item No. from the drop-down list. Then click on the Display Saved User-defined Values radio button. (Basically this entire step enables the system to automatically show the Foreign Name of the respective Item Code every time you select an item on a new row or even change the item on an existing row).
6. Now test it out. Create a Sales Order and enter an item on the row; the Foreign Name of the item will automatically appear within the UDF.