Hello Team,
Could you please help me with the following questions?
1) Is it possible to integrate data from Sage 100 via web API?
2) Does Sage 100 have a cloud version? or it is just an On-Premise version?
Hello Team,
Could you please help me with the following questions?
1) Is it possible to integrate data from Sage 100 via web API?
2) Does Sage 100 have a cloud version? or it is just an On-Premise version?
There are a few options that you have with the first question you have. Most are not a true API but ways that you can get and write data to Sage 100 from other sources.
Kevin is right on. If you only need to read data, ODBC is the way to go. BOI requires a lot of work and understanding of the system to read through a table and deal with the business classes. Security…
If all you want to do is read data (not write anything back into Sage 100), use ODBC. BOI has a high learning curve, but ODBC queries are pretty standard (as long as you know where the data lives).
There are a few options that you have with the first question you have. Most are not a true API but ways that you can get and write data to Sage 100 from other sources.
For your second question, Sage 100 can be hosted in the cloud but it is not a cloud based solution.
Hey T-Man, thanks for such an extensive description!
I am looking for ways to integrate our application with Sage100 cloud and it looks like the BOI approach might be useful.
We want to read clients' Account Receivable data, and they won't be paying for any licenses, and any 3rd party clients are also not an option. Would you agree that it might suit better for such a purpose?
I'd appreciate if you could point me to the reference materials you mention.
Cheers
ScriptBasic's approach to securing connection info is use the ODBC Connect() function with a name as its argument. The associated connection info is stored in the ScriptBasic configuration file which is compiled to binary format.
Kevin is right on. If you only need to read data, ODBC is the way to go. BOI requires a lot of work and understanding of the system to read through a table and deal with the business classes. Security in Sage 100 handles security for ODBC, if is turned on. Kevin is referring to a method that uses ODBC to create a linked table in SQL, of the data in Sage. This makes it so that SQL is your interface to the data. This can be very handy, depending on your queries, as the ProvideX ODBC driver doesn't handle some types of joins like right outer joins and such. It can also speed the query when joining complex joins. He is suggesting this as a way to deal with security and managing the queries through SQL and depending on your use, it may be the better way to go.
If you are doing a true integration where you are both reading from and writing back, this will not be what you will use as ODBC is READ ONLY. If the customer is on Premium, they are already on SQL so the read will already be there and you can, but wouldn't suggest it, write back to the SQL tables. The reason I would not suggest it is you are bypassing the business logic. That is what the business class is doing and when you bypass it you can cause a world of hurt if you are not careful with what you are doing. It is better to use the BOI and it would make it more consistent if they change from Standard, Advanced or Premium versions.
If you don't want 3rd party and you feel BOI may be too complicated then the power apps would be an option if you are connected through Microsoft. it has more limitations and not much for help/documentation about it. But it is evolving.
It really depends on what you are integrating and how you expect it to flow but 3rd party might speed up the ability to integrate and strengthen the integration as the 3rd party can handle issues with Sage and you get get or put data where you want it.
so to get more Accounts Receivable data than just Customer and Sales orders available with e-Business Web Services, it looks like I should use ODBC. could you point me to how it should be installed on client’s side and what does client should do for it? should the client open access to their DB? how is the 3rd party app is then interfaced?
Perhaps the least complicated strategy to explain is set up MS SQL Server on the Sage server with a Linked Server to the each Sage company code you need to query. (There are a few posts here on Sage City about how to set up a Linked Server).
When done, SQL queries work for Sage data, directly from MS SQL Server. I'd create a database with SQL Views pointed at Sage data to make it easier to manage, but that is just a personal preference.
How to access and secure queries to SQL Server should be well documented elsewhere.
Deal with that as it comes up, table by table and query by query. At it's worst, I've used SQL Merge statements to maintain mirror tables in SQL, and if you use SQL Views to start with, making that kind of change later would be be like a black-box change.
I know you mentioned already that SData's development is discontinued, but I also saw people saying it is pretty easy for reading from Sage. would Accounts Receivable data be available with its integration?
I wonder about the API alternatives, like the ones here, but for 100cloud nothing is available there yet.
I know you mentioned already that SData's development is discontinued, but I also saw people saying it is pretty easy for reading from Sage. would Accounts Receivable data be available with its integration?
I wonder about the API alternatives, like the ones here, but for 100cloud nothing is available there yet.
I worked with SData on one project and it was a hassle getting things configure. On top of the overhead to get it configured, the version Sage implemented at the time was SData 1.0 or something which is an older spec and can't filter on date fields. As far as I know Sage never implemented the later versions of SData so if you search the web, you might find documentation on newer versions but and new implementations in the later versions likely won't work.
In all honesty, if you have access to the server where Sage 100 is installed and you only need read only access, as others have said, ODBC is the easiest way to go.
If you need to write back and you have access to the server where Sage 100 is installed, then you could use the BOI.
If you need remote access like a true web API, then you are probably better off going with an existing solution, such as one that T-Man included in his post.
thanks, David.
so the true web API method would be the 3rd party solutions then?
I'm checking the OData method as well: is it possible to read data from Sage100 through PowerApps (thus requiring Azure license) connection only or it would require to build own connector otherwise for such purposes?
I had mentioned that Sage has a connector for Power Apps.
Sage 100 Power Apps Connector
- Need to be connected through Microsoft eco system
- Limited function but is actively being developed
- New and not much documentation but more is expected to come as more adopt it
Depending on what you need to do, it may be available with the connector. You can use ODBC where you may not have a connector if you just need to read the data. Then use the connector to write back.
*Community Hub is the new name for Sage City