Creating a SAP Business One SQL Query – Part One29 Aug 2010 | by Brett Mundell
This is part one of my two-part series on creating a basic query in SAP Business One. Over the next few months, I will be providing regular tips and tricks on some of the great functions and features available to users.
Many users of SAP Business One (SBO) are unaware that there is a powerful and simple query generator available within the standard product. The query wizard and the associated query generator are tools that make it easy for authorised users to extract information from the company databases.
There are many reasons why companies would want to query the database but generally, the requirement is to produce a view of the data that does not exist in one of the standard reports offered by SAP or to present the data in a customised format.
Take for example the common requirement to extract a customer listing main contact details. This is a simple task using the query wizard. Let’s take a look how SAP supports this requirement.
The first thing to do is identify the names of the database fields that we want to extract in our query. This is made simple by asking the SAP client to show ‘System Information’. To do this, drop down the ‘View’ menu from within the SBO client and select the ‘System Information’ option so that it shows a tick as indicated in the screenshot below:
Enabling the ‘system Information’ option allows the easy identification of field names and tables in the database simply by moving the mouse over the required data in the SBO screen. Identifying the data names and tables in the database is the most difficult part of the query so SBO has already facilitated the bulk of the report creation activity.
The screenshot below illustrates how this process works. First, move the mouse over the field in SAP Business One and then look in the bottom left-hand corner of the screen to see the information provided by the system about that field.
We can repeat this process by moving the mouse over all the fields that we would like to see in the proposed query and making notes of the field and table names.
Once that process is complete we are ready to create our query. The most accessible tool to do this is the Query Wizard. This can be accessed by dropping down the ‘Tools’ Menus from the SBO client and selecting queries à Query Wizard as shown below.
Another three steps to go….I will finish the blog with these steps early next week.
Brett has more than 20 years of business software sales and company management experience. Brett has been involved in more than 300 ERP projects. His passion is customer satisfaction, making sure every client is more than just satisfied. Brett wants our customers to be driven to refer their friends and peers because we offer the best services and technology available and because we exceeded their expectations.