REST Web Services

From iDempiere en

Introduction

The iDempiere REST API is a plugin that allows developers to communicate with an iDempiere server via a RESTful API. This information is delivered via HTTP using JSON.

The iDempiere REST API seeks to follow the OData standard and uses the following structure and endpoints.

You can find examples of every call in the postman collection sample files in the official repository.

Login in to get your Bearer authorization token

IMPORTANT: REST API just allows to log in to roles with Role Type = WebService, or without Role Type. This means by default is not possible to login with role System, you need to clear the Role Type in System, or even better create a WebService role for System tenant.

There are two ways to log in to get your authorization token:

One-step log-in

When you know beforehand all the login information needed to get into the system, you need to do a POST request to the following endpoint
.../api/v1/auth/tokens

With a body like this: 

{
    "userName": "{{userName}}",
    "password": "{{password}}",
    "parameters": {
        "clientId": {{clientId}},
        "roleId": {{roleId}},
        "organizationId": {{organizationId}},
        "warehouseId": {{warehouseId}},
        "language": "{{language}}"
    }
}

Change all the Template:Propertyname values to the ones you want to use in your instance.

Normal log-in

If you want to log in as a user would normally do in iDempiere (choosing a role, warehouse, client, etc), you need to do the following requests

POST .../api/v1/auth/tokens
Body: 

{
    "userName": "{{userName}}",
    "password": "{{password}}"
}

Response Payload 

{
    "clients": [
        {
            "id": 11,
            "name": "GardenWorld"
        }
    ],
    "token": "eyJraWQiOiJpZGVtcGllcmUiLCJ0eXAiOiJKV1QiLCJhbGciOiJIUzUxMiJ9.eyJzdWIiOiJHYXJkZW5BZG1pbiIsImlzcyI6ImlkZW1waWVyZS5vcmciLCJDbGllbnRzIjoiMTEiLCJleHAiOjE2NjY5NjQ5Mjh9.3t0MuK6ReF7xNmb36ITM36VSKI5QnK3n0ZF_LIgPQSrso4oRhDsL8Mudc0NqH4qjvvKDlYsPquYKtrHnB5UiZg"
}

With that token you can request for login information from the user that is being authenticated. You need to add it to the request Header with Key value as:
Authorization: Bearer {authToken}

The information you can request is the following, and it needs to be done in this order because each request needs information from the previous call:
GET .../api/v1/auth/roles?client={clientId}

Returns an array with the roles that the user has access to


GET .../api/v1/auth/organizations?client={clientId}&role={roleId}

Returns an array with the organizations that the user has access to.


/api/v1/auth/warehouses?client={clientId}&role={roleId}&organization={organizationId}

Returns an array with the warehouses that the user has access to.


GET .../api/v1/auth/language?client={clientId}

Returns an array with the languages the user can login with.


When you have all the data you need to do a final PUT request like this:

PUT .../api/v1/auth/tokens

Body: 

{
  "clientId": {clientId},
  "roleId": {roleId},
  "organizationId": {organizationId},
  "warehouseId": {warehouseId},
  "language": "{language}"
}

* The language, organizationId and warehouseId properties are optional, it can be omitted and the system will use the default language and 0 respectively.

Response Payload 

{
    "userId": 101,
    "language": "en_US",
    "token": "eyJraWQiOiJpZGVtcGllcmUiLCJhbGciOiJIUzUxMiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJHYXJkZW5BZG1pbiIsIkFEX0NsaWVudF9JRCI6MTEsIkFEX1VzZXJfSUQiOjEwMSwiQURfUm9sZV9JRCI6MjAwMDAxLCJBRF9PcmdfSUQiOjExLCJNX1dhcmVob3VzZV9JRCI6MTAzLCJBRF9MYW5ndWFnZSI6ImVuX1VTIiwiQURfU2Vzc2lvbl9JRCI6MTAwMDE2MiwiaXNzIjoiaWRlbXBpZXJlLm9yZyIsImV4cCI6MTcwMTc3NTUzN30.bAUEhPylAQhZjZquJhvLpO9zMZG3g6zlM_IqO9ifeXJpAJBOoJtDqd8CrYPU1PKURzoPRSUglbKqr1LsXdz38A",
    "refresh_token": "eyJraWQiOiJpZGVtcGllcmUiLCJhbGciOiJIUzUxMiIsInR5cCI6IkpXVCJ9.eyJqdGkiOiIwODk1OTIyNC1iYzBhLTRkNTQtOTlhZS1jNmRmZjNiOGEwMzUiLCJpc3MiOiJpZGVtcGllcmUub3JnIiwiZXhwIjoxNzAxODU4MzM3fQ.uSE5SOtWgPvReC4JtyV4alHd-ccU0L9QhIpP2TwT7C5TJFeCGVYTdyWc291DaIweyiIGCfWFgQlbe0oH1EEXXg"
}

Refresh Token

As you see above, the login process returns a token and a refresh_token, normally the token expires in 1 hour and the refresh_token expires in 24 hours. These expiration defaults can be changed using SysConfig keys REST_TOKEN_EXPIRE_IN_MINUTES and REST_REFRESH_TOKEN_EXPIRE_IN_MINUTES.

It is a common practice in front-end development to store the refresh_token in a cookie or browser data, while keeping the token itself just in memory. This approach is taken due to the sensitive nature of the information contained within the token, which must not be disclosed.

When the token expires, you can get a new token calling the following request:

POST .../api/v1/auth/refresh

Body:

{
    "refresh_token": "{{refreshToken}}"
}

Response Payload 

{
    "token": "eyJraWQiOiJpZGVtcGllcmUiLCJhbGciOiJIUzUxMiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiJTdXBlclVzZXIiLCJBRF9DbGllbnRfSUQiOjAsIkFEX1VzZXJfSUQiOjEwMCwiQURfUm9sZV9JRCI6MTAwMDAxNCwiQURfT3JnX0lEIjowLCJBRF9MYW5ndWFnZSI6ImVuX1VTIiwiQURfU2Vzc2lvbl9JRCI6MTAwMDE2MywiaXNzIjoiaWRlbXBpZXJlLm9yZyIsImV4cCI6MTcwMTc3NTc5NX0.pdMdr3ihTz9fSOkoDP-tvrirjQNAw0Z8OHn1xMCq3A6b94JBD74MHd4hvb6y51xp8eezElsg1nVq0sMh7AsEvA",
    "refresh_token": "eyJraWQiOiJpZGVtcGllcmUiLCJhbGciOiJIUzUxMiIsInR5cCI6IkpXVCJ9.eyJqdGkiOiJhNWQ0ZjM3NS1hMzAzLTRiNTYtYjFjYi1hZWJkYTRmNjRkOTciLCJpc3MiOiJpZGVtcGllcmUub3JnIiwiZXhwIjoxNzAxODU4NTk1fQ.2ISJgrKBvcKOob1Jruy2apGqv_WcEu_ayGRDkgxadAoHGaG6c1WwcCi7thrrJ5OwOvS4fPh4d63REuRezLJjSw"
}

The new tokens are set with new expiration times as configured.

Refresh tokens can be expired (deleted) using the process "Expire Refresh Tokens"

A refresh token can be used just one time.

Logout

Logout a session from a token.

POST .../api/v1/auth/logout

Body:

{
    "token": "{{authToken}}"
}

The session associated to the token will be logged out.

This token cannot be used again, neither the refresh token associated.

Common Headers

  • Content-Type: application/json
  • Accept: application/json
  • Authorization: Bearer {authToken}
    • All calls except the first one (POST/auth) require an authorization bearer that is returned by the /auth APIs from the previous steps.

Requesting Data

iDempiere supports requests for data via HTTP GET requests.

On every get request the response will include this information, in addition to the one requested:

  • id: the record ID
  • uid: the UUID of the record
  • model-name: the table name of the record
  • {columnname}: each pair of column/value is represented as a json object with the columnname:
    • String: strings are represented directly as a json string quoted value
    • Yes/No: these are represented as true/false
    • Numbers: numbers are represented as a json number without quotes
    • Dates/Times: timestamp values are represented as a json date (quoted string with format ISO_8601
    • Foreign keys: these are represented as a json object with the following values:
      • propertyLabel -> the foreign name
      • id -> the foreign record ID
      • identifier -> the foreign identifier
      • model-name -> the foreign table name
    • Binary: binary columns are represented directly as a json string base64 encoded value
    • Images: these are represented as a json object with the following values:
      • propertyLabel -> the foreign name
      • id -> the foreign record ID
      • data -> a string containing the base64 encoded data for the image
      • model-name -> the foreign table name

Requesting PO Collections

The request below returns the collection of taxes. GET /api/v1/models/c_tax

Response Payload 

{
    "page-count": 4,
    "records-size": 2,
    "skip-records": 0,
    "row-count": 8,
    "array-count": 2,
    "records": [
        {
            "id": 106,
            "uid": "315dacb1-415c-45e7-8dac-a9a44fa8aca7",
            "AD_Client_ID": {
                "propertyLabel": "Client",
                "id": 11,
                "identifier": "MyGST",
                "model-name": "ad_client"
            },
            "AD_Org_ID": {
                "propertyLabel": "Organization",
                "id": 0,
                "identifier": "*",
                "model-name": "ad_org"
            },
            "IsActive": true,
            "Created": "2003-01-08T13:14:01Z",
            "CreatedBy": {
                "propertyLabel": "Created By",
                "id": 100,
                "identifier": "SuperUser",
                "model-name": "ad_user"
            },
            "Name": "GST",
            "Description": "Canadian Federal Sales Tax",
            "Parent_Tax_ID": {
                "propertyLabel": "Parent Tax",
                "id": 108,
                "identifier": "GST/PST",
                "model-name": "c_tax"
            },
            "C_Country_ID": {
                "propertyLabel": "Country",
                "id": 109,
                "identifier": "Canada",
                "model-name": "c_country"
            },
            "To_Country_ID": {
                "propertyLabel": "To",
                "id": 109,
                "identifier": "Canada",
                "model-name": "c_country"
            },
            "C_TaxCategory_ID": {
                "propertyLabel": "Tax Category",
                "id": 107,
                "identifier": "Standard",
                "model-name": "c_taxcategory"
            },
            "Updated": "2003-02-22T02:20:37Z",
            "UpdatedBy": {
                "propertyLabel": "Updated By",
                "id": 0,
                "identifier": "~System (deprecated)~",
                "model-name": "ad_user"
            },
            "IsDocumentLevel": false,
            "ValidFrom": "2001-01-01",
            "IsSummary": false,
            "Rate": 7,
            "RequiresTaxCertificate": false,
            "TaxIndicator": "GST",
            "IsDefault": false,
            "IsTaxExempt": false,
            "SOPOType": {
                "propertyLabel": "SO/PO Type",
                "id": "B",
                "identifier": "Both",
                "model-name": "ad_ref_list"
            },
            "IsSalesTax": false,
            "TaxPostingIndicator": {
                "propertyLabel": "Posting Indicator",
                "id": "0",
                "identifier": "Separate Tax Posting",
                "model-name": "ad_ref_list"
            },
            "model-name": "c_tax"
        },
        {
            "id": 107,
            "uid": "d76b09c5-531b-4f98-a8c6-9af848b6697b",
            "AD_Client_ID": {
                "propertyLabel": "Client",
                "id": 11,
                "identifier": "MyGST",
                "model-name": "ad_client"
            },
            "AD_Org_ID": {
                "propertyLabel": "Organization",
                "id": 0,
                "identifier": "*",
                "model-name": "ad_org"
            },
            "IsActive": true,
            "Created": "2003-01-08T15:06:11Z",
            "CreatedBy": {
                "propertyLabel": "Created By",
                "id": 100,
                "identifier": "SuperUser",
                "model-name": "ad_user"
            },
            "Name": "PST",
            "Description": "Canadian Provintial Tax",
            "Parent_Tax_ID": {
                "propertyLabel": "Parent Tax",
                "id": 108,
                "identifier": "GST/PST",
                "model-name": "c_tax"
            },
            "C_Country_ID": {
                "propertyLabel": "Country",
                "id": 109,
                "identifier": "Canada",
                "model-name": "c_country"
            },
            "To_Country_ID": {
                "propertyLabel": "To",
                "id": 109,
                "identifier": "Canada",
                "model-name": "c_country"
            },
            "C_TaxCategory_ID": {
                "propertyLabel": "Tax Category",
                "id": 107,
                "identifier": "Standard",
                "model-name": "c_taxcategory"
            },
            "Updated": "2003-02-22T02:20:37Z",
            "UpdatedBy": {
                "propertyLabel": "Updated By",
                "id": 0,
                "identifier": "~System (deprecated)~",
                "model-name": "ad_user"
            },
            "IsDocumentLevel": false,
            "ValidFrom": "2002-01-01",
            "IsSummary": false,
            "Rate": 7.5,
            "RequiresTaxCertificate": false,
            "TaxIndicator": "PST",
            "IsDefault": false,
            "IsTaxExempt": false,
            "SOPOType": {
                "propertyLabel": "SO/PO Type",
                "id": "B",
                "identifier": "Both",
                "model-name": "ad_ref_list"
            },
            "IsSalesTax": false,
            "TaxPostingIndicator": {
                "propertyLabel": "Posting Indicator",
                "id": "0",
                "identifier": "Separate Tax Posting",
                "model-name": "ad_ref_list"
            },
            "model-name": "c_tax"
        }
    ]
}

Requesting an Individual PO by ID

The request below returns an individual PO of type tax by the given id "106". * You can use here either record id or the UUID (C_Tax_ID or C_Tax_UU value)

GET /api/v1/models/c_tax/106

Response Payload 

{
    "id": 106,
    "uid": "315dacb1-415c-45e7-8dac-a9a44fa8aca7",
    "AD_Client_ID": {
        "propertyLabel": "Client",
        "id": 11,
        "identifier": "MyGST",
        "model-name": "ad_client"
    },
    "AD_Org_ID": {
        "propertyLabel": "Organization",
        "id": 0,
        "identifier": "*",
        "model-name": "ad_org"
    },
    "IsActive": true,
    "Created": "2003-01-08T13:14:01Z",
    "CreatedBy": {
        "propertyLabel": "Created By",
        "id": 100,
        "identifier": "SuperUser",
        "model-name": "ad_user"
    },
    "Name": "GST",
    "Description": "Canadian Federal Sales Tax",
    "Parent_Tax_ID": {
        "propertyLabel": "Parent Tax",
        "id": 108,
        "identifier": "GST/PST",
        "model-name": "c_tax"
    },
    "C_Country_ID": {
        "propertyLabel": "Country",
        "id": 109,
        "identifier": "Canada",
        "model-name": "c_country"
    },
    "To_Country_ID": {
        "propertyLabel": "To",
        "id": 109,
        "identifier": "Canada",
        "model-name": "c_country"
    },
    "C_TaxCategory_ID": {
        "propertyLabel": "Tax Category",
        "id": 107,
        "identifier": "Standard",
        "model-name": "c_taxcategory"
    },
    "Updated": "2003-02-22T02:20:37Z",
    "UpdatedBy": {
        "propertyLabel": "Updated By",
        "id": 0,
        "identifier": "~System (deprecated)~",
        "model-name": "ad_user"
    },
    "IsDocumentLevel": false,
    "ValidFrom": "2001-01-01",
    "IsSummary": false,
    "Rate": 7,
    "RequiresTaxCertificate": false,
    "TaxIndicator": "GST",
    "IsDefault": false,
    "IsTaxExempt": false,
    "SOPOType": {
        "propertyLabel": "SO/PO Type",
        "id": "B",
        "identifier": "Both",
        "model-name": "ad_ref_list"
    },
    "IsSalesTax": false,
    "TaxPostingIndicator": {
        "propertyLabel": "Posting Indicator",
        "id": "0",
        "identifier": "Separate Tax Posting",
        "model-name": "ad_ref_list"
    },
    "model-name": "c_tax"
}

Requesting an Individual Property

To address an entity property clients append a path segment containing property name to the URL of the entity. The request below returns the Rate property of a tax. GET /api/v1/models/c_tax/106/rate

Response Payload 

{
    "id": 106,
    "uid": "315dacb1-415c-45e7-8dac-a9a44fa8aca7",
    "Rate": 7,
    "model-name": "c_tax"
}

Querying Data

iDempiere supports various kinds of query options for querying data. This section will help you go through the common scenarios for these query options.

Query Option $filter

The $filter query option allows developers to filter a collection of resources that are addressed by a request URL. The expression specified with $filter is evaluated for each resource in the collection, and only items where the expression evaluates to true are included in the response. These are the basic predicates that are supported:

  • $filter
    • Logical operators
      • eq -> equals =
      • neq -> not equals !=
      • gt -> greater than >
      • ge -> greater or equal >=
      • lt -> less than <
      • le -> less or equal <=
      • and -> &
      • or -> |
      • not -> !
    • Method operators (WARNING: there must not be spaces before/after the parameters on these functions, strings must be surrounded by single quotes):
      • contains -> if a string contains another string
      • startswith -> if a string starts with another string
      • endswith -> if a string ends with another string
      • tolower -> convert a string to lowercase
      • toupper -> convert a string to uppercase
    • Parenthesis are supported

Examples

The request below using $filter to get business partners where IsCustomer = true and IsActive = true and the name starts with 'Pa' GET /api/v1/models/c_bpartner?$filter=isCustomer eq true AND isActive eq true AND startswith(name,'Pa')

Response Payload 

{
    "page-count": 1,
    "records-size": 3,
    "skip-records": 0,
    "row-count": 1,
    "array-count": 1,
    "records": [
        {
            "id": 121,
            "uid": "39e85feb-94a2-4e41-ae45-e7d49d7be077",
            "AD_Client_ID": {
                "propertyLabel": "Client",
                "id": 11,
                "identifier": "MyGST",
                "model-name": "ad_client"
            },
            "AD_Org_ID": {
                "propertyLabel": "Organization",
                "id": 0,
                "identifier": "*",
                "model-name": "ad_org"
            },
            "IsActive": true,
            "Created": "2003-01-13T10:14:03Z",
            "CreatedBy": {
                "propertyLabel": "Created By",
                "id": 100,
                "identifier": "SuperUser",
                "model-name": "ad_user"
            },
            "Updated": "2020-11-11T13:16:05Z",
            "UpdatedBy": {
                "propertyLabel": "Updated By",
                "id": 100,
                "identifier": "SuperUser",
                "model-name": "ad_user"
            },
            "Value": "Patio",
            "Name": "Patio Fun, Inc.",
            "SalesVolume": 0,
            "NumberEmployees": 0,
            "IsSummary": false,
            "AD_Language": {
                "propertyLabel": "Language",
                "id": "es_CO",
                "identifier": "Spanish (Colombia)",
                "model-name": "ad_language"
            },
            "IsVendor": true,
            "IsCustomer": true,
            "IsProspect": false,
            "FirstSale": "2020-11-05",
            "SO_CreditLimit": 0,
            "SO_CreditUsed": 588.24,
            "AcqusitionCost": 0,
            "PotentialLifeTimeValue": 0,
            "ActualLifeTimeValue": 588.24,
            "ShareOfCustomer": 0,
            "IsEmployee": false,
            "IsSalesRep": false,
            "IsOneTime": false,
            "IsTaxExempt": false,
            "IsDiscountPrinted": true,
            "C_BP_Group_ID": {
                "propertyLabel": "Business Partner Group",
                "id": 104,
                "identifier": "Vendors",
                "model-name": "c_bp_group"
            },
            "SendEMail": false,
            "SOCreditStatus": {
                "propertyLabel": "Credit Status",
                "id": "O",
                "identifier": "Credit OK",
                "model-name": "ad_ref_list"
            },
            "TotalOpenBalance": 588.240,
            "IsPOTaxExempt": false,
            "IsManufacturer": false,
            "Is1099Vendor": false,
            "IsUseTaxIdDigit": false,
            "IsDetailedNames": false,
            "model-name": "c_bpartner"
        }
    ]
}

Query Option $expand

iDempiere supports nested filters in $expand.

The request below return Orders and all their Lines and Taxes. GET /api/v1/models/c_order?$expand=c_orderLine,c_ordertax

Response Payload 

{
    "page-count": 68,
    "records-size": 1,
    "skip-records": 0,
    "row-count": 68,
    "array-count": 1,
    "records": [
        {
            "id": 1000019,
            "uid": "7144b00f-a283-465e-8d2d-1fe89eac5748",
            "AD_Client_ID": {
                "propertyLabel": "Client",
                "id": 11,
                "identifier": "MyGST",
                "model-name": "ad_client"
            },
            "AD_Org_ID": {
                "propertyLabel": "Organization",
                "id": 11,
                "identifier": "HQ",
                "model-name": "ad_org"
            },
            "IsActive": true,
            "Created": "2021-07-22T17:36:20Z",
            "CreatedBy": {
                "propertyLabel": "Created By",
                "id": 100,
                "identifier": "SuperUser",
                "model-name": "ad_user"
            },
            "Updated": "2021-07-22T17:36:20Z",
            "UpdatedBy": {
                "propertyLabel": "Updated By",
                "id": 100,
                "identifier": "SuperUser",
                "model-name": "ad_user"
            },
            "DocumentNo": "50012",
            "DocStatus": {
                "propertyLabel": "Document Status",
                "id": "DR",
                "identifier": "Drafted",
                "model-name": "ad_ref_list"
            },
            "C_DocType_ID": {
                "propertyLabel": "Document Type",
                "id": 0,
                "identifier": "** New **",
                "model-name": "c_doctype"
            },
            "C_DocTypeTarget_ID": {
                "propertyLabel": "Target Document Type",
                "id": 132,
                "identifier": "Standard Order",
                "model-name": "c_doctype"
            },
            "Description": "Order Description from Business Partner Definition",
            "IsApproved": true,
            "IsCreditApproved": false,
            "IsDelivered": false,
            "IsInvoiced": false,
            "IsPrinted": false,
            "IsTransferred": false,
            "DateOrdered": "2021-07-19",
            "DatePromised": "2021-07-19",
            "DateAcct": "2021-07-19",
            "SalesRep_ID": {
                "propertyLabel": "Sales Representative",
                "id": 101,
                "identifier": "GardenAdmin",
                "model-name": "ad_user"
            },
            "C_PaymentTerm_ID": {
                "propertyLabel": "Payment Term",
                "id": 106,
                "identifier": "2%10 Net 30",
                "model-name": "c_paymentterm"
            },
            "C_Currency_ID": {
                "propertyLabel": "Currency",
                "id": 102,
                "identifier": "EUR",
                "model-name": "c_currency"
            },
            "InvoiceRule": {
                "propertyLabel": "Invoice Rule",
                "id": "I",
                "identifier": "Immediate",
                "model-name": "ad_ref_list"
            },
            "FreightAmt": 0,
            "DeliveryViaRule": {
                "propertyLabel": "Delivery Via",
                "id": "P",
                "identifier": "Pickup",
                "model-name": "ad_ref_list"
            },
            "PriorityRule": {
                "propertyLabel": "Priority",
                "id": "5",
                "identifier": "Medium",
                "model-name": "ad_ref_list"
            },
            "TotalLines": 14500,
            "GrandTotal": 14500,
            "M_Warehouse_ID": {
                "propertyLabel": "Warehouse",
                "id": 103,
                "identifier": "HQ Warehouse",
                "model-name": "m_warehouse"
            },
            "M_PriceList_ID": {
                "propertyLabel": "Price List",
                "id": 101,
                "identifier": "Standard",
                "model-name": "m_pricelist"
            },
            "C_BPartner_ID": {
                "propertyLabel": "Business Partner",
                "id": 117,
                "identifier": "C&W Construction",
                "model-name": "c_bpartner"
            },
            "AD_User_ID": {
                "propertyLabel": "User/Contact",
                "id": 1000011,
                "identifier": "Carlitos Boss",
                "model-name": "ad_user"
            },
            "POReference": "Order Reference 1234",
            "ChargeAmt": 0,
            "Processed": false,
            "C_BPartner_Location_ID": {
                "propertyLabel": "Partner Location",
                "id": 112,
                "identifier": "Stamford",
                "model-name": "c_bpartner_location"
            },
            "IsSOTrx": true,
            "DeliveryRule": {
                "propertyLabel": "Delivery Rule",
                "id": "A",
                "identifier": "Availability",
                "model-name": "ad_ref_list"
            },
            "FreightCostRule": {
                "propertyLabel": "Freight Cost Rule",
                "id": "I",
                "identifier": "Freight included",
                "model-name": "ad_ref_list"
            },
            "PaymentRule": {
                "propertyLabel": "Payment Rule",
                "id": "P",
                "identifier": "On Credit",
                "model-name": "ad_ref_list"
            },
            "IsDiscountPrinted": true,
            "IsTaxIncluded": false,
            "IsSelected": false,
            "SendEMail": false,
            "Bill_User_ID": {
                "propertyLabel": "Invoice Contact",
                "id": 1000011,
                "identifier": "Carlitos Boss",
                "model-name": "ad_user"
            },
            "Bill_BPartner_ID": {
                "propertyLabel": "Invoice Partner",
                "id": 117,
                "identifier": "C&W Construction",
                "model-name": "c_bpartner"
            },
            "Bill_Location_ID": {
                "propertyLabel": "Invoice Location",
                "id": 112,
                "identifier": "Stamford",
                "model-name": "c_bpartner_location"
            },
            "IsSelfService": false,
            "IsDropShip": false,
            "Volume": 0.00,
            "Weight": 0.00,
            "AmountTendered": 0.00,
            "AmountRefunded": 0.00,
            "ProcessedOn": 0,
            "IsPayScheduleValid": false,
            "IsPriviledgedRate": false,
            "NetAmtToInvoice": 0,
            "model-name": "c_order",
            "c_orderLine": [
                {
                    "id": 1000033,
                    "uid": "0b02614d-bdcd-406f-a9e6-5399ed3a9997",
                    "AD_Client_ID": {
                        "propertyLabel": "Client",
                        "id": 11,
                        "identifier": "MyGST",
                        "model-name": "ad_client"
                    },
                    "AD_Org_ID": {
                        "propertyLabel": "Organization",
                        "id": 11,
                        "identifier": "HQ",
                        "model-name": "ad_org"
                    },
                    "IsActive": true,
                    "Created": "2021-07-22T17:36:50Z",
                    "CreatedBy": {
                        "propertyLabel": "Created By",
                        "id": 100,
                        "identifier": "SuperUser",
                        "model-name": "ad_user"
                    },
                    "Updated": "2021-07-22T17:36:50Z",
                    "UpdatedBy": {
                        "propertyLabel": "Updated By",
                        "id": 100,
                        "identifier": "SuperUser",
                        "model-name": "ad_user"
                    },
                    "Line": 10,
                    "DateOrdered": "2021-07-19",
                    "DatePromised": "2021-07-19",
                    "M_Product_ID": {
                        "propertyLabel": "Product",
                        "id": 1000295,
                        "identifier": "Poker_Poker",
                        "model-name": "m_product"
                    },
                    "C_UOM_ID": {
                        "propertyLabel": "UOM",
                        "id": 109,
                        "identifier": "6-Pack",
                        "model-name": "c_uom"
                    },
                    "M_Warehouse_ID": {
                        "propertyLabel": "Warehouse",
                        "id": 103,
                        "identifier": "HQ Warehouse",
                        "model-name": "m_warehouse"
                    },
                    "QtyOrdered": 1,
                    "QtyReserved": 0,
                    "QtyDelivered": 0,
                    "QtyInvoiced": 0,
                    "C_Currency_ID": {
                        "propertyLabel": "Currency",
                        "id": 102,
                        "identifier": "EUR",
                        "model-name": "c_currency"
                    },
                    "PriceList": 14500,
                    "PriceActual": 14500,
                    "C_Tax_ID": {
                        "propertyLabel": "Tax",
                        "id": 104,
                        "identifier": "Standard",
                        "model-name": "c_tax"
                    },
                    "C_BPartner_ID": {
                        "propertyLabel": "Business Partner",
                        "id": 117,
                        "identifier": "C&W Construction",
                        "model-name": "c_bpartner"
                    },
                    "FreightAmt": 0,
                    "C_BPartner_Location_ID": {
                        "propertyLabel": "Partner Location",
                        "id": 112,
                        "identifier": "Stamford",
                        "model-name": "c_bpartner_location"
                    },
                    "LineNetAmt": 14500,
                    "PriceLimit": 14500,
                    "Discount": 0.00,
                    "M_AttributeSetInstance_ID": {
                        "propertyLabel": "Attribute Set Instance",
                        "id": 0,
                        "model-name": "m_attributesetinstance"
                    },
                    "IsDescription": false,
                    "Processed": false,
                    "PriceEntered": 14500,
                    "QtyEntered": 1,
                    "PriceCost": 0,
                    "QtyLostSales": 0,
                    "RRAmt": 0
                }
            ]
        }
    ]
}

You can add query operators ($filter, $orderyby, $top, $skip, $select) to the detail tables by adding the query operators between parentheses separated by ;.

The request below returns Orders and their Lines (Line number and line net amount) from lines with linnetamt > 1000.

GET /api/v1/models/c_order?$select=DocumentNo,Description&$expand=C_OrderLine($select=Line,Linenetamt ; $filter=LineNetAmt gt 1000 ; $orderby=Line)&$top=5

Response Payload 

{
    "page-count": 14,
    "records-size": 5,
    "skip-records": 0,
    "row-count": 68,
    "array-count": 0,
    "records": [
        {
            "id": 1000019,
            "uid": "7144b00f-a283-465e-8d2d-1fe89eac5748",
            "DocumentNo": "50012",
            "Description": "Order Description from Business Partner Definition",
            "model-name": "c_order",
            "C_OrderLine": [
                {
                    "id": 1000033,
                    "uid": "0b02614d-bdcd-406f-a9e6-5399ed3a9997",
                    "Line": 10,
                    "LineNetAmt": 14500
                }
            ]
        },
        {
            "id": 1000020,
            "uid": "470c98e0-5a19-413d-b589-89fc67272823",
            "DocumentNo": "50013",
            "Description": "MyCanadian Federal Sales Tax",
            "model-name": "c_order"
        },
        {
            "id": 1000018,
            "uid": "fb485eb0-6e79-439d-926f-cce53d76d806",
            "DocumentNo": "50011",
            "Description": "Order Description from Business Partner Definition",
            "model-name": "c_order"
        },
        {
            "id": 1000015,
            "uid": "040ffe18-04ab-4989-ae83-9ed295146042",
            "DocumentNo": "50008",
            "model-name": "c_order",
            "C_OrderLine": [
                {
                    "id": 1000026,
                    "uid": "d3eac7ec-caf1-473e-8c22-f5b8cee55d30",
                    "Line": 10,
                    "LineNetAmt": 4275.00
                }
            ]
        },
        {
            "id": 1000017,
            "uid": "ee3a9225-439a-4022-8eaa-1109e5e4f049",
            "DocumentNo": "50010",
            "Description": "My Sales Tax",
            "model-name": "c_order"
        }
    ]
}

By default expand searches for the primary key column in the master table and tries to create the relation with this column name in the expanded table. /ad_user?$expand=c_order expands using ad_user_id by default. Sometimes you want to specify which column must be used for the expanding. In this case, you need to specify the column name using a dot after the table name.

The request below returns users and their corresponding orders as sales representatives.

/api/v1/models/ad_user?$expand=C_order.salesrep_id($select=documentno)&$select=Name

Response Payload 

{
    "id": 100,
    "uid": "986cb4dc-7161-4d77-a7c7-3832d24b19de",
    "Name": "SuperUser",
    "model-name": "ad_user",
    "C_order": [
        {
            "id": 104,
            "uid": "3456b6d0-b911-42e6-8432-f52ded58713f",
            "DocumentNo": "800000"
        }
    ]
}

iDempiere has special tables where record_id is used, for instance, fact_acct. You can expand to this table using the same syntax as explained above. The plug-in will automatically set the correct value for AD_Table_ID

The request below returns the invoice 104 and their corresponding records on fact_acct.

/api/v1/models/c_invoice/104?$expand=fact_acct.record_id($select=fact_acct_id)&$select=DocumentNo

Response Payload 

{
    "id": 104,
    "uid": "6d2768c9-b3cc-4820-b903-9748b416b98f",
    "DocumentNo": "10000001",
    "model-name": "c_invoice",
    "fact_acct": [
        {
            "id": 1001729,
            "uid": "cd02c4e7-b386-46aa-b367-f5ec618dccc8"
        },
        {
            "id": 1001730,
            "uid": "e8dfd3ec-d5bf-4ded-9646-0a2a8d86a680"
        },
        {
            "id": 1001731,
            "uid": "2bf88ace-83bc-4968-abdd-fd211765da92"
        },
        {
            "id": 1001732,
            "uid": "26735041-c88e-4670-89e5-95ae49beb4dc"
        }
    ]
}

Sometimes you want to expand to a master record as well, for instance, from product to product category. You accomplish this by writing expand followed by the column name you want to expand.

The request below returns the product 122 and its corresponding product category.

/api/v1/models/m_product/122?$expand=m_product_category_id($select=Name,IsDefault)&$select=Name

Response Payload 

{
    "id": 122,
    "uid": "c713192a-9ed3-4740-ad32-9583c30d0206",
    "Name": "Standard",
    "model-name": "m_product",
    "M_Product_Category_ID": {
        "id": 105,
        "uid": "be686f7e-993f-4162-a02d-ad4bb8c6987b",
        "Name": "Standard",
        "IsDefault": true,
        "model-name": "m_product_category"
    }
}

Note: For obvious reasons. $select is the only query operator accepted in this case.

You can expand on multiple levels by using the $expand operators within the $expand clause. You can only expand deeper on master > detail records. Cases like GET ../orders?$expand=AD_user_ID($expand=C_orderLine) are forbidden.

Query Option $orderby

The $orderby query option allows developers to request resources in either ascending order using asc or descending order using desc. If asc or desc not specified, then the resources will be ordered in ascending order.

The request below orders products on property Value in descending order. GET /api/v1/models/m_product?$orderby=Value desc

Response Payload 

{
    "page-count": 37,
    "records-size": 3,
    "skip-records": 0,
    "row-count": 111,
    "array-count": 3,
    "records": [
        {
            "id": 1000216,
            "uid": "e19114c8-86cc-4fb4-b6eb-15f8fa350d38",
            "AD_Client_ID": {
                "propertyLabel": "Client",
                "id": 11,
                "identifier": "MyGST",
                "model-name": "ad_client"
            },
            "AD_Org_ID": {
                "propertyLabel": "Organization",
                "id": 50001,
                "identifier": "Fertilizer",
                "model-name": "ad_org"
            },
            "IsActive": true,
            "Created": "2020-10-05T18:46:14Z",
            "CreatedBy": {
                "propertyLabel": "Created By",
                "id": 101,
                "identifier": "GardenAdmin",
                "model-name": "ad_user"
            },
            "Updated": "2020-11-23T19:45:12Z",
            "UpdatedBy": {
                "propertyLabel": "Updated By",
                "id": 101,
                "identifier": "GardenAdmin",
                "model-name": "ad_user"
            },
            "Name": "Tron",
            "IsSummary": false,
            "C_UOM_ID": {
                "propertyLabel": "UOM",
                "id": 100,
                "identifier": "Each",
                "model-name": "c_uom"
            },
            "IsStocked": true,
            "IsPurchased": false,
            "IsSold": false,
            "Volume": 0,
            "Weight": 0,
            "Value": "zas",
            "M_Product_Category_ID": {
                "propertyLabel": "Product Category",
                "id": 105,
                "identifier": "Standard",
                "model-name": "m_product_category"
            },
            "C_TaxCategory_ID": {
                "propertyLabel": "Tax Category",
                "id": 107,
                "identifier": "Standard",
                "model-name": "c_taxcategory"
            },
            "Discontinued": false,
            "IsBOM": false,
            "IsInvoicePrintDetails": false,
            "IsPickListPrintDetails": false,
            "IsVerified": false,
            "ProductType": {
                "propertyLabel": "Product Type",
                "id": "I",
                "identifier": "Item",
                "model-name": "ad_ref_list"
            },
            "M_AttributeSetInstance_ID": {
                "propertyLabel": "Attribute Set Instance",
                "id": 0,
                "model-name": "m_attributesetinstance"
            },
            "IsWebStoreFeatured": false,
            "IsSelfService": false,
            "IsDropShip": false,
            "IsExcludeAutoDelivery": false,
            "UnitsPerPack": 1,
            "LowLevel": 0,
            "IsKanban": false,
            "IsManufactured": false,
            "IsPhantom": false,
            "IsOwnBox": false,
            "IsAutoProduce": false,
            "BXS_IsPricePerUOM": false,
            "model-name": "m_product"
        },
        {
            "id": 1000398,
            "uid": "d5d1bb16-81b4-429b-86bf-f667f22b93df",
            "AD_Client_ID": {
                "propertyLabel": "Client",
                "id": 11,
                "identifier": "MyGST",
                "model-name": "ad_client"
            },
            "AD_Org_ID": {
                "propertyLabel": "Organization",
                "id": 50001,
                "identifier": "Fertilizer",
                "model-name": "ad_org"
            },
            "IsActive": true,
            "Created": "2021-01-20T18:52:06Z",
            "CreatedBy": {
                "propertyLabel": "Created By",
                "id": 101,
                "identifier": "GardenAdmin",
                "model-name": "ad_user"
            },
            "Updated": "2021-01-20T18:52:06Z",
            "UpdatedBy": {
                "propertyLabel": "Updated By",
                "id": 101,
                "identifier": "GardenAdmin",
                "model-name": "ad_user"
            },
            "Name": "yudo mat",
            "IsSummary": false,
            "C_UOM_ID": {
                "propertyLabel": "UOM",
                "id": 100,
                "identifier": "Each",
                "model-name": "c_uom"
            },
            "IsStocked": true,
            "IsPurchased": true,
            "IsSold": true,
            "Volume": 0,
            "Weight": 0,
            "Value": "ym",
            "M_Product_Category_ID": {
                "propertyLabel": "Product Category",
                "id": 106,
                "identifier": "Trees",
                "model-name": "m_product_category"
            },
            "C_TaxCategory_ID": {
                "propertyLabel": "Tax Category",
                "id": 107,
                "identifier": "Standard",
                "model-name": "c_taxcategory"
            },
            "Discontinued": false,
            "IsBOM": false,
            "IsInvoicePrintDetails": false,
            "IsPickListPrintDetails": false,
            "IsVerified": false,
            "ProductType": {
                "propertyLabel": "Product Type",
                "id": "I",
                "identifier": "Item",
                "model-name": "ad_ref_list"
            },
            "M_AttributeSetInstance_ID": {
                "propertyLabel": "Attribute Set Instance",
                "id": 0,
                "model-name": "m_attributesetinstance"
            },
            "IsWebStoreFeatured": false,
            "IsSelfService": true,
            "IsDropShip": false,
            "IsExcludeAutoDelivery": false,
            "UnitsPerPack": 1,
            "LowLevel": 0,
            "IsKanban": false,
            "IsManufactured": false,
            "IsPhantom": false,
            "IsOwnBox": false,
            "IsAutoProduce": false,
            "BXS_IsPricePerUOM": false,
            "model-name": "m_product"
        },
        {
            "id": 1000299,
            "uid": "1726c817-71bc-4abf-8949-0372c9ffb0d3",
            "AD_Client_ID": {
                "propertyLabel": "Client",
                "id": 11,
                "identifier": "MyGST",
                "model-name": "ad_client"
            },
            "AD_Org_ID": {
                "propertyLabel": "Organization",
                "id": 0,
                "identifier": "*",
                "model-name": "ad_org"
            },
            "IsActive": true,
            "Created": "2020-10-08T19:04:22Z",
            "CreatedBy": {
                "propertyLabel": "Created By",
                "id": 100,
                "identifier": "SuperUser",
                "model-name": "ad_user"
            },
            "Updated": "2020-10-08T19:04:22Z",
            "UpdatedBy": {
                "propertyLabel": "Updated By",
                "id": 100,
                "identifier": "SuperUser",
                "model-name": "ad_user"
            },
            "Name": "Xiaomi Redmi Airdots",
            "IsSummary": false,
            "C_UOM_ID": {
                "propertyLabel": "UOM",
                "id": 100,
                "identifier": "Each",
                "model-name": "c_uom"
            },
            "IsStocked": true,
            "IsPurchased": true,
            "IsSold": true,
            "Volume": 0,
            "Weight": 0,
            "Value": "Xiaomi Redmi Airdots",
            "M_Product_Category_ID": {
                "propertyLabel": "Product Category",
                "id": 1000015,
                "identifier": "Gadgets",
                "model-name": "m_product_category"
            },
            "C_TaxCategory_ID": {
                "propertyLabel": "Tax Category",
                "id": 107,
                "identifier": "Standard",
                "model-name": "c_taxcategory"
            },
            "ShelfWidth": 0,
            "ShelfHeight": 0,
            "ShelfDepth": 0,
            "UnitsPerPallet": 0,
            "Discontinued": false,
            "IsBOM": false,
            "IsInvoicePrintDetails": false,
            "IsPickListPrintDetails": false,
            "IsVerified": false,
            "ProductType": {
                "propertyLabel": "Product Type",
                "id": "I",
                "identifier": "Item",
                "model-name": "ad_ref_list"
            },
            "GuaranteeDays": 0,
            "M_AttributeSetInstance_ID": {
                "propertyLabel": "Attribute Set Instance",
                "id": 0,
                "model-name": "m_attributesetinstance"
            },
            "GuaranteeDaysMin": 0,
            "IsWebStoreFeatured": false,
            "IsSelfService": true,
            "IsDropShip": false,
            "IsExcludeAutoDelivery": false,
            "UnitsPerPack": 0,
            "LowLevel": 0,
            "IsKanban": false,
            "IsManufactured": false,
            "IsPhantom": false,
            "IsOwnBox": false,
            "IsAutoProduce": false,
            "BXS_IsPricePerUOM": false,
            "model-name": "m_product"
        }
    ]
}

Query Options $top and $skip

The $top query option requests the number of items in the queried collection to be included in the result. The $skip query option requests the number of items in the queried collection that are to be skipped and not included in the result.

The request below return the first two Orders starting with other 5th order. GET /api/v1/models/c_order?$top=2&$skip=5

Response Payload 

{
    "page-count": 34,
    "records-size": 2,
    "skip-records": 5,
    "row-count": 68,
    "array-count": 2,
    "records": [
        {
            "id": 1000022,
            "uid": "2238dc66-edda-41f7-8348-d781342f6dfc",
            "AD_Client_ID": {
                "propertyLabel": "Client",
                "id": 11,
                "identifier": "MyGST",
                "model-name": "ad_client"
            },
            "AD_Org_ID": {
                "propertyLabel": "Organization",
                "id": 11,
                "identifier": "HQ",
                "model-name": "ad_org"
            },
            "IsActive": true,
            "Created": "2021-07-29T15:59:51Z",
            "CreatedBy": {
                "propertyLabel": "Created By",
                "id": 101,
                "identifier": "GardenAdmin",
                "model-name": "ad_user"
            },
            "Updated": "2021-07-29T15:59:51Z",
            "UpdatedBy": {
                "propertyLabel": "Updated By",
                "id": 101,
                "identifier": "GardenAdmin",
                "model-name": "ad_user"
            },
            "DocumentNo": "50015",
            "DocStatus": {
                "propertyLabel": "Document Status",
                "id": "DR",
                "identifier": "Drafted",
                "model-name": "ad_ref_list"
            },
            "C_DocType_ID": {
                "propertyLabel": "Document Type",
                "id": 0,
                "identifier": "** New **",
                "model-name": "c_doctype"
            },
            "C_DocTypeTarget_ID": {
                "propertyLabel": "Target Document Type",
                "id": 132,
                "identifier": "Standard Order",
                "model-name": "c_doctype"
            },
            "IsApproved": false,
            "IsCreditApproved": false,
            "IsDelivered": false,
            "IsInvoiced": false,
            "IsPrinted": false,
            "IsTransferred": false,
            "DateOrdered": "2021-07-29",
            "DatePromised": "2021-07-29",
            "DateAcct": "2021-07-29",
            "SalesRep_ID": {
                "propertyLabel": "Sales Representative",
                "id": 101,
                "identifier": "GardenAdmin",
                "model-name": "ad_user"
            },
            "C_PaymentTerm_ID": {
                "propertyLabel": "Payment Term",
                "id": 105,
                "identifier": "Immediate",
                "model-name": "c_paymentterm"
            },
            "C_Currency_ID": {
                "propertyLabel": "Currency",
                "id": 102,
                "identifier": "EUR",
                "model-name": "c_currency"
            },
            "InvoiceRule": {
                "propertyLabel": "Invoice Rule",
                "id": "I",
                "identifier": "Immediate",
                "model-name": "ad_ref_list"
            },
            "FreightAmt": 0.0,
            "DeliveryViaRule": {
                "propertyLabel": "Delivery Via",
                "id": "P",
                "identifier": "Pickup",
                "model-name": "ad_ref_list"
            },
            "PriorityRule": {
                "propertyLabel": "Priority",
                "id": "5",
                "identifier": "Medium",
                "model-name": "ad_ref_list"
            },
            "TotalLines": 0.0,
            "GrandTotal": 0.0,
            "M_Warehouse_ID": {
                "propertyLabel": "Warehouse",
                "id": 103,
                "identifier": "HQ Warehouse",
                "model-name": "m_warehouse"
            },
            "M_PriceList_ID": {
                "propertyLabel": "Price List",
                "id": 101,
                "identifier": "Standard",
                "model-name": "m_pricelist"
            },
            "C_BPartner_ID": {
                "propertyLabel": "Business Partner",
                "id": 112,
                "identifier": "Standard",
                "model-name": "c_bpartner"
            },
            "AD_User_ID": {
                "propertyLabel": "User/Contact",
                "id": 101,
                "identifier": "GardenAdmin",
                "model-name": "ad_user"
            },
            "ChargeAmt": 0.0,
            "Processed": false,
            "C_BPartner_Location_ID": {
                "propertyLabel": "Partner Location",
                "id": 108,
                "identifier": "Monroe",
                "model-name": "c_bpartner_location"
            },
            "IsSOTrx": true,
            "DeliveryRule": {
                "propertyLabel": "Delivery Rule",
                "id": "A",
                "identifier": "Availability",
                "model-name": "ad_ref_list"
            },
            "FreightCostRule": {
                "propertyLabel": "Freight Cost Rule",
                "id": "I",
                "identifier": "Freight included",
                "model-name": "ad_ref_list"
            },
            "PaymentRule": {
                "propertyLabel": "Payment Rule",
                "id": "P",
                "identifier": "On Credit",
                "model-name": "ad_ref_list"
            },
            "IsDiscountPrinted": false,
            "IsTaxIncluded": false,
            "IsSelected": false,
            "SendEMail": false,
            "Bill_BPartner_ID": {
                "propertyLabel": "Invoice Partner",
                "id": 112,
                "identifier": "Standard",
                "model-name": "c_bpartner"
            },
            "Bill_Location_ID": {
                "propertyLabel": "Invoice Location",
                "id": 108,
                "identifier": "Monroe",
                "model-name": "c_bpartner_location"
            },
            "IsSelfService": false,
            "IsDropShip": false,
            "IsPayScheduleValid": false,
            "IsPriviledgedRate": false,
            "NetAmtToInvoice": 0.0,
            "model-name": "c_order"
        },
        {
            "id": 1000016,
            "uid": "5565e213-a119-4bcc-beb6-c9f9a4e75dab",
            "AD_Client_ID": {
                "propertyLabel": "Client",
                "id": 11,
                "identifier": "MyGST",
                "model-name": "ad_client"
            },
            "AD_Org_ID": {
                "propertyLabel": "Organization",
                "id": 11,
                "identifier": "HQ",
                "model-name": "ad_org"
            },
            "IsActive": true,
            "Created": "2021-03-09T14:20:31Z",
            "CreatedBy": {
                "propertyLabel": "Created By",
                "id": 100,
                "identifier": "SuperUser",
                "model-name": "ad_user"
            },
            "Updated": "2021-03-09T14:20:59Z",
            "UpdatedBy": {
                "propertyLabel": "Updated By",
                "id": 100,
                "identifier": "SuperUser",
                "model-name": "ad_user"
            },
            "DocumentNo": "50009",
            "DocStatus": {
                "propertyLabel": "Document Status",
                "id": "CO",
                "identifier": "Completed",
                "model-name": "ad_ref_list"
            },
            "C_DocType_ID": {
                "propertyLabel": "Document Type",
                "id": 132,
                "identifier": "Standard Order",
                "model-name": "c_doctype"
            },
            "C_DocTypeTarget_ID": {
                "propertyLabel": "Target Document Type",
                "id": 132,
                "identifier": "Standard Order",
                "model-name": "c_doctype"
            },
            "Description": "Order Description from Business Partner Definition",
            "IsApproved": true,
            "IsCreditApproved": false,
            "IsDelivered": false,
            "IsInvoiced": false,
            "IsPrinted": false,
            "IsTransferred": false,
            "DateOrdered": "2021-02-16",
            "DatePromised": "2021-02-25",
            "DateAcct": "2021-02-16",
            "SalesRep_ID": {
                "propertyLabel": "Sales Representative",
                "id": 101,
                "identifier": "GardenAdmin",
                "model-name": "ad_user"
            },
            "C_PaymentTerm_ID": {
                "propertyLabel": "Payment Term",
                "id": 106,
                "identifier": "2%10 Net 30",
                "model-name": "c_paymentterm"
            },
            "C_Currency_ID": {
                "propertyLabel": "Currency",
                "id": 102,
                "identifier": "EUR",
                "model-name": "c_currency"
            },
            "InvoiceRule": {
                "propertyLabel": "Invoice Rule",
                "id": "I",
                "identifier": "Immediate",
                "model-name": "ad_ref_list"
            },
            "FreightAmt": 0,
            "DeliveryViaRule": {
                "propertyLabel": "Delivery Via",
                "id": "P",
                "identifier": "Pickup",
                "model-name": "ad_ref_list"
            },
            "M_Shipper_ID": {
                "propertyLabel": "Shipper",
                "id": 100,
                "identifier": "UPS",
                "model-name": "m_shipper"
            },
            "PriorityRule": {
                "propertyLabel": "Priority",
                "id": "5",
                "identifier": "Medium",
                "model-name": "ad_ref_list"
            },
            "TotalLines": 95.00,
            "GrandTotal": 95.00,
            "M_Warehouse_ID": {
                "propertyLabel": "Warehouse",
                "id": 103,
                "identifier": "HQ Warehouse",
                "model-name": "m_warehouse"
            },
            "M_PriceList_ID": {
                "propertyLabel": "Price List",
                "id": 101,
                "identifier": "Standard",
                "model-name": "m_pricelist"
            },
            "C_BPartner_ID": {
                "propertyLabel": "Business Partner",
                "id": 117,
                "identifier": "C&W Construction",
                "model-name": "c_bpartner"
            },
            "AD_User_ID": {
                "propertyLabel": "User/Contact",
                "id": 1000011,
                "identifier": "Carlitos Boss",
                "model-name": "ad_user"
            },
            "POReference": "Order Reference 1234",
            "ChargeAmt": 0,
            "Processed": true,
            "C_BPartner_Location_ID": {
                "propertyLabel": "Partner Location",
                "id": 112,
                "identifier": "Stamford",
                "model-name": "c_bpartner_location"
            },
            "IsSOTrx": true,
            "DeliveryRule": {
                "propertyLabel": "Delivery Rule",
                "id": "A",
                "identifier": "Availability",
                "model-name": "ad_ref_list"
            },
            "FreightCostRule": {
                "propertyLabel": "Freight Cost Rule",
                "id": "F",
                "identifier": "Fix price",
                "model-name": "ad_ref_list"
            },
            "PaymentRule": {
                "propertyLabel": "Payment Rule",
                "id": "P",
                "identifier": "On Credit",
                "model-name": "ad_ref_list"
            },
            "IsDiscountPrinted": true,
            "IsTaxIncluded": false,
            "IsSelected": false,
            "SendEMail": false,
            "Bill_User_ID": {
                "propertyLabel": "Invoice Contact",
                "id": 1000011,
                "identifier": "Carlitos Boss",
                "model-name": "ad_user"
            },
            "Bill_BPartner_ID": {
                "propertyLabel": "Invoice Partner",
                "id": 117,
                "identifier": "C&W Construction",
                "model-name": "c_bpartner"
            },
            "Bill_Location_ID": {
                "propertyLabel": "Invoice Location",
                "id": 112,
                "identifier": "Stamford",
                "model-name": "c_bpartner_location"
            },
            "IsSelfService": false,
            "IsDropShip": false,
            "Volume": 0.00,
            "Weight": 0.00,
            "ProcessedOn": 1615296059621.6216,
            "IsPayScheduleValid": false,
            "IsPriviledgedRate": false,
            "NetAmtToInvoice": 0.00,
            "model-name": "c_order"
        }
    ]
}

Query Option $select

The $select query option allows the clients to requests a limited set of properties for each entity or complex type.

The request below returns Name and Value of all products. GET /api/v1/models/m_product?$select=Name,Value

Response Payload 

{
    "page-count": 28,
    "records-size": 4,
    "skip-records": 0,
    "row-count": 111,
    "array-count": 4,
    "records": [
        {
            "id": 1000322,
            "uid": "b0e7e8b6-13ed-406d-a105-bd67ddb7f423",
            "Name": "Mug 50",
            "Value": "Mug 50",
            "model-name": "m_product"
        },
        {
            "id": 134,
            "uid": "01254fee-c75f-42f0-941c-142e27078643",
            "Name": "Patio Table",
            "Value": "PTable",
            "model-name": "m_product"
        },
        {
            "id": 137,
            "uid": "26a7e6d7-e2c1-4c21-97f2-773dc222e6a2",
            "Name": "Mulch 10#",
            "Value": "Mulch",
            "model-name": "m_product"
        },
        {
            "id": 50003,
            "uid": "8a27ac0a-56c0-4a3a-8eb8-ff87c94899c1",
            "Name": "Ultra Glue",
            "Value": "UltraGlue",
            "model-name": "m_product"
        }
    ]
}

iDempiere specific query Options

Query Option $valrule

The query option $valrule can be used to get PO records using a validation rule - AD_ValRule_ID or AD_ValRule_UU

Query Option $context

The query option $context can be used to put variables in context to be parsed by the validation rule

Query Option showsql

The query option showsql can be used to add the SQL query being executed on GET.

You can also use showsql=nodata to obtain just information about the query without returning the real data.

The payload will include a JSON element with name sql-command containing the query that is executed in the database to obtain the record(s).

When showsql is used at the same time with $expand, then the payload will include the sql-command JSON element for the query on the master table, and also it will include the elements with format sql-command-[table] for each expanded table, for example the following query:

GET .../api/v1/models/c_tax/106?$expand=c_tax_acct&showsql

will return the following elements in the payload: 

    "sql-command": "SELECT C_Tax_ID,AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Name,Description,Parent_Tax_ID,C_Country_ID,C_Region_ID,To_Country_ID,To_Region_ID,C_TaxCategory_ID,Updated,UpdatedBy,IsDocumentLevel,ValidFrom,IsSummary,Rate,RequiresTaxCertificate,TaxIndicator,IsDefault,IsTaxExempt,SOPOType,IsSalesTax,AD_Rule_ID,C_Tax_UU,C_TaxProvider_ID,C_CountryGroupTo_ID,C_CountryGroupFrom_ID FROM C_Tax WHERE (c_tax_ID=?) AND C_Tax.AD_Client_ID IN(0,11) AND (C_Tax.AD_Org_ID IN (0,11,12,50000,1000000,50002,50001,50004,50006,50005,50007))",
    "sql-command-c_tax_acct": "SELECT C_Tax_ID,C_AcctSchema_ID,AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy,T_Due_Acct,T_Credit_Acct,T_Expense_Acct,C_Tax_Acct_UU FROM C_Tax_Acct WHERE (c_tax_ID=?) AND IsActive=? AND C_Tax_Acct.AD_Client_ID IN(0,11) AND (C_Tax_Acct.AD_Org_ID IN (0,11,12,50000,1000000,50002,50001,50004,50006,50005,50007))",

Data Modification

iDempiere supports Create, Update and Delete operation for some or all exposed POs.

Create a PO (POST)

To create a PO in a collection, the client sends a POST request to that collection's URL. The POST body MUST contain a single valid entity representation.

The request below creates a Tax which contains complex type and collection property. POST api/v1/models/c_tax

Body

{
    "AD_Org_ID": {
        "id": 0,
        "tableName": "AD_Org"
    },
    "isActive": true,
    "name": "MyGST",
    "description": "MyCanadian Federal Sales Tax",
    "Parent_Tax_ID": {
        "identifier": "GST/PST",
        "tableName": "C_Tax"
    },
    "C_Country_ID": {
        "identifier": "Canada",
        "tableName": "C_Country"
    },
    "To_Country_ID": {
        "identifier": "Canada",
        "tableName": "C_Country"
    },
    "C_TaxCategory_ID": {
        "identifier": "Standard",
        "tableName": "C_TaxCategory"
    },
    "isDocumentLevel": false,
    "validFrom": "01/01/2001",
    "isSummary": false,
    "rate": "7.0",
    "requiresTaxCertificate": false,
    "taxIndicator": "GST",
    "isDefault": false,
    "isTaxExempt": false,
    "sOPOType": {
        "id": "B"
    },
    "isSalesTax": false,
    "tableName": "C_Tax"
}


Response Payload 

{
    "id": 1000005,
    "uid": "4e71df80-f3f5-41cd-84cf-2bc8e12322d0",
    "AD_Client_ID": {
        "propertyLabel": "Client",
        "id": 11,
        "identifier": "MyGST",
        "model-name": "ad_client"
    },
    "AD_Org_ID": {
        "propertyLabel": "Organization",
        "id": 0,
        "identifier": "*",
        "model-name": "ad_org"
    },
    "IsActive": true,
    "Created": "2022-10-28T15:44:44Z",
    "CreatedBy": {
        "propertyLabel": "Created By",
        "id": 101,
        "identifier": "GardenAdmin",
        "model-name": "ad_user"
    },
    "Name": "Standard 19%",
    "Description": "MyCanadian Federal Sales Tax",
    "Parent_Tax_ID": {
        "propertyLabel": "Parent Tax",
        "id": 108,
        "identifier": "GST/PST",
        "model-name": "c_tax"
    },
    "C_Country_ID": {
        "propertyLabel": "Country",
        "id": 109,
        "identifier": "Canada",
        "model-name": "c_country"
    },
    "To_Country_ID": {
        "propertyLabel": "To",
        "id": 109,
        "identifier": "Canada",
        "model-name": "c_country"
    },
    "C_TaxCategory_ID": {
        "propertyLabel": "Tax Category",
        "id": 107,
        "identifier": "Standard",
        "model-name": "c_taxcategory"
    },
    "Updated": "2022-10-28T15:44:44Z",
    "UpdatedBy": {
        "propertyLabel": "Updated By",
        "id": 101,
        "identifier": "GardenAdmin",
        "model-name": "ad_user"
    },
    "IsDocumentLevel": false,
    "ValidFrom": "1990-01-01",
    "IsSummary": false,
    "Rate": 7.0,
    "RequiresTaxCertificate": false,
    "TaxIndicator": "GST",
    "IsDefault": false,
    "IsTaxExempt": false,
    "SOPOType": {
        "propertyLabel": "SO/PO Type",
        "id": "B",
        "identifier": "Both",
        "model-name": "ad_ref_list"
    },
    "IsSalesTax": false,
    "TaxPostingIndicator": {
        "propertyLabel": "Posting Indicator",
        "id": "0",
        "identifier": "Separate Tax Posting",
        "model-name": "ad_ref_list"
    },
    "model-name": "c_tax"
}

Remove a PO (DELETE)

The request below deletes the Tax with id '1000005'. DELETE /api/v1/models/c_tax/1000005

Response Payload 

{
    "msg": "Deleted"
}

Update a PO (PUT)

The request below updates the tax with id 1000000 PUT /api/v1/models/c_tax/1000000

Body: 

{
    "name": "Standard Tax"
}

Response Payload 

{
    "id": 1000002,
    "uid": "a1c76f9b-30ea-44c1-a8c3-2bc27daa1cd1",
    "AD_Client_ID": {
        "propertyLabel": "Client",
        "id": 11,
        "identifier": "MyGST",
        "model-name": "ad_client"
    },
    "AD_Org_ID": {
        "propertyLabel": "Organization",
        "id": 0,
        "identifier": "*",
        "model-name": "ad_org"
    },
    "IsActive": true,
    "Created": "2020-08-21T18:29:28Z",
    "CreatedBy": {
        "propertyLabel": "Created By",
        "id": 101,
        "identifier": "GardenAdmin",
        "model-name": "ad_user"
    },
    "Name": "Standard Tax",
    "Description": "My Sales Tax",
    "Parent_Tax_ID": {
        "propertyLabel": "Parent Tax",
        "id": 108,
        "identifier": "GST/PST",
        "model-name": "c_tax"
    },
    "C_Country_ID": {
        "propertyLabel": "Country",
        "id": 109,
        "identifier": "Canada",
        "model-name": "c_country"
    },
    "To_Country_ID": {
        "propertyLabel": "To",
        "id": 109,
        "identifier": "Canada",
        "model-name": "c_country"
    },
    "C_TaxCategory_ID": {
        "propertyLabel": "Tax Category",
        "id": 107,
        "identifier": "Standard",
        "model-name": "c_taxcategory"
    },
    "Updated": "2022-10-28T15:48:14Z",
    "UpdatedBy": {
        "propertyLabel": "Updated By",
        "id": 101,
        "identifier": "GardenAdmin",
        "model-name": "ad_user"
    },
    "IsDocumentLevel": false,
    "ValidFrom": "1990-01-01",
    "IsSummary": false,
    "Rate": 6.0,
    "RequiresTaxCertificate": false,
    "TaxIndicator": "GST",
    "IsDefault": false,
    "IsTaxExempt": false,
    "SOPOType": {
        "propertyLabel": "SO/PO Type",
        "id": "B",
        "identifier": "Both",
        "model-name": "ad_ref_list"
    },
    "IsSalesTax": true,
    "TaxPostingIndicator": {
        "propertyLabel": "Posting Indicator",
        "id": "0",
        "identifier": "Separate Tax Posting",
        "model-name": "ad_ref_list"
    },
    "model-name": "c_tax"
}

Special Entity Type Representations

Image

If you are modifying or creating values of an Image type column/field, the json entity representation must be as follows:

  • id -> the foreign record ID. If not informed a new foreign record will be created and linked to the main record
  • file_name -> the readable file name to be stored at the foreign table
  • url -> an optional URL for the image to be stored at the foreign table
  • data -> a string with the base64 encoded data for the image

Other Resources

GET api/v1/auth/jwk

Calling this endpoint returns the key used to sign the JWT.

By default this endpoint returns nothing. It requires the System Configurator IDEMPIERE_REST_EXPOSE_JWK to be set to Y on System tenant.

WARNING! Exposing the signature key is intended exclusively for integration with API gateways that requires it (like krakend), and this endpoint MUST be hidden from public usage and exposed JUST to the API gateway. Exposing the key publicly open the REST interface to attacks changing the JWT token.

Returns:

  • keys
    • alg
    • k
    • kid
    • kty

GET api/v1/models/{tablename}/{recordId}/print

This method is used to get the print format from the model's record id.

URL Parameters:

  • $report_type -> optional - ( PDF | HTML | CSV | XLSX | XLS )

Returns:

  • AD_PInstance_ID
  • process
  • summary
  • isError
  • reportFile
  • reportFileName
  • reportFileLength
  • nodeId

Windows api/v1/windows

working with AD_Window and AD_Tab

Forms api/v1/forms

GET api/v1/forms

Get Forms available

URL Parameters:

  • $filter -> optional - used to filter the query

Returns:

  • an array of PO json records from the forms (AD_Form) queried

Processes api/v1/processes

working with process and reports

GET api/v1/processes

Get Processes available

URL Parameters:

  • $filter -> optional - used to filter the query

Returns:

  • an array of PO json records from the process (AD_Process) queried

GET api/v1/processes/{process_value}

Get Information from a process

URL Parameters:

  • {process_value} -> value of the process (in slug form - lowercase)

Returns:

  • an PO json record from the process (AD_Process) queried

POST api/v1/processes/{process_value}

Run a process

URL Parameters:

  • {process_value} -> value of the process (in slug form - lowercase)

for example https://demo.globalqss.com/api/v1/processes/translationimpexp

Body:

  • {process_parameters} -> a JSON representation of the process parameters, for example:
{
    "ImportOrExport": "export",
    "AD_Language": "es_CO",
    "IsOnlyCentralizedData": true
}

for reports three special parameters can be added:

  • report-type: with the possible values HTML, CSV, XLS or PDF (the default)
  • is-summary
  • print-format-id

also, for buttons is possible to define two special parameters:

  • table-id or model-name
  • record-id

Returns:

  • JSON information about the process/report executed:
    • AD_PInstance_ID
    • process
    • summary
    • isError
    • nodeId
    • logs - array containing the logs from the process

The reports will be returned in an object with name reportFile containing the base64 encoded report, and additionally the objects reportFileName and reportFileLength.

If the process produces an export file it will be returned in an object exportFile, and additionally the objects exportFileName and exportFileLength.

GET api/v1/processes/jobs

Get information about jobs running

Returns:

  • an array of json records from the process running (AD_PInstance)

GET api/v1/processes/jobs/{ad_pinstance_id}

Get Information from a job

URL Parameters:

  • {ad_pinstance_id}

Returns:

  • a json record from the process (AD_PInstance)

POST api/v1/processes/jobs/{process_value}

Run a process in background (as job)

URL Parameters:

  • {process_value} -> value of the process (in slug form - lowercase)

for example https://demo.globalqss.com/api/v1/processes/jobs/translationimpexp

Body:

  • {process_parameters} -> a JSON representation of the process parameters (same as for running the process)

same special parameters apply as for the reports, and additionally you can define the parameter notification-type with the values E, N or B (EMail, Notification or Both)

Returns:

  • JSON information about the AD_PInstance scheduled

Files api/v1/files

to access files created by api/v1/processes

TBD

Caches api/v1/caches

GET api/v1/caches

Get caches information

URL Parameters:

  • table_name -> optional - to filter the list of caches
  • name -> optional - to filter the list of caches

Returns:

  • caches (array)
    • name
    • table_name
    • size
    • expireMinutes
    • maxSize
    • distributed (boolean)

DEL api/v1/caches

Reset cache

When called without parameters it reset the whole cache, or just from a table, or just one record.

URL Parameters:

  • table_name -> optional - to restrict the reset to just one table
  • record_id -> optional - to restrict the reset to just one record

Returns:

  • entriesReset (number of entries cleared)

Nodes api/v1/nodes

get nodes info, get log files, reset and rotate logs.

TBD

Servers api/v1/servers

servers and schedulers resource

TBD

Infos api/v1/infos

info windows

TBD

Reference api/v1/reference/{id}

Get reference list by id/uuid

TBD

Workflows api/v1/workflow

GET api/v1/workflow

Returns:

  • all suspended, non-processed nodes of the current user
    • id
    • uid
    • model-name
    • node-name
    • node-description
    • priority
    • summary
    • node-help
    • history-records
    • table-name
    • ad_table_id
    • record_id
    • node-approval
    • node-confirmation
    • created

GET api/v1/workflow/{userid}

Returns:

  • all suspended, non-processed nodes of the user id passed in the URL
    • id
    • uid
    • model-name
    • node-name
    • node-description
    • priority
    • summary
    • node-help
    • history-records
    • table-name
    • ad_table_id
    • record_id
    • node-approval
    • node-confirmation
    • created

Examples: 

/api/v1/workflow/100

PUT api/v1/workflow/approve/{nodeid}

Approves the corresponding node. Just for nodes of type = Approval (Column name = IsApproved).

Body Parameters:

  • "message": "This is an example message for approval" (Optional)

Returns:

  • Confirmation of the approval

PUT api/v1/workflow/reject/{nodeid}

Rejects the corresponding node. Just for nodes of type = Approval (Column name = IsApproved).

Body Parameters:

  • "message": "This is an example message for rejection" (Optional)

Returns:

  • Confirmation of the rejection

PUT api/v1/workflow/forward/{nodeid}

Forwards the corresponding node to a specific user. Just for nodes of type = Approval (Column name = IsApproved).

Body Parameters:

  • "userTo": {userToID} (Mandatory)
  • "message": "This is an example message for fowarding" (Optional)

Returns:

  • Confirmation of the forwarding

PUT api/v1/workflow/acknowledge/{nodeid}

Implementation of the workflow nodes where a user gets notified when something happens, it does not require any action from the user but the user receives a message after a specific action is performed. This is used when the node is from type Window/form

Body Parameters:

  • "message": "This is an example message for acknowledge" (Optional)

Returns:

  • Confirmation of the acknowledge

PUT api/v1/workflow/setuserchoice/{nodeid}

This is similar to approve and reject but it supports columns with Type List and String.

Body Parameters:

  • "value":"Value to be set in the corresponding column" (Mandatory)
  • "message": "This is an example message for userschoice" (Optional)

Returns:

  • Confirmation of the acknowledge

SysConfig Keys

IDEMPIERE_REST_EXPOSE_JWK

Y/N - defaults to N

For integration with krakend (see Proxy iDempiere-Rest Through KrakenD) it is necessary to expose the signing key in the endpoint api/v1/auth/jwk

Setting this to N (the default) the endpoint is not exposed, changing to Y it will expose the endpoint.

REST_COLUMNNAME_TOLOWERCASE

Y/N - defaults to N

When set to Y the columnnames are translated to be named as json properties. The first letter is changed to lowercase when the columnname doesn't contain the underline character ( _ )

When set to N (the default) the columnname is unchanged

REST_MAX_RECORDS_SIZE

Numeric, default 100

This is a System SysConfig defining the maximum number of records returned in a get operation.

After changing this SysConfig key a server restart is required.

Note that $top cannot be higher than this configured value.

Also, REST_MAX_RECORDS_SIZE=0 means unlimited, and in this case the $top takes precedence when defined.

REST_USE_SYSCONFIG_SECRET

Y/N, defaults to Y (true)

When set to false (N) iDempiere uses a random key generated every time it is rebooted. Note this creates two problems:

  • The tokens generated do not survive a reboot, because the key is changed on every reboot, if there is a valid token, it becomes invalid immediately after the server is rebooted, requiring a new login
  • The token is generated per server, so they are not valid in a multi-server environment, load balancing the REST API in a multi-server environment can make the API useless because the token of one server is not valid in the other server

When set to true (Y), the default, the tokens are generated and signed using a key saved in the SysConfig REST_TOKEN_SECRET, so they are shared between servers and survive a reboot.

REST_TOKEN_SECRET

In this SysConfig is preserved a random key generated, in case they key is compromised, or as a security measure, a new key can be generated using the process Expire Server Secret

You don't need to touch this SysConfig, it is managed automatically.

REST_TOKEN_EXPIRE_IN_MINUTES

This SysConfig set the duration of token expiration in minutes. The default is 60 minutes (1 hour). If you need to change the default number, you can add this to System Configurator and set configured value with your value.

REST_REFRESH_TOKEN_EXPIRE_IN_MINUTES

This SysConfig set the duration of refresh token expiration in minutes. The default is 1440 minutes (1 day). If you need to change the default number, you can add this to System Configurator and set configured value with your value.

REST_ALLOW_UPDATE_SECURE_COLUMN

By default REST allows to update columns marked as secure or encrypted. This SysConfig changes this behavior, if you add it and set it to N, then the secure or encrypted columns cannot be updated.

REST_ERROR_ON_NON_EXISTING_COLUMN

By default REST throws errors when a column mentioned in JSON does not exist. This SysConfig changes this behavior, if you add it and set it to N, then the REST will not throw an error.

REST_ERROR_ON_NON_UPDATABLE_COLUMN

By default REST throws errors when a column mentioned in JSON is not updatable. This SysConfig changes this behavior, if you add it and set it to N, then the REST will not update the column but it won't throw an error

REST_EXPAND_SEPARATOR

Character - defaults to ;

Expand supports sub-operators, f.i. select, expand, order by ... By default, these operators are separated using a semicolon ';'. However, this causes a problem because there are API gateways that do not support semicolons as separators, for example, Traefik by default changes semicolons to ampersands or Krakend throws an error when a URL contains a semicolon.

If you want to change the separator character to something different than ';', set it using this System Configurator.

Server to Server Tokens

You can generate server to server tokens in the window Rest Auth Token.

Simply fill the fields:

  • Organization
  • User
  • Role
  • Warehouse
  • Language
  • Expire in Minutes (0 means no expiration)

And when saving the token will be generated for these parameters.

01 Rest Auth Token.png

When a token is generated, the flag Active indicates if it is a valid token. To active/inactive a token you can use the process Inactive/Active Token.

02 Activate Inactivate Token.png

Also, you can change the server secret key, and this inactivate all the issued tokens, using the process Expire Server Secret

03 Expire Server Secret.png

Retrieved from "https://wiki.idempiere.org/w-en/index.php?title=REST_Web_Services&oldid=22298"
Cookies help us deliver our services. By using our services, you agree to our use of cookies.
GNU-Lizenz für freie Dokumentation 1.3 oder höher
Powered by MediaWiki