DAC-Based OData: To Retrieve Data by Using the $expand and $select Parameters

This activity will walk you through the process of retrieving a list of records by using a GET request with the $expand and $select URL parameters through the DAC-based OData interface.

Story

In the business intelligence (BI) application of the MyStore company, a marketing manager should be able to view analytics for the distribution of existing customers by geographical state. To display the needed customer information to the marketing manager, you need to retrieve from MYOB Acumatica the relevant data on the customers, their contacts, and their addresses. This data has been entered on the Customers (AR303000) form in MYOB Acumatica. You want to use the DAC-based OData interface to obtain this data.

Process Overview

Through the DAC-based OData interface, you will request records directly from the PX.Objects.AR.Customer DAC. In the $select parameter, you will list every field you want to provide in the result. In the $expand parameter, you will specify navigation properties for every related DAC you want to provide in the result. For navigation properties, you will specify the DAC fields in the $select parameters in parentheses.

System Preparation

Before you begin performing the steps of this activity, do the following:

  1. Deploy an instance of MYOB Acumatica with the MyStoreInstance name and a tenant that has the MyStore name and contains the T100 data.
  2. Make sure the Postman application is installed on your computer. To download and install Postman, follow the instructions on https://www.postman.com/downloads/.
  3. Complete the following prerequisite activity: DAC-Based OData: To Sign In to MYOB Acumatica and Retrieve the Metadata.
  4. If you have created a Postman collection with the basic authentication configured, add a new request to the collection and configure the request to inherit the authorization type from the parent collection.

Step 1: Researching the Needed Fields

To provide the list of customers to the BI application, you need to retrieve the following values from MYOB Acumatica, which are listed with the corresponding locations on the Customers (AR303000) form:

  • Customer ID (the Customer ID box of the Summary area)
  • Account name (the Account Name box of the Account Info section on the General tab)
  • Customer class (the Customer Class box of the Summary area)
  • Details of the main contact of the customer (the Additional Account Info section of the General tab):
    • Email address (the Account Email box)
    • Primary phone number (the Business 1 box)
  • Customer address (the Account Address section of the General tab):
    • City (the City box)
    • State (the State box)
    • Postal code (the Postal Code box)
    • Address line (the Address Line 1 and Address Line 2 boxes)

These elements are shown in the following screenshot.

Figure 1. Elements on the Customers form


To understand which DACs and fields you need to use to retrieve the list of customer records with contacts, you can investigate the Customers form by using the Element Inspector tool.

To request the list of customers with contacts, you need the following fields of the DACs:

  • The AcctCD, AcctName, and CustomerClassID fields of the PX.Objects.AR.Customer DAC
  • The Email and Phone1 fields of the PX.Objects.CR.Contact DAC
  • The AddressLine1, AddressLine2, City, State, and PostalCode fields of the PX.Objects.CR.Address DAC

Step 2: Retrieving the List of Customers

Along with the information from the PX.Objects.AR.Customer DAC, you need to retrieve information from the PX.Objects.CR.Contact and PX.Objects.CR.Address DACs, which are related to the PX.Objects.AR.Customer DAC. The ContactByDefBillContactID navigation property of the Customer entity refers to the contact information contained in the Contact DAC. The AddressByDefAddressID navigation property of the Customer entity refers to the address contained in the Address DAC. To understand which navigation properties to use, you can review the metadata that you have received in DAC-Based OData: To Sign In to MYOB Acumatica and Retrieve the Metadata.

To retrieve the list of customers with contacts, do the following:

  1. In the Postman collection, add a request with the following settings:
    • HTTP method: GET
    • URL: http://localhost/MyStoreInstance/t/MyStore/api/odata/dac/PX_Objects_AR_Customer
    • The parameters in the following table
      Parameter Value
      $select AcctCD,AcctName,CustomerClassID
      $expand 
      ContactByDefBillContactID($select=Email,Phone1),
AddressByDefAddressID($select=AddressLine1,
AddressLine2,City,State,PostalCode)
  2. Send the request. If the request is successful, its response contains the 200 OK status code. The following code example shows a fragment of the response body.
    {
    "@odata.context": 
      "http://localhost/MyStoreInstance/t/MyStore/api/odata/dac/$metadata
      #PX_Objects_AR_Customer(
      AcctCD,AcctName,CustomerClassID,ContactByDefBillContactID(EMail,Phone1),
      AddressByDefAddressID(AddressLine1,AddressLine2,City,State,PostalCode))",
    "value": [
        {
            "AcctCD": "C000000001",
            "AcctName": "Jersey Central Office Equip",
            "CustomerClassID": "DEFAULT",
            "ContactByDefBillContactID": {
                "EMail": "jersey-equip@mail.con",
                "Phone1": "+1 (777) 283-0414"
            },
            "AddressByDefAddressID": {
                "AddressLine1": "1 De Villiers & Harrison St, 11-th Flr.",
                "AddressLine2": null,
                "City": "Johannesburg",
                "State": null,
                "PostalCode": null
            }
        },
    ...
  ]
}
  3. Save the request.