Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘.net

Using the Sage 300 .Net Helper APIs

with 8 comments

Introduction

The main purpose of our .Net API is to access our Business Logic Views which I’ve blogged on in these articles:

An Introduction to the Sage 300 ERP .Net API
Starting to Program the Sage 300 ERP Views in .Net
Composing Views in the Sage 300 ERP .Net API
Using the Sage 300 ERP View Protocols with .Net
Using Browse Filters in the Sage 300 ERP .Net API
Using the Sage 300 .Net API from ASP.Net MVC
Error Reporting in Sage 300 ERP
Sage 300 ERP Metadata
Sage 300 ERP Optional Fields

However there are a number of simple things that you need to do repeatedly which would be a bit of a pain to use the Views every time to do these. So in our .Net API we provide a number of APIs that give you efficient quick mechanisms to access things like company, fiscal calendar and currency information.

With Sage Summit 2014 just a few weeks away (you can still register here), I can’t pre-empt any of the big announcements here in my blog (as much as I’d like to), so perhaps a bit of an easier .Net article instead. For many these examples are fairly simple, but I’m always getting requests for source code, and I happen to have a test program that exercises these APIs that I can provide as an examples. This program was to help ensure and debug these APIs for our 64 Bit/Unicode version which might indicate why it tends to print rather a strange selection of fields from some of the classes.

Sample Program

The sample program for this article is a simple WinForms application that uses the Sage 300 ERP API to get various information from these helper classes and then populates a multi-line edit control with the information gathered. The code is the dotnetsample (folder or zip) located in the folder on Google Drive at this URL. The code is hard coded to access SAMLTD with ADMIN/ADMIN as the user id and password. You may need to change this in the session.open to match what you have installed/configured on your local system. I’ve been building and running this using Visual Studio 2013 with the latest SPs and the latest .Net.

dotnetsample

The Session Class

The session class is the starting point for everything else. Besides opening the session get establishing the DBLink, you can use this class to get some useful version information as well as some information about the user like their language.

The DBLink Class

From the session you get a DBLink object that is then your connection to the database and everything in it. From this object you can open any of our Business Logic Views and do any processing that you like. Similarly you can also get quick access to currency and fiscal calendar information from here. Of course you could do much of this by opening various Common Service Views, but this would involve quite a few calls. Additionally the helper APIs provide some caching and calculation support.

The Company Class

Accessing the company property from the DBLink object is your quick shortcut to the various Company options information stored in Common Services in the CS0001 View. This is where you get things like the home currency, number of fiscal periods, whether the company is multi-currency or get address type information. Generally you will need something from here in pretty much anything you do.

The FiscalCalendar Class

You can get a FiscalCalendar object from the FiscalCalendar property of the DBLink. In accounting fiscal periods are very important, since everything is eventually recorded in the General Ledger in a specific fiscal year/fiscal period. G/L mostly doesn’t care about exact dates, but really cares about the fiscal year and period. For accurate accounting you always have to be very careful that you are putting things in the correct fiscal year and periods. In Common Services we setup our financial years and fiscal periods assigning them various starting and ending dates. Corporate fiscal years don’t have to correspond to a calendar year and usually don’t. For instance the Sage fiscal year starts on October 1, and ends on September 30.

This object then gives you methods and properties to get the starting and ending dates for fiscal periods, years or quarters. Further it helps you calculate which fiscal year/period a particular date falls in. Often all these calculations are done for you by the Views, but if you are entering things directly into G/L these can be quite useful. Some of the parameters to these methods are a bit cryptic, so perhaps the sample program will help with anyone having troubles.

The Currency Classes

There are several classes for dealing with currencies, there are the Currency, CurrencyTable and CurrencyRate classes. You get these from the DBLink’s GetCurrency, GetCurrencyTable and GetCurrencyRate methods. There is also a GetCurrencyRateTypeDescription method to get the description for a given Currency Rate Type.

The Currency object contains information for a given currency like the description, number of decimals and decimal separator. Combined with the Currency Rate Type, we have a Currency Table entry for each Currency Code and Currency Rate Type. Then for each of these there are multiple CurrencyRate’s for each Currency on a given date.

So if you want to do some custom currency processing for some reason, then these are very useful objects to use. The sample program for this article has lots of examples of using all of these.

Remember to always test your programs against a multi-currency database. A common bug is to do all your testing against SAMINC and then have your program fail at a customer site who is running multi-currency. Similarly it helps to test with a home currency like Japanese Yen that doesn’t have two decimal places.

Summary

This was just a quick article to talk about some of the useful helper functions in our Sage 300 ERP .Net API that help you access various system data quickly. You can perform any of these functions through the Business Logic Views, but since these are used so frequently, they save a lot of programming time.

Written by smist08

July 19, 2014 at 5:48 pm

Using the Sage 300 .Net API from ASP.Net MVC

with 11 comments

Introduction

In this blog posting we are going to switch from using WinForms projects to an ASP.Net MVC project. ASP.Net MVC is Microsoft’s newest Web Development Platform. Be careful not to confuse ASP.Net MVC with ASP.Net. Both are still available and have similar version numbers, both are still part of Visual Studio, but they are quite different. The main problem with ASP.Net is that it doesn’t handle HTML5 web pages with lots of JavaScript well. Generally if you write a lot of JavaScript, the ASP.Net framework becomes pretty much useless. Microsoft then answered these complaints with ASP.Net MVC which is a more modern framework designed to build modern interactive web sites.

ASP.NET-MVC-4

I’ve now installed Visual Studio 2013, so all the projects going forwards are going to be stored in that format. As a result I created the sample application that goes with this article in Visual Studio 2013 using ASP.Net MVC version 5.

MVC versus WinForms

I’ve been using WinForms for the samples in this series of articles on the Sage 300 .Net API because WinForms makes it really easy to write simple programs. I just create a simple project, throw some controls on the form, double click on the control and write some code to do something. Very simple and easy. But for the end result I just get a Windows application that needs to be installed on a given computer along with the .Net framework. ASP.Net MVC is for writing Web applications and it is much more complicated to get started. But the end result is a scalable web application that can be running on a farm of web servers and the UI runs in any browser on the clients computer with nothing else installed except the browser. I can produce UIs just as rich as with WinForms, but now they need to be specified in HTML5/CSS and there are a number of additional capabilities that they get as a result. With WinForms I can create global variables (or leave objects instantiated for the duration of the program), and keep state easily and pass data around freely. With ASP.Net MVC each operation causes new objects to be created and nothing is remembered from call to call (unless I do something special). This way a farm of application servers could be handling the requests each working independently. With WinForms there is no particular structure to the programs I produced, In ASP.Net MVC we are dealing with a very definite MVC structure.

MVC

Model – View – Controller (MVC) is a design pattern for creating more robust and more maintainable user interfaces programs. It separates the concerns into three parts to produce a more modular design.

mvc3

I’m not going to go too much into the details of creating a program and setting these up, since there are dozens of really good tutorials on the Internet like this one. Rather let’s look at our example and talk about the various elements in a real setting.

Sample Program

For a sample program I ported the first WinForms application to ASP.Net MVC. This one is the ARInvEntry project. It basically puts up a web form where you enter the customer number and then hit the “Create Invoice” button and it creates an A/R Invoice for that customer. The main goal here is to get a starting project with a Razor View, a controller and then a Model that calls into the Sage 300 Business Logic using our .Net API.

Note that since Sage 300 ERP is a 32 Bit application, you must run this project as a 32 Bit application, which means if you run this through IIS, you must have the application pool used set to have 32 Bit mode enabled (probably the default application pool).

In this program the main web page is a razor view and it is connected to the model which allows the framework to provide data binding of the customer number into the model, so we don’t need to write any code to move the data from the browser into the model’s member variable. Inside Index.cshtml there is the statement:

@model ARInvEntry.Models.CreateInvoice

 

Which connects the View to the Model. Then the form is defined by:

        @using (Html.BeginForm("CreateInvoice", "Home")) {
        <p>
            Customer Number:
            @Html.TextBoxFor(model => model.CustomerNumber)
        </p>
        <p>           
            <input type="Submit" value="Create Invoice" />
        </p>

Notice that this code is a combination of HTML like <p> as well as the template helper functions like @Html.TextBoxFor (whose argument binds it to the model’s member variable). The Html.BeginForm command connects the “Create Invoice” button to the controller’s action method. “Home” is the name of the controller (which was generated by Visual Studio) and its code is in HomeController.cs. Then the first argument “CreateInvoice” is the method to call when the form is submitted (ie the button is pressed). All the @ tag template functions will be converted to pure HTML by the razor view engine when the page needs to be generated.

It may not look like there is a lot of glue holding things together here, that is because ASP.Net does a lot of things by convention, and so if you don’t specify something explicitly, it has a convention to find it. So generally if things have the same (or similar) names then they go together. This makes coding faster since you just follow some coding conventions and then all the attendant glue is provided automatically and you don’t need to worry about it. This convention over coding approach takes a bit of getting used to, but once you are, it makes development go quite a bit quicker.

In the controller (HomeController.cs) there is the action method which gets called when the user hits submit:

        public ActionResult CreateInvoice(Models.CreateInvoice crInvObj)
        {
            crInvObj.DoCreateInvoice();
            return RedirectToAction("Index");          
        }

Basically this is called with the model as a parameter where the data bound members will already be filled in. So all it has to do is call the method in the model to create the invoice.

It then returns redirecting us back to the main page.

The model has a public member CustomerNumber:

        public string CustomerNumber { get; set; }

 

which is used by the data binding support and populated automatically from the browser. Then there is the DoCreateInvoice method that contains the code from the previous project to create the invoice.

Limitations and Future Work

At this point, this is a very basic MVC project which just really establishes our framework for future work. There is no error reporting (except for writing to the VS output window). After you hit submit, we do the work and then return a complete page. We aren’t doing the searching we supported in the WinForms application.

For the error reporting we can’t just popup a message box from the Model when an error occurs. Generally all UI communications needs to be communicated from the model back to a View. We could add JavaScript to the View to get the response from the model to display a message box (JavaScript alert). We could add a new razor view for displaying error and status messages. We could provide a message area on the main form to fill in. We’ll look at implementing some of these in future articles.

Right now when the invoice is created, we simply refresh the home screen. This isn’t particularly efficient or elegant. Partly this is because of how the submit button is implemented as a form submit. What we would really like to do is to submit an Ajax web service request when the button is pressed and then just update select parts of the screen based on the reply.

If you choose to deploy this project to Azure as a PaaS application, you will run into problems due to the dependency on the Sage 300 .Net API which requires at least workstation setup be installed so we will need to talk about how we deploy a project of this nature to the cloud.

Summary

This article presents a very simple ASP.Net MVC application who’s Model communicates with Sage 300 via our .Net API. Now that we have this starting point, we can start developing some new elements to the project and explore various aspects of writing programs with ASP.Net MVC.

Written by smist08

November 23, 2013 at 9:40 pm

Using Browse Filters in the Sage 300 ERP .Net API

with 3 comments

Introduction

So far as we’ve explored the Sage 300 ERP .Net API we’ve either read rather specific records via their key segments or we’ve browsed through all the records in a table. But more generally applications are interested in processing through subsets of the entire table in an efficient manner. For instance to get all invoices for a given customer or all G/L Accounts for a specific region. In this article we are going to look at some of the mechanisms that we provide to do this.

Generally you browse through a set of records by calling the View’s Browse method to set a filter on the records and then you call the various methods like Fetch, GoTop, GoNext, etc. to iterate through the records. There are also the FilterSelect and FilterFetch routines to iterate through a selection of records. First we’ll talk about the actual filters, then we’ll return to the methods in the API that use them.

Browse Filters

Filters are strings that are passed as parameters to various methods in the API to restrict Views according to various criteria. This would be used when a user enters selection criteria to locate data.

The format of the string expression is:

expression ::= [(…] condition [)…] [Boolean-operator [(…] condition [)…]…]

where:

  • condition ::= field-name relational-operator operand
  • Boolean-operator ::= AND | OR
  • operand ::= <field-name | constant>
  • relational-operator ::= > | < | = | <= | >= | != | LIKE

An example of an expression is:

LASTNAME = “SMITH” AND AGE < 30 OR AGE > 40

where LASTNAME and AGE are field names within the View.

Brackets are allowed. Expressions are evaluated strictly from left to right unless brackets are put in. Thus, in the previous example, the expression is evaluated as:

(LASTNAME = “SMITH” AND AGE < 30) OR AGE > 40

This is true for SMITHs under 30 years of age, and for any person over the age of 40. Put in brackets if you want the other order. For example:

LASTNAME = “SMITH” AND (AGE < 30 OR AGE > 40)

returns only SMITHs, under 30 years of age or over 40.

All the relational operators work with all the field types in the expected way, except for Boolean fields, where only the = and != operators apply.

Note that both operands can be fields. If they are both fields, they must be the same type. When the expression is parsed, if the second operand is a constant, it is converted to the type of the first operand (which is always a field).

The filter mechanism handles all the field types, but since filters are strings, you have to know how to represent the various values as strings. Boolean fields are always TRUE or FALSE. Date fields are always formatted as YYYYMMDD. Time fields are formatted as HHMMSSHH (the last HH is hundredths of a second).

The use of white space around operators, field names and constants is necessary. If a constant is a string and contains white space then it must be enclosed in quotes. The quote character (“) can be embedded by using \” (no other replacements are performed).

The LIKE operator is like the = operator, except the second operand can contain the wild cards % and _, where:

  • % matches any group of characters.
  • _ matches any single character.

For example, LASTNAME LIKE “SM%” is true for any LASTNAME that begins with SM. LIKE can only be used with string field types.

Optimization

The internal mechanisms of our API will optimize the processing of filter strings to either convert them to SQL where clauses when processing the requests via a SQL database or will process them based on choosing the best key segment and traversing that key segment if it needs to do record based processing. Note that you should still pay attention of indexes when using SQL, SQL databases aren’t magical and do require good indexes when filtering on large tables, or the query could take quite a long time.

Using Filters

Sage 300 version 1.0A only ran on Btrieve as a database and the only methods that used filters were Browse and Fetch. You can still use these today and they are very useful methods. As the API evolved and we added more and more SQL support we added to these basic methods to help with performance and to make the API easier to use. The Fetch method behave very much like the Btrieve API behaves. The consequence of this is that it will sometimes do a get greater than equal to and sometimes do a get next. It will do the get GE (or get LE for going backwards) after a browse call or after one of the key segments has its value set. For UI programmers this was annoying since they are often changing the direction frequently and had to handle the case when Fetch would return the current record. To simplify this we added GoTop, GoNext, GoPrev and GoBottom. GoTop will get the first record, GoBottom the last and GoNext will always get the next record, never the current record, similarly for GoPrev. Generally I find these new methods far easier to use, and tend not to use Fetch anymore. Note that these Go methods are part of the COM and .Net APIs and are not part of the regular View API and so may not be available to other API sets, internally the .Net API translates these to the correct Fetch calls to do the job.

To aid in SQL performance we added FilterSelect which will actually issue a SQL query to retrieve a results set with the indicated records. Then FilterFetch browses through these records. For Pervasive.SQL these methods work under the covers just like Browse/Fetch. But note that you can’t update records that you retrieve this way. If you want to update such a record you need to call Read first. This is due to multi-user issues as well as the semantics of how SQL result sets work.

There are FilterCount which will return a count of the records that match the given filter and FilterDelete that delete’s the records that match the filter. Beware that these two functions will translate into SQL statements for SQL databases and will execute as fast as the database server can process them (which might not be fast if there aren’t indexes to exploit), and that Pervasive will iterate through the records to perform the operation. Typically you call FilterDelete only on header or flat type Views, and leave the header to call FilterDelete on the details for you.

Sample Program

In the sample program we add a search box where you can type a string and then the program fills the list box with any records where either customer number or customer name contains the string you typed. The sample does the search first using Browse/GoTop/GoNext and then does the same loop using FilterSelect/FilterFetch. Note that this search isn’t scalable, since the database will need to search the entire customer table looking for these substrings. But often SQL Server can do these sort of searches reasonably quickly as long as the data isn’t too big. Note that adding an index won’t help because we are looking for substrings. The more scalable way to do this would be via full text indexing or via a google like search tool.

Boolean gotOne;
string searchFilter =
     "IDCUST LIKE %" + SearchBox.Text + "% OR NAMECUST LIKE %"
     + SearchBox.Text + "%";

// Fill up the list box using Browse/GoTop/GoNext
ResultsList.Items.Clear();
arCus.Browse( searchFilter, true );

gotOne = arCus.GoTop();
while (gotOne)
{
    ResultsList.Items.Add(
         arCus.Fields.FieldByName("IDCUST").Value + " " +
         arCus.Fields.FieldByName("NAMECUST").Value );
    gotOne = arCus.GoNext();
}

// Go through the records using FilterSelect/FilterBrowse but
 // just print them to the console.
arCus.FilterSelect(searchFilter, true, 0, ViewFilterOrigin.FromStart);
while (arCus.FilterFetch(false))
{
     Console.WriteLine(arCus.Fields.FieldByName("IDCUST").Value +
          " " + arCus.Fields.FieldByName("NAMECUST").Value);
}

Summary

This was a quick introduction to Browse Filters in the Sage 300 ERP API. These form the basis for selecting sets of records and we will be using these extensively in future articles.

 

Written by smist08

November 9, 2013 at 1:10 am

Using the Sage 300 ERP View Protocols with .Net

with 35 comments

Introduction

Last week we looked at opening and composing groups of Views that will work together to accomplish a task. Now we will start to look at how to use the View API via .Net to accomplish common tasks. For Views that work alone this is fairly straight forwards and is just a matter of knowing which methods to call and in which order. For using multiple Views together it is a bit more complicated, but follows a standard pattern and once you get the hang of the pattern it is fairly straight forward also.

This article is just looking at the general algorithms rather than the details. We do provide one concrete example in the sample code. In future articles we will be using these protocols extensively so you will see more concrete examples. Some terms like read and browse/fetch are a bit vague and generally in the API there are several ways to do these which we will look at in future articles. Also some of the mentioned methods have parameters that we will look into in later articles as well.

You can skip any step marked as optional, and sometimes you can skip some steps in simpler situations, it isn’t only till you get to more complicated situations when the need for these steps become more evident. Part of using these algorithms or protocols is to ensure good multi-user throughput, so although another order of steps might work, it might lead to poor performance. So these tend to be a combinations of best practices along with some absolutely required parts.

If you’ve worked with Sage 300 ERP for any length of time, you might know we have an Init method which used to do the work of RecordCreate and RecordClear. We’ve deprecated this method and I’ve tried to use all the new up to date ways of doing things in this article. But if you do see an error in any of these articles, just leave a comment and I’ll fix it.

View Classes

Basically there are six classes of Views where all the Views in the same class use the same protocol for their basic operations. Here we will go through each class quickly with quick algorithms of how to accomplish various standard CRUD operations.

The classes of views are:

  • Flat
  • Ordered header/detail
  • Sequenced header/detail
  • Batch/header/detail
  • Processing
  • Detail

Flat Views

Flat Views are Views that do not need to be composed with any other Views. The key for a flat view may have multiple segments. Most setup Views are flat. Most master Views can be used as flat Views if you aren’t changing the details. Generally you will use these protocols quite a lot.

Insertion protocol

  1. RecordClear the view to initialize the record.
  2. Set values to the fields, including the key fields.
  3. Insert the record.
  4. If there are more records to insert, go to step 1.

Deletion protocol

  1. Set key values to the key fields.
  2. Read or Browse/Fetch the record.
  3. Delete the record.
  4. If there are more records to delete, go to step 1 to Read another record, or go to step 2 to Fetch the next record to delete. (Optional.)

Update protocol

  1. Set key values to the key fields.
  2. Read or Browse/Fetch the record.
  3. Set values to the fields to be changed.
  4. Update the record.
  5. If there are more records to update, go to step 1 or 2 depending on whether Read or Browse/Fetch is used. (Optional.)

Ordered Header/Detail Views

This is the protocol for two Views composed together where one is the header and the other is the detail. The details are kept in key order (as opposed to position order). You won’t see that many Views of this type, typically these are the header/detail Views used in setup forms.

The detail View’s primary key always starts with the primary key of the header View and then adds its own key segments. This then establishes which details belong to the header. Whenever a key field in the header is set, it is automatically also set in the detail View. When you Browse/Fetch through the detail records, you only get the details for the current header View.

Insertion protocol

  1. RecordClear header to initialize the fields.
  2. Set the fields in the header.
  3. RecordClear detail to initialize the fields.
  4. Set the fields in the detail.
  5. Insert detail.
  6. Go to step 3 if there are more details.
  7. Insert header. (This will Post the details)

Update protocol (Include deleting details)

  1. Set header key into header view.
  2. Read or Browse/Fetch the header view to get to the header.
  3. Set the fields to be updated in the header view. (Optional.)
  4. Set detail key into the detail view.
  5. Read or Browse/Fetch the detail view to get to the detail.

The first n segments (where n is the number of segments in the header key) of the detail key will have been Put by the header because of the composition.

  1. Set the fields to be updated in the detail view. (Optional.)
  2. Update or Delete the detail.
  3. Go to step 4 to update another detail.
  4. Update header. (This will Post the details)

Protocol for deleting header

  1. Set the header key in the header view.
  2. Read or Browse/Fetch the header. This causes the audit stamp to be read.
  3. Delete the header. (This will cause all details in the header to be deleted.)

Protocol for browsing

  1. Set the header key in the header view.
  2. Read or Browse/Fetch the header.
  3. Get fields from the header view.
  4. Browse/Fetch the detail view. (Browse/Fetch will not go beyond the header key.)

Sequenced Header/Detail Views

This class of Views is the most common for document entry in the operations modules. The detail View has all the key segments from the header View and then adds one numeric type field to act as the sequence number. The last segment of the header key is also numeric and contains the header number. Note that these numbers can be stored in string type fields, in which case they are masked to only contain the decimal digits ‘0’ to ‘9’. Often you can either set the header key or you can get the header to generate a key which means it allocates the next header number. The header key segments are always automatically set in the detail view and when you browse through the detail records you only get the records for the current header.

Insertion protocol

  1. If the next header number is generated by the view, Use RecordCreate to generate the next available header number. If the header number is specified by the caller, use RecordClear to initialize the fields of the header, then Set the header number in the header view.
  2. Set the fields in the header.
  3. RecordClear detail to initialize the fields.
  4. Set zero into the detail number field to insert from the start.
  5. Set values in the other detail fields.
  6. Insert detail.
  7. Go to step 5 until no more detail.
  8. Insert the header. (This will do a Post of the details.)

Note that an insert of the details does an “insert after”. So because we don’t reset the sequence number in the detail each insert will be after the previous one. If we want to insert after a specific record then put that sequence number into the key field. A common bug is to set the key field to 0 each time which then causes the records to be inserted in reverse order since each new one is inserted at the beginning.

Protocol for update (Include deleting details)

  1. Set header key into header view.
  2. Read or Browse/Fetch the header view to get to the header.
  3. Set the fields to be updated in the header view. (Optional.)
  4. Set detail key into the detail view.
  5. Read or Browse/Fetch the detail view to get to the detail. The header number in the detail view will have been set by the header.
  6. Set the fields to be updated in the detail view. (Optional.)
  7. Update or delete the detail.
  8. Go to step 4 to process another detail.
  9. Update the header. (This will do a Post of the details.)

Protocol for deleting header

  1. Set the header key in the header view.
  2. Read or Browse/Fetch the header. This causes the audit stamp to be read.
  3. Delete the header. (This causes all details in the header to be deleted.)

Protocol for browsing

  1. Set the header key in the header view.
  2. Read or Browse/Fetch the header.
  3. Get fields from the header view.
  4. Browse/Fetch the detail view. (This will not go beyond the header key.)

Batch/Header/Detail Views

This class of Views contain a batch View, header View and detail View. These are used for document entry in the Financial Modules. The header/detail part is just the sequenced header/detail class indicated above and works pretty much the same way. In a similar manner the Batch View has some key segments, one of which is the batch number which is a numeric type (or string where only ‘0’ through ‘9’ are allowed). Then the header shares the batch number and adds its header number and then the detail shares the header’s keys adding its detail sequence number. The batch number is usually generated and rarely can be specified for a new batch. Again the key fields from a higher level View are automatically set to the lower level Views so when you browser headers, you only see the headers for the current batch and when you browse details you only see details for the current header.

Insertion protocol

  1. RecordCreate batch to get the next available key for the batch.
  2. Set the fields in the batch.
  3. Update batch. (The batch record is already inserted by the RecordCreate call, in step 1.)
  4. RecordCreate header to get the next available key for the header. The key for the batch in the header view is assumed to be set already.
  5. Set the fields in the header.
  6. RecordCreate detail to initialize the fields.
  7. Set zero (or last detail number) into the detail key field to insert from the start.
  8. Set values in the other detail fields.
  9. Insert detail.
  10. Go to step 6 until no more detail.
  11. Insert header. (This will do a Post of the details.)
  12. Go back to step 4 to add another header.

Protocol for update (Include deleting details)

  1. Set batch key into the batch view.
  2. Read the batch record.
  3. Set the fields to be updated in the batch view. (Optional.)
  4. Update batch view. (Optional.)
  5. Set header key into header view.
  6. Read or Browse/Fetch the header view to get to the header. The key for the batch in the header view is assumed to be set already.
  7. Set the fields to be updated in the header view. (Optional.)
  8. Set detail key into the detail view.
  9. Read or Browse/Fetch the detail view to get to the detail. The key for the batch and the header in the detail view are assumed to be set already.
  10. Put the fields to be updated in the detail view. (Optional.)
  11. Update or Delete the detail.
  12. Go to step 8 to update another detail.
  13. Update the header. (This will do a Post of the details.)
  14. Go to step 5 to update another header.

Protocol for deleting batch

  1. Set the batch key in the batch view.
  2. Read or Browse/Fetch the batch. This causes the audit stamp to be read.
  3. Delete the batch. (This causes all headers and details in that batch to be deleted.)

Protocol for deleting header

  1. Set the batch key in the batch view.
  2. Read or Browse/Fetch the batch.
  3. Set the header key in the header view.
  4. Read or Browse/Fetch the header. This causes the audit stamp to be read.
  5. Delete the header. (The details will be deleted.)

Protocol for browsing

  1. Set the batch key in the batch view.
  2. Read or Browse/Fetch the batch.
  3. Get fields from the batch view.
  4. Browse/Fetch the header view. (This will not go beyond the batch key in the batch view.)
  5. Get fields from the header view.
  6. Browse/Fetch the details. (This will not go beyond thebatch and the header keys.)

Process Views (SuperViews)

This class of views is mainly used to implement procedures that do not involve editing data. Examples are consolidation, year-end procedures, and posting a batch.

Typically you set some fields in the View and the call Process to do the operation. These tend to be the simplest Views to use and yet often do the most processing.

What Class is my View?

OK, but now I want to program some set of Views, but what class are they? One way to find out is to look in the application’s xx.ini file. For instance for say A/R 6.1A, look in “Sage 300 Program Files”\ar61a\ar.ini and at the top is an [Objects] section that lists all the View classes for A/R. This is here so standards system components know what protocol to use on a given set of Views. Further you can check how to make the calls by doing a macro recording of the UI doing the operations you are interested in.

Sample Program

The sample program ARInvEntryWinForms (located here) has been updated to exactly follow the insert protocol for a batch/header/detail View with all the steps from above added as comments.

// 1. RecordCreate batch to get the next available key for the batch.
arInvoiceBatch.RecordCreate(ViewRecordCreate.Insert);

// 2. Set the fields in the batch.
arInvoiceBatch.Fields.FieldByName("BTCHDESC").SetValue("My new batch", false);

// 3. Update batch. (The batch record is already inserted by the RecordGenerate call, in step 1.)
arInvoiceBatch.Update();

// 4. RecordCreate header to get the next available key for the header.
//    The key for the batch in the header view is assumed to be set already.
arInvoiceHeader.RecordCreate(ViewRecordCreate.DelayKey);

// 5. Set the fields in the header.
arInvoiceHeader.Fields.FieldByName("IDCUST").SetValue(custNumber.Text, false);

// 6. RecordCreate detail to initialize the fields.
arInvoiceDetail.RecordCreate(ViewRecordCreate.NoInsert);

// 7. Set zero (or last detail number) into the detail key field to insert from the start.
arInvoiceDetail.Fields.FieldByName("CNTLINE").SetValue(0, false);

// 8. Set values in the other detail fields.
arInvoiceDetail.Fields.FieldByName("IDITEM").SetValue("CA-78", false);

// 9. Insert detail.
arInvoiceDetail.Insert();   // Insert the detail line (only in memory at this point).

// 11. Insert header. (This will do a Post of the details.)
arInvoiceHeader.Insert();

Summary

This was a quick introduction to the View Protocols on how to do basic CRUD operations on the various classes of Views. These are all one level descriptions, but can be generalized to more complicated cases like header-detail-detail views where the middle detail acts as a header to the second detail. Generally the ideas of these protocols will be used extensively in all future articles.

Written by smist08

November 2, 2013 at 6:22 pm

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