Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘views

Composing Views in the Sage 300 ERP .Net API

with 23 comments

Introduction

In the past couple of weeks we’ve had a look at opening sessions with the Sage 300 ERP .Net API and then opening a View and doing some simple View operations. This week we are going to start looking at the more complicated case of document entry which involves “header/detail” type Views as well as composing multiple Views together. I talked a bit about these concepts in this article on entering O/E Orders via the COM API.

Composing Views

Composing Views is a topic that usually confuses people that are new to using the Sage 300 API. It is a basic mechanism to allow multiple Views to work together with each other as well as to work with the person calling the Views via the API. Roughly for data Views, each View corresponds to a database table. But when we are doing document entry like A/R Invoices or O/E Orders, multiple Views must be used together to build up the document. Further we want to write the document in a single database transaction (we wouldn’t want some tables updated and others not). We also want to ensure that the various Views are used in such a way to avoid multi-user problems like deadlocks.

View composition is the basic mechanism that enables this functionality. It is really a way of sharing View objects, so that everyone is using the same copy rather than opening their own copies. So who is everyone? There is only me the programmer. Well not only do you call Views via the API, but Views call each other all the time. We will look at why they do this in the next section.

Our convention is that the person using the API is responsible for opening all the Views that will be shared. This is just via regular OpenView method calls from a DBLink object. Then we give a reference to all these Views to everyone else via the View’s compose method.

Let’s look at an example for opening and composing the Views required to enter A/R Invoices:

// Open the A/R Invoice Entry Views
arInvoiceBatch = mDBLinkCmpRW.OpenView("AR0031");
arInvoiceHeader = mDBLinkCmpRW.OpenView("AR0032");
arInvoiceDetail = mDBLinkCmpRW.OpenView("AR0033");
arInvoicePaymentSchedules = mDBLinkCmpRW.OpenView("AR0034");
arInvoiceHeaderOptFields = mDBLinkCmpRW.OpenView("AR0402");
arInvoiceDetailOptFields = mDBLinkCmpRW.OpenView("AR0401");


// Compose the Batch, Header and Detail views together.
arInvoiceBatch.Compose(new ACCPAC.Advantage.View[] { arInvoiceHeader });
arInvoiceHeader.Compose(new ACCPAC.Advantage.View[] { arInvoiceBatch, arInvoiceDetail,
      arInvoicePaymentSchedules, arInvoiceHeaderOptFields });
arInvoiceDetail.Compose(new ACCPAC.Advantage.View[] { arInvoiceHeader,
      arInvoiceBatch, arInvoiceDetailOptFields });
arInvoicePaymentSchedules.Compose(new ACCPAC.Advantage.View[] {
      arInvoiceHeader });
arInvoiceHeaderOptFields.Compose(new ACCPAC.Advantage.View[] { arInvoiceHeader });
arInvoiceDetailOptFields.Compose(new ACCPAC.Advantage.View[] { arInvoiceDetail });

To work with A/R Invoices requires using six Views which we open first. Now we as the caller of the API have a View object for each of these. So we can go ahead and make method calls to all these Views. However, these Views need to know about each other so they can work together. There may be fields in the header that the details need to update as they are updated, deleted or inserted, like totals for each invoice. When we save the invoice, it must be able to start a database transaction and save all the other Views as well as itself to work correctly. So basically the compose calls are giving the Views the ability to use the other Views they need that we opened. For instance the first Compose call is the batch.Compose which gives the A/R Invoice Batch View access to the A/R Invoice Header View that we opened. Now the batch View can call the same instance of the header View that we opened and if we look at the fields in the header View we will see the values set by the batch View before things are saved to the disk.

But the code above does seem rather magic. How did I know which Views to open and how did I know which Views to compose? It is very important that you compose the correct Views and the order the Views appear in the array passed into the Compose call is very important. The way I create these, is to run macro recording on the regular UI that uses the document I am programming. Then I get all the correct open and compose calls in VBA syntax which is quite easy to translate into C# and then I have something I know will work. Sometimes you can leave out composite views that aren’t used in a situation, but I find that leads to more problems than its worth, so I generally just include them all. Similarly the ViewDoc and the Application Object Model list all the Views a View will compose with, but I find translating this information into code more tedious.

Header/Detail

Within the Sage 300 system, most accounting documents use a header/detail structure. This means that we have a single header record for the document (like an Invoice) and then multiple detail lines, one for each of the line item detail lines. This gets more complicated since details can have sub-details and so on, but the basic concept is the same. We often are using View composition to get header and detail Views to work together to correctly insert, delete and edit accounting documents.

As an example of header/detail we have the Order Header and then its detail lines. The Order header has information that there is one of for the order. The details then contain data that there are many of. The Order Header contains information like the customer id and the ship-to address. Then each detail line contains an I/C Item number and quantity. So for an order you can order as many items as you like.

The Order Header also stores information like various totals. So as each detail line is added, updated or deleted these header totals have to be updated to keep them correct. Details are always associated with their header by having their primary key start with the header’s primary key, then just adding an uniquifier or counter. This is show in the diagram below:

headerdetail

Sample Program

The sample code for this article is the ARInvEntryWinForms sample on the GDrive here. This sample opens and composes the A/R Invoice Views, then it goes a bit further to create a batch with a single entry for the customer number you enter on the form and then inserts one detail line with a fixed item number (the quantity is defaulted to 1 by the detail View). There is a bit of error handling. In future articles we will talk in detail about the protocol for entering header/detail documents, we will also talk a lot about exception handling and debugging in a future article. But just describing the View composing and header/detail concepts has already lead to a full article. But I did want to include a bit more code than just opening and composing the Views, so it would really do something. You need to go into A/R Invoice Entry to see the invoices created.

Summary

This article covered the basic concepts of View composition and of header/detail relationships. We will start to use these frequently in future articles. These are an important building block to much more sophisticated programs.

Written by smist08

October 27, 2013 at 3:31 am

Starting to Program the Sage 300 ERP Views in .Net

with 26 comments

Introduction

Last time we used the Sage 300 ERP .Net Interface to open a session and create a database link to a Sage 300 ERP company. In this article we will start to investigate how to use the API to manipulate the Sage 300 ERP business logic. The individual business logic objects are known as Views (not to be confused with the Views in MVC or SQL Server Views). For a bit more background on the Views have a look at this article.

These business logic Views represent all the various objects in Sage 300 like G/L Accounts, A/R Customers or O/E Orders. There are also Views for doing processing operations like posting G/L batches or running I/C Day End. The nice thing about these Views is that they all share the same interface and this complete interface is accessible from the .Net API.  Although the API to each view is standard, sometimes you need to use several Views together to accomplish a task and there are about 5 protocols for how you use the Views together to accomplish something. But if you learn the API for one set of Views and learn the 5 protocols then you can do anything in any of the Sage 300 applications from any of the several hundred Views. Additionally you can utilize any Views created by third party ISV solutions.

Since the .Net interface is used by our VB UIs when they are running in the old 5.0A style web deployed, via the .Net Remoting option, you know that the .Net API is capable of performing any task that can be performed by a regular Sage 300 form.

As we proceed we’ll look into the various parts of the API in more detail, but for this article we’ll just look at how to get started and do some basic operations with data views.

Opening a View

To use a View, first we need to open it from the database link (DBLink). Doing this is quite simple:

ACCPAC.Advantage.View arCustView = mDBLinkCmpRW.OpenView("AR0024");

In this case we needed to add the “ACCPAC.Advantage” part to the definition of View, because there is a System.Windows.Forms.View and the compiler needs to know which one we mean. Unfortunately the word View is a bit over used in Computer Science which can lead to some confusion.

But what is this “AR0024” that we are opening? Where did that come from? In the Sage 300 world, all UIs and Views are uniquely identified by what is called a Roto ID which consists of two alphabetic characters followed by four decimal digits. Every Sage 300 SDK application whether written by Sage or an ISV must register a unique two letter prefix for their application with the DPP program. This then guarantees that two SDK modules won’t conflict with each other. Then the developer of the module (in this case A/R) assigns the numbers to all their Views and UIs. Sage’s convention is to start the Views at 0001 and to start the UIs at 1000.

So how do you know what to specify? There are several ways to figure this out.

  1. Use the Sage 300 ERP Application Object Model (AOM). Which is on our Web site here. From this site you can get a list of all Views for all the Sage applications along with any underlying database table structure. Using this site requires using Internet Explorer. You can’t use this for information on ISV applications.
  2. If you have the SDK then you can use the very helpful ViewDoc utility which is part of the SDK application (which you must activate inside Sage 300). A benefit of this is that you can get information on ISV applications that are installed on your system as well.
  3. Use macro recording. If you macro record a UI which uses the View you are after, then the macro recording will record the DBLink OpenView call with the roto view. Just note you need to change the syntax from VBA/COM to C#/.Net (which is fairly easy).
  4. The UI Info tool that is included with the core product can be used, but you need to first get the info on a UI that uses the View then drill down into the View by getting info on the data source.

After calling OpenView, your view object is ready to use, so let’s see some things we can do.

CRUD

CRUD stands for “Create, Read, Update and Delete”. Here we’ll look at reading and updating anyway.

When you open a View there is not data loaded. If we don’t know what record we want, one way to find out is to iterate through all the records or to just read in the first one. Calling GoTop will get the first record.

bool gotOne = arCustView.GoTop();

This function returns a bool to specify true if it returned a record and false if it didn’t. Most of the .Net API functions have simple return codes like this. These are usually the things you want to handle easily programmatically. If something else happens then this function will throw an exception, these could be things like a network connectivity errors or some bad SQL Server index corruption error. Today we’ll just handle the easy cases. In a future article we’ll look more at error handling and what to do when one of these methods throws an exception.

Now let’s iterate through all the records and print out the customer records (assuming the GoTop above was called first).

String custNum;
String custName;

while (gotOne)
{
    custNum = (String) arCustView.Fields.FieldByName("IDCUST").Value;
    custName = (String) arCustView.Fields.FieldByName("NAMECUST").Value;
    Console.WriteLine("Customer Number: " + custNum +
        " Customer Name: " + custName);
    gotOne = arCustView.GoNext();
}

If we got a record then get the customer number and customer name and write them to the console. Inside each View there is a collection of Fields. These usually include the database table fields along with some calculated fields. We’ll look at these in much more detail in a future article. For now this is how you get the various fields from the customer record. How do you know the strings “IDCUST” and “NAMECUST”? You find these the same way you find the Roto ID. The four methods mentioned above will also give you all the fields for each View. We had to cast the result to “String” because the field value is an object. The reason for this is that each field has a type like number, string, date or time and depending on the type will affect the object type. In this case we know these are both strings, so we can just tell the compiler that by setting the cast. If we got this wrong we’ll get an exception when we run. Again the four methods above will give you all the field types as well as some more useful information.

OK so that reads all the records, but what if we know which record we want and just want to read it? This is done as follows:

arCustView.Fields.FieldByName("IDCUST").SetValue("1200", false);
arCustView.Read(false);
custNum = (String) arCustView.Fields.FieldByName("IDCUST").Value;
custName = (String) arCustView.Fields.FieldByName("NAMECUST").Value;
Console.WriteLine("After Read, Customer Number = " + custNum +
     " Customer Name: " + custName);

Here we see how to set the key field for the customer record with the SetValue method of the field. The second parameter is verify which we’ll talk about another time, but for now this is fine set to false. This just determines if we should verify this field right away or wait for later.

Then we call Read to read the record. The parameter lock is set to false, which is nearly always the case (if you set it to true then you will get an exception and an error about needing to be in a transaction which we’ll talk about another time).

Then there is the code to get the field value and print them to the console. A bit of bad programming here with no error checking. Note that this will only work if there is a customer 1200 like in sample data.

Suppose now that we’ve read this record we want to update it? Well that turns out to be quite easy:

arCustView.Fields.FieldByName("NAMECUST").SetValue("Ronald MacDonald", false);
arCustView.Update();

Here we set the field “NAMECUST” to a new value and then call the Update method which has no paramters. You can then run Sage 300 and run the A/R customer’s screen, bring up customer 1200 and see that his name is in fact changed.

Summary

This was a quick introduction to the basics of how to access and use the Business Logic Views in Sage 300 ERP. All the API elements described apply to all the Views in Sage 300 ERP, so learning to manipulate on object goes a long way to proficiently manipulating all objects.

I’ve updated the sample application here as mentioned in this article.

Written by smist08

October 20, 2013 at 12:27 am

A Short History of the Accpac View Interface

with 6 comments

In the beginning, there was the View. The View is a Sage ERP Accpac Business Logic object, for an introduction to Views see: https://smist08.wordpress.com/2010/09/11/accpac%e2%80%99s-business-logic/. The View is a Windows DLL with a set interface. Views expose a common set of functions, which are displayed below (this set of functions has grown over time):

There are functions to control the View, manipulate fields, get meta-data, manipulate records and manipulate sets of records. Each of these routines has a corresponding function inside the View DLL. In the early days all these functions were exported from the View DLL and theoretically could be called directly. However we never endorsed this, did it ourselves or provided instructions on how to do that. In a way calling these functions directly in the View DLL was the first View interface, even though it was never used. Not listed here is the real function that is called in the View DLL, namely rotoEntry. This function takes a structure as its argument that specifies the function to call and has some generic arguments that are used as the arguments to the various functions following a set convention. Again the users of Views have never loaded the View DLL and called the rotoEntry function directly.

Roto/RotoView

You can call the Views by a couple of System Manager DLLs called Roto and RotoView. Roto is the low level object manager in Accpac, it handles the loading of View DLLs and figuring out things like what to load when Views are sub-classed. Roto also routes messages within Accpac to make sure the right View is called when a View call is made. RotoView is the first View interface that people actually used and is still in use today. Rotoview manages Roto to load the right View and provides convenient functions that correspond to the real functions inside all View DLLs. Below is a diagram showing RotoView and some of  its functions using Roto to access  a View through its rotoEntry routine.

RotoView provides an easy to use API for accessing Views, you just call viewLoad, viewOpen and then start doing real work. The Views still use this interface to directly talk to each other. All other interfaces talked about below also ultimately call the RotoView interface to do their real work.

Icmd/cmd

One thing that was considered difficult when using the View interface is something called “View Composition” which is how you combine a number of low level Views into higher level objects. For instance to enter an order into O/E you need to use the order header view and order detail view together to enter the order. To use these they need to be composed together to make an order set of Views. This process can be a bit tricky so we wanted to hide it from users using macros. In the original version of CA-Accpac/2000, it shipped with a macro language called CA-BLE (Computer Associates Basic Language Engine). In the macro language we wanted to ability to call Views, but to hide some of the complexity, such as composing Views. The Icmd and cmd interfaces were intended to do this. The View compositions were specified in the Objects section of the application’s xx.ini file, and were done automatically for you. These interfaces were also used for the original import/export program used by our CA-Realizer based User Interface programs. This then led to the architecture of CA-Accpac/2000 1.0A as shown in the diagram below (Roto and RotoView are combined into Roto):

Icmd and cmd are nearly the same, Icmd is just a little lower level and is the actual interface used by the old import/export program. Some other programs used the cmd interface such as the data integrity checker program. Cmd/Icmd are still included with System Manager for any third party programs that might use them, but aren’t used by anything that Sage ships.

Xapi

A weakness of all the APIs discussed so far is that they are only callable by a true Accpac SDK application launched from the Accpac Desktop. As we developed Accpac for Windows 3.0A we were developing the iConnect Server which needed to run independently on a server and not from the desktop. We were also getting complaints that it was hard to integrate Accpac to an existing standalone application, since you needed to develop an Accpac SDK part that would talk to the Views, the standalone non-SDK application couldn’t just call the Views directly itself.

And so was born XAPI. The main innovation in XAPI was that you first opened a session to Accpac providing basic sign-on information then went ahead and used the Views. This meant that anyone could use the XAPI interface whether they were an SDK application or not. So XAPI consists of some session management routines, then a set of routines very similar to what is in RotoView, except that they take the session as a parameter rather than the roto view handle used by RotoView. XAPI also contains routines to print reports and access other System Manager info easily. This way iConnect and non-SDK applications could fully integrate into Accpac. Later the iConnect program would be evolved into the Process Server, but both of these programs are long gone. The XAPI interface remains since it is used by many third party programs to interface to Accpac to this day.

This interface also stopped hiding the View compositions. We found that hiding compositions made it very hard to do some things with the CMD interfaces.

A4wcom

A further weakness of all the APIs mentioned so far is that they are all native DLL interfaces. These are easy to call from C programs, but problematic for other programming systems like Delphi or FoxPro. For Accpac version 4.0A we went 32-Bits and changed macro languages from CABLE to VBA. To integrate to VBA you need to provide a COM interface. So we could kill two birds with one stone, provide a COM interface to allow an easily usable interface for programming systems that have trouble calling DLLs directly and add VBA as Accpac’s macro language.

So was born the a4wcom interface. This is a COM interface that is layered on top of the XAPI interface and hence provides the same functionality of XAPI. It can be used by SDK or non-SDK applications, it can call the Views and print reports. Further it’s easy to use by any programming system that can call COM objects which includes Delphi, Foxpro, VBA, VB, VBScript, JavaScript, Perl, etc.

A4wcomex

Advance to Accpac Advantage Series 5.0A. With this release we re-wrote our User Interface programs from CA-Realizer to Visual Basic (VB). To do this we needed a strong COM interface. Unfortunately you can’t just change an existing COM interface since it will break everything that uses it. So we made the decision to leave a4wcom alone and develop a version 2 COM interface for Accpac namely a4wcomex. This interface doesn’t go through XAPI, it goes directly to RotoView. It’s also designed so that it can be “remoted” meaning that it can be called over the Internet. This was then the basis for Accpac 5.x web deployed mode. See the diagrams below for how remoting is fit into our model (the top purple boxes are the typical components that make up a VB UI program). The AccpacCOMAPI below is really a4wcomex.

Datasources

When talking to Views from a VB UI program, the VB code doesn’t talk directly to a4wcomex, instead the VB UI code talks to an ActiveX control called the DataSource control. The DataSource control then talks to the AccpacCOMAPI to perform the actual View operations. The reason we do this is that the DataSource is actually a visual control which means it can be configured at design time in the form designer. This means you specify which views to use and such in the form designer saving you writing quite a bit of code, most View compositions can also be specified this way. Another function of the DataSource is that it provides caching to reduce the number of View calls over the Internet when running in Web deployed mode.

.Net

With the Accpac Advantage Series 5.0A release we introduced Web Deployed mode where the UIs run as ActiveX controls inside the IE browser. With the original release we accomplished this by using DCOM in place of COM to do the View calls over the Internet. However this proved problematic, because DCOM is slow and DCOM uses random ports to communicate, which means it is very firewall unfriendly. You don’t know what ports it might use, so you have to leave them all open, which is very bad from a security point of View.

So with version 5.1A we added the option to use .Net Remoting as the communications protocol to talk over the Internet. To use .Net Remoting we needed to develop a .Net layer that sat over top of a4wcomex which we could then remote. Since we had to develop this layer anyway, we made it publicly available as another View interface for the added convenience of third party applications that were developing in .Net languages like C# or VB.Net.

Soap

If you have a .Net interface to something, then Microsoft through Visual Studio provides a tool that lets you expose that .Net interface externally as a SOAP web services interface. So when we developed the .Net interface, we got the SOAP interface for free.

However, you tend to get what you pay for, and like relying on DCOM to give you internet access for free over COM, it turned out to not work all that well.

With Version 6 we are removing the SOAP web service to be replaced by the much superior REST based SData web services interface.

Java

Fast forward to Accpac 6.0 where we are using the Google Web Toolkit to move all our user interface programs from VB to the web using HTML and JavaScript. For server processing we are handling Web service requests via a Java program which then needs to talk to the View. Java doesn’t like using COM or .Net. And for that matter it’s hard to use those on Linux Servers (if we ever want to do that).

So for Version 6 we’ve developed a native Java interface that is similar to the a4wcomex interface and talks to RotoView directly. The Java interface also has the distinction of being our first truly multi-threading interface. Multi-threaded programs could call our other interfaces, but would probably be blocked. The Java interface allows many threads to be running at once all doing work without overly blocking each other from operating.

SData

SData is Accpac 6.x’s new REST base Web Services Interface. For more information on SData see https://smist08.wordpress.com/2009/11/24/sdata-in-sage-erp-accpac-6/, https://smist08.wordpress.com/2010/01/18/more-on-sdata-and-sage-erp-accpac-6/, https://smist08.wordpress.com/2010/02/14/stateful-sdata/ and http://sdata.sage.com/.

The way the SData interface works is we have a Java Server application (SDataServlet) that receives the SData webserver requests; it parses and processes these converting the SData web service requests into View calls which are made via the new Sage Accpac Java interface (SA-Java).

Written by smist08

September 18, 2010 at 5:34 pm