PT Sterling Tulus Cemerlang
www.sterling-team.com

Phone
+62-877-8655-5055
+62 (21) 580-6055

Email
sales-enquiry@sterling-team.com

Address
Sampoerna Strategic Square
South Tower 18th
Jl Jend. Sudirman kav 45-46
Jakarta 12930 - Indonesia

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.


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 data.


This article was written by PT. Sterling Tulus Cemerlang, SAP Business One GOLD Partner in Indonesia.
More details about SAP Business One,
email to: sales-enquiry@sterling-team.com or call +6221-5806336 or wa +6287786555055

, , ,