I have a customer that uses the Kerridge ERP solution based on their own proprietary k-Isam database engine. Whilst they offer ODBC connectivity (read only) they charge £1,000 per ODBC licence and it is licensed by machine using a hardware calculated licence code.
The customer is a timber merchant who have a mill for cutting, edge banding etc - the module within the Kerridge system for works orders is pretty weak sauce and doesn’t offer them any scheduling capability or visibility.
My proposed solution is to create a database (PostgreSQL) residing on a Linux server and then have a console application, running as a service, that on a frequent basis picks up some csv files exported on a schedule from the ERP system containing data such as customers, works order data and have that data inserted into the database by the Xojo console application.
The users (5) would access the new solution with connection to the PostgreSQL via a standard windows application I am developing.
Does this sound viable - can a console app access databases and the filesystem to collect the csv files and process?
Thanks Kevin - as a newbie to Xojo does it have any csv handling statements or is it simply a case of reading the file in and parsing into the relevant parts?
[quote=496863:@Kevin Gale]You have to read it and parse it yourself.
If you search the forum you should find posts regarding modules that people have already written. We rolled our own so cannot vouch for any of them.
FYI, if you haven’t already done so - check out the MBS & Einhuger plugins or the Omega Bundle that is currently on offer until the end of the month.[/quote]
Yes I’ve purchased the OmegaBundle this week - thanks for help.
I would check to see if the ERP has a REST API for external data access. Even if the only method is ODBC, it’s probably worth the license fee.
Either way, I’m not sure that I would create a solution in which the desktop application attempts to keep it’s own copy of the data. This is counter-intuitive to the centralized data module. What happens if a work order is altered after your CSV file is imported into your console app? Wouldn’t it be better to request the data directly when you need it? Or, be able to check back to see if the work order has been altered?
I built a system like this. It lives on a Linux server and extracts data from the client’s ERP system every 5 minutes. The data is then used to create a web application that allows users to schedule deliveries, manage a fleet of trucks, assign drivers, etc…
If I had to do it again, I would do two things:
I would take the job of extracting data much more seriously. It wouldn’t just be a simple extraction… it would have to be a complete synchronization… even if just ‘one-way’… so that changes made in the ERP would appear in my system as well… not just new work orders. I might even go so far as to abandon any attempts to sync data and merely create one-to-one relationships with objects in my own system and then use the API/ODBC connector to fetch fresh data on demand.
In addition to writing a server in the cloud, I would write a desktop application as the method of presenting the data to the user. I didn’t do that and I’m regretting it due to the limitations of web browsers. (Try printing a weather-proof skid-label from a web application. Then a report. Then a label. Then a work order ticket.)
[quote=496889:@Kristin Green]I would check to see if the ERP has a REST API for external data access. Even if the only method is ODBC, it’s probably worth the license fee.
Either way, I’m not sure that I would create a solution in which the desktop application attempts to keep it’s own copy of the data. This is counter-intuitive to the centralized data module. What happens if a work order is altered after your CSV file is imported into your console app? Wouldn’t it be better to request the data directly when you need it? Or, be able to check back to see if the work order has been altered?
[/quote]
Hi Kristin,
Thanks for the response - unfortunately they don’t offer any form of REST API in the platform my customer is on - in a more recent version (which my customer is unlikely to ever move to due to the costs involved) they have moved away from their proprietary DB and to the Oracle platform and they have made available a REST based API.
I hear what you are saying re the syncronisation of data, and if I were looking to pull down large amounts of information I would tend to agree. However I will be syncing minimal data in the first instance - customer table and basic works order header info. I’m only talking 10 fields in total.
They currently manage all this on a spreadsheet (all manually rekeyed) and then have a dry wipe board for the production schedule.
Thanks for your advice, stage 2 of the project may well require the purchase of a licence for the ODBC but I can’t purchase this at the desktop level as that would need 5 licences for minimal data transfer.
youwch !
Would writing a “front end API” yourself that talks to their DB be an option ?
That way it can be live data but only using a single connection from that app to the db ?
I have seen that before. A million dollars worth of artwork shipped around the country and tracked on a whiteboard in the back room. Their operation now purrs like a kitten. I don’t know what the break-even point looks like for your client, but at least look at buying a single license and creating an intermediate application that mediates between your software and the ERP system, like Norman suggested.
[quote=496926:@Norman Palardy]youwch !
Would writing a “front end API” yourself that talks to their DB be an option ?
That way it can be live data but only using a single connection from that app to the db ?[/quote]
Using ODBC on the console app you mean and the desktop clients talk to the console API ? This was my thinking for stage 2 (which may not happen dependent on other work loads etc).