ERP Software Blog | SAP Blogs | MS Dynamics 365 Blogs

How to get a list of the SAP Business One tables and fields

By Mark Burt

In order to create a report with SQL queries (Query GeneratorTools -> Queries -> Query Generator), or Crystal Report, it is necessary to know which SAP Business One tables and fields 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 SAP tables may not be listed in the Query Generator - Choose from list.

Identify Tables and Fields Names:

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 check-mark indicator. If it is not enabled, simply choosing this menu option will turn on the feature.

View Menu.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 SAP business one data dictionary example, I have the Business Partner Master Data open, and have the mouse hovering over the “Name” field.

Business Partner Master Data.png
 

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.

Field Name - CardName.png

SAP Business One Property, Tables and Fields:

 

Property

Table

Field

Word Order

OWOR

DocNum

Name

OWOR

DocNum

Work Order Number

OWOR

DocNum

Operation Sequence

WOR1

LineNum

Required Quantity

OITM

PlannedQty

Run Time

@NBS_RESOURCE

U_NBS_DirType

Work Order Number

OWOR

DocNum

Routing Step

WOR1

LineNum

Item Number

OITM

ItemCode

Plant

NA

[Harcoded]

Department

@NBS_Resource

U_NBS_DeptID

Resource Code

@NBS_Resource

Code

Resource ID

@NBS_Resource

U_NBS_ResID

Machine Ability

@NBS_Capability

Code

Machine Name

@NBS_Capability

Name

Purchase Order Number

DocNum & POR1

DocNum +’-‘+ LineNumber

Item Number

POR1

ItemCode

Location Code

POR1

[Hardcoded]

Quantity Ordered

POR1

OpenQty

Expected Delivery Data

OPOR

DocDueDate

Work Order

OWOR

DocNum

Need By Date

OWOR

DueDate


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.

SAP Business One Demos:

Checkout the page SAP business one 9.3 demos which includes:

  1. Blanket Agreements
  2. Project Management
  3. Cost Accounting
  4. GL Account Determination
  5. Price Lists
  6. Return Management Agreement
  7. My Settings Support
  8. Electronic File Templates
  9. Campaign Management
  10. Analytics Portal
  11. New Cockpit Templates
  12. Centralized Deployment

For more information, contact Mark Burt at (803) 740-5008. Or visit our contact us page.

Recent Posts

How to Copy Goods Issue to Goods Receipt in SAP Business One!

Nov 2, 2018 12:48:44 PM

Beware of Vulnerabilities in Windows and Office

Nov 2, 2018 3:50:35 AM

7 Signs You Should Invest in a Cloud ERP System

Oct 12, 2018 3:59:01 AM

There Are New Options for Emailing Documents in SAP Business One!

Oct 3, 2018 3:13:59 PM

How Query Manager Gets User-Friendlier In SAP Business One!

Sep 24, 2018 4:28:26 PM

Posts by Topic

see all