Base tables versus target tables

Base tables and target tables are special linked tables within SAP Business One. They are the most often used linked tables for SQL queries too.

You may find most of them related to "Sales-A/R" and "Purchase-A/P" documents or so-called "Marketing Documents".

Marketing documents may not have base tables or target tables. From the previous screenshot, you could clearly find that the Base Document and Target Document are available to this Sales Order. To get the Base Document, you may click on the "left arrow icon" or use the shortcut key Ctrl+N. To get the Target Document, you may click on the "right arrow icon" or use the shortcut key Ctrl+T. Only when the base table or target table is available to the current document, will you find the menu items and icons in active status. Otherwise, both icons and menu items are grayed out.

From the terms "Base" and "Target", it is clear that the target table can be based upon the base table.

One table could be based on different types of tables:

From this demonstration, you could get a clear picture about the relationship between Base Document (table) and Target Document (table). A specific pair of Purchase Order and Good Receipt PO tables is shown here. This concept applies to all document type tables. Here is a list of commonly used base-target pairs; they are not inclusive. You may find more, but the following are the most frequently used ones:

Base Table	Target Table
OQUT-Sales Quotation	ORDR-Sales Order
OQUT-Sales Quotation	ODLN-Delivery
OQUT-Sales Quotation	OINV-A/R Invoice
ORDR-Sales Order	ODLN-Delivery
ORDR-Sales Order	OINV-A/R Invoice
ODLN-Delivery	ORDN-Returns
ODLN-Delivery	OINV-A/R Invoice
ORDN-Returns	ORIN-A/R Credit Note
ODLN-A/R Invoice	ORIN-A/R Credit Note
OPOR-Purchase Order	OPDN-Goods Receipt PO
OPOR-Purchase Order	OPCH-A/P Invoice
OPDN-Goods Receipt PO	ORPD-Goods return
OPDN-Goods Receipt PO	OPCH-A/P Invoice
ORPD-Goods return	ORPC-A/P Credit Note
OPCH-A/P Invoice	ORPC-A/P Credit Note

I have omitted the details for the link. Actually, you will find that all the links exist on the first child table or so-called row table for the header table, such as QUT1 instead of OQUT.

The linking fields are very clear. For example:

·         BaseEntry in the target table refers to the base table's DocEntry

·         BaseType refers to the types of the base table

·         BaseRef is usually linked to DocNum field in the base table

·         BaseLine will be the line number in the base line table

·         TargetEntry in the base table refers to the target table's DocEntry

·         TargetType refers to the types of the target table

Keeping it simple—The key to build a good query

Simplicity is in need everywhere in the current changing world. Wherever you make things complicated, you may find yourself in an awkward position to compete with others.

My slogan is: simple, simpler, the simplest.

I have a habit in query building: the last step for any new query would be checking to see if it is the simplest one. In this way, "keep it simple" would not only be kept in the already built query, but also helps new queries to be the simplest in the beginning.

By keeping a query as simple as possible, it will ensure that the system performance is not affected. It will also be a great help to the troubleshooting process. A short checklist for simplicity is as follows:

·         Other queries: Are there any other queries doing a similar job, and if yes, why does the new query need to be built?

·         Tables: Are there any tables that have not been used for the query?

·         Fields: Are there any fields that have not been used for the query?

·         Conditions: Are there any condition overlaps?

The list can be much longer. The meaning behind it is clear: there is a never ending battle to get rid of complications.

When you try this method and it becomes a routine, you will find that query building becomes an enjoyable process.