We have some external code (which serves some web pages) which does some read-only SQL Server queries as well as hits a few API endpoints for cases where Epicor is doing some non-trivial (opaque) internal calculations to produce fields in the Epicor client and we want to fetch those fields.
In the interest of adhering to best practices and making our code future proof (i.e. if Epicor decides to switch from MS-SQL/TSQL to some other database back end at some point) we are looking at how we can implement these directly in Epicor in a way that follows the latest trends in where Epicor is going with things like Kinetic. For instance, in the past there has been some back and forth about whether even using custom C# code in a BPM is really future-proof or if we must stick to using the other widgets that are available (of which it’s often hard to get equivalent functionality). Having to do a lot of work to bring custom functionality to a newer version of Epicor is one thing that limits our ability to stay current as we have limited manpower available, and this was one reason for offloading into something hopefully a bit more independent from the Epicor version, although as Epicor has evolved we find ourselves questioning that.
One thing we have established is that simple SQL queries that generate a web page with a table can be converted into a corresponding BAQ and it should be possible to get it really really close to producing identical results. I don’t say 100% because I know there are some things that BAQs hide from the user dealing with grouping and aggregates, which is one reason why the “query phrase” the BAQ sometimes gives you results that aren’t quite valid SQL. One a BAQ is created it can be hooked up to a dashboard where it can be filtered, sorted, aggregated, etc.
However, in looking at our code we have found that a large portion of the time we are doing something a bit more complicated which doesn’t seem to translate into a simple dashboard. For instance:
running multiple queries to get available values for things like political boundaries (city, state, country) and presenting them in dropdown boxes. then when the user selects one, running a query specific to the political boundary they selected from and displaying the results. (the columns are the same in all three cases)
a query that requires an API call be made for each row that gets returned from the query. (this is being done in the background after the main data loads, as it takes a while to hit the API several hundred times)
pages that call different queries based on the results of the first one or otherwise use the results of the query to determine subsequent interactions with Epicor.
I think these would require something more complicated in Epicor involving customizations with C# code, but I’m not sure if these would be any more future proof than what we are trying to avoid here, namely having to rewrite our SQL queries if Epicor decides to switch the backend database in the future. (Which I’m not sure of the likelihood of this happening either.) What is the current thinking on best practices in a situation like this?
You have
BAQs: Can be used to run any retrieval queries from the Database even VERY complex ones using Windowing Functions and CTE
UBAQS: Same as above, but you can inject Post / Pre Processing Code to Augment your Results and or make updates using the built in Business Objects
Functions: Allow you to bridge the gaps for more complex processes you define inputs and outputs and can execute the logic in between using either widgets or custom C# code these are wonderful for those more complex things that are harder to do with SQL but still want to live in Epicor and upgrade with Epicor.
Method / Data Directives: Allow you to take action based on various events that happen within Epicor
With the above 4 you should be able to accomplish pretty much ANY integration , lookup or customization you’d like
I’ll have to research UBAQs and “Functions” a bit more as I haven’t used either of these, but from what you are saying it sounds like there would be custom C# code. Is Epicor going to continue using C# for internal things like this moving forward or are they moving towards Javascript like the client has been doing? (Also, if they are committed to C# does that imply a commitment to continuing to use MS-SQL perhaps rendering this whole debate moot?)
No one can tell the future but considering their relationship with MSFT I very much doubt the backend changing from C# to anything else or the DB going from MSSQL to anything else is very low.
That being said a lot of us survived the move from Progress and ABL to C# and MSSQL with honestly relatively low turbulence. Was it a lot of work sure but… nothing is forever.
You mean rewrite their whole app which they ported from Progress to Entity only a few years ago? Unlikely in the near-to-mid-term. If they go that route, I’d imagine they’d be dumping dotnet for God only knows what trendy platform is en vogue at the time. I wouldn’t assume any code would port cleanly.
Epicor backend is a straightforward (at least as straightforward as an ERP can be) implementation of the Microsoft Entity Framework stack. So, yeah, MSSQL and C# for the foreseeable future.
Javascript is a front-end language. It should stay in the client. Sure, there are platforms that use JS on the backend, but I’m not Satan’s hype man so I’ll say no more.
There can be, but all those options allow you to use built in widgets if you are opposed. It is impossible to 100% safeguard for the future. The best you can do is play within the sandbox you are given and stick to the rules it won’t always be guaranteed but it is way better than the alternative.
Yeah I don’t know any ERP system that changes their DB backend just for fun. Epicor did it once because Progress was so old and crusty there weren’t even any programmers left.
But they chose MSSQL because it is state of the art and standard across the board I suspect that is never going away. But as long as you stick to the Business Objects and their tooling whatever Epicor does on the back end shouldn’t affect you much.
