In order to create a report with SQL (Query Generator: Tools -> Queries -> Query Generator), or Crystal Report, it is necessary to know which SAP Business One tables are involved in the functionality or in the business process.
The most common tables and all User Defined Tables are found in the Query Generator. Go to Tools -> Queries -> Query Generator.Press Tab in the first top field to open the Choose from List window.
By default, the Choose from List window is sorted in an ascending order by the Name column.To sort the list alphabetically in a descending order, double-click twice on the Name field title.
To look for the table by its description in the Find field, double-click on the Description field title; this sorts the list alphabetically in an ascending order.
This list however, is not that extensive and some of the required tables may not be listed in the Query Generator – Choose from list.
You can also use the System Information tool to identify Table and Field Names.
SAP Business One uses tables in its database to store the information table setups, master data, transactions and logs. While there are several ways to research the definitions of these tables, such as using the SDK Help or searching online resources, one of the simplest ways is to use the “System Information” tool built right into the SAP Business One Client. Once enabled, this option will give you the exact table name and field name for almost every field in SAP Business One.
To access the tool, you first need to enable it from the View Menu. You can tell if it is already enabled by looking for the checkmark indicator. If it is not enabled, simply choosing this menu option will turn on the feature.
Once it is enabled, you just need to point your mouse over the field you are interested in and the table name and field name will be displayed. In this example, I have the Business Partner Master Data open, and have the mouse hovering over the “Name” field.
The result is that the table for Business Partners is identified as OCRD, and the field for “Name” is identified as “CardName” as shown below. Note that the table name is displayed first, and that the field name follows the “,” separator. The majority of SAP Business One table names will be 4 characters long. Also, when working in a SAP Business One on HANA system, the proper capitalization of the field name should be noted.
A last point is that some fields, particularly those with currency symbols, will not display the table and field name using this method. The reason for this is that the value shown on the screen is actually the composite of two separate fields on the table, the numerical value and the currency indicator. SAP Business One does this, so that the performance of the database can be optimized and it is easier to perform mathematical operations on the data, while still retaining full multi-currency support. In this case, the easiest way to “find” the correct field is just to hover over another field on the form to get the right table name, then either use the SDK Help, or execute a Select * (selects all fields) query and look through the data.