Most of us are familiar with the BAQ Export process, which allows selecting a .CSV export or an .XML export for a BAQ. The BAQ Export can also be scripted via DMT.
Well, our web developer is telling us that XML can work for him but he would really prefer JSON format. Is there an easy way to export BAQ results to JSON format or does that require a bunch of custom code? I am not a developer. It would be nice if JSON was an option in BAQ Export Process but it is not.
Before you suggest that we give him REST API access, this is a security decision that has already been made to NOT expose our ERP system to external (non-LAN) systems. So while we can use REST API locally, it doesn’t help the website developer in this case. We are currently shipping them .XML BAQ exports via SFTP but would just like to do the same with JSON instead of XML.
You can do this. It’s a SQL function that takes the whole query and packs it into a single cell. At some point, I think that’s going to be disallowed in Epicor due to security issues. But that this time, it should work. You just have to find where you can get the FOR XML PATH() command to show up at the end.
Could run middleware internally to grab the JSON from the API and then ship that off via SFTP. Wouldn’t have to expose the API to the web if the middleware ran internally. I’m sure there are other ways. Just one way you could possibly make it work.
@chaddb does this middleware exist? That was my first thought, but seems like this would require a lot of custom code and I am not a developer, so am looking for a (hopefully) easier way to accomplish the same. Maybe its not possible except via how you suggest.
It does. We are using Jitterbit for it. It’s rather expensive but we were already using Jitterbit for our Salesforce sync. Epicor is a Jitterbit partner so you can work with your CAM on pricing. I’m sure there are other options out there. I just know you can do this with Jitterbit. You could, as you said, write your own but that’s complicated and would take a developer most likely.
Bump for this answer. If you’re already going to be orchestrating with DMT and Powershell, you can invoke the BAQ to get your results, store it as a file, and then transform to JSON all with the DMT API and Powershell. Example:
#Extract Data From From BAQ -> CSV File -> Load in with DMT
$DMTPath = "C:\Epicor\Clients\EpicorTEST\DMT.exe"
$User = "epicor"
$Pass = "epicor"
Write-Output "Extracting Data via BAQ $(get-date)"
#Extract Data from TEST
$Source = "C:\Temp\TEST_Exports\BAQResults.csv"
$completeLog = $source + ".CompleteLog.txt"
$BAQ = "YourBAQID"
Start-Process -Wait -FilePath $DMTPath -ArgumentList "-User $User -Pass $Pass -Export -BAQ $BAQ -Target $Source -NoUI -ConfigValue=Epicor10TEST"
Write-Output "Exporting Data $(get-date) " $Source
Then, use Marks code to transform that CSV (in this case) to JSON
