<In its entirety>
My apologies if you are confused about the REST services so let me try to fix that by giving you a fly over. The new REST ‘wrappers’ have all the features of the existing web services. GetByID, GetRows, GetList, Update and all those custom methods on every service are all there with full abilities.
The difference is a different technology than the WCF endpoints where people are used to going into Visual Studio and clicking on ‘Add Web Reference’ and sucking in SOAP. That’s all still there, has full love and still worked on – evidence is all the new bindings people see lighting up all over for the various HTTP bindings including full F5 support. Nothing removed. Instead this is a recognition that one size does not fit all.
So why the heck did you add these?
REST at its core is simply a URL. You call www.bing.com and you get back an html page and some pretty pictures in a format to show in a browser. You call http://www.bing.com/search?q=Epicor and you get back an html page about Epicor. You just did a ‘SQL Query’ of sorts but using a URL. We have done the same thing for all the ERP 10 services.
You want to get records from the SalesOrder service? Once REST is enabled in Admin Console and IIS, pop this into your browser:
You get a login prompt for your Epicor credentials (e.g. – manager / manager).
Ok that and voila you get back a ‘GetRows’ of a bunch of data in some goofy format no human wants to read. (Ok, I like reading it but I’m a geek not a normal human being…)
Wait, what’s this goofy data, where is my where clause, I thought going at the database with a query by passes calculations and security! how the heck am I supposed to find anything…
First, the security aspect. Even though this is similar to querying a table (or tables) in the database, this is actually querying the public services we have been using for years. If you have column filtering, a calculation in a BPM or anything else, that all appears in the REST endpoints – even your UD fields.
Next, let’s pull apart that URL to start trying to understand how this is put together.
The root is the same as you are used to pointing at. It’s wherever your IIS server is serving up the server or wherever the SaaS Ops team told you to select during install.
Wait, what’s that? Usually there is ERP for the product, ICE for the system?
Well that’s the new home for all the REST toys we are talking about.
This is our new versioning mechanism. We recognize we will have to change this technology someday. That’s technology. I still miss my VT100 against a VAX but it’s hard to fit that in my Android phone. Things change so we assume at some point we will have to as well. When we do improve things and version, we need to preserve the current signatures so integrations you do from your IoT Refrigerator into ERP keep running.
Ok, something that finally looks somewhat familiar. Under the ERP / BO folder there is a file named SalesOrder.svc, right?
Yup gold star. We use the same organization in REST as you have been doing in v10 for some time.
SalesOrders. I already said SalesOrders so why am I repeating myself??
Well you drilled down into the SalesOrder Service but now you need to ask for what you want once you got there. OrderDel? OrderRel? In this case we asked for SalesOrders. These are all ‘Resources’ of the Service.
Another example might be:
Ford provides an F150. SalesOrderSvc provides Sales Orders.
Now let’s get back to that goofy return value. What’s with that??
But I like my xml for interop? It’s self-describing!
Where was the where clause? I need to search on the customer!
Where am I going to find anything about any of this? Do I have to read some huge dev doc??
Let’s sneak up on all these by first not ignoring all the doc effort put into the standard docs. We have some excellent info on this in the online help so please browse over to your help site and drill down into System Management -> Working with System Management for the REST Implementation section. This has all the setup and background that you would need and some good examples.
But if you are like me… who reads the manual?
Instead let’s look back at the URL again
Remember I said api had all the toys here? What about help?
The REST services are their own documentation. Want to see what services are available? Click on the Service List and search for the one you are interested in. (Yea, we have something like 1600 services so added a search box there for you, try it out).
Also if you have spent a week building up that perfect shape of data in a BAQ, have no fear, it’s there. Start typing its name and you’ll see it appear to select it.
Once you find your service or BAQ of interest then you move onto the help page for that resource. Let’s look at SalesOrderSvc for a reference-
Whoa, swagger? GET / POST / DELETE / PATCH?
Let’s get some background. Swagger is a formatting standard to doc REST services. It’s one of the gazillion bouncing around these days with all the cool kids doing internet enabling refrigerators and IoT devices monitoring their dogs eating habits. Luckily it is actually really nicely done and helpful as heck. So we decided to leverage it. Microsoft Azure did also so we feel pretty good about using the same approach.
Get / Post / Delete / Patch is the http version of the Epicor core methods of GetRows, GetByID, Update, Delete, etc. These are standard verbs for HTTP and allows the Epicor REST services to play nice with other internet technologies. It makes getting the data in and out of ERP 10 easier with a variety of modern internet tools. This is not to say we don’t have the Core methods anymore though! If you look at the top of the page you will see ‘Custom Methods’. This flips the docs over to look at the entire list of service methods you are used to seeing in a client customization or in Visual Studio Add web reference:
Let’s go back to the oData list and take a look at the $metadata resource. This is a standard ‘method’ on all services that list all the resources available for the service and the shape of the data going in and out of the service:
This is kind of a combination of a database ERD and an API reference all in one. It lets you know what is needed or available and a start to providing much richer documentation.
Now let’s drill into that original SalesOrder resource:
There are a couple of sections here. The upper section is the Model and Model Schema. The Model Schema is the shape of the data and some sample data as shown above.
The Model itself has a ton of information on the actual table and its columns:
And this Resource has a few parameters it can receive to start doing a ‘where clause’ of sorts.
These parameters and a bunch more are part of an open standard called oData (www.oData.org). A bunch of tools speak this language already – Excel, PowerBI, Tableau, LINQPad, probably a few dozens more I have no clue about. They send the ‘SQL Queries’ / ‘Where clauses’ in this format. You can leverage the tools and point them at the REST service or go learn the language at oData org, it’s YOUR CHOICE.
Let’s try a couple of simple examples.
First, that json format is great for browsers but rough on integrations. To get the xml you are used to seeing you need to simply ask for it:
We are starting to dig into the syntax of the ‘query string’. We start with a ‘?’ to tell the server I have a question on that resource. Then you tell it to give it to you in ‘atom’ format. This is a web standard for xml (https://en.wikipedia.org/wiki/Atom_(standard)). If anyone remembers RSS newsfeeds, this is an enhanced version and plays great with data analytics tools like Excel and PowerBI.
What this means is as a consumer of the REST services, you can ask the server how you want the data shaped. You don’t have to do that in your client. We are trying to allow for flexibility in what you want and not force you to do the shaping of the data in the client.
Next, let’s think about how a normal flow in using a form. You go to the form –
|Open Sales Order|
|Search and get a list of Sales Orders||Sales Order service – GetList is called|
|Select one (or more)||Sales Order service – GetById (or GetRows) is called|
|Make a change and save||Sales Order service – Update is called|
Obviously grossly simplified but this is an intro 😉
In the REST world, we are using standard HTTP verbs. GET is the verb for any query. Just like we had to ask for xml, you need to ask for one or more and in what shape explicitly. GetList is similar to a view in SQL server. No child tables, just an extremely lightweight and fast way to query against a flattened version of a resource – Sales Order in this case. You see that in the Search forms normally. It’s not perfectly in alignment with the internet religion but does provide a helper for those used to the List / Full pattern of querying in ERP10 / E9 / Vantage 8 / E4SE / Clientele. In REST we provided a simplified version of this through the ‘List’ resource –
Note the first three records for ‘query’. Let’s note the first one and do a ‘GetByID’. SalesOrder as a resource has two primary keys to identify itself. A Company and an OrderNumber. The help doc highlights this for us and gives a hint on the syntax for the ‘SQL where clause’:
Note how the Company and OrderNum fields are prompting you to enter the primary keys. Let’s grab that first record retrieved in the List and ‘try it out’:
There is the record in the Response Body. Note the ‘Request URL’ builds the ‘query’ for you. Let’s try that in a browser:
We are starting to see the syntax in querying that ERP10 supports. It’s an open standard named oData (www.oData.org). Let’s pull it apart a moment.
‘SalesOrder’ is the resource as previously stated. If you used OrderRels it would have queried against that table instead.
(Epic06,5000) is the where clause. Note above the help doc said Company and OrderNum were required. oData uses that requirement to infer the column values being passed and which is which based upon the order they were displayed in the metadata above. If you want to be verbose you could state /SalesOrders(company=Epic06,OrderNum=5000). Both are supported, again the philosophy of doing what is asked of the client or integration.
So we can query in a fast lightweight fashion like a GetList. We can do a GetByID. What about retrieving multiple rows like GetRows? Time to drop into more oData syntax ad try out the filter command:
This builds out the URL:
Or in a browser address as ->
https://localhost/erp10/api/v1/Erp.BO.SalesOrderSvc/SalesOrders?filter=Company eq ‘Epic06’
What is ‘eq’?
What’s with the goofy % things in the URL?
We are looking at a lot of columns in these tables making it hard to understand!
First let’s pull apart the query – ‘eq’?
Since this is an HTTP URL you have to follow all internet standards in making an address. ‘>’, ‘<’, ‘=’, ‘!=’ etc all mess up a normal URL. Instead of these oData has replacements:
|Greater Than or Equal||ge|
|Less Than or Equal||le|
Spend some time in the oData examples if you need to learn the new syntax.
Next is the padding in the URL. This is URL Encoding (https://en.wikipedia.org/wiki/Percent-encoding). It’s a mechanism to not mess up URL parsing by using a ‘%20’ instead of a space for example. Most browsers automagically change spaces to %20 for you for example. It’s a simple translatioin.
Lastly, there are some extremely verbose tables in many Enterprise systems. One of my favorite parts of oData is being able to specify just the columns I care about.
Maybe we only want to see the open order status and the customer number?
Wow, just the data you want almost like a BAQ?
Pretty much. Without having to persist a new query to the server or ask IT for a new service you can tell the server through REST what data and how to shape it.
Not too many people are going to want to live their days in a browser to query the database though so let’s look at the world’s most popular querying client – Excel. You’ll need to refer to the docs for the appropriate version of when you need to download the oData addin from Microsoft but in the 2016 version it’s already baked in:
Let’s point at the SalesOrder service again with our ERP 10 user credentials:
And select the table you want to show
And you have a live link to the data behind the services.
The really nice thing about this under the covers is that Excel is speaking oData. Sorting, limiting columns, filtering of a column in the table all send across queries in oData syntax. This means you don’t dump the entire data base across the network and into Excel.
PowerBI, Tableau and anything speaking oData does the same thing.
Great for a readonly view of the world but what about Updating ERP 10?
All the URLs shown so far are simply reading data out of the system. An HTTP GET. GETS’s can be executed as a browser URL address and the response is shown in the browser client. Updating data uses the other HTTP verbs which need a tool or code to execute
POST – Does an Insert
PATCH – Does an Update
DELETE – Does a Delete
So take it for a tour, please ask questions. I am curious about what we did NOT think about.