MCG University

SAP Business One Creating a Query

In this course of MCG University, we will show you how to create a Query in SAP Business One. This session takes a closer look at how to get the proper information in order to build a query, how to create a query, how to create filters and conditions for the query, how to save it, and how to run it after creation.

Getting Started

  1. Identify what information is needed to query
  2. All Tables and Fields throughout the system can be queried
  3. In order to identify the Fields and Tables needed, go to Modules > Sales A/R > A/R Invoice

Navigating Through A/R Invoice Data

  1. Once we navigate through A/R invoice data, go to View and make sure that the “System Information” option is flagged
  2. This will allow information for SAP Tables and Fields to be displayed once you go over any field

In this case, the pointer is over Customer ID

The system displays at the bottom left of the window that this field pertains to the OINV table and that field name is Cardcode

On the other hand, this is all Header Information

If we go over any line within that same document, the system will also display the table and field name for it

Same logic is applied to any other sections or tabs within that document

Query Generator

  1. Now that we know the table names, we can navigate to Tools > Queries > Query Generator
  2. If the table name is not known, you can click Tab to display all the lists of available tables
  3. If we know the table we are looking for, enter Table Name and click Tab
  4. Repeat the same for each table that you would like to use in your query, and the system will automatically create the code to link this tables for you
  5. Click on each table added and select each field that you would like to see in your query
  6. Every selected field will be shown in the Select Statement of the query
  7. Once all desired fields are selected, click Execute. This will return all A/R Invoice Data as if no filters had been added

This information can be filtered by clicking on Filter at the top of the window and then selecting the desired filters

If instead of adding filters to all returned data you would like your query to return the data already filtered, go back to the Query Generator Window

For example, if you would like to filter the data by Customer, you can enter this in the “Where” box within the query generator and specify the Customer for which you would like to pull the data for

Keep in mind that the “Conditions” box located at the bottom right of the window will give you the ability of selecting the correct condition for your filter

While adding the data in the “Where” box, there are two options

You can either hard code the customer code for which you would like to see the data for or select the [%] which will add a variable for you

This means the query will prompt you to type in a Customer Code once you run the query

Multiple filters can be added to the “Where” box based on what the needs of your query are. Make sure the correct conditions and variables are added and click execute to display the outcome

Wrapping Up

  1. A new prompt will include the new added filters
  2. Enter the desired filters and select OK to display the data
  3. In order to save your Query, click Save in the bottom right of the window
  4. Identify the option where you would like to put the query in and create a Name
  5. Click Save again
  6. Previously saved queries will be available from Tools > Queries > User Queries  > General