Lesson 4: Getting Data from Multiple DACs

This lessons explains how to get data from multiple data access classes (DACs).

Data from Multiple Data Sources: General Information

In most cases, you want generic inquiry forms to give users the ability to review the data of some entity along with the data from other related entities. For example, suppose that you are creating a generic inquiry that lists open sales orders by customer. To build such an inquiry, you need to combine data from two data access classes (DACs): one that holds information about sales orders, and another that holds information about customers. For details on data access classes, which are referred to as tables on the user interface of the Generic Inquiry (SM208000) form, see DAC Discovery: General Information.

          We recommend that before you work with table relations, you have basic knowledge of SQL (which
          is used for storing, manipulating, and retrieving data in databases), so that you can understand how
          inquiries retrieve data.

You can use other generic inquiries as data sources, or have both DACs and inquiries as data sources for an inquiry, and combine then into query by setting relations between the sources. For details on using generic inquiries as data sources, see Data from Multiple Data Sources: Use of Generic Inquiry as Data Source.

Learning Objectives

In this lesson, you will learn how to construct a data request to retrieve data from multiple data access classes.

Applicable Scenarios

You may find the information in this lesson useful when you are responsible for the customization of Acumatica ERP in your company, including developing and modifying generic inquiries to give users information they need to do their jobs. You need to deliver diﬀerent inquiries that your colleagues may need to perform their jobs eﬀectively. Many of these inquiries require the retrieval of data from multiple related data access classes.

Construction of a Data Request from Multiple Tables

You start from inspecting the related forms (that is, the data entry forms of the data you will use) to determine the list of data access classes and fields you need to have in the results grid and use in formulas. Aer the list of the tables is prepared, you determine how the tables will relate to each other (parent-child pairs) and what join type the system should use. Thus, you define how the system returns combined records in case either a parent or a child table is missing a record to combine. Then for each pair you map a field from the parent table to the corresponding field from the child table. Thus, you define how the system will combine the records of the paired tables—that is, join conditions in SQL terms. We recommend using the Related Tables dialog box to simplify configuration of relations between the tables. If the system oﬀers you multiple suggestions on how a pair of tables can be linked, you can read detailed information about the tables and their fields in the DAC Schema Browser. You can construct a data request manually (or you can change automatically configured settings) by using the instructions for the manual procedure. For more information, see Data from Multiple Data Sources: Discovery of Key Fields. Lesson 4: Getting Data from Multiple DACs | 36

Definition of Relations Between Data Sources

Aer you have determined all the data sources you need for your inquiry, you need to decide how you will pair the sources and in what order you will list the pairs. For each pair you determine which source is considered the parent table and which is the child one. Usually, the parent table is the one that provides the primary data and the child table provides additional information. For example, for an inquiry that lists sales orders by customers, you specify SOOrder as the parent table, because the primary data that the inquiry is displaying is provided by the SOOrder class. The Customer class provides additional information, so you select it as the child table. The order in which you add the pairs to the system determines the sequence in which the system will retrieve the data. First, the system retrieves and combines data for the first pair of the sources to the single table. Then, the system adds up to this constructed table the data retrieved and combined from the next pair of the sources until there are no more pairs.

Selection of a Join Type

Aer you have decided which data source is considered the parent table and which is the child one, you decide on a type of join for these sources. In Acumatica ERP you can use one of the following join types:

          The join types that you can select in Acumatica ERP work in exactly the same way as the
          corresponding SQL JOIN statements do.

Inner: An Inner join creates a result by combining the records of the parent and child tables when there is at least one match in both tables (see the figure below). For example, suppose that for an inquiry that lists open sales orders by customers, you join SOOrder and Customer with an Inner join. The system will return only those open sales orders (from SOOrder) for which there are customer records in the Customer table. The system will not display customers who do not have open sales orders.
Le: A Le join returns all the records from the parent table combined with any matching records of the child table (see the figure below). For example, suppose that for an inquiry that lists open sales orders by customers, you join SOOrder (the parent table) and Customer (the child table) with the Le join. The system returns all open sales orders. For an open sales order for which the customer record was not found, the system returns an empty value in the column with customer information.
Right: A Right join returns all the records from the child table combined with any matching records from the parent table (see the figure below). For example, suppose that for an inquiry that lists open sales orders by customers, you join SOOrder (parent table) and Customer (child table) with the Right join. The system will return all customers. For a customer for which a sales order record was not found, the system returns an empty value in the column with sales order information.
Full: A Full join returns all the records from both the parent table and the child table when there is a match in a parent or child table record (see the figure below). For example, suppose that for an inquiry that lists open sales orders by customers, you join SOOrder and Customer with a Full join. The system will return all open sales orders (from SOOrder) and all customers (from the Customer table). A Full join can return a huge number of records.
Cross: A Cross join returns each record from the parent table combined with each record from the child table. Thus, the number of records in the result set is the number of records in the parent table multiplied by the number of records in the child table (see the figure below). Unlike the Inner, Le, Right, and Full join, the Cross join does not require a joining condition. Lesson 4: Getting Data from Multiple DACs | 37
```
       The inquiry results may be empty because of your access rights to Acumatica ERP forms, or because
       some data has been deleted in the database. For example, with the Inner join, the system will not
       display the open sales orders of the customers to whose accounts your access is restricted or whose
       accounts were deleted for some reason.
```

Definition of Join Conditions

Aer you have specified how the system should return combined records, you need to specify what data needs to be combined. To link the parent and child tables, you should specify the fields and conditions to link.

         You can use any fields to join the tables. We recommend using the key fields because it allows the
         system to retrieve the data more quickly.

For example, suppose that for an inquiry that lists open sales orders by customers, you joined SOOrder as the parent table and Customer as the child table (the type of join does not matter for linking fields). This means that the system should combine the records of these two tables by adding data from the child table to the parent one. The child table provides customer details, so you should indicate to the system that the data of the particular customer needs to be joined with the data of particular open sales order of this customer. To do this, you add the link that indicates to the system that the customerID field from the SOOrder table equals the BAccountID field from the Customer table. The BAccountID field is a key field in the Customer table. The Lesson 4: Getting Data from Multiple DACs | 38

system finds an open sales order, identifies the value of the customerID field, and searches for the same value in the BAccountID column of the Customer table. When the customer record is found, the system combines these two records into one and proceeds to the next open sales order. The following screenshot shows the SOOrder and Customer tables and the result of their combination.

Figure: Data combined from two tables

You can link the same two tables by using diﬀerent join conditions. The join conditions you specify should be determined by the result that you need to receive. For example, you establish a relation between the Users (parent) and CRActivity (child) tables. You can use the PKID field for the Users table and one of the CreatedByID and LastModifiedByID fields in the CRActivity table. If you are designing a generic inquiry to get data about users who created records in the CRActivity table, you link the PKID field with the Equal condition to the CreatedByID field. If you are designing a generic inquiry to get data about users who modified records in the CRActivity table, you link the PKID field with the Equal condition to the LastModifiedByID field.

Data from Multiple Data Sources: Discovery of Key Fields

On the Generic Inquiry (SM208000) form, you can manually configure the relation between each pair of tables that you are going to use in your inquiry form. In this case, you should perform the following actions: to decide what type of join to use, to discover the key fields of the tables, and to define how to link the discovered fields to get the desired output. If you are using the Add Relation wizard on the Generic Inquiry form, you need to specify only a parent table and the system will suggest child tables with the possible linking options. In this topic, you will read about the ways to discover key fields of a table that you can use for linking the tables.

          You can use any fields to join the tables. We recommend using the key and foreign key fields because
          it allows the system to retrieve the data more quickly.

Lesson 4: Getting Data from Multiple DACs | 39

Discovery of Key Fields by Using the DAC Schema Browser

Suppose that you need to create an inquiry that shows a list of AR invoices with detailed information about customers and the branch related to each AR invoice. You need to select the correct DACs for this inquiry and to specify the correct fields to link these DACs. Your primary goal is to show the list of AR invoices. You open the Invoices and Memos (AR301000) form and invoke the Element Properties dialog box for the Reference Nbr. box (which holds a unique identifier of a document) in the Summary area of the form. In the dialog box, you click the ARInvoice link in the Data Class box. The system opens the DAC Schema Browser in a separate browser tab with the detailed information about the ARInvoice DAC. In the DAC Schema Browser, in the Name column of the Fields table, you look for the fields that hold information about the branch and customer of an AR invoice. These fields are BranchID and CustomerID, which are foreign keys, which means that there are DACs that reference these fields (listed in the Foreign Reference column, as shown in the following screenshot).

Figure: Details of the BranchID field

For the BranchID field, the Branch DAC is listed as a foreign reference. You can view the details of this DAC by clicking its link. You may want to make sure that this is the class that holds information about branches and it has all the fields you need for your inquiry. If you are satisfied with the DAC, you should look for it either in the Incoming References or Outgoing References section to find its key field that you should use for linking. (See the following screenshot.)

          The first column in the Incoming References and Outgoing References sections lists the fields of the
          selected DAC.

Lesson 4: Getting Data from Multiple DACs | 40

Figure: The information about the Branch DAC and its key field

For the CustomerID field, the BAccount and Customer DACs are listed. While reading about the BAccount DAC, you find out that it is the base class for the Customer DAC. That is, the Customer DAC takes information from the BAccount DAC and has additional fields. So, if the information about the customers that you need is available in the BAccount class, you should use this class for the performance reasons. Otherwise, you should use the Customer class. Suppose that you need to show only customer's identifier, name, and default address. All this information is present in the BAccount class. You search for the BAccount class either in the Incoming References or Outgoing References section to find its key field that you should use for linking (see the following screenshot).

Figure: The information about BAccount DAC and its key field

Thus, you determine that the following relations should be used for the selected DACs:

ARInvoice and Branch: ARInvoice.BranchID = Branch.BranchID
ARInvoice and BAccount: ARInvoice.CusomerID = BAccount.BAccountID With this information, you add the DACs to the Data Tables tab and specify the relations on the Relations tab of the Generic Inquiry form. For details on DAC Schema Browser, see Data from Multiple Data Sources: DAC Schema Browser.

Discovery of Key Fields on the Source Code Form

The information about each key field—the field in the applicable record that holds unique data identifying that record from all the other records in the database—of the data access class you need is stored in the source code. Lesson 4: Getting Data from Multiple DACs | 41

You can get more information about the data access class you need on the Source Code (SM204570) form, which you can access in the following ways:

From the Element Properties dialog box, as you are using it to explore a UI element on a particular form, by clicking Actions > View Data Class Source. The form opens in a pop-up window. The specified data access class is shown on the Data Access tab (see Item 1 in the screenshot below).
By directly navigating to the Source Code form. Then in the Table Name box on the Data Access tab, you select the data access class you need. All fields of a data access class are listed on the Data Access tab, as shown in the following screenshot. You can explore any field further as you look for the key field; you generally focus on fields whose names seem to allude to numbers or identifiers. In the example shown in the screenshot, you would click #region OrderNbr to expand its attributes (Item 2 in the screenshot). Here you can find the string IsKey = true, which means that OrderNbr is included in the key of this class (Item 3).

Figure: Exploration of a data access class on the Source Code form

Particular types of key fields are distinguished as follows:

On the application level, key fields are the fields that are marked with IsKey = true.
On the database level, key fields are the fields that are marked with the PXDBIdentity attribute and with IsKey = true. Key fields of this type are used to join data access classes. The key field with the PXDBIdentity attribute is a part of the database index, so the queries with the fields with the PXDBIdentity attribute execute faster than the queries with fields with only the IsKey attribute do. Acumatica ERP master classes (which are categorized as Profiles in the UI in workspaces and search results), such as Customer and InventoryItem, usually have two key fields—that is, one with the IsKey attribute, and another with the PXDBIdentity attribute. The key fields of the InventoryItem class are InventoryID, which is marked with the PXDBIdentity attribute, and InventoryCD, which is marked with the IsKey attribute. For these Lesson 4: Getting Data from Multiple DACs | 42

classes, you use the field with the PXDBIdentity attribute to join classes in queries and the field with the IsKey attribute in other cases, such as for inquiry or report parameters. Acumatica ERP document and transaction classes (which are mentioned as Transactions in the UI in workspaces and search results)—such as SOOrder, ARInvoice, and ARPayment—usually have two or more key fields, which are marked with the IsKey attribute. For example, the key fields of the ARInvoice class are RefNbr and DocType. You can use both of these fields to join data access classes in queries. You can perform similar actions to explore any element you need for your inquiry. For the key fields of the data access class, you have to observe the data access class and the database table. To reveal the relationships between the data access classes, you inspect the fields of the main data access class and the related data access classes and review the structure of the corresponding database tables. Lesson 5: Filling a Report with Content | 43