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.
