Sage 300 ERP Metadata

Introduction

For the past few weeks we’ve been playing around with the Sage 300 ERP .Net API and we’ve progress from working with WinForms projects to ASP.Net MVC projects. In this article we are going to look at the metadata that is available from the Sage 300 .Net API and we are going to build a simple ASP.Net MVC project to create a program similar to the SDK’s ViewDoc program to dump out this metadata for our perusal. We won’t do anything fancy in this program, but it will give us a chance to practice ASP.Net MVC and to practice our HTML skills.

Views Expose Themselves

The Sage 300 ERP Business Logic Views are self-documenting. They will tell you quite a lot of information about the information they hold. They will tell you how to compose them, what indexes they have and all sorts of information about all their fields. Within the .Net API are a number of properties and method to help provide all this information.

The View Itself

First the View exposes a few fields that tell us something about it including its Roto ID, Description, Template Version and Template Date. We should know the Roto ID already, since we needed this to open the View. Then the description gives a readable title for the View, the template version tells us which version of the View template it was created with (useful if we want to support old applications and avoid methods that were added later), along with the template date. These are access as follows:

ACCPAC.Advantage.View view;

view.ViewID;

view.Description;

view.TemplateVersion;

view.TemplateDate;

Then the View has a number of collections for the other information on Keys, Fields and Composites. The API for these collections isn’t entirely consistent, sometimes these are true collections and you can iterate through them via foreach, sometime you need to do an indexed for loop and access them using one of either (i) or [i]. Then there are usually some other accessor methods to look things up other ways. Generally check back to the reference help file or the object explorer in Visual Studio to get help on which to use.

The View Keys Collection

Each View has a Keys property that returns information on all the keys for a View. You can get the count, you can index to get each key using square brackets and you can access a list of field objects that make up each key. Below is a bit of sample code that reads through the keys and builds all the returned information into HTML.

            for ( i = 0; i < viewToProcess.Keys.Count; i++)
            {
                ACCPAC.Advantage.ViewKey key = viewToProcess.Keys[i];
                string fields = "";

                for (int j = 0; j < viewToProcess.Keys[i].FieldCount; j++ )
                {
                    if (j != 0)
                    {
                        fields = fields + ", ";
                    }
                    fields = fields + viewToProcess.Keys[i].Field(j).Name;
                }
                returnHTML = returnHTML + "<tr><td>" + key.ID + 
                    "</td><td>" + key.Name + "</td><td>" + fields + "</td></tr>";
            }

The View Composites Collection

The View Composites are returned as an array of strings from the CompositeNames property of the View. This is quite easy to deal with since you can use foreach on it to iterate through the members. These are just the roto IDs for the Views and in our sample program below we just dump these out. These are the Views you can explicitly compose to this view as explained here. Note that the real ViewDoc loads each View referenced here and adds the description, which I’ll leave as an exercise for the reader.

            foreach (string viewID in viewToProcess.CompositeNames)
            {
                returnHTML = returnHTML + "<tr><td>" +viewID + "</td></tr>";
            }

The View Fields Collection

In the attached sample program we iterate through the fields and dump out some of the contents into an HTML table. We loop through all the elements of the Fields property of the View. Count is the count and we reference each field using square brackets [].

            for(  i = 0; i < viewToProcess.Fields.Count; i++ )
            {
                ACCPAC.Advantage.ViewField field = viewToProcess.Fields[i];

                returnHTML = returnHTML + "<tr><td>" + field.Name + 
                     "</td><td>" + field.Description + "</td><td>" + 
                     field.Type + "</td><td>" + field.Size + "</td><td>" + 
                     field.PresentationMask + "</td></tr>";
            }

 

Sample Program

The sample program is available here and is the ViewDoc project. It is a very simple ASP.Net MVC program that basically asks for the roto ID and then does an old style submit. The model then builds the response as an HTML page which it constructs by brute force using string concatenation. There isn’t any Ajax or JQuery in this project.

When we generate the HTML we put each collection in an HTML <table>. Each row is contained in <tr></tr> tags and each element in <td></td> tags.

Exercises for the Reader

We may revisit this project later and spiff it up a bit. But some obvious areas for improvement would be:

• Add descriptions for the View Roto IDs in the composites list.
• Add and field presentation lists to the fields table.
• Add other missing field elements like attributes and default value.
• Instead of generating the HTML in the model, move all the data into the models class variables and then have the razor view templating mechanisms display the data nicely using the razor view mechanisms for this.
• Change the form from a submit page form, to Ajax and make it a bit better formatted.
• Add error handling as described in the last project.
• For a more advanced exercise, provide a list of all the Views to choose from (like ViewDoc) rather than having to know the Roto ID first.

Summary

This was a quick overview of how to access the metadata available to you from the Sage 300 Business Logic. This can really come in handy when programming, especially when creating general tools or utilities. For instance the Finder and Import/Export modules use this metadata to do their job, just being passed the View to work on and then figuring out everything else.