I have a BAQ that can create a CSV file in BAQ Export Process to use in an external program. I would like to update that CSV file each time a new Customer Shipment is completed. Can I trigger the BAQ Export process in a BPM? Or is there another approach to having a file that always has current data?
You can do either.
Iām gonna let the others pipe in so you can hash out whatās the best path for you.
Would you mind expanding on what exactly you are doing so we can give you more relevant advice?
Sure. A very simple integration with UPS Worldship to get shipment information out of Epicor. Currently I have a SQL View of the Shipment data and an ODBC connection from UPS Worldship to that SQL View. Works great. Users in UPS initiate a Keyed Import, type in a PackList number, and all the shipment info populates into UPS.
The problem is that I am transitioning from On-Prem to Cloud, so I canāt use that SQL View in future.
My first attempt was to use REST within an Excel sheet to run a BAQ, and get UPS to import from that Excel sheet. But, UPS Worldship wouldnāt auto-update the REST query when opening the sheet.
So, that got me onto the CSV file that is updated when a new PackList is created.
If you want to go āall inā on a cloud solution, hereās a suggestion:
Upon some trigger (click a UPS button on a layer/form, Directive when Packlist printed, whatever you choose), post the shipping information (JSON string) to an Azure Function. The function does one thing and one thing only, it writes the information to Queue storage. A local .NET program (probably running as a service at the UPS computer) monitors the queue. When a record appears, it reads it from the queue, writes it to a local ODBC compatible database. If successful, deletes it from the queue. (Example Code) UPS Worldship connects to that database using ODBC, similar to the way your view works today.
Azure costs? Less than $1 USD/month, even if you did 10K shipments.
BTW, back in my Vantage 8 days, this is how I did my UPS Worldship integration, except I, yes even I, had code running in a client customization (before I knew better) that wrote to a local SQL Server databases when the Shipped button was clicked and Worldship used ODBC to get the shipping information.
1 Like
Mark, your āall-inā process is way over my capabilities (but thanks for thinking I might understand it all).
I was hoping to just invoke a BO method for the BAQ Export Process in a BPM.
A trace of the BAQ Export Process shows this:
<tracePacket>
<businessObject>Ice.Proxy.Proc.DynamicQueryExportImpl</businessObject>
<methodName>SubmitToAgent</methodName>
<appServerUri>net.tcp://server/ERP102700Test/</appServerUri>
<returnType>System.Void</returnType>
<localTime>10/11/2023 10:22:57:2035375 AM</localTime>
<threadID>1</threadID>
<correlationId>7c51b876-4f70-41b0-bebb-8173953c2462</correlationId>
But, I can not find that business object in the BO Widget that Iām used to using in the Erp.BO.CustShip where I am creating a new BPM. Is there some other trick to including that DynamicQueryExportImpl BO?
You would run RunDirect instead of SubmitToAgent.
As for the cloud solution, it is far easier than you might imagine. If you can do a POST from REST, youāre half there. The other part is probably less than 50 lines of code. The Azure Function is only a few lines too. Get your Coding Spice on.
When you get the BAQ Export Process to work, where will the file go? That process is running at the server. It can only go to the EpicorData folder on the server. Youāll need some @klincecum Magic to download it to your UPS computer.
You could run a PowerShell script locally passing in the Shipment ID and have it run the BAQ from the UPS computer and return the file to you. Just another thought. You wonāt be able to launch it from the browser however for obvious security reason.
That sounds pretty simple.
Just not automatic.
Iām looking to do something similar with a BPM triggering a BAQ Export Process.
@Mark_Wonsil, do you know if the RunDirect Method you mention is available in BPMs? Iām not seeing it in BPM BO Method search anywhereā¦
- ICE>BO>DynamicQueryExport isnāt listed
- ICE>BO>DynamicQuery is listed but not seeing anything there
- Also checked ICE>BO>ProcessSet , ProcessTask , SysAgent, SysTask, SysMonitor, SysMonitorTasksā¦
Or is RunDirect only available on the UI side? IE instead of being able to run the BAQ export via BPM, it needs to be done with customization code?
Thanks!
To be clear, Iām looking hereā¦
I found this in the References->Add Assemblies:
But not sure how to invoke.
I believe they are methods for objects that go to the System Agent to run. Instead of SubmitToAgent, one would use RunDirect.
Whatās the business problem you have, and how are you planning to solve it?
Thanks for reply. Weāve got a subset of our parts list we want to export when that parts list changes. This thread implies that it is doable with a BPM, but Iām not seeing how.
Export immediately? Where would this file get consumed?
Yes ideally immediately. Another external program reads it for parts to choose from.
Iām also looking at having that external program recoded to access database directly, but it would be great if export was possible via BPM.
Sometimes I would run into the problem of exporting too many times, e.g., if I triggered on each Update, and there were three or four changes, then I would get three of four files.
While it might be a PITA, having the external program download the file might be cleaner in the long run.
Thanks for thoughts Mark. Itās a valid concern but I know how to address. So you donāt have advice on how to invoke the export with a BPM?
@klincecum, you mentioned it is possible with BPM? Any guidance you can offer?
Again thanks much fellasā¦ I know yall are volunteering your time.
Yeah, let me take a look.
Done, works, gimme a minute to post.
You could do a standard Data Directive on ShipHead:
(ReadyToInvoice is the āShippedā checkbox on Customer Shipment Entry)
In the custom code widget, you can call the Export with:
CallService<Ice.Contracts.DynamicQueryExportSvcContract>( svc => {
var ds = svc.GetNewParameters();
var row = ds.DynQueryExpParam[0];
row.QueryID = "BAQ_NAME";
row.ExportFilename = "Baq_Export_File.csv"; // "My_BAQ.xml";
row.ExportFormat = "CSV"; // "XML";
row.OutputLabels = true;
row.TaskNote = "Automated BAQ Export";
svc.SubmitToAgent( ds, "", 0, 0, "SystemAgent" );
} );
1 Like