Starting to Program the Sage 300 ERP Views in .Net

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.