DAC-Based OData: Data Retrieval

In this topic, you can find details about how to retrieve data by using the DAC-based OData interface. To retrieve the data, you append the DAC name and various parameters to the base URL of the DAC-based OData interface. For details about the base URL, see The URL for the DAC-Based OData Interface.

Retrieving All Records of a DAC

You can use the following request to get all records of a DAC.

GET https://<MYOB Acumatica instance URL>/t/<TenantName>/api/odata/dac/<DACName>

In this URL, the DAC is specified by its fully qualified name, by its display name, or by its short name (which does not include the namespace). For example, the use of either of the following URLs receives the same contents of the SOOrder DAC:

https://sweetlife.com/erp/t/U100/api/odata/dac/SOOrder
https://sweetlife.com/erp/t/U100/api/odata/dac/PX_Objects_SO_SOOrder

Tip:

Password fields are retrieved encrypted.

Retrieving a Record by Its Key Fields

You can retrieve a record with certain values of key fields by specifying these fields and their values in the URL.

The following sample request retrieves the sales order for which the OrderType field is set to SO and the OrderNbr field is set to 000058.

GET https://localhost/AcumaticaERP/t/U100/api/odata/dac/SOOrder(OrderType='SO',OrderNbr='000058')

Specifying a Filter

You can obtain the records that meet a particular filter’s criteria by specifying this filter in the $filter parameter of the URL. You use OData URL conventions to specify the conditions in the $filter parameter.

The following sample URL retrieves the sales orders whose OrderType field value is IN.

GET https://localhost/AcumaticaERP/t/U100/api/odata/dac/SOOrder?
$filter=(OrderType eq 'IN')

Retrieving Related Data

You can obtain records along with related data, such as detail records or other linked records. For example, consider a sales order record: Each sales order record may have one or more detail records, which contain data about the inventory items included in the sales order. A sales order record also includes the identifier of the customer, which can be used to retrieve the linked customer record. Therefore, along with a sales order record, you can retrieve detail records and the linked customer record.

You can obtain the records, along with their detail records, in a single query by using the $expand parameter.

The following sample request retrieves sales orders along with their detail information.

GET https://localhost/AcumaticaERP/t/U100/api/odata/dac/SOOrder?
$filter=(OrderType eq 'IN')&
$expand=SOLineCollection

The following sample request retrieves sales orders along with data of the customer record related to each of those sales orders.

GET https://localhost/AcumaticaERP/t/U100/api/odata/dac/SOOrder?
$filter=(OrderType eq 'IN')&
$expand=BAccountByCustomerID

Specifying Particular Fields in a Response

In a request, you can specify the DAC fields that you want the response to contain. You do this by specifying the desired fields in the $select parameter.

The following sample request retrieves customers and provides the AcctCD, AcctName, and CustomerClassID fields in the response.

GET https://localhost/AcumaticaERP/t/U100/api/odata/dac/PX_Objects_AR_Customer?
$select=AcctCD,AcctName,CustomerClassID

To list the fields of the detail records, you specify the following in the request:

In the $expand parameter, the navigation property for the detail DAC
After the property, in parentheses, the DAC fields as the values of the $select parameter

Tip:

By default, the maximum depth of expansion of the detail DACs in the $expand parameter is 3. You can change this value by specifying the value of the maxExpansionDepth attribute of the odata tag, which is available inside the px.core tag in the Web.config file of your MYOB Acumatica instance.

The following sample URL retrieves customers along with their default addresses.

GET https://localhost/AcumaticaERP/t/U100/api/odata/dac/PX_Objects_AR_Customer?
$select=AcctCD,AcctName,CustomerClassID&
$expand=AddressByDefAddressID(
$select=AddressLine1,AddressLine2,City,State,PostalCode)

The response includes the customers' AcctCD, AcctName, and CustomerClassID fields and the following fields of the customers’ addresses: AddressLine1, AddressLine2, City, State, and PostalCode.

Retrieving Records in Batches

To export records in batches from MYOB Acumatica by using DAC-based OData, you need to use the $top and $skip parameters of the request along with the $orderby parameter.

Attention:

The $orderby parameter is required for retrieving batches of records.

The following example retrieves a batch of warehouse detail records. It indicates that the records should be ordered by inventory ID in ascending order. The response will contain the top 500 records, excluding the first 50.

GET https://localhost/AcumaticaERP/t/U100/api/odata/dac/PX_Objects_IN_INSiteStatus?
$skip=50&
$top=500&
$orderby=InventoryID

Retrieving Multilingual Fields

If a record has multilingual fields, you can obtain the values of these fields in any available language. You can do this in either of the following ways:

By using the Accept-Language HTTP header and specifying the desired locale as its value
By using the locale URL parameter and specifying the desired locale as its value

If neither the Accept-Language HTTP header nor the locale URL parameter is used, the values of multilingual fields are returned in the default language (see Setting Up Languages). If both the Accept-Language HTTP header and the locale URL parameter are used, the locale specified in the locale URL parameter is taken into account.

The following sample URL retrieves the AACOMPUT01 inventory item with the French values of the multilingual fields.

GET https://localhost/AcumaticaERP/t/U100/api/odata/dac/PX_Objects_IN_InventoryItem?
locale=fr-FR&
$filter=InventoryCD eq 'AACOMPUT01'

The use of the following request has the same effect.

GET /AcumaticaERP/t/U100/api/odata/dac/PX_Objects_IN_InventoryItem?
$filter=InventoryCD eq 'AACOMPUT01' HTTP/1.1
Host: localhost
Accept-Language: fr-FR

Retrieving Archived Records

You can retrieve archived records along with non-archived ones. To make archived records visible for requests, you use the PX-ApiArchive HTTP header with the SHOW value.

The following sample request retrieves the shipments (including archived ones) that were created before December 31, 2022.

GET /AcumaticaERP/t/U100/api/odata/dac/PX_Objects_SO_SOShipment?
$filter=ShipDate gt 2022-12-31T00:00:00%2b03:00
&$select=ShipmentNbr,ShipDate HTTP/1.1
Host: localhost
PX-ApiArchive: SHOW

Retrieving Removed Records

The removed records are stored in the database if the corresponding database table has the DeletedDatabaseRecord column. For details about this mechanism, see Preservation of Deleted Records (DeletedDatabaseRecord). By default, the removed records are not returned if you request data through the DAC-based OData interface.

To retrieve the removed records, you need to specify the PX-ApiDeleted HTTP header with the SHOW value in the request.

For example, the following request retrieves both removed customer records and customer records that have not been removed.

GET /AcumaticaERP/t/U100/api/OData/DAC/Customer?
$select=BAccountID,DeletedDatabaseRecord HTTP/1.1
Host: localhost
PX-ApiDeleted: SHOW

Retrieving Custom and User-Defined Fields

In a customization project, you can add custom fields to MYOB Acumatica forms. You can also add user-defined fields to MYOB Acumatica forms and include them in a customization project. (For details about user-defined fields, see User-Defined Fields.)

To retrieve custom and user-defined fields through the DAC-based OData interface, you do not need to perform special actions. You address custom and user-defined fields by their names.