"Best" way to export data on a schedule

I know there’s many ways to skin a cat, but what are few ways to accomplish the following. Note there are kind of two distinct tasks.

Let me explain what I did in V8, and then I’ll open it up to for ways to do it better in E10.

  1. Need to query the APInvHed table and related tables. That data is then formatted for a text file, and the text file saved to the server.

I did this via a BPM for DynamicQuery.ExportByID.

The Actions were 100% 4GL/ABL code to create a text file based on data from APInvHed and related tables.

  1. Created a BAQ (“MC-TRIGGER”) that didn’t actually do anything.
  2. Ran the BAQ Export Process, with the BAQ ID “MC-TRIGGER” and other fields set for params to pass to the BPM. And scheduled it to run daily at 8:00 PM

So my questions are:

Better way to generate a text file directly to the server, with no user interaction.?

Better way to have that happen automatically?

Are you opposed to using custom C# code in a bpm to do this? If not I’ll
share some code that will get you 90% of the way there

Not to beat the REST drum again but…

You have the BAQ you want to run
Make sure of the url you want and get that going in a browser.
Note how one of the suggested ‘try it outs’ in the help page is CURL. Command line execution of URLs. (https://curl.haxx.se/).
Get that command line dumping data how you want / where you want.
Have Windows Task Scheduler call your curl command line whenever you want.

thinking outside of the box

Sounds neat Bart. Do you have a link to the help page you are referring to?

Should all be in this post

I do need to play with some cURL examples. I honestly had not considered that use case for cUrl - scheduled ‘reports’ - really just data dumps. Now I need to play. It’s always good to add another tool to the tool belt when considering solutions.

2 Likes

hmmm curl has some interesting issues with SSL. We deploy REST ONLY as HttpS. We try to treat security seriously and encourage that of others. That’s why you don’t see anything encrypted on the wire as an option anywhere in E10.

Something to play with in the background when not playing with my new vNextNext efforts :wink:

Understanding the learning curve and all, I’m looking at you PowerShell:

Mark W.

Another interesting alternative. argh… too many toys to play with on a weekend / evening. I need to go paint a room :wink:

1 Like

Chris - Not opposed to C# at all. Any help would be greatly appreciated.

Ok bear with me - I am sure I may get slapped for sloppy code. Here is some code I use to Link records from part and partmtl tables. You would create a BPM triggered on a data field and then add a C# custom code object using the code below. You could also customize a form, add a button, and place this code on the click event.

foreach (var mat_iterator in 
(from mat_Row in Db.PartMtl.With(LockHint.NoLock) //search thru every record in PartMtl table
                                     where (ttPartTranRow.Company == mat_Row.Company  //where these conditions are true
	                                     && ttPartTranRow.PartNum == mat_Row.PartNum
										 &&	mat_Row.UOMCode == "LB")
                                     select mat_Row  //get the current record
))
    { //mat_iter - note the code in these brackets occur for each qualifying record
				var matRow = mat_iterator;  //we are making the previous iterator (data row) accessible for further use
				matpn = matRow.MtlPartNum; // i want this data - will use below to 'link' tables
				
				foreach (var partmat_iterator in  // we are going to search thru Part table now
				(from partmat_Row in Db.Part.With(LockHint.NoLock)
                                     where (ttPartTranRow.Company == partmat_Row.Company
	                                     && matpn == partmat_Row.PartNum)  //notice here we are using the previous iterator field to link
                                     select partmat_Row  //these records will only be ones that meet that criteria
				))
				{  
					matdes = partmat_iterator.PartDescription; // here I  getting more data I like
                    // this area is where you would presumable grab all your data 
//Like Part.Field1, Part.Field2, PartMtl.Field1, PartMtl.Field2, etc
				}
    }

//writing text files can be accomplished in different ways - heres an easy and flexible way
string SUMTEXT = "sum text";
string[] lines = {SUMTEXT, "more text", "last text" }; //as many as you want - could reference you previously captured strings - all of these datas will be written to file (on new line)
string myfilename = @"\\myserver\c$\myfilename.txt";
System.IO.File.WriteAllLines(myfilename, lines);

If you wanted to get multiple records - I think I’d store all of those fields per record in one string, maybe comma delimited.
string rec1 = “record1.field1, record1.field2, record1.field3”;
string rec2 = “record2.field1,record2.field2, record2.field4”;

And feed those to the lines[] variable

Thanks for all that. I’ll build and test it using a button in a custom form.

Now about executing it on a schedule.
In V8 I put the code in a DynamicQuery.ExportByID BPM. Then scheduled a BAQ Export Process.


Conditions:

the pcFilenameOverride argument contains the "APExport" value
and the pcQueryID argument is equal to the "MC-Trigger" value

But in E10, there is no DynamicQuery.ExportByID

What should I use to schedule it.

FWIW - the BAQ (“MC-Trigger”) that the BAQ Export Process ran, does nothing. just used to trigger the BPM.

I imagine E10 has a way to schedule things - but since I don’t know about it, I’ll give an alternate.

I am by no means broke into the ‘best practices’ aspect of E10 but I can tell you how I’d do it. If your schedule isn’t too picky and you can piggy back it on something else already scheduled, that could be easy. For example, let’s assume you had MRP running daily, you can create a DATA DIRECTIVE on the SysTask table and use a condition to look for only MRP events (and only starts, not completions - see screenshot)

I have nothing else to piggy back it on to. That’s why I created and scheduled the BAQ Export Process of a BAQ that does nothing.

I am going to ask for some backup - @josecgomez.trigemco @rbucek

Can you teach us how to schedule events proper?

Short answer is you can’t…
The DMT Folks (formerly DotNetIT now Official Epicor) have a custom process you can buy which I assume they still sell? @Edge? That allows you to schedule arbitrary code to execute on a schedule within Epicor.

http://www.dotnetit.co.uk/solutions/solution/user-process-scheduler

That is the best and most “official” way to do this. Other than buying this product you could write your own custom code and schedule it as an external process (using Windows Task Manager). The export BAQ Process as far as I’ve seen doesn’t trigger BPMs I’ve tried putting BPMs on GetList, ExecuteByID, Execute and none of them will fire.

You could hang it of a System Monitor Task like you suggested and that would work, but again is kind of a hack (though it would work)

1 Like

Jose,

When you say write you own code and schedule it thru windows - are we talking about writing a custom standalone app to access Epicor, or something else? (i.e, create a connection, authenticate, query)

Yes,
I have written a utility that will basically run a BAQ that you give it as a config parameter @jgiese.wci did this too I believe.

So you schedule the BAQ to run and then we wrote a BPM that executes the custom code with that BAQ results. So basically all the scheduled task does is called BAQ.Execute() and the BPM takes care of the custom functionality (In my case it puts customers on credit hold that are past due on their invoices)

Then the next time they need to schedule something all you have to do is write the BAQ and the BPM side and schedule the external process to run the BAQ at whatever time.

1 Like

Nice. Is the bpm just a method directive on baq.execute or something else?

Correct, though if you are clever you can put all the code in the BAQ itself, make it an “Updatable” BAQ which then triggers a GETLIST event and an Update event and you get write your code in there. To keep it segregated from just standard BAQ.Execute method

So basically the BAQ carries the code with it

1 Like

Thanks, but let’s just assume I’m not clever :smile: That is pretty tricksy, me’sah like it, and apparently it’s made me start channeling Gollum (rubbing my hands together)