So we are having a mobile app developed for us to make packing lists since we make large things that are packed in multiple trucks (for one order line). E-10 only handles combining order lines to packing lists, not splitting up pieces of them.
Aaaanways… So the application will be reading the database to get the necessary information from within the job tables, customer tables and a few other things. Whatever this external application needs to do what it needs to do. Currently, we are operating under the idea that it would then have it’s own separate SQL database that it would write any of it’s information to in order to do what it needs to do.
My question is, how bad of an idea would it be to create user defined tables within epicor that the application could write to instead of having it’s own database. We need tables somewhere anyways, and these wouldn’t have any interaction with existing tables or built in screens. However, it would give me the ability to run queries and create dashboards based on that information directly within Epicor using their handy dandy wizards since I can’t custom program my way out or a paper bag. As with any data collection tool, there will always be some question that needs to be answered what wasn’t originally thought of, and if we don’t have anyone here that can write SQL queries in the backend, or display that in convenient places for end users to use that data, we have to pay someone to develop those things for us. (or we just fly around blind because that’s what we’ve always done)
OK, rant over. But what’s your opinion on using the existing database to run another application? Are there lots of bad things that are likely to happen that means I shouldn’t do this? Or is this kind of typical in the programming world?
You can use the existing UDTables for that without issue. However do not add your own custom tables to the Epicor DB that is a sure way to make your upgrades a nightmare.
If you are going to write to these UDTables I also recommend that whomever is writting your mobile app uses the REST API or the WebServices to write to ANY table in the Epicor DB
ok, that sounds like it might work. Is the REST API and industry standard thing? (seems like it is since when I google it, it shows up) so when I talk to my developers, they should now what I’m talking about right?
And does adding UDFields if necessary to the UDtables cause issues? It looks like most of the tables are pretty well filled out with what we will need, but you never know when you need another of a certain type.
You can add as many fields as you’d like.
And yes REST or RESTFUL API’s are industry standard though each company kinda implements them however they want. However Epicor has chosen to use the OData standard which is awesome and very well known.
All good discussions. I respect the known issues with extra tables during upgrade - known as in looking at options and stay tuned. One aspect that is also a concern is that spanning databases usually entails adding DTC which can be a pain in the performance category if not done well.
I’d take the UDT approach for now so we can easily support you and stay tuned.
So my developers say that using a REST API will add a significant cost to the project. Not that they can’t do it, but they don’t have specific epicor experience, so I think that are not quite sure yet. I’m not savvy enough with this to really help them too much. I copied and pasted the REST overview novel to show them what you had said Bart. Is there any other documentation that is available to help them know best practices to writing into the E-10 database? Specifically into the UDtables, since they will only be reading native tables and only writing to the UDtables.
Is this something I really should get an epicor consultant in to give them a hand in doing the reading writing thing? Or is this something that they should be able to handle without specific epicor knowledge? (They make business mobile applications for companies so they have a pretty good handle on software development, so it’s not just a couple of guys trying to figure stuff out. You know like I do…)
Also, we can just do this totally outside of epicor, worst case. It would just be so nice to be able to keep everything together.
REST API or not without Epicor experience how exactly did they plan on integrating it…? The REST API would make their lives so much easier they could write the app cross platform and have it use the native BOs you really can’t get any closer to perfect than that. If they went the route of a separate database was the intent to then use service connect to marry that to Epicor?
I’ve used Webservices, WCF, REST API, and (@aidacra & @Bart_Elia earmuffs) Direct SQL Updates over the life of my integrations and by far REST has been the least painful of them all and my upgrade process just got a whole lot easier.
I would STRONGLY recommend you find an Epicor VAR that can in-house or out-sources the mobile development portion. Interfaces are one thing dealing with back-end BO’s is completely different animal, you have to know the database in and out, or at least how to use traces to learn it.
So they aren’t going to be doing anything that is interacting with database other that the udtables we would be using. They won’t be writing to job tables, or part tables or customer tables or any other tables other than udtables. The only integration would happen in tying tables together in simple baqs and dashboards that I would be making. We aren’t completing any Epicor functions with this project. Epicor has zero structure anywhere to handle shipping parts of a sales order line, which is what we need, so we have to create something from scratch. The whole system can live outside of epicor altogether, and in fact we may still do that if this proves to be too difficult, there is simply some convenience and future flexibility by having it live within the UD tables.
As far as using traces, what trace would you follow to write to a UDtable that isn’t linked to anything in E-10? There isn’t anything that writes to them now so what would you trace?
If this not going to interact with Epicor at all (EVER) then I strongly recommend you keep it in its own database and then they can write directly to it without issue.
If you insist in keeping it within Epicor then you really should use their business logic and the REST API or WCF Endpoints to prevent you future headaches.
I just wanted to say thanks for the help in working through this. I found the screens and did some walk through using the tools guide to see how the connection to the external database works. It looks like it should work fine. I’ll have to wait for my IT guy to get back from vacation to see if I can get a sample database to connect to in order to make sure everything works the way I would expect it to. The only thing that I have available to me right now is an access database, and I don’t think that I can connect to that.
Am I correct in my assumption that an external query can only look at the information in the one database that it’s pointed to? If you needed to relate info from the external to an internal table (which I won’t in this case) is that what you would use an executive query for?
The external query must be associated with an external data source which of course contains a connection string to a specific database. You could however get a little clever and do publish and subscribe in a dashboard between queries some external and some internal. Your best bet if you want to mix external databases with internals is to make all that happen in the view itself that the external query is looking at.
That makes sense. I have to pass a lot of information (to view) to the outside database to get what it needs to function anyways. They only thing that it would limit is if someone down the road wants some of that information tied to another piece of information that was not originally needed. (like tying an invoice number to a shipment on it or something like that), I may not have the view set up and they would have to manually do a cross reference, which won’t be the end of the world. I can use the publish subscribe like you said too.
Side note, with an external BAQ, will any of the open with (right click) functions work? We will have job numbers, and part numbers, and information like that in it, but I would imagine with the external that those shortcuts won’t work. Is that a correct assumption? Not a deal breaker, but just a nice to use function for looking things up in the system.
You might want to look at Bezl.io. They are using the Epicor BO’s and allow you to build Angular apps that they host. Depending on the complexity it might be a lot easier and faster to just provide the mobile UI and use all the Epicor BO’s. Epicor was supposed to have a Mobile Framework out as well but I haven’t heard anything about it for quite a while.
Epicor doesn’t have schema structured to handle what we need to do, hence the external database. Since we aren’t doing anything native to epicor, I don’t think their BO’s would be that helpful. If I was trying to make something that was doing a an epicor process, then I think we would be looking that way. It might be useful for other projects in the future though. I’ll have to keep that site bookmarked.