Determine Correct JSON Fields for REST API based on Endpoint and Screen names (Customers & Sales Orders) - Acumatica

Question

My project is to import Customers and Sales orders. I take a CSV file from an external program, and am to import them -- field by field -- into Sales Orders and Customers.

My challenge is determining the correct field names to get the REST API to work correctly.

I have the following fields:

I am looking to include the three fields: Description, Gift Message, and Public Comment. I inspect the element, which gives me nothing useful that I can tell. (i.e. none of the values map to the field name that I would use in JSON.)

When I go to the Endpoint for Customers, Description already exists:

And therefore, this JSON Record succeeds in an ADD or UPDATE:

{
    "OrderNbr"  : {"value": "SO003525"},
    "Description"  : {"value": "This is the Description"},
}

I extend the default customer (version 18.200.001) and I find and add the "Gift Description" and the "Public Comments" -- and they are listed as part of the "Order Summary" -- which is exactly where Description was.

However, this JSON data does add anything to Gift or Public:

{
    "OrderNbr"  : {"value": "SO003525"},
    "Description"  : {"value": "This is the Description"},
    "PublicComment"  : {"value": "This is the Public Comment"},
    "GiftMessage": { "value": "This is the Gift Message"},
}

In talking to the (in-house) developer who set up the screen, he told me that the "Gift Message" and "Public Comments" fields were added on. But they are not showing as "User Defined" fields in the Endpoint.

Questions:

1) How can I know what the JSON structure should look like? Is there some sort of a mapping document, or a printout that shows JSON-to-Endpoint data? What I mean is that sometimes I need to use a sub-object to reference data in JSON, for example, if I want to update "Main Contact" information, I do it like this:

{
 "CustomerClass":{"value":"INDIVIDUAL"},
 "CustomerID":{"value":"78577"},
 "CustomerName":{"value":"TEST CUSTOMER "},
 "MainContact":
   {
     "CompanyName":{"value":"TEST CUSTOMER COMPANY"},  
     "DisplayName":{"value": "TEST CUSTOMER DISPLAY"}, 
   }
}

How can I know to do that? (I know only because I saw it in the data that got returned when I added a record... Which brings up a second question:

2) Is there a way for retrieving an entire data record in its JSON format. Sort of like a : "GET - SELECT ALL - EXPAND ALL" so I can see all of the data in a record in a properly set up JSON format?

3) Any idea why the gift Message and Public Comments are showing as "Order Summary" but aren't updating like Description is?

4) Why are some things "un-expandable?" For example, I cannot expand Payments in Sales Order. I get an error:

Is there a reason why you would not use an Import Scenario for this since the source of the data is a .CSV file? — Jerry Kurtz
@JerryKurtz: Interesting question. I haven't seen a way of calling an Import Scenario via the API. Can you point me to that? — MarkJoel60
Sorry, not what I meant. I guess I was just wondering why you would use the REST API for this task instead of just using a simple Import Scenario? — Jerry Kurtz
Ahhh... darn, I thought you knew a way of doing that! To your question, 2 reasons: First off, there is manipulation being done to the CSV file values. Secondly, we want this done on a scheduled interval, which means we don't want a user to have to initialize it. — MarkJoel60

Hugues Beauséjour Hugues Beauséjour · Accepted Answer · 2020-03-09T15:36:05

1) How can I know what the JSON structure should look like

The easiest way is to execute a GET request. The field names are equivalent to the DisplayName property stripped of whitespaces. So if on screen the field is labelled Order Type the field name will be OrderType in the request.

To make a GET request, the URL format is /Entity/Keys.

For example entity SalesOrder has two keys: OrderType and OrderNbr.

To get entity SalesOrder of type SO and with the number SO005051 the URL would end with: /SalesOrder/SO/SO005051.

2) Is there a way for retrieving an entire data record in its JSON format. Sort of like a : "GET - SELECT ALL - EXPAND ALL" so I can see all of the data in a record in a properly set up JSON format?

If you want all the details, you need to specify them all in the expand clause of the GET query: $expand=Contacts,MainContact,BillingContact

The name of the details array can be looked up in the Web Service Endpoint screen SM207060. They are the tree view elements with a empty array [] suffix.

3) Any idea why the gift Message and Public Comments are showing as "Order Summary" but aren't updating like Description is?

Those are custom fields. You need to extend the endpoint to add those custom fields otherwise they will be ignored. If there's no issue with the custom endpoint maybe the field aren't bound to database.

But they are not showing as "User Defined" fields in the Endpoint.

If by User Defined you mean those fields were added with a wizard (without programming) then this is likely the reason why they don't work in the web service. You would need to add custom field with a DAC extension which requires programming instead of the User Defined field.

Determine Correct JSON Fields for REST API based on Endpoint and Screen names (Customers & Sales Orders) - Acumatica

1 Answers