Using the Sage 300 ERP View Protocols with .Net

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

RecordClear the view to initialize the record.

Set values to the fields, including the key fields.

Insert the record.

If there are more records to insert, go to step 1.

Deletion protocol

Set key values to the key fields.

Read or Browse/Fetch the record.

Delete the record.

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

Set key values to the key fields.

Read or Browse/Fetch the record.

Set values to the fields to be changed.

Update the record.

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

RecordClear header to initialize the fields.

Set the fields in the header.

RecordClear detail to initialize the fields.

Set the fields in the detail.

Insert detail.

Go to step 3 if there are more details.

Insert header. (This will Post the details)

Update protocol (Include deleting details)

Set header key into header view.

Read or Browse/Fetch the header view to get to the header.

Set the fields to be updated in the header view. (Optional.)

Set detail key into the detail view.

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.

Set the fields to be updated in the detail view. (Optional.)

Update or Delete the detail.

Go to step 4 to update another detail.

Update header. (This will Post the details)

Protocol for deleting header

Set the header key in the header view.

Read or Browse/Fetch the header. This causes the audit stamp to be read.

Delete the header. (This will cause all details in the header to be deleted.)

Protocol for browsing

Set the header key in the header view.

Read or Browse/Fetch the header.

Get fields from the header view.

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

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.

Set the fields in the header.

RecordClear detail to initialize the fields.

Set zero into the detail number field to insert from the start.

Set values in the other detail fields.

Insert detail.

Go to step 5 until no more detail.

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)

Set header key into header view.

Read or Browse/Fetch the header view to get to the header.

Set the fields to be updated in the header view. (Optional.)

Set detail key into the detail view.

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.

Set the fields to be updated in the detail view. (Optional.)

Update or delete the detail.

Go to step 4 to process another detail.

Update the header. (This will do a Post of the details.)

Protocol for deleting header

Set the header key in the header view.

Read or Browse/Fetch the header. This causes the audit stamp to be read.

Delete the header. (This causes all details in the header to be deleted.)

Protocol for browsing

Set the header key in the header view.

Read or Browse/Fetch the header.

Get fields from the header view.

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

RecordCreate batch to get the next available key for the batch.

Set the fields in the batch.

Update batch. (The batch record is already inserted by the RecordCreate call, in step 1.)

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.

Set the fields in the header.

RecordCreate detail to initialize the fields.

Set zero (or last detail number) into the detail key field to insert from the start.

Set values in the other detail fields.

Insert detail.

Go to step 6 until no more detail.

Insert header. (This will do a Post of the details.)

Go back to step 4 to add another header.

Protocol for update (Include deleting details)

Set batch key into the batch view.

Read the batch record.

Set the fields to be updated in the batch view. (Optional.)

Update batch view. (Optional.)

Set header key into header view.

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.

Set the fields to be updated in the header view. (Optional.)

Set detail key into the detail view.

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.

Put the fields to be updated in the detail view. (Optional.)

Update or Delete the detail.

Go to step 8 to update another detail.

Update the header. (This will do a Post of the details.)

Go to step 5 to update another header.

Protocol for deleting batch

Set the batch key in the batch view.

Read or Browse/Fetch the batch. This causes the audit stamp to be read.

Delete the batch. (This causes all headers and details in that batch to be deleted.)

Protocol for deleting header

Set the batch key in the batch view.

Read or Browse/Fetch the batch.

Set the header key in the header view.

Read or Browse/Fetch the header. This causes the audit stamp to be read.

Delete the header. (The details will be deleted.)

Protocol for browsing

Set the batch key in the batch view.

Read or Browse/Fetch the batch.

Get fields from the batch view.

Browse/Fetch the header view. (This will not go beyond the batch key in the batch view.)

Get fields from the header view.

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.

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.

35 Responses

[…] 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 c… […]

I am using the VB.NET 2012 code to drill down to a GL batch in Sage 300 ERP 2012. I am only getting a blank GL screen with no records. However, the same code is working for AR and AP with ROTOID = AR2100 and AP2100. What could be wrong with this code?

How to drill down the screen what defined in Roto.dat as Object instead of ComObject and if the screen is simple EXE application? It’s mean what screen can’t have ClassID and it’s impossible to use CreateObjectHandle.

You probably need to find the exact line that is throwing the exception and the value being passed. It could be due to a value out of range or perhaps due to the regional settings on that computer (date format, decimal separator, etc.).

Usually this is some sort of installation problem, since this is the first API call to actually access Sage 300. You could try running sage300\runtime\regacc.exe to ensure all the activeX controls are registered properly. You could try running sage300\runtime\smdotnet.exe to ensure the .Net API components are installed (note it will offer to uninstall them if they are installed). Also having two separate installs of Sage 300 on the same machine can cause problems of this nature.

I’m experiencing a strange problem with Sage 300 API. When I open a Order from the UI and I add a new line Item (Order Detail), I get this warning:

“Information needed to set up the unit price does not exist for price list EUA, item 046200 in currency EUR. The price list record may not exist, or details have not been entered to price multiple units of measure.”

After the warning, I can save the line item and save the Order.

From the API, it throws an Exception. Do you know if there is a way to suppress the warnings when using the APIs?

You might check the session errors to see if you are getting the same message. Generally warnings don’t cause an exception (though sometimes they do). It might be that you are getting something quite different.

Sorry Stephen, do you also have an idea why I get this error message when creating an order line item?

Information needed to set up the unit price does not exist for price list EUA, item 046200 in currency EUR. The price list record may not exist, or details have not been entered to price multiple units of measure.

It’s been a while since we’ve connected. I hope that retiring from Sage has been as great as you hoped. I also hope that you are still monitoring this blog!

I have a VBA macro in Sage 300 that reads a csv file from Bill.com and creates a payment batch – the invoice batch is created separately and is working fine. The problem comes when I try to insert the first header record in the batch (after the detail record is inserted, of course). I get the error: “Invoice Number cannot be blank.” I’ve made the test data a single record for simplicity in debugging and checked that the values of the detail fields are present and valid – they are. Any ideas?