Working with Views in Sage 300 ERP ( Accpac )

In Sage 300 ERP views are used as a standard way of manipulating and retrieving data. You will also need to use views in order to execute some commands. Views in Sage 300 closely resemble MVC and MVVM patterns that have been used when simplifying user interfaces. Becoming familiar with these patterns may help you to understand how things work in Sage 300.
Views match up closely to what you see on the Sage 300 user interface. However, you may find that the names for certain fields are not the same as you see on your screen. Most fields in Sage 300 are abbreviated and some are named completely different from the label on the forms.

Why not use the database?

If you are handy with SQL and know how to find the Sage 300 databases then you might be tempted to circumvent the SDK all together and go straight for accessing Sage via the database. Although this may be necessary in some situations, I recommend that you save this as a last resort for several reason.

The business logic will be bypassed

There is actually a lot going on behind the scenes when you are using the application. If you go straight to the database then the business logic can be bypassed. This can mean that you will have to recalculate calculated fields, updated other tables based on your changed values, and/or write your own data validation routines.
I would like to point out that this can be a problem with any application that you work with. It is typically poor practice to go straight to the database when an SDK is available. Again, if its the only way we can accomplish the task then it is acceptable.

You might corrupt the database

Sage 300 does things a little differently then standard SQL, so you may inadvertently insert a value that it does not expect. For instance a numeric field can be stored as a string and if an extra space or a leading zero is not populated correctly then you can cause unexpected results.

Distribution can be a little more tricky

If you are writing triggers, stored procedures etc. then you could make distributing your application a little difficult. For small one off solutions this might be a valid tradeoff. However, if you are distributing to several users then it can become a problem. What if you have credential problems? What if they revert to an old version of the database?

Using Views

Please refer to the previous example where we connected to Sage 300 and retrieved the current version information if you need help getting started. Instead of retrieving a version, this time we will retrieve all the data in the view. The code is as follows:

using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Example2
{
    class Program
    {
        static void Main(string[] args)
        {
            //Connect to the sample company
            var accpacSession = new AccpacSession();
            accpacSession.Init("", "XY", "XY1000", "61A");
            accpacSession.Open("ADMIN", "ADMIN", "SAMINC", DateTime.Now, 0, String.Empty);

            //Open the db link. This should be the Company link type and not the system. 
            var dbLink = accpacSession.OpenDBLink(tagDBLinkTypeEnum.DBLINK_COMPANY, tagDBLinkFlagsEnum.DBLINK_FLG_READONLY);

            AccpacView view;
            int result = dbLink.OpenView("AR0024", out view);

            //This just says find all records. We pass it an empty string for the search expression
            view.Browse(String.Empty, true);
            
            while (view.Fetch())
            {
                string id = view.Fields.FieldByName["IDCUST"].get_Value().ToString();
                string name = view.Fields.FieldByName["NAMECUST"].get_Value().ToString();
                string phone = view.Fields.FieldByName["TEXTPHON1"].get_Value().ToString();
                Console.WriteLine("{0}\t{1}\t{2}\t", id, name, phone);
            }

            //Strange things will happen if you do not close both the view and the db link.
            view.Close();
            dbLink.Close();
            
            //Always close the session when you are done.
            accpacSession.Close(); 
        }
    }
}

The above example shows a very simple use case were we just want to retrieve a few fields and display them. Although you can supply filter information and let the SDK filter your data, I find it useful to grab the entire view and manipulate it using LINQ or custom code. I still use the SDK to filter data if the dataset is to large. I can, for instance, only grab records that have been modified today.
You may also find it useful to refer to the Sage 300 Data Dictionary for a reference to what fields are in what view.