SAP B1 tables with field names

5 pts.
Tags:
SAP
SAP B1
Hello,
Can you please provide detail SAP B1 table names with fields names in related to different database?


Software/Hardware used:
SAP b1
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

In order to create a report with SQL (Query GeneratorTools -> 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.

Query_Generator.png

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.

View.png

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.

CardName.png

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.

source

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Dinesh01
    Hi, But the system information is not working on the read only field. For example take a A/R invoice screen and tax field name. when i move over the mouse on the tax field it will not display the tax read only field name. How find those fields name.
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: