Excel and REST - getting started

Epicor ERP 10
1

I’m going to start a new topic here instead of adding on to the one I just did ( Excel REST Odata retrieve could not establish trust relationship - #11 by jimship )

What I am trying to do is get started using Excel to access some Epicor data, perhaps to be able to analyze it with pivot tables or something. I don’t really have a specific application but I wanted to examine the feasibility of this approach. We saw a cool demo at Insights of someone doing this and I thought I’d give it a try.

So for background info, we are On Prem, and I am using Excel 365. I have full security permissions in Epicor. I started out in a browser window and was able to see the business objects and got that far.

image
image955×118 8.74 KB

The trouble I am having is getting Excel to connect to the OData feed. I am using:

image

This is the ‘Link for File’:

https://OURSERVER/epicorerp/api/v2/odata/MFA/Erp.BO.CustomerSvc/Customers

And I’m getting this error:

image

I have looked at a bunch of help topics and am confused as to what to try next. Could it be that it is not a secure site? Something with certs (which I know nothing about but our server guy does)? Other potential fixes are changes to the App.config, file? Also, what is the correct version v1 or v2? That seems to be another option.

Thanks for any help to get started.

2

Rest Call Trust Error - ERP 10 - Epicor User Help Forum (epiusers.help)

We just redid the self cert on the server. Just make sure you set it to 10 years.

If you self deploy you need to also install the cert locally. We did so via a GPO.

something like this.

We used v1. once all the pieces are there this is EZPZ!!.

https://e102.embedtek.local/ERP10PROD/api/v1/BaqSvc/ETK_InstalledOnAdmin/

1 Like
3

Hello,

I was working on rest API a couple days ago to add data to xl Connect 7 and I had the same error. I changed how I opened the ODATA file (thru query versus direct from the ribbon… it shows up differently) and changed API to V1 from V2. On the link I removed the company code at the end e.g. (EPI01). It then worked just fine.

image
image420×716 20.3 KB

image
image722×234 4.33 KB

versus

image

image

I submitted a case with epiccare and they provided this knowledge base video.
https://epiccare.epicor.com/epiccare?id=epiccare_kb_article&sys_id=81d39a80db5cb4906a0493b7f4961928

1 Like
4

OK, let me figure out this Cert stuff with our network guy and go from there. If I have more questions after that, I’ll post them. Thanks for the help!

1 Like
5

I believe excel will only connect to rest with a valid SSL certificate in place. It won’t use untrusted connections.

1 Like
6

I’ve defaulted to using the “From Web” option instead of the odata feed because it doesn’t flag you for a SSL Certificate.

My question though, if you are on prem, why bother using REST? I figured the prime benefit of on-prem was being able to connect right to the database.

7

I believe the from web also needs SSL cert though?

8

Here I describe how certificate is trusted:

Check the cert you use, what is missing in your case.