PT Sterling Tulus Cemerlang



Sampoerna Strategic Square
South Tower Level 30
Jl Jend. Sudirman No. 45-46
Jakarta 12930 – Indonesia

500+ FREE Tips SAP Business One for you to Become SAP B1 Super User from Sterling Tulus Cemerlang (STEM)

Auto Populate User Defined Fields Using Formatted Search

SAP Business One Tips Auto Populate Fields Using Search

SAP Business One provides users with a function so users can attach a logic, a query, or a formula to a field and have it filled with data. It is called the Formatted Search. We can use Formatted Search not only to fill in data in base fields but also on our own defined fields (UDF).

This is very beneficial especially if we know have data stored in another module or table and we want to display it somewhere else.

We’re going to look at an example where we are going to use Formatted Search to fill in a data to Purchase Order document where we want to display the Bank Name, Bank Account, and Beneficiary Name in accordance to the information we have filled in the Payment Run tab in BP Master Data.

Also read:

Let’s say that we’ve already  created the User Defined Fields on the Marketing Document (Bank Name, Bank Account, and Beneficiary Name) and we’ve configured it to the document’s display.

In order to automatically fill in the data, we need to create a query which we will retrieve data from a column on the source table which is the BP Master Data (OCRD) then show it on the Purchase Order document where then it will be adjusted based on the Vendor Code (CardCode). We can make a query by accessing the menu:

Tools -> Queries -> Query Generator

For example we want to fill in the UDF “Bank Name” on the Purchase Order document

The query will be like this:

SELECT T0.”HouseBank” FROM OCRD T0 WHERE T0.”CardCode”=$[“OPOR”.”CardCode”]

We then save the query.

After we have saved the query we open the Purchase Order Document and then we Click the targeted column (in this example the “Bank Name” column) and then go to:

Tools -> Customization Tools -> User Defined Values-Setup

After we click on the User-Defined Values-Setup this menu will show up and we fill it based on the picture below.

  • Choose the option “Search in Existing User-Defined Values according to Saved Query” then click on the button “Open Saved Query” and chose the query that we have saved.
  • Tick the “Auto Refresh When Field Changes” checkbox,
  • Choose from the list “Customer/Vendor Code”.
  • And then choose the option “Refresh Regularly”

After we’ve done the setup, a magnifying glass will pop on the edge of the column that we chose. When we click the magnifying glass icon the data will show up. And also, when we open the Purchase Order document after we chose a Vendor Code (which already has information of the bank name on the BP master data) the “Bank Name” column will automatically be filled with the according to data.

Run Your Business With The Piece of Mind Using SAP Business One

This article was written by PT. Sterling Tulus Cemerlang
More details about SAP Business One,
email to: or call +6221-5806336 or wa +6287786555055

, , ,