Stephen Smith's Blog

All things Sage 300…

Posts Tagged ‘sage 300 erp

Synchronizing Data with Sage 300

with 3 comments

Introduction

Often there is a need to synchronize data from an external source with Sage 300 data. For instance, with Sage CRM we want to keep the list of customers synchronized between the two systems. This way if you enter a new customer in one, it gets automatically added to the other. Similarly, if you update say an address in one, then it is updated in the other. Along the way there have been various techniques to accomplish this. In this blog post I’ll cover how this has been done in the past and present a new idea on how to do this now.

In the past there have been a number of constraints, such as supporting multiple databases like Pervasive.SQL, SQL Server, IBM DB2 and Oracle. But today Sage 300 only supports SQL Server. Similarly, some suggested approaches would be quite expensive to implement inside Sage 300. Tied closely to synchronization is the desire by some customers for database auditing which we will also touch upon since this is closely related.

Sage CRM Synchronization

The first two-way data synchronization we did was the original Sage 300 to Sage CRM integration. The original integration used sub-classed views to capture changes to the data and to then use the Sage CRM API to make the matching change to Sage CRM. Sage CRM then did something similar and would write the change back to Sage 300 via one of its APIs.

The main problem with this integration technique is that its fairly brittle. You can configure the integration to either fail, warn or ignore errors in the other system. If you select error, then both systems need to be running in order for any one to use either. So if Sage CRM is offline, then so is Sage 300. If you select warn or ignore then the record will be updated in one system and not the other. This will put the databases out of sync and a manual full re-sync will need to be performed.

For the most part this system works pretty well, but isn’t ideal due to the trade off of either requiring both systems always be up, or having to run manual re-syncs every now and then. The integration is now built into the Sage 300 business logic; so sub-classed Views are no longer used.

The Sage Data Cloud

The intent of the Sage data cloud was to synchronize data with the cloud but not to require the on premise accounting system be always online. As a consequence, it couldn’t use the same approach as the original Sage CRM integration. In the meantime, Sage CRM added support for vector clock synchronization via SData. The problem with SData synchronization was that it was too expensive to retrofit that into all the accounting packages that needed to work with the Sage Data Cloud.

The approach the Sage Data Cloud connector took was to keep a table that matched the accounting data in a separate database. This table just had the key and a checksum so it could tell what changed in the database by scanning it and re-computing the checksums and if they didn’t match then the record had been modified and needed synching.

This approach didn’t require manual re-synchs or require both systems be online to work. However, it was expensive to keep scanning the database looking for changes, so they may not be reflected terribly quickly or would add unnecessary load to the database server.

What Does Synchronization Need?

The question is then what does a modern synchronization algorithm like vector clock sync require to operate efficiently? It requires the ability to ask the system what has changed since it last ran. This query has to be efficient and reliable.

You could do a CSQRY call and select based on the audit stamps that are newer than our last clock tick (sync time). However, the audit stamp isn’t and index and this query will be slow on larger tables. Further it doesn’t easily give you inserted or deleted records.

Another suggested approach would be to implement database auditing on the Sage 300 application’s tables. Then you get a database audit feature and if done right, you can use this to query for changed records and then base a synchronization algorithm on it. However, this has never been done since it’s a fairly large job and the ROI was never deemed worthwhile.

Another approach that is specific to SQL Server would be to query the database transaction logs. These will tell you what happened in the database. This has a couple of problems namely the queries on the transaction logs aren’t oriented around since the last sync, and so are either slow or return too much information. Further SQL Server manages these logs fairly aggressively so if your synchronization app was offline for too long, SQL Server would recycle the logs and the data wouldn’t be available anymore. Plus, this would force everyone to manage logs, rather than just have them truncated on checkpoint.

SQL Server 2008 to the Rescue

Fortunately, SQL Sever 2008 added some nice change tracking/auditing functionality that does what we need. And fortunately Sage 300 only supports SQL Server so we can use this functionality. There is a very good article on MSDN about this and how it applies to synchronization here. Basically the SQL Server team recognized that both data synchronization and auditing were important and quite time consuming to add at the application level.

Using this functionality is quite simple, you need to turn on change tracking for the database, then you need to turn on change tracking for each table you want to track changes for.

trackchanges

Then there is a SQL function that you can select from to get the data. For instance, I updated a couple of records in ARCUS and then inserted a new one and the result is shown.

changetable

This is giving me the minimal information, which is all I require for synchronization since I really only need to know which records to synchronize and then can get the full information form the main database table.

If you want to use this to audit all the changes in your database, then there are more options you can set to give you more complete information on what happened.

Summary

If you are writing an application that needs to synchronize data with Sage 300 (either one way or two way), consider using these features of SQL Server since you can add them externally to Sage 300 without affecting the application.

Similarly, if you are writing a database logging/auditing application you might want to look at what Microsoft has been adding to SQL Server starting with version 2008.

 

Written by smist08

April 18, 2016 at 10:09 pm

Passing the Torch

with 16 comments

Introduction

As many people already know, I’ve now retired after 23+ years with Computer Associates/Sage working on the Accpac/Sage 300 product line. I’m now happily living in Gibsons, BC which is a short 40 minute ferry ride from Vancouver.

John Thomas (aka JT)

John Thomas will be taking over for me as the Chief Architect for the Sage 300 product line. He will also be taking over my role as Sage 300’s main blog writer. His blog is on WordPress here: https://jthomas903.wordpress.com/. Follow this blog to keep up to date on Sage 300.

It will also be posted on Sage City here: http://sagecity.na.sage.com/support_communities/sage300_erp/b/sage_300_erp_r_and_d/archive/2016/03/21/sage-300c-transition. He will continue to post articles to the Sage 300 In Development blog area on Sage City as well as on WordPress.

Check out his first blog posting where he introduces himself and you can learn a little about him. Introduce yourself via the comments section. All bloggers always appreciate any suggestions on topics for future articles.

JT and myself by a giant Banyan Tree in Bangalore, India.

JT and myself by a giant Banyan Tree in Bangalore, India.

My Blog

I’ll continue writing my blog, but it probably won’t be on Sage 300 anymore. A lot of my blogs relied on having the support and expertise of the team around me to help out. I really appreciate all the help and support I’ve received over the years from everyone at Sage as well as all the people in the wider Sage 300 community.

Of course I won’t delete my blog, or delete any articles. They will remain on WordPress as long as WordPress keeps hosting them. They will also remain on Sage City where I have always mirrored them. I’ll probably continue to fix typos or make corrections to any errors that I notice or are pointed out to me.

Also feel free to keep asking questions, but beware that I can’t go down the hall to ask an expert on a topic and I can’t consult the source code anymore. But I’ll still do my best to answer, though the answer may be to go ask tech support.

I am very tempted to finish a few projects I never had time for at Sage, like investigating tying Azure Machine Learning (or perhaps Google’s or Amazon’s machine learning) to Sage 300. Or perhaps using Azure Logic App (or Azure App Service) to create workflows around Sage 300. Or perhaps do a POC with Sage 300 and Microsoft’s Power BI. But I think I’ll leave those projects to others for now. I do have a few opinions and bias’s on Accounting Software, Software Development, the Cloud and future trends that I may still blog on in the future.

Chances are what I will be blogging on will be more around my other interests that I’ve been getting back to now that I’m retired. These would include:

  • Photography – including via DLSR and via my Drone.
  • Guitar – now that I have time to practice again.
  • Triathlon – running, swimming and biking.
  • Hiking – there’s great hiking here on BC’s Sunshine Coast.
  • Video Games – I’ve been playing with the Amazon Lumberyard game engine.
  • Artificial Intelligence – I’ve been reading quite a bit about this lately and there are some amazing advances currently in the works. For instance, Google’s AI just beat the world Go champion.
  • Travel – I do plan to do a lot more travelling and suspect I’ll be blogging about it.

The nice thing about being retired is that I can pursue a lot of diverse interests. So who knows where these will lead over the coming months.

Summary

As I move on to the next phase of my life, so will my blog. But I have full confidence in JT and the Sage 300 team to carry the torch forward. I’m eagerly waiting to see JT’s future blog postings and see the various press releases as Sage 300 continues to evolve.

Written by smist08

March 21, 2016 at 9:54 pm

Sage Connect 2016

with 4 comments

Introduction

The Sage Connect 2016 conference has just wrapped up in Sydney, Australia. I was very happy to be able to head over there and give a one-day training class on our new Web UIs SDK, and then give a few sessions in the main conferences. This year the conference combined all the Sage Australia/New Zealand/Pacific Islands products into one show. So there were customers and partners from Sage HandiSoft, Sage MicrOpay, Sage One as well as the usual people from Sage CRM, Sage 300, Sage CRE and Sage X3.

The show was on for two days where the first day was for customers and partners and then the second day was for partners only. As a result, the first day had around 600 people in attendance. There was a networking event for everyone at the end of the first day and then a gala awards dinner for the partners after the second day.

A notable part of the keynote was the kick-off of the Sage Foundation in Australia with a sponsorship of Orange Sky Laundry. Certainly a worthwhile cause that is doing a lot of good work helping Australia’s homeless population.

There was a leadership forum featuring three prominent Australian entrepreneurs discussing their careers and providing advice based on their experience. These were Naomi Simpson of Red Balloon, Brad Smith of Braaap Motorcycles and Steve Vamos of Telstra. I found Brad Smith especially interesting as he created a motorcycle manufacturer from scratch.

The event was held at the conference center at the Australian Technology Park. This was very interesting since it was converted from the Eveleigh Railway Workshops and still contains many exhibits and equipment from that era. It created an interesting contrast of 2016 era high tech to the heavy industry that was high tech around 1900.

Sage 300

The big news for Sage 300 was the continued roll out of our Web UIs. With the Sage 300 2016.1 release just being rolled out this adds the I/C, O/E and P/O screens along with quite a few other screens and quite a few other enhancements. Jaqueline Li, the Product Manager for Sage 300 was also at the show and presented the roadmap for what customers and partners can expect in the next release as well.

Sage is big on promoting the golden triangle of Accounting, Payments and Payroll. In Australia this is represented by Sage 300, Sage Payment Solutions and Sage MicrOPay which all integrate to complete the triangle for the customers. Sage Payment Solutions (SPS) is the same one as in North American and now operates in the USA, Canada and Australia.

Don Thomson one of the original founders of Accpac and the developer of the Access-C compiler was present representing his current venture TaiRox. Here he is being interviewed by Mike Lorge, the Managing Director Sage Business Solutions, on the direction of Sage 300 during one of the keynote sessions.

donthom

Development Partners

Sage 300 has a large community of ISVs that provide specialized vertical Accounting modules, reporting tools, utilities and customized solutions. These solutions have been instrumental in making Sage 300 a successful product and a successful platform for business applications. Without these company’s relentless passionate support, Sage 300 wouldn’t have anywhere near the market share it has today.

There were quite a few exhibiting at the Connect conference as well as providing pre-conference training and conference sessions. Some of the participants were: Altec, Accu-Dart, AutoSimply, BSP Software, Dingosoft, Enabling, Greytrix, HighJump, InfoCentral, Orchid, Pacific Technologies, Symphony, TaiRox and Technisoft.

exhibis

I gave a pre-conference SDK training class on our new Web UIs, so hopefully we will be seeing some Web versions of these products shortly.

Summary

It’s a long flight from Vancouver to Sydney, but at least it’s a direct flight. The time zone difference is 19 hours ahead, so you feel it as 5 hours back which isn’t too bad. Going from Canadian winter to Australian summer is always enjoyable to get some sunshine and feel the warmth. Sydney was hopping with tourist season in full swing, multiple cruise ships docked in the harbor, Chinese new year celebrations in full swing and all sorts of other events going on.

The conference went really well, and was exciting and energizing. Hopefully everyone learned something and became more excited about what we have today and what is coming down the road.

Of course you can’t visit Australia without going to the beach, so here is one last photo, in this case of Bondi Beach. Surf’s up!

bondi

Written by smist08

February 25, 2016 at 2:46 am

Sage 300c Web Services

with 5 comments

Introduction

Hand in hand with true HTML/JavaScript/CSS Web UI’s you also want to access the same logic from other general programs using RESTful Web Services. This gives a general API to access the application which doesn’t require any Sage 300 components be installed on the client computer and doesn’t require the calling application be on the same computer or even at the same location.

ASP.Net MVC Web screens tend to have quite quickly changing interfaces between the Views, Controllers and Models which makes using then same Web Services as the UI a bit problematic, especially as the screens evolve quickly. You want a stable Web Services interface that preserves compatibility from version to version and provides a wider more general interface. At the same time the developer of an ASP.Net MVC program doesn’t want to do a completely different implementation for exposing Web Services.

The way ASP.Net MVC solves this dilemma is by allowing you to add a Web Services stack on top of your existing models (which in our case means fully leveraging the business repositories and Sage 300 Business Logic as well). But it uses a custom controller to handle the Web Services requests. In the Microsoft stack there are several supported standards for Web Services, but the one we used is OData. This means that using our Web Services you can do all the standard OData queries and supports the standard OData meta-data.

With our Sage 300 2016 Product Update 1 we have included a number of Web Services in the product. These are automatically installed if you select the Web UIs option from the main installation. So if the Web UIs are up and running then you can try playing with the Web Services. In this article we’ll show how to get started using these. Over next couple of releases, we’ll be fleshing these out to support all the Business logic as well as services beyond the basic CRUD operations.

Some Examples

If you type:

https://yourservername/Sage300webapi/sdata/-/SAMLTD/GL/Accounts

into the Chrome browser you will be prompted for your Sage 300 login credentials, which you can enter. Note that from this browser prompt the password is case sensitive, so you need to uppercase your normal Sage 300 password (since our regular login screen normally does this).

webserv1

Then after entering the correct data you will get back a JSON object with all the information in your chart of Accounts (including details like optional fields):

webserv2

Working with the Browser directly, although fun, will soon become tedious. Another easier approach is to install the Chrome add-in PostMan which will remember your Web Services so you can adjust and repeat them. You need to set the Basic Authorization header with your Sage 300 login and password. Below we use the shortened URL to get the list of all the available feeds for SAMLTD with the URL:

https://yourservername/Sage300webapi/sdata/-/SAMLTD

using PostMan:

webserv3

And we get the returned JSON object containing the list of Web Services we support. It by company since not all accounting application may be activated in the database.

Queries

You can do standard OData queries to filter the returned data. For instance:

https://yourservername/Sage300webapi/sdata/-/SAMLTD/GL/Accounts?$filter=UnformattedAccount eq ‘1020’

will result in just this one account being returned:

webserv4

The way we implement queries is via adding LINQ support that will convert the LINQ query to a Browse filter for our Sage 300 View. This means we will support any query as long as we can translated it into a Browse filter. If the filter contains a SQL function we don’t support, then you will get back a not supported error for your query. Note that often people writing code for the regular Web UIs just use our C# LINQ support to browse/fetch rather than calling browse/fetch directly since this lets you leverage other advanced features in C# and .Net.

Other Clauses

You can specify a sort order as long as what you requests matches an index in the Sage 300 database:

https://yourservername/Sage300webapi/sdata/-/SAMLTD/GL/Accounts?$orderby=UnformattedAccount desc

webserv5

You can specify to get the top n records or to skip n records via:

https://yourservername/Sage300webapi/sdata/-/SAMLTD/GL/Accounts?$top=2

https://yourservername/Sage300webapi/sdata/-/SAMLTD/GL/Accounts?$skip=2

which is useful to page data.

Meta Data

You can get meta data for all the feeds using the $metadata tag. For instance:

https://yourservername/Sage300webapi/sdata/-/SAMLTD/$metadata

will return the meta data for all the feeds that are relevant for SAMLTD:

webserv6

(Note that this is quite a large JSON object to process).

Updating/Inserting/Deleting

This initial implementation includes sufficient G/L feeds for supporting financial reporting. Hence these G/L feeds are read only at this point. We do support inserting G/L Batches, O/E Orders and A/R Customers. Many of the non-G/L feeds support updating, inserting and deleting. If the entity supports these then you can delete the record by specifying DELETE as the HTTP verb (which is easy in PostMan), similarly insert if via POST and update if via PUT or PATCH.

Generally, the best way to figure out the format of the payload to include with these is to do a GET and then use that payload as a template to build the JSON object with the data you want to update or insert.

Since these Web APIs are built on the Sage 300 Business Logic all the usual validation will take place and you will get back Sage 300 error messages in the response payload if the request fails.

Troubleshooting

Ideally the responses from the server will include error messages to tell you what went wrong, so always check these. If they aren’t helpful, then on your web server check the Web API trace log which is located at:

Sage300InstallDir/Online/WebApi/Logs/trace.log

This will usually have the raw error when something has gone wrong.

If you don’t see anything in either of these places, perhaps check your IIS log to make sure that the request didn’t get rejected for some other reason. Especially remember to include your basic authorization header.

Security

If you expose your Web Services to the general Internet, ensure that you follow all the security measures in this article. You will need to do this if you are integrating with an external cloud service or other client located outside your network. Generally, you want to keep your Web Service communications private, so they can’t be accessed by hackers or spied on by hackers. Using good practices around enforcing HTTPS is crucial here.

Summary

The set of Web Services included in the Sage 300 2016 Product Update 1 are intended to support Financial Reporting on General Ledger as well as basic e-Commerce functionality like accessing Customers and entering Orders. Part of the intent of this release is to let people play with these and provide feedback as we move to complete out the full set of Web Services for our next version.

Written by smist08

February 15, 2016 at 11:26 pm

Accessing Sage 300c’s Business Logic from the Web UIs

with 5 comments

Introduction

In the Sage 300 VB UIs, a user would do something in the UI (press a button or tab out of a field) and then the VB UI would be notified of this and would possibly execute a number of Sage 300 Business Logic (View) calls and based on their results update various other fields and possibly provide user feedback via a message box.

In the Web UIs we want to do similar processing since we want to re-use the tried and true Sage 300 Business Logic, but we have to be careful since now the Web UI is half running as JavaScript in the Browser and half running as .Net assemblies on the server. We have to be careful of the communication between the Browser and the server since there will be quite a bit of latency in each call over the Internet. Generally, we never want one user action to result in more than one call to the server (and ideally most user actions shouldn’t result in any calls to the server).

This blog post talks about where you put your code to access the Sage 300 Business Logic and how a UI interaction in the Browser flows through the system to execute this business logic.

Architecture

In the new Web UI architecture, we access the Sage 300 Business logic from our Business Repository classes. The base classes for these provide a wrapper of the Sage 300 .Net API to actually access the Views, but hiding the details of things like session and database link management. Then above this layer are the usual ASP.Net MVC Models and Controllers.

cna2arch

The Sage 300c Web UI Architecture

Generally, we want to put all this logic in the Business Repository so it can be used by multiple higher level clients including the Web UIs, our new RESTful WebAPI and services which are available for other applications to utilize.

Some of the layering is in place ready for additional functionality like customization. We need provide the common interfaces that can act as the basis for programmatic customization by inserting custom modules into the processing flow via Unity Interception.

Moving VB Code

In VB we often make lots of Business Logic (View) calls all interspersed with lots of interactions with various UI controls. This code has to be separated where the Business Logic (View) calls will go in the Business Repository which runs on the server and then the part that interacts with the controls has to move to the JavaScript code running in the Browser. The Business Repository has to provide the necessary data in a single payload which the mode/controller will transport to the Browser for processing.

The easiest way for the repository to transfer data is to have the model provide extra fields for this communication. This way no extra layers need to be involved, the business repository just populates these fields and the JavaScript layers pull them out of the returned JSON object and use them.

But you only want to add so much to the model, since you don’t want it to be too cumbersome to move around and you might want more focused calls. For these we usually define special calls in the controller and these go through a services layer to execute the code in the repository. The service call only passes the exact data needed (like parameters to a function) and knows what data to expect back.

Example

Adding extra fields to the model is fairly straight forward, so let’s trace through the logic of making a services call. In this example we’ll look at the simple case of checking a customer’s credit limit in A/R Invoice Entry (which is using a stateful business repository). We’ll start up in the JavaScript code and work our way down through the layers to get an idea of who does what.

So let’s start near the top. In the A/R Invoice Entry UI there are various times when the credit limit needs to be looked up. So the JavaScript code in the InvoiceEntryBehaviour.js file has a routine to initiate this process. Note that server calls are asynchronous so the response is handled in a callback function.

    showCreditLimit: function (result) {
        // Open Credit Check pop up window
        if (result) {
            var jsonResult = JSON.parse(result);
            if (jsonResult.ShowCreditCheck) {
                arInvoiceEntryRepository.getCreditCheck(jsonResult.id,
                    sg.utls.kndoUI.getFormattedDate(jsonResult.docDate),
                    sg.utls.kndoUI.getFormattedDate(jsonResult.dueDate),
                    "n" + invoiceEntryUI.CurrencyDecimals, jsonResult.totalPaymentAmountScheduled,
                    jsonResult.prepaymentAmount);
            } else {
                onSuccess.onCreditClose();
            }
        }
        invoiceEntryUI.ModelData.isModelDirty.reset();
    },

This calls a function in the InvoiceEntryRepository.js file to actually make the call to the server:

    getCreditCheck: function (customerNumber, documentDate, dueDate, decimals, invoiceAmount,
        prepaymentAmount) {
        var data = {
            id: customerNumber,
            docDate: documentDate,
            dueDate: dueDate,
            decimals: decimals,
            totalPaymentAmountScheduled: invoiceAmount,
            prepaymentAmount: prepaymentAmount
        };
        sg.utls.ajaxPostHtml(sg.utls.url.buildUrl("AR", "InvoiceEntry", "GetCreditLimit"), data,
              onSuccess.loadCreditLimit);
    },

This will initiate the call to the server. The URL will be built something like servername/Sage300/AR/InvoiceEntry/GetCreditLimit. The ASP.Net MVC infrastructure will use configuration by convention to look for a matching entry point in a loaded controller and hence call the  GetCreditLimit method in the InvoiceEntryController.cs file:

        [HttpPost]
        public virtual ActionResult GetCreditLimit(string id, string docDate, string dueDate,
               string decimals,decimal totalPaymentAmountScheduled, decimal prepaymentAmount)
        {
            try
            {
                return PartialView(AccountReceivable.ARInvoiceCreditCheck,
                      ControllerInternal.GetCreditLimit(id, docDate, dueDate, decimals,
                      totalPaymentAmountScheduled, prepaymentAmount));
            }
            catch (BusinessException businessException)
            {
                return JsonNet(BuildErrorModelBase(CommonResx.NotFoundMessage, businessException,
                    InvoiceEntryResx.Entity));
            }
        }

Which will call the InvoiceControllerInternal.cs GetCreditLimit method:

        internal ViewModelBase<CustomerBalance> GetCreditLimit(string customerNumber,
            string documentDate,
            string dueDate, string decimals
            , decimal totalPaymentAmountScheduled, decimal prepaymentAmount)
        {
            var creditBalance = Service.GetCreditLimit(customerNumber, totalPaymentAmountScheduled,
                 prepaymentAmount);

            if (creditBalance.CalcCustomerOverdue == CalcCustomerOverdue.Yes &&
                creditBalance.CustomerBalanceOverdue > creditBalance.CustomerAmountOverdue)
            {
                creditBalance.CustomerCreditMessage = string.Format(
                        InvoiceEntryResx.CustCreditDaysOverdue,
                        creditBalance.CustomerDaysOverdue,
                        creditBalance.CustomerBalanceOverdue.ToString(decimals),
                        creditBalance.CustomerAmountOverdue.ToString(decimals));
            }

            if (creditBalance.CalcNatAcctOverdue == CalcNatAcctOverdue.Yes &&
                creditBalance.NatAcctBalanceOverdue > creditBalance.NatAcctAmountOverdue)
            {
                creditBalance.NationalCreditMessage = string.Format(
                        InvoiceEntryResx.NatCreditDaysOverdue,
                        creditBalance.NatAcctDaysOverdue,
                        creditBalance.NatAcctBalanceOverdue.ToString(decimals),
                        creditBalance.NatAcctAmountOverdue.ToString(decimals));
            }

            creditBalance.CustomerCreditLimit =
                 Convert.ToDecimal(creditBalance.CustomerCreditLimit.ToString(decimals));
            creditBalance.CustomerBalanceVal =
                 Convert.ToDecimal(creditBalance.CustomerBalanceVal.ToString(decimals));
            creditBalance.PendingARAmount =
                 Convert.ToDecimal(creditBalance.PendingARAmount.ToString(decimals));
            creditBalance.PendingOEAmount =
                 Convert.ToDecimal(creditBalance.PendingOEAmount.ToString(decimals));
            creditBalance.PendingOtherAmount =
                 Convert.ToDecimal(creditBalance.PendingOtherAmount.ToString(decimals));
            creditBalance.CurrentARInvoiceAmount =
                 Convert.ToDecimal(creditBalance.CurrentARInvoiceAmount.ToString(decimals));
            creditBalance.CurrentARPrepaymentAmount =
                 Convert.ToDecimal(creditBalance.CurrentARPrepaymentAmount.ToString(decimals));
            creditBalance.CustomerOutstanding =
                 Convert.ToDecimal(creditBalance.CustomerOutstanding.ToString(decimals));
            creditBalance.CustomerLimitExceeded =
                 Convert.ToDecimal(creditBalance.CustomerLimitExceeded.ToString(decimals));
            creditBalance.NatAcctCreditLimit =
                 Convert.ToDecimal(creditBalance.NatAcctCreditLimit.ToString(decimals));
            creditBalance.NationalAccountBalance =
                 Convert.ToDecimal(creditBalance.NationalAccountBalance.ToString(decimals));
            creditBalance.NatAcctOutstanding =
                 Convert.ToDecimal(creditBalance.NatAcctOutstanding.ToString(decimals));
            creditBalance.NatAcctLimitLeft =
                 Convert.ToDecimal(creditBalance.NatAcctLimitLeft.ToString(decimals));
            creditBalance.NatAcctLimitExceeded =
                 Convert.ToDecimal(creditBalance.NatAcctLimitExceeded.ToString(decimals));

            return new ViewModelBase<CustomerBalance> { Data = creditBalance };
        }

This routine first calls the GetCreditLimit service in InvoiceEntryEntityService.cs:

        public virtual CustomerBalance GetCreditLimit(string customerNumber,
            decimal totalPaymentAmountScheduled, decimal prepaymentAmount)
        {
            var repository = Resolve<IInvoiceEntryEntity<TBatch, THeader,
                 TDetail, TPayment, TDetailOptional>>();
            return repository.GetCreditLimit(customerNumber,
                 totalPaymentAmountScheduled, prepaymentAmount);
        }

Who then calls the repository GetCreditLimit routine in InvoiceEntryRepository.cs. This routine then appears to do regular View processing using the base repository wrapper routines that insulate us from the session/dblink handling logic as well as do some basic error processing:

        public virtual CustomerBalance GetCreditLimit(string customerNum,
            decimal totalPaymentAmountScheduled, decimal prepaymentAmount)
        {
            _header.Read(false);
            _creditCheck.SetValue(CustomerBalance.Fields.CustomerNumber, customerNum);
            _creditCheck.SetValue(CustomerBalance.Fields.CurrentARInvoiceAmount,
                totalPaymentAmountScheduled);
            _creditCheck.SetValue(CustomerBalance.Fields.CurrentARPrepaymentAmount,
                prepaymentAmount);
            _creditCheck.Process();
            return _creditCheckMapper.Map(_creditCheck);
        }

Finally, down in the business repository, the code should look fairly familiar to anyone you has done any C# coding using our Sage 300 .Net API. Further this code should also appear somewhere in the matching VB code, and besides being translated to using the .Net API, its become quite separated from the UI control code (in this case the JavaScript).

At the end of this all the calls return propagating the returned data back to the Browser in answer to the AJAX call that it made.

It might look like a lot of code here, but remember the business repository and JavaScript bits have corresponding VB code. Then the other layers are there to make all the code more re-usable so that it can be used in contexts like WebAPIs and allow interfaces to provide the hooks needed for customization.

Summary

This article is intended to give you an idea of where to put your code that accesses the Sage 300 Business Logic and then how to call that from the Web UIs. There are a lot of layers but individually most of the layers are fairly simple and most of the code will appear in the Business Repository and the JavaScript behavior code.

Written by smist08

February 12, 2016 at 3:27 am

Stateless Versus Stateful Sage 300c Web UIs

with 2 comments

Introduction

When two computers communicate they use a well-defined communications protocol. In Browser to Server communications these are often broadly categorized as either stateless or stateful. A stateless communications protocol doesn’t require that anything be remembered between calls. In web applications this is desirable for a number of reasons:

  • often calls are load balanced across multiple servers (perhaps even in different locations).
  • storing state in memory can be expensive and will limit how many users can be accessing a web server at once.

On the other hand, maintaining server state has some advantages:

  • less information needs to be transferred between the client and server since the server knows what has gone before.
  • having things handy in memory can make operations faster and more context aware.

Often in the real world these components are combined or stacked on each other, for instance the TCP protocol is stateful and then the HTTP stateless protocol is layered on top of TCP.

The Sage 300c Web UIs use both stateless and stateful technology. This article will talk about when we use each, how to program either case and what are the advantages and disadvantages of each method. Like many things in programming the lines between these two things can become quite blurred.

Since we do have stateful UIs, this does require that if you scale out to multiple web servers then the load balancer must use sticky sessions as we explained in this blog posting. Basically guarantee that all the requests from a given client go to the same web server in case they are running a stateful UI.

Stateless Sage 300 Web UIs

Most of the Sage 300c setup UIs, processing UIs and report UIs are all stateless. This means IIS could be reset between requests and things would still work. It would just be quite slow since it would need to re-open a Sage 300 session and re-open the Views to process the next request. To avoid this we keep a cache of open sessions along with their open Views. This way when a stateless request comes in we match up a session for the right company, user and with the right Views from the session pool and use that to handle the request. If a session in the pool hasn’t been used for a while we will release it and if too many sessions are in use, we will release the oldest if we need another one. This way practically speaking you usually open a session for your first stateless request and then keep using it from the cache of the duration of your work.

schedules

A Typical Stateless UI

Every Sage 300c business logic entity (View) will have a stateless repository defined for it. This is because only the document entry UI needs stateful operation, everything else like finders, export and other UIs will want to use the stateless version. You get all the default behavior for your stateless repository by having your specific repository inherit from one of the stateless base repositories in Sage.CA.SBS.ERP.Sage300.Common.BusinessRepository.Base like FlatRepository, ProcessingRepository or InquiryRepository.

Generally stateless operation fits very well with ASP.Net MVC since this is the natural way that it works. There is a lot of infrastructure to pass the data model back and forth between the client and server in a stateless manner. Add to that our use of knockout data binding and this makes most basic CRUD operations all handled by the framework.

This doesn’t mean that a stateless UI can’t dynamically interact with the user. By default, you have the CRUD operation off the basic navigation and save/delete buttons. But you can certainly add your own custom AJAX calls to dynamically update areas of your form. We provide support in the framework to update things like descriptions of external keys, but in fact you have a lot of power to make your UI very interactive and polished.

Stateful Sage 300 Web UIs

Most of the main Accounting document entry UIs are stateful. This includes UIs like Order Entry, Purchase Orders, G/L Journal Entry and A/R Invoice Entry. These UIs have a lot of sophistication in building up the Accounting document and the size of these documents can be quite large. Sending this entire document back and forth between the browser and the server as a JSON object is quite impractical. When one of these UIs is started, a session and set of Views are assigned to it for the duration that it runs. As changes are made to the UIs they are sent to the server to make the attendant changes to the server model.

oe1100new

A Typical Stateful UI

To create a stateful business repository, just inherit from one of the stateful base repositories in Sage.CA.SBS.ERP.Sage300.Common.BusinessRepository.Base.Statefull like BatchHeaderDetailRepository or SequencedHeaderDetailRepository.

When you exchange information between the server and browser rather than send the entire document back and forth, you tend to send one component back and forth like one order detail line or the order header. This then reduces the amount of data transmitted, but more importantly greatly reduces the size of the JSON object that JavaScript needs to process. If the transferred document gets too large then JavaScript processing speed can become a real bottleneck.

You don’t need to send something to the server every time every field changes, only the fields that cause some business logic to execute. So you don’t need to do anything say when the user changes a description field. When a field changes that causes business logic on the server to execute then you need to send that to the server and get back all the fields that changed as a result. We tend to mostly do this using the default ASP.Net MVC mechanisms along with the knockout data binding. But only doing it for the component record. So say the item number in a detail record is changed, then we would refresh that record by sending it to the server, which would set the changed fields, do the Sage 300 business logic operations (puts and gets) and return the newly updated record where a number of fields have changed.

This works for most cases, but there is room for optimization. If something is rather a major operation, you might want a more tailored AJAX call for the processing. Similarly, if multiple records are affected you might want to denormalize a bit to reduce the dataflow. For instance, when dealing with details, often header totals change, but you might want to associate these with the detail, rather than also refreshing the header.

Summary

This was a quick overview of the main modes or how our Sage 300 Web UIs operation. Stateless operation reduces server overhead, while stateful operation lets us fully leverage our existing business logic, while reducing our bandwidth requirements.

Written by smist08

February 9, 2016 at 8:44 pm

Adding a Grid to Your Sage 300 Web UI

with one comment

Introduction

The grid or table control is a key element for data entry in any Accounting application. With Sage 300 we use the grid control to enter things like Order or Invoice details. Interactions with a grid control tend to be quite complex. The data has to be managed so it is loaded only a page at a time (often called virtual scrolling), since there could be thousands of detail lines and loading them all at once would be quite slow. There is the ability to edit, delete and add lines. Tabbing has to be handled well to enhance data entry. People also have the ability to re-arrange the grid columns, hide columns and then expect these changes to be remembered.

icreceipts

This blog article will talk about the key elements to adding a grid control to your Sage 300 Web UI and what sort of support you need in your UI to support all the desired functionality. A fair bit is handled for you in the Sage 300 Web UI Framework, however you have to handle various events and there is a lot of power to add your own programming.

Configuration

There is a lot of support for standard grid operations in the Sage 300 Web UI framework. Much of this is controlled by a config JSON object which is passed to our @Html.KoKendoGrid function that defines the grid in the Razor View. This file defines a number of properties of the grid along with a number of standard callout functions you can define to add your custom processing. The good news is we have a utility to generate much of this from the ASP.Net MVC Model.

JavaScript Generation Utility

To generate this code we provide a utility which will generate the Razor View code and a lot of the standard JavaScript code that you need. So for instance the code for the Razor View might be:

jsgen1

And then some of the JavaScript code for the config object might be:

jsgen2

Server Side Pagination

In our VB UIs, we had virtual scrolling in our grids, which would basically bring in a page or two at a time. It supported scrolling one page ahead or one page back, go to the top or bottom but you couldn’t go to an arbitrary point in the file without searching (in fact the scroll bar would always be at the top, bottom or right in the middle). In the Web UIs we use the Kendo UI Grid control and try to keep the scrolling mechanism standard for the Web, which means the control tells you how many pages there are and lets you go to any page you like as well as going to the next or previous one.

We provide a lot of the support for doing this in our business repository base classes which expose a get method which takes the page number, page size, filter and order as parameters. Then as long as you match and set the configuration data in the grid’s JavaScript config JSON object, you get the pagination support. There are a couple of things to keep in mind, one is that we rely on our filterCount API call, which translates directly to a SQL statement, which means it can only count based on database fields and not calculated fields, so you can’t restrict the records in your grid based on any calculated fields or the count will be wrong (if you really need this then you need to disable the ability to go to a specific page). You also need to have a hidden SerialNumber column in the grid which contains the record number.

ViewListControl vs AccpacGrid

In our VB UIs, we actually had two grid controls. One was the ViewListControl which would show a separate View record in each line and supported virtual scrolling. Then we had the AccpacGrid control which would usually show an array of fields from a single record (like tax information, or perhaps item structure information).

In the Web UIs we only have one Grid control. It naturally works more like VB’s ViewListControl. So how do we handle the other case of the AccpacGrid? We do this in our controller by translating the array of fields into what looks like a list of details. This way to the Grid control, it doesn’t really see a difference. Usually you don’t need to enable virtual scrolling in this case since there is typically 5 or 10 records and you just provide them all at once. So typically your ViewModel will have a list of records which the controller will populate and then this is set as the Grid’s data source.

Editing

Like VB, the intent of editing cells is to place the correct edit control over the grid cell to perform the edit. There is a lot of framework support for this as well as lots of callouts for you to do your own custom processing. The same is true for adding a new line and deleting a set of lines (note that the Web UI grid supports multiple selection). Also note that the add line, delete line, edit columns buttons aren’t part of the Grid, these are separate buttons styled to look like part of the grid in a region just above the Grid. This means you can easily add your own buttons and controls to this area if you wish.

Saving Preferences

We have API support to help with loading and saving grid column preferences. In VB these are stored in the *_p.ism files, in the Web UIs these are stored in the SQL database in the new USRPROP table. So emptying USRPROP is the Web UI equivalent of deleting the *_p.ism files. Generally, we want to move everything into the database and remove our reliance on the shared data folder over time.

Summary

This article was just a quick introduction to adding a Grid control to a Web UI. Similar to the VB UIs, the grid control is potentially quite complicated as it supports a lot of diverse functionality. But, if you are doing fairly standard functionality, look for a lot of support in the Web UI framework to help you get the job done.

Written by smist08

February 3, 2016 at 6:29 pm

Follow

Get every new post delivered to your Inbox.

Join 342 other followers