Working with Odata like sql

Epicor ERP 10
1

When I use the Odata syntax to get a list of BAQ’s in chrome, I get a well-formatted return:
https://epicorsi/ERP10/api/v1/BaqSvc/

When I use this test feed from cData for a simple Odata feed, i get a well formatted return
https://services.odata.org/V3/OData/OData.svc/ (No authentication needed)

However, when I use the cdata example in Excel, it works beautifully, gives you a list of the available odata ‘tables’, you can pick one or more and work with it.

When I use the epicor url, I get an error of ‘an item with the same key has been added’. The only way i can pull in BAQ data is via the direct BAQ syntax of **https://epicorsi/ERP10/api/v1/BaqSvc/{BAQName}?

However, I need to pull multiple baq’s in, but without being able to get to the ‘catalog’ view (like the cdata example), it’s somewhat useless. I’m using 10.2.200

Am I doing something wrong or is there a bug? @Olga

1 Like
2

What URL are you entering in Excel that gives an error?

1 Like
3

https://epicorsi/ERP10/api/v1/BaqSvc/

4

Probably your list of BAQs contains duplicates, which is not expected. This must be some data anomaly.

5

Olga, Dupe between company specific and global baqs in his data?

6

yes, and only one should be shown with the name.

7

Olga this comes from the Epicor SE image (Sales Engineer) (I’m a partner). Is there a way to filter on company in the URL syntax to avoid the duplicates? Is the duplicates possible to create or is this bugged data due to the long history of the SE Image database/

8

This seems to work for me in Postman:

{host}/api/v1/BaqSvc?Company=‘101’

9

yes, i think you should report it as a bug in data on that image.

10

There should be some name duplication and postman does not care about it

11

Gotcha.

12

So I’m still struggling on this.

I’m trying to use a client’s Epicor SaaS Odata feed. I can see it in the browser fine.
If I use excel’s LEGACY Odata connection, it shows me the list of baq’s like a list of tables and i can select 1 or more and build Excel reports from that.

However, if I use Excel’s newer (power query) based “Get Data” functions, i get the following:
Details: “OData: Request failed (404): The remote server returned an error: (404) Not Found.”

I get the same problem in Power BI (Both use the same engine) and Power BI doesn’t support the Legacy odata driver that excel does (only after you expose it in the newest versions buried in options)

There appears to be something wrong with the way epicor (10.2.200 and 10.2.300) expose the list of BAQ’s or something that is not compatible with the way Microsoft expects to read odata. Microsoft is an Odata v4 compatible driver but I assume it’s backward compatible to Epicor’s older V3 implementation.

Has anyone been able to use Excel to see a list of baq’s and pull them into sheets?

13

@Olga Any thoughts? Is there any workaround?

14

Hi,I don’t know about pulling the list in Excel sheets. But you can see the list of BAQs by using the legacy wizards from the Data tab.

15

I don’t think there is a built-in browser like that Mark. You could do a REST call that returns all BAQs (probably a long list, so you might want to filter it) and put that into a Combo Box.

The trickier part is once selected, I would do another REST call on that BAQ to get any required parameters and create labels and fields on the form.

Finally, I would call the BAQ through REST supplying the parameters and returning the results into a tab OR create an OData connection with the URL you made up.

So, not out of the box, but it is possible to write.

Mark W.

16

don’t assume backward compatibility. OData v3 and v4 are not compatible. Every Excel version works differently.
you can look into IIS log to see what exact query causes 404. Probably it will give more info about what went wrong.