Epicor REST Novel

<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? 

The reality of technology today is everyone wants to unlock their data and get it where they want it. If you can only put functionality on a green screen in front of people today, a large majority wouldn’t be happy. They need their data in a browser remote anywhere, a phone, in Excel or some data analytics tool Epicor has never heard of. Also, you don’t want to pay a gazillion dollars to us for the client tool. In fact, most don’t want a tool at all. They want to point Excel at some Epicor server and get data without installing anything. If some JavaScript guru in your company wants to throw together some cool form for a browser mashup for their group or a little phone app, they don’t want a rigid path to freeing the data from an Epicor Server. Also, this whole Internet of Things and sensors all over the place don’t always have a full version of Windows running on them needed to push data into Epicor. All these use cases come back to needing a simple means to access / submit data in Enterprise Systems that SOAP promised long ago by bridging the gaps between competing technologies. That where the new REST services come in… 

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: 

https://YourServer/YourShare/api/v1/Erp.BO.SalesOrderSvc/SalesOrders 

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.  

https://YourServer/YourShare /api /v1 /Erp.BO.SalesOrderSvc/ SalesOrders 

https://YourServer/YourShare 

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.  

/api 

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. 

/v1 

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. 

/Erp.BO.SalesOrderSvc 

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 

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: 

<…>/FordSvc/F150 

Ford provides an F150. SalesOrderSvc provides Sales Orders.  

Now let’s get back to that goofy return value. What’s with that??  

The format is called json (pronounced Jason). It’s extremely terse. No pretty xml element names to give you a hint about what it is. All that knowledge is kept outside the payload and you have to ask for help instead of having it forced on you when asking for data. That makes this format very small and perfect for going across the internet and slow cell phone networks. It’s also the native format in JavaScript to light up on a web page in a browser or on a phone. Those technologies no longer have to parse through a large slug of data returned to show on the screen. Got json? Slap to screen, done. 

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 

https://YourServer/YourShare /api /v1 /Erp.BO.SalesOrderSvc/ SalesOrders 

Remember I said api had all the toys here? What about help? 

https://YourServer/YourShare/api/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: 

https://localhost/erp10/api/v1/Erp.Bo.SalesOrderSvc/SalesOrders/?$format=atom 

 

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 – 

https://localhost/erp10/api/v1/Erp.BO.SalesOrderSvc/List 

 

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(Epic06,5000) 

‘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: 

Equals  eq 
Not Equals  ne 
Greater Than  gt 
Greater Than or Equal  ge 
Less Than  lt 
Less Than or Equal  le 
And  and 
Or  or 
Not  Not 

 

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? 

 

https://localhost/erp10/api/v1/Erp.BO.SalesOrderSvc/SalesOrders?%24select=Company%2COrderNum%2COpenOrder%2CCustNum&%24filter=Company%20eq%20%27Epic06%27 

 

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 

Underneath the covers the normal Update method is followed with validations, BPM intercepts, etc. There are code examples in the online help so based on your comfort zone learn what URL helpers play nice in your language and platform. They all have them. C#, VB, JavaScript, Python, PHP, Windows, Linux, it doesn’t matter. They always have an HTTP stack so can access ERP 10 directly now. 

So take it for a tour, please ask questions. I am curious about what we did NOT think about.

Epicor REST Intro

I am reposting my REST overview novel  over here from a few locations it has been published so I can update this as things evolve. This originated on a flight home after a long week of planning in San Diego to home in Portland, Oregon. It was one of those classic visit with the boss and stakeholders over vNext where *that should be simple* glint in the eye of a PM gets decomposed into meaningful efforts. Straining and taking a ton of concentration.

Don’t get me wrong, I love the folks I work with. I wouldn’t still be there after 17 years if I didn’t. The effort to decompose glint is VPs eye into a product is something I adore doing and have done a ton of in my career. There is a conflict of sorts when doing this and that’s welcome. ‘Conflict’ or really just different points of view is healthy. When done professionally and with a common goal it turns out wonderful result. But EXHAUSTING…. Combined with that being locked into a windowless office from 8 am until 6 or 7 in the evening does not give you down time to lounge on a San Diego beach 😉

In the middle of this the vLast effort is picking up steam and being adopted internally, in beta and just going public. Lots of in depth conversations and education and tons of confusion. After several dozen pings from every side I started to respond in disconnected Outlook, said the heck with that and cut and pasted into the stream of conscience shown.

Now that I have gone through an Insights presenting and seeing how different audiences react, I’ll probably be doing some edits. The embedded help in the product is quite good. The doc team I work with does a great Geek to English translation. I’ll probably go into some more depth now that Mobile CRM, the new Homepage and EPM are all public things. More is coming as REST is the way to communicate on the web today so continues to be leveraged as the wonderful platform it is.

 

 

A Uri is not a Url. A URI is not a URL

I spent a good portion of the last year around REST and Urls. https://MyServer/Epicor/Customer and so on. https://MyServer or https://MYSERVER or https://myserver who cares. The DNS handling of the internet translates over to a number IPv4 or IPv6 and life is good.

Lately I spent a lot of time with Uris though. Identifiers. Not real addresses to browse. Oh they can be real. And in many use cases they were. And slap a little culture ignore case on them when comparing and all is good still.

Now here comes Azure Active Directory. Lots of Uris. Lots of magic token handling for you. But I deal with a LOT of legacy customers where OAuth and all the beauty of the modern web authentication approaches are not automatic. All those libraries that assume you have the most modern hosts and do magic for you are not available when a customer is still playing in a ws-* world. So a lot of manually managing Audiences and ResourceIds and the like. All LOOK like Urls.

But alas you end up banging your head against the wall when you get

IDX10214: Audience validation failed.
Audiences: ‘https://coderpages.com/EpicorErp-Server’.
Did not match:  validationParameters.
ValidAudience: ‘https://coderpages.com/EpicorERP-Server’ 
Took me a half hour to backtrack to looking at the error and realize it was a Uri dummy!

Development

I love doing DIY – ok maybe love is not the correct term. I’ve had an addiction to it since a kid and done it forever. It’s probably just a part of why I wanted to be an Engineer since an early age. Everyone around me was struggling with what they wanted to do. I never had those issues. The only concern I had was do I want to make the microprocessors or use them in software. Fast forward a few decades and I have a two house overhauls and a bunch of other things in my past.

Some dirty – I had to replace all the sewer lines in my first house but paid for it to be done the next time.

Some beneficial – Replacing all the carpeting in the house with bamboo hardwood. My allergies loved me for that

Some detrimental to my marriage – Replacing the roof on our first house and just get the plywood down after taking off two layers of asphalt over wood shingle. Ready to tack down the moisture barrier on a lazy Sunday morning in the middle of a two week vacation. Your boss drives up to your house to ask you to do an emergency trip to a critical customer. For a week. In Honolulu. That cost a lot of wife points.

All of these experiences play back through my thought processes in the latest project for the grandkids

The stakeholders were clearly defined. The Wife – not grandkids. Getting this correct is critical.

The requirements were reasonably laid out. Summer with Grandma? Need a playground to wear their 5 and 7 year old butts off. Specifics? hmmm that’s was a little scary.

The deadlines were a little fuzzy. This is a long term product delivery that will be used for years (Youngest grandchild is 6 months). So it needed immediate functionality and could be enhanced in further iterations. There was an ability to deliver incremental customer value. I originally made the frame of the structure with the swing set but the overlook, the rock wall, the slide all were delivered in future sprints. Being able to break down the customer requirements into incremental releases pleased the customers, the budget and the staff (My body from not having to kill myself for a week straight on it).

At this point there is ability for future enhancements (A sandbox in the base with Anti-Cat features). There are companion products available – I am reducing the third acre of grass to mow via garden beds and a few dozen yards of bark dust around a dozen fruit trees. A side benefit is a levelling of the yard. You may note the base of the play structure is elevated 6 inches from the ‘current’ base of the ground. I planned ahead to the build it to the desired level. It may be a little goofy today but later this summer when ~15 yards of bark dust level the area it will fit right in.

What the heck does all this mean???

Don’t be overwhelmed by the process du jour in software dev. Engineering principles go back to Roman, Egyptian, Chinese times with greater efforts then any flappy bird cloud enabled fart app on your internet connected refrigerator. Reach outside your norms and see how things are built that are not software. The principles of other efforts can help you focus on the struggles in your organization and projects.

 

 

 

Epicor in the Wild

Always dangerous to be doing errands with The Wife and just *stopping in for a quick item at the craft store*. I am blessed to have a reformed Sys Admin who evolved into a Graphics Designer and retired into Grandma Daycare. But those artistic tendencies are always there. After investigating all the nooks and crannies of the local craft store, I see this at checkout

So Craft Warehouse is running Epicor Eagle? I don’t get involved in the Eagle portion of the company too often though that is evolving. They do some nice work in the Merchant Processing arena – allowing an Epicor Customer like Craft Warehouse to handle credit cards at a much cheaper rate than other options. With my Daughter and Wife both working in that industry a decade ago its amazing the abusive prices many stores get stuck with when handling cards. An interesting business and cool to see some of our tech in the wild helping.

Also dangerous now that The Wife wants to support my Company by doing more shopping at Craft Warehouse. 😉

My Own Voice

I spend a lot of time on internal social sites. Too much in email in general. I have been blessed to work in a great company that puts me in contact with a ton of customers doing wonderfully crazy things with projects I have helped on. I have tried all the social platforms out there and participate on most of them. But I have not had that place to get my own voice without the shell of other owners so here I am again. I’ve had the domain forever and this is probably my 4th generation of blogging starts. Here’s to a fresh start…