Stephen Smith's Blog

Musings on Machine Learning…

Composing Views in the Sage 300 ERP .Net API

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

Advertisements

Written by smist08

October 27, 2013 at 3:31 am

21 Responses

Subscribe to comments with RSS.

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

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

    • initialize and after assign

      AccpacCOMAPI.AccpacView ARCUSTOMER1header ;
      AccpacCOMAPI.AccpacView ARCUSTOMER1detail;

      need to convert VB6 to C# the below line of code

      ARCUSTOMER1header.Compose Array(ARCUSTOMER1detail)
      ARCUSTOMER1detail.Compose Array(ARCUSTOMER1header)

      how to convert this above code in C#

      Shubham

      June 23, 2014 at 6:03 am

      • Have a look at the code referenced in the article. It would be something like:

        ARCustomer1Header.Compose(new ACCPAC.Advantage.View[] { ARCustomer1Detail });
        ARCustomer1Detail.Compose(new ACCPAC.Advantage.View[] { ARCustomer1Header });

        smist08

        June 23, 2014 at 2:27 pm

      • Thanks for quick reply, I already refer the above and tried to implement in same way but still it will thrown an exception of invalid argument.

        Shubham

        June 24, 2014 at 4:00 am

    • How to fix this error “”. Error HRESULT E_FAIL has been returned from a call to a COM component.

      Juwel

      August 27, 2015 at 6:48 am

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

  4. Hi,
    I was able to create an order given the order number and automatically create an invoice from it using API calls
    But how I can control the invoice number in this case?
    Thanks!

    Roberto

    September 5, 2014 at 4:54 pm

  5. I write this cod but got this error

    Error HRESULT E_FAIL has been returned from a call to a COM component.

    how can i fiex this problem

    session = new Session();
    session.Init(“”, “XY”, “XY1000”, “62A”);
    session.Open(“ADMIN”, “ADMIN”, “SAMINC”, DateTime.Today, 0);
    DBLink mDBLinkSysRW;
    mDBLinkSysRW = session.OpenDBLink(DBLinkType.System, DBLinkFlags.ReadWrite);
    DBLink mDBLinkSysRW;
    mDBLinkSysRW = session.OpenDBLink(DBLinkType.System, DBLinkFlags.ReadWrite);

    BKTRANSENT1header.Compose(new ACCPAC.Advantage.View[] { BKTRANSENT1detail });
    BKTRANSENT1detail.Compose(new ACCPAC.Advantage.View[] { BKTRANSENT1header });

    BKTRANSENT1header.Browse(“”, true);
    BKTRANSENT1header.Fields.FieldByName(“SEQUENCENO”).SetValue(“0”, false);

    temp = BKTRANSENT1header.Exists;

    BKTRANSENT1header.Init();
    temp = BKTRANSENT1header.Exists;
    temp = BKTRANSENT1header.Exists;
    temp = BKTRANSENT1header.Exists;
    temp = BKTRANSENT1header.Exists;

    BKTRANSENT1header.Fields.FieldByName(“BANK”).SetValue(“CCB”, false);
    temp = BKTRANSENT1header.Exists ;
    temp = BKTRANSENT1header.Exists ;
    temp = BKTRANSENT1header.Exists;

    BKTRANSENT1header.Fields.FieldByName(“TRANSTYPE”).SetValue(“2”, false);
    BKTRANSENT1header.Fields.FieldByName(“TYPE”).SetValue(“1”, false);
    BKTRANSENT1detail.RecordClear();

    BKTRANSENT1detail.RecordCreate( 0);
    temp = BKTRANSENT1header.Exists;
    BKTRANSENT1detail.Fields.FieldByName(“GLACCOUNT”).SetValue(row[0], false);
    amount=Convert.ToDecimal(row[9]) + Convert.ToDecimal(row[10]);
    BKTRANSENT1detail.Fields.FieldByName(“SRCEAMT”).SetValue(amount, false);
    BKTRANSENT1detail.Fields.FieldByName(“REFERENCE”).SetValue(dr[0].ItemArray[9], false);
    BKTRANSENT1detail.Fields.FieldByName(“COMMENT”).SetValue(“na”, false);
    BKTRANSENT1detail.Fields.FieldByName(“BIGCOMMENT”).SetValue(dr[0].ItemArray[13], false);
    BKTRANSENT1detail.Insert();

    temp = BKTRANSENT1header.Exists;
    BKTRANSENT1header.Insert();
    temp = BKTRANSENT1header.Exi

    Juwel

    August 27, 2015 at 6:51 am

  6. Hi i am also getting the same error.
    Steve please if you can help on this.

    Error HRESULT E_FAIL has been returned from a call to a COM component.

    Arpit

    February 5, 2017 at 10:18 pm

  7. Hi Stephen,
    I have created record in invoice header tables which are AR0031,AR0032..etc.

    I have a query regarding order details. I need to update the order details by adding a new item into order details table which OESHID and OEORDD.
    can you please help me out that how can i add a new line in above mentioned table by using c#.net using Sage AccPac API. I am using “AccpacCOMAPI” for integration as Accpac.Advantage is not working for me.

    Thanks in Advance.

    Amit Sharma

    March 1, 2017 at 5:06 am

    • You insert records using the insert command. However note that you have to follow the view protocol to do that (have a look at: https://smist08.wordpress.com/2013/11/02/using-the-sage-300-erp-view-protocols-with-net/). Ie you need to read the header, set the detail the record to insert after, insert the record and then update the header to update the database. Also depending on the status of the order/shipment/invoice (and perhaps based on some options settings) you might not be allowed to edit the document.

      smist08

      March 1, 2017 at 9:57 pm

      • Hi Stephen,
        Hope you are doing well. I am getting an error while creating view for Shipment tables. Error

        Shipment Kitting Detail

        This view’s foreign key declaration is incompatible with the composite view ‘Compose Views’.

        Below is my code

        AccpacCOMAPI.AccpacView OEORD1header;
        AccpacCOMAPI.AccpacView OEORD1detail1;
        AccpacCOMAPI.AccpacView OEORD1detail2;
        AccpacCOMAPI.AccpacView OEORD1detail3;
        AccpacCOMAPI.AccpacView OEORD1detail4;
        AccpacCOMAPI.AccpacView OEORD1detail5;
        AccpacCOMAPI.AccpacView OEORD1detail6;
        AccpacCOMAPI.AccpacView OEORD1detail7;
        AccpacCOMAPI.AccpacView OEORD1detail8;
        AccpacCOMAPI.AccpacView OEORD1detail9;
        AccpacCOMAPI.AccpacView OEORD1detail10;
        AccpacCOMAPI.AccpacView OEORD1detail11;
        AccpacCOMAPI.AccpacView OEORD1detail12;
        AccpacCOMAPI.AccpacView OEORD1detail13;

        mDBLinkCmpRW.OpenView(“OE0692”, out OEORD1header);
        mDBLinkCmpRW.OpenView(“OE0691”, out OEORD1detail1);
        mDBLinkCmpRW.OpenView(“OE0745”, out OEORD1detail2);
        mDBLinkCmpRW.OpenView(“OE0190”, out OEORD1detail3);
        mDBLinkCmpRW.OpenView(“OE0694”, out OEORD1detail4);
        mDBLinkCmpRW.OpenView(“OE0704”, out OEORD1detail5);
        mDBLinkCmpRW.OpenView(“OE0709”, out OEORD1detail6);
        mDBLinkCmpRW.OpenView(“OE0708”, out OEORD1detail7);
        mDBLinkCmpRW.OpenView(“OE0702”, out OEORD1detail8);
        mDBLinkCmpRW.OpenView(“OE0703”, out OEORD1detail9);
        mDBLinkCmpRW.OpenView(“OE0706”, out OEORD1detail10);
        mDBLinkCmpRW.OpenView(“OE0707”, out OEORD1detail11);
        mDBLinkCmpRW.OpenView(“OE0705”, out OEORD1detail12);
        mDBLinkCmpRW.OpenView(“OE0699”, out OEORD1detail13);

        OEORD1header.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1detail1, null, OEORD1detail3, OEORD1detail2, OEORD1detail5 });
        OEORD1detail1.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1header, OEORD1detail8, OEORD1detail12, OEORD1detail9, OEORD1detail6, OEORD1detail7 });
        OEORD1detail2.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1header });
        OEORD1detail3.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1header, OEORD1detail1 });
        OEORD1detail4.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1header });
        OEORD1detail5.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1header });
        OEORD1detail6.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1detail1 });
        OEORD1detail7.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1detail1 });
        OEORD1detail8.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1detail1 });

        //Getting the above mentioned error in below line
        OEORD1detail9.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1detail1, OEORD1detail10, OEORD1detail11, OEORD1detail13 });
        OEORD1detail10.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1detail9 });
        OEORD1detail11.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1detail9 });
        OEORD1detail12.Compose(new AccpacCOMAPI.AccpacView[] { OEORD1detail1 });

        Can you please help me out that what i can do with the above code?

        Thanks in advance.

        Amit Sharma

        March 7, 2017 at 1:58 am

  8. Hi Stephen,
    I have resolved the above error but getting an error while updating QTYSHIPPED of OESHID table.
    Error
    —————————

    —————————
    Shipment Detail Serial Number

    Invalid view operation requested: 104.
    —————————
    OK
    —————————

    below is the where i am getting the error :

    OEORD1detail1.Fields.FieldByName[“QTYSHIPPED”].set_Value(5);

    Please help

    Amit Sharma

    March 7, 2017 at 2:23 am

  9. HI Stephen,
    How can i bypass warnings which are appearing during order entry creation.
    Like QTYSHIPPED should not be greater than QTYORDERED.

    Please Help

    Amit Sharma

    March 8, 2017 at 5:46 am

    • You can just clear the error stack rather than displaying it.

      smist08

      March 8, 2017 at 4:04 pm

      • Hi Stephen,
        i dont want to clear the error stack, i want to update the QTYSHIPPED without any warning.

        how can i do that.

        Thanks

        Amit Sharma

        March 10, 2017 at 12:31 pm

      • Not sure what the problem is. Warning don’t prevent things getting saved. Usually its just a matter of displaying the message or just suppressing it.

        smist08

        March 10, 2017 at 9:52 pm

  10. Hi Stephen,

    Not sure what the problem is. Warning don’t prevent things getting saved. Usually its just a matter of displaying the message or just suppressing it.

    So how can i suppress the warnings using .net API.

    Thanks

    Amit Sharma

    March 11, 2017 at 8:14 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: