Stephen Smith's Blog

Musings on Machine Learning…

Using the Sage 300 ERP View Protocols with .Net

with 32 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.

Advertisements

Written by smist08

November 2, 2013 at 6:22 pm

32 Responses

Subscribe to comments with RSS.

  1. […] 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…  […]

  2. Hi,
    Can i get any example for creating a new customer record through .net api

    Thanks & Regards
    Phaneendra

    Phaneendra

    November 5, 2013 at 11:55 am

  3. […] my previous article on the View Protocols, any optional field view is an ordered detail where its header is whatever it is an optional field […]

  4. 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?

    ‘=================================================================================
    Public Sub Launch_Batch_Screen(Optional Modal As Boolean = False, Optional hWnd As Long = 0)

    Try
    ‘—————————————————————————————————————————-
    Dim strROTOID = “GL2100”
    Dim strCodebase As String = “”
    Dim strObjHandle As String = “”
    Dim strClass As String = “”
    Dim DF_DYNAMIC As Integer = 0
    Dim sObjectKey = “BATCH=” + “171” + vbLf + “ENTRY=” + “2”
    Dim strArgumentsFormat As String = “/clsid:{0} /codebase:””{1}”” /objecthandle:{2}”
    Dim strContainerEXE As String = ACCPAC_Session.ProgramsPathOnServer + “\runtime\A4WCONTAINERXP.EXE”
    ‘—————————————————————————————————————————-
    If ACCPAC_Session.GetObjectCLSID(strROTOID, DF_DYNAMIC, strClass, strCodebase) Then
    ACCPAC_Session.CreateObjectHandle(strROTOID, sObjectKey, strObjHandle, strClass, strCodebase)
    Dim arguments As String = String.Format(strArgumentsFormat, strClass, strCodebase, strObjHandle)
    Dim process As Process = process.Start(strContainerEXE, arguments)
    End If
    ‘—————————————————————————————————————————-

    Catch ex As Exception
    Call Error_Handler(“Lauching Batch “)
    End Try

    End Sub

    ‘==================================================================================

    groomtech

    May 8, 2014 at 7:54 am

    • It looks like the keys are BATCHID and ENTRYNBR for GL2100. If you use these in your objectkey then it should work.

      smist08

      May 11, 2014 at 7:42 pm

  5. […] 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 […]

  6. 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.

    Karen

    December 17, 2015 at 12:13 pm

  7. Hi Stephen, we are currently using the ACCPAC Com API to read and write data to and from the GL module. Everything is working as intended and i found the blog to be very useful.

    We have however run into a scenario where we get a COM Exception when the software runs on Windows 10. The exception is as follows: Exceotion form HRESULT: 0x800200005 (DISP_E_TYPEMISMATCH).

    Any ideas on the issue ?

    You will probably need more information from me but i would rather share that with you via email.

    Jaco Visagé

    January 25, 2016 at 9:30 am

    • 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.).

      smist08

      January 25, 2016 at 4:32 pm

  8. Thank you Stephen!

    I am currently trying to run what I’ve done on another Sage 300 instance, but I’m getting this Exception

    Unhandled Exception: ACCPAC.Advantage.SessionException —> System.Runtime.Inter
    opServices.COMException: Exception from HRESULT: 0xFFFFFFFE

    on this line

    sageSession.Init(“”, “XY”, “XY1000”, “62A”);

    I read the documentation and everything looks fine to me. The error message is not helping a lot.

    Do you have any idea?

    Thank you!

    Egidio

    aegidius157

    April 10, 2016 at 6:47 pm

    • 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.

      smist08

      April 10, 2016 at 10:57 pm

  9. Thank you Stephen!

    I think I’m close. I fetched the .dll from the Sage 300 instance and now I am getting a logon error. I think that’s because I’m guessing the organization ID.

    Is there a way to find this value from the UI?

    aegidius157

    April 11, 2016 at 8:43 am

  10. Ok, I found the organization ID. Now, using the same credentials from the UI I can access while using the same ones from my application I get this error:

    Unhandled Exception: ACCPAC.Advantage.SessionException —> System.Runtime.Inter
    opServices.COMException: Invalid Signon Information.

    Make sure you supply the correct User ID and Password.
    at AccpacCOMSVR.AccpacSvrSessionClass.Open(String UserIdentifier, String Pass
    word, String DataBase, DateTime SessionDate, Int32 Flags, String Reserved, Boole
    an CheckStatus, Byte[]& SessionInfo)
    at ACCPAC.Advantage.Server.Session.Open(String userID, String password, Strin
    g orgID, DateTime sessionDate, Int32 flags, Boolean checkStatus)

    aegidius157

    April 11, 2016 at 9:09 am

    • One thing is that you need to upper case the password. To the API its case sensitive (but the UI always upper cases it behind the scenes).

      smist08

      April 11, 2016 at 8:08 pm

  11. Hi Stephen,

    Can Sage 300 store more then one Contact per Customer?

    aegidius157

    April 11, 2016 at 3:45 pm

    • Not in the standard field. But you could define an optional field perhaps using text with their name and email in it.

      smist08

      April 11, 2016 at 8:07 pm

  12. Thank you Stephen. With your help I managed to solve my problem.

    aegidius157

    April 12, 2016 at 9:34 am

  13. Hi Stephen,

    I have this code for creating or updating a customer: http://pastebin.com/YA2vGryB

    The Insert() method works fine, while I’m getting an error when Update() is called. The error I’m getting is:
    “Record has been modified by another program”

    I cannot find any information about this error and I don’t have any concurrency in my system.

    aegidius157

    May 11, 2016 at 10:27 am

    • You have to do a read operation before doing an update. Try adding a read call first. In normal operations it needs to be a fetch or read first (or a lock variety).

      smist08

      May 12, 2016 at 2:53 pm

  14. Hi Stephen,

    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?

    Thank you,

    Egidio

    Egidio Caprino

    August 4, 2016 at 6:48 pm

    • In the API you can just catch the exception, check the error code and if its a warning then safely ignore it (perhaps clear the error stack as well).

      smist08

      August 8, 2016 at 3:07 pm

      • Thank you Stephen! How do I clear the error stack?

        Egidio Caprino

        August 8, 2016 at 3:13 pm

      • session.Errors.Clear()

        smist08

        August 8, 2016 at 3:16 pm

      • OK, I’ll try that. Thank you Stephen!

        Egidio Caprino

        August 8, 2016 at 3:17 pm

      • Hi Stephen,

        I tried but now I’m getting the Exception when I call the Insert method. Is there any way to suppress warnings at all?

        Thank you,

        Egidio

        Egidio Caprino

        August 9, 2016 at 1:24 pm

      • 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.

        smist08

        August 9, 2016 at 5:01 pm

      • 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.

        Egidio Caprino

        August 9, 2016 at 1:28 pm

      • If you think the price list information is there, it might be you are missing some currency exchange rates. Perhaps have a look at these as well.

        smist08

        August 9, 2016 at 5:02 pm

  15. Hi Stephen,

    Is there any website with the documentation of each Sage 300 view? Like, list of fields, which are required, the types, etc…?

    Thank you,

    Egidio

    Egidio Caprino

    August 19, 2016 at 10:05 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: