Oh REST Guru,
Does anyone know if it’s possible to use a BAQ to pull data into Excel based on a column of values?
Meaning;
If I have an excel document with a column of part numbers (BOM) and I have BAQ the pulls last cost for a part. Can I call the BAQ through REST in excel, filter the BAQ by the value in that column and return the results?
Yes it is possible with the VB Macros functionality, or a COM plugin. Nothing native though. However an easier / alternative solution could be
Use the oData feed to download a BAQ of all your Costs for the part then use the lookup function in Excel to find the right one…
Same result a bit less magic but in the end it should be fairly seamless
So basically using the standard oData BAQ Feed run your Part Last Cost BAQ that should return ALL the last costs for all the parts.
Then in your original worksheet, use the LOOKUP (Excel) function to find the corresponding Part and grab its costs from the oData sheet.
Thanks @josecgomez, I assumed it was possible and your alternative could be much simpler to implement, meaning it won’t be on my long list of to-do projects as long!
I expect, it will be done in 20 mins, chop chop lol
You can do this using Power Query with Excel. Depending on version of Excel how you access Power Query may vary. You can basically then pass parameters to your BAQ via REST using the columns in your spreadsheet. I can post a few examples of this tomorrow. Power Query is a free add-in for 2010 and 2013 and bundled with 2016 including O365.
The example below has a table in rows 1 & 2 which contains a Parameter Name and Value
In Row 6 is a REST Query that uses the value in cell B2 (under value) to feed to a BAQ with a Parameter of PartNum. This then returns those values to the sheet. You can simply change the value again and click refresh to retrieve different part data. Now you could have a list of Part Numbers under value instead of just one and use some query merge functionality to provide what you are looking for.
Now I really need to figure out the security cert issue with REST & excel, I guess. By any chance does there happen to be an easy, concise guide to get this setup, for someone with no background in security certs?
Sure let’s encrypt is your friend but you’ll have to have a way to open up your epicor box briefly to the wan
Just search for “letsencrypt IIS” should find plenty of resources.
I’ve been trying to get an SSL cert with LetsEncrypt on our test server, and it’s not working for me, getting an “Name does not end in a public suffix” error.
This might be a stupid question, but does the domain need to be a registered domain?
Meaning, let’s say our internal AD domain is ABC.local and our host company domain is ABCcompany.com. Can I still use LetsEncrypt to create an SSL cert for your test Epicor server named test-erp10.ABC.local?
If not, do I have any other options?
Yes it needs to be a valid and registered TLD/Domain for it to work with LetsEncrypt
Well I guess I’ll stop fighting to get that working…
You can create a self-signed then go visit the API Help or something via Chrome.
You can then click on the Not Secure Area on the URL Bar (the red) and Click on Certificate… then it brings up a Dialog you can Export it to a File
Then on Win7 atleast If I recall you go to Internet Options and you can there import that Cert in the Trusted Root Tab, when it comes to mass scale you can prob use GPO to push that out to your local users, i am sure there is a powershell or something out there.
Thats how you can get it working internally, locally and Excel will be happy. Atleast on your dev machine.
When I look at our AD we have a few Root ones like that and alot of Intermediate Cert Authorities with Internal Server Names, so perhaps thats what internally is done… Thats a little bit out of my Windows Admin Role Scope 
I created a BAQ that pull from the Cost table and BOM table.
Then use rest data only and not have to combine using formulas.
But if you want to combine,sumifs & vlookup should be enough to combine data.
Hey @danbedwards, did you ever get around to making that example? I downloaded power query for 2013 and I can get the fill list, but I can’t seem to figure out how you do what you are showing in your screen shot.
Dan, Did you end up creating an example of this. Im having difficulty using a parameter to update the query in power query and would appreciate any help you might be able to provide
i wonder why not passing your parameter as a filter in your REST call, so the called BAQ will return only relevant data
This is exactly what im attempting to do, When i look at the parameter that is drilled down it shows exactly what i would expect (ie pPeriod = 10)
If i use the parameters
?FiscalPeriod=9&FiscalYear=2022
if i change the =9 to =pPeriod (this is my parameter name) then i get the following error returned
DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request.
OData Version: 4, Error: The remote server returned an error: (500) Internal Server Error. (Internal Server Error)
OData Version: 3, Error: The remote server returned an error: (400) Bad Request.
Details:
DataSourceKind=OData
DataSourcePath=https://auseastdtapp00.epicorsaas.com/saas****/api/v1/BaqSvc/HDTA-Rebate
