How to read single record with BOI (C#)

SOLVED

Hello All,

Can anyone help me in getting a single record from Sage BOI script. I am using the following code but no data is returned.

int taskId = (int)oSs.InvokeMethod("nLookupTask", "AR_Customer_ui");
oSs.InvokeMethod("nSetProgram", taskId);

using (var arCustSvc = new DispatchObject(pvx.InvokeMethod("NewObject", "AR_Customer_svc", oSs.GetObject())))
{
    arCustSvc.InvokeMethod("nSetKeyValue", "ARDivisionNo$", "00");
    arCustSvc.InvokeMethod("nSetKeyValue", "CustomerNo$", "0000007");

    int returnValue = (int)arCustSvc.InvokeMethod("nFind");
    if (returnValue > 0)
    {
        int retVal = 0;

        string str1 = "", str2 = "";
        retVal = (int)arCustSvc.InvokeMethod("nGetRecord", str1, str2);
    }
}

here retVal is 1, but str1 and str2 is empty.

Is there anything i am doing wrong here?

  • 0

    This is what the file and layouts says about the GetRecord method.

    So i don't think you are going to be able to use it with C# unless you use the Evaluate method of the PVX object to return the compiled IOLIST. Depending on what you are trying to accomplish, it might be easier to just use the GetValue method of your arCustSvc object.

  • 0 in reply to David Speck

    I tried to use the GetValue method also, but no luck there either.

    string customerId = string.Empty;

    retVal = (int)arCustSvc.InvokeMethod("nGetValue", "CustomerNo$", customerId);

  • 0 in reply to Bhagavan Reddy

    Did you get an error with GetValue or did your customerId not have a value?

  • 0 in reply to David Speck

    retVal is 1 but customerId is empty

  • +1 in reply to David Speck
    verified answer

    Finally got the solution like following

    var data = new object[] { str1, str2 };
    retVal = (int)arCustSvc.InvokeMethodByRef("nGetRecord", data);

    now data[0] is having the record information.

  • 0

    I've finally got a sample working from the Sage BOI Course "Object Interface: Foundation Class Overview", using C#. Hopefully this will help someone else getting started. 

    using Microsoft.Win32;
    
    RegistryKey dsn = Registry.CurrentUser.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\SOTAMAS90");
    if (dsn == null)
    {
        Console.WriteLine("Registry key not found.");
        Environment.Exit(0);
    }
    
    string initPath = dsn.GetValue("Directory") + "\\Home";
    
    Type PVXType = Type.GetTypeFromProgID("ProvideX.Script");
    
    dynamic oPVX = Activator.CreateInstance(PVXType);
    
    oPVX.Init(initPath);
    
    dynamic oSS = oPVX.NewObject("SY_Session");
    int retVal = oSS.nSetUser(__username, __password);
    retVal = oSS.nSetCompany("ABC");
    retVal = oSS.nSetDate("G/L", "20211123");
    
    
    
    oSS.nSetProgram(oSS.nLookupTask("GL_Account_ui"));
    dynamic GL_Account_svc = oPVX.NewObject("GL_Account_svc", oSS);
    
    oSS.nSetProgram(oSS.nLookupTask("GL_Allocation_ui"));
    dynamic GL_Allocation_svc = oPVX.NewObject("GL_Allocation_svc", oSS);
    
    string AccountKey;
    string Account;
    string AccountDesc;
    double AccountValue = 0;
    
    string FiscalYear = "2021";
    string FiscalPeriod = "11";
    
    
    int i = 0;
    for (i = 0; i < 5; i++)
    {
        GL_Account_svc.nMoveNext();
        AccountKey = GL_Account_svc.sAccountKey;
        Account = GL_Account_svc.sAccount;
        AccountDesc = GL_Account_svc.sAccountDesc;
        
        GL_Allocation_svc.nGetAccountBalance(AccountKey, FiscalYear, FiscalPeriod, ref AccountValue);   
        Console.WriteLine(Account + " - " + AccountDesc + ": " + AccountValue.ToString());
    }
    
    
    GL_Allocation_svc.DropObject();
    GL_Account_svc.DropObject();
    oSS.nCleanup();
    oSS.DropObject();

  • FormerMember
    0 FormerMember

    I gave your C# program a try in ScriptBasic.

    IMPORT com.sbi
    
    oscript = COM::CREATE(:SET, "ProvideX.Script")
    COM::CBN(oscript, "Init", :CALL, "C:\\Sage\\Sage 100 Standard\\MAS90\\Home")
    osession = COM::CBN(oscript, "NewObject", :SET, "SY_Session")
    COM::CBN(osession, "nSetUser", :CALL, "UserID", "Password")
    COM::CBN(osession, "nsetcompany", :CALL, "ABC")
    COM::CBN(osession, "nSetDate", :CALL, "G/L", "20211124")
    
    COM::CBN(osession, "nSetProgram", :CALL, COM::CBN(osession, "nLookupTask", :CALL, "GL_Account_ui"))
    oacct = COM::CBN(oscript, "NewObject", :SET, "GL_Account_svc", osession)
    COM::CBN(osession, "nSetProgram", :CALL, COM::CBN(osession, "nLookupTask", :CALL, "GL_Allocation_ui"))
    oalloc = COM::CBN(oscript, "NewObject", :SET, "GL_Allocation_svc", osession)
    
    FOR i = 0 TO 4
      COM::CBN(oacct, "nMoveNext", :CALL)
      AccountKey = COM::CBN(oacct, "sAccountKey", :GET)
      Account = COM::CBN(oacct, "sAccount", :GET)
      AccountDesc = COM::CBN(oacct, "sAccountDesc", :GET)
      AccountValue = 0
      COM::CBN(oalloc, "nGetAccountBalance", :CALL, AccountKey, "2025", "05", AccountValue)
      PRINT Account & " - " & AccountDesc & ": " & FORMAT("%~###,##0.00-~" & AccountValue) & "\n"
    NEXT
    
    COM::CBN(oalloc, "DropObject", :CALL)
    COM::CBN(oacct, "DropObject", :CALL)
    COM::CBN(osession, "nCleanup", :CALL)
    COM::CBN(osession, "DropObject", :CALL)
    COM::RELEASE(oscript)

    C:\OpenSage\test>sbc glbal.sb
    100-00-00 - Cash on hand:   1,408.00
    100-00-A - Cash in Trust Fund:       0.00
    101-01-00 - Cash in bank - Reg. checking: 191,565.00-
    101-02-00 - Cash in bank - payroll:  16,154.00
    101-03-00 - Cash in bank - savings: 497,324.00

  • FormerMember
    0 FormerMember in reply to FormerMember

    I updated to ScriptBasic example to remove the unneeded SetProgram / LookupTask calls. The example displays all the accounts and their balances.

    IMPORT com.sbi
    
    oscript = COM::CREATE(:SET, "ProvideX.Script")
    COM::CBN(oscript, "Init", :CALL, "C:\\Sage\\Sage 100 Standard\\MAS90\\Home")
    osession = COM::CBN(oscript, "NewObject", :SET, "SY_Session")
    COM::CBN(osession, "nSetUser", :CALL, "UserID", "Password")
    COM::CBN(osession, "nsetcompany", :CALL, "ABC")
    COM::CBN(osession, "nSetDate", :CALL, "G/L", "20211124")
    
    oacct = COM::CBN(oscript, "NewObject", :SET, "GL_Account_svc", osession)
    oalloc = COM::CBN(oscript, "NewObject", :SET, "GL_Allocation_svc", osession)
    
    COM::CBN(oacct, "nMoveFirst", :CALL)
    DO UNTIL COM::CBN(oacct, "nEOF", :GET)
      AccountKey = COM::CBN(oacct, "sAccountKey", :GET)
      Account = COM::CBN(oacct, "sAccount", :GET)
      AccountDesc = COM::CBN(oacct, "sAccountDesc", :GET)
      AccountValue = 0
      COM::CBN(oalloc, "nGetAccountBalance", :CALL, AccountKey, "2025", "05", AccountValue)
      PRINT Account & " - " & AccountDesc & STRING(40 - LEN(AccountDesc), " ") & FORMAT("%~$#,###,##0.00-~", AccountValue) & "\n"
      COM::CBN(oacct, "nMoveNext", :CALL)
    LOOP
    
    COM::CBN(oalloc, "DropObject", :CALL)
    COM::CBN(oacct, "DropObject", :CALL)
    COM::CBN(osession, "nCleanup", :CALL)
    COM::CBN(osession, "DropObject", :CALL)
    COM::RELEASE(oscript)

    C:\OpenSage\test>sbc glbal.sb
    100-00-00 - Cash on hand                            $    1,408.00
    100-00-A - Cash in Trust Fund                      $        0.00
    101-01-00 - Cash in bank - Reg. checking            $  191,565.00-
    101-02-00 - Cash in bank - payroll                  $   16,154.00
    101-03-00 - Cash in bank - savings                  $  497,324.00
    105-00-01 - Accts. receiv. - East Warehse           $  548,651.00
    105-00-02 - Accts. receiv. - West Warehse           $  145,481.00
    110-01-00 - Note receivable                         $  203,236.00
    111-00-00 - Other Receivables                       $        0.00
    115-00-01 - Inventory - East Warehouse              $   96,029.00-
    115-00-02 - Inventory - West Warehouse              $  109,577.00
    115-00-03 - Inventory - Central Warehouse           $  448,267.00
    115-01-00 - Inventory-Miscellaneous                 $        0.00
    116-00-00 - Inventory-Scrap                         $       34.00
    117-00-00 - Inventory-Repairs in Process            $        0.00
    118-00-00 - Inventory-Repairs Clearing              $        0.00
    120-00-00 - Prepaid insurance                       $   30,000.00-
    125-00-00 - Prepaid advertising                     $    3,050.00
    130-00-00 - Prepaid income taxes                    $   27,750.00
    150-00-00 - Land                                    $   95,000.00
    155-00-00 - Buildings                               $  282,500.00
    155-01-00 - Accum. depr. - buildings                $   44,305.00-
    160-00-00 - Furniture                               $   23,680.00
    160-01-00 - Accum. depr. - furniture                $    7,456.00-
    165-00-00 - Office and computer equipment           $   44,800.00
    165-01-00 - Accum. depr. - off & cmp equip          $   20,541.00-
    170-00-00 - Warehouse equipment                     $   17,530.00
    170-01-00 - Accum. depr. - warehse equip            $    5,959.00-
    175-00-00 - Trucks                                  $   45,600.00
    175-01-00 - Accum. depr. - trucks                   $   36,734.00-
    180-00-00 - Rent deposits                           $    2,000.00
    185-00-00 - Workmans' comp. deposit                 $      500.00
    195-00-00 - Software costs (net)                    $    4,844.00
    200-01-00 - Accounts payable - trade                $  130,602.00-
    200-02-00 - Accounts payable - other                $    5,484.00
    200-03-00 - Purchases clearing account              $  174,980.00-
    201-00-00 - Customer Deposits                       $        0.00
    205-00-00 - Notes payable                           $   13,226.00-
    210-00-00 - Accrued payroll                         $        0.00
    220-00-00 - Other accrued expenses                  $        0.00
    225-01-00 - F.I.C.A. taxes payable                  $   50,543.00-
    225-02-00 - Federal taxes withheld                  $   37,501.00-
    225-03-00 - State tax withheld                      $   13,672.00-
    225-04-00 - City tax withheld                       $    1,243.00-
    230-01-00 - Accrued federal employment              $    7,901.00-
    230-02-00 - Accrued state unemployment              $    5,562.00-
    230-03-00 - Accrued workmans' comp                  $    1,085.00-
    235-01-00 - Sales tax payable - East Cnty           $   33,348.00-
    235-02-00 - Sales tax payable - West Cnty           $   20,131.00-
    245-00-00 - Withheld credit union                   $      900.00-
    250-00-00 - Current portion of l-t debt             $   20,000.00-
    255-00-00 - Income taxes payable                    $   67,050.00-
    275-01-00 - Loan payable to Bank 1                  $   36,666.00-
    275-02-00 - Loan payable to Bank 2                  $    6,666.00
    280-00-00 - Loan payable to shareholders            $   90,000.00-
    300-00-00 - Common stock                            $    2,000.00-
    310-00-00 - Paid-In capital                         $    8,000.00-
    320-00-00 - Retained earnings                       $1,105,531.00-
    400-01-00 - Distribution sales (history)            $  291,142.00-
    400-01-01 - Distribution sales - East               $1,182,772.00-
    400-01-02 - Distribution sales - West               $  611,389.00-
    400-02-00 - Service fees                            $    3,000.00-
    400-02-01 - Service fees - East                     $   44,362.00-
    400-02-02 - Service fees - West                     $   20,853.00-
    400-03-00 - Freight charges                         $        0.00
    400-03-01 - Freight charges - East                  $   24,752.00-
    400-03-02 - Freight charges - West                  $   12,487.00-
    425-00-00 - Returns & allowances                    $       81.00
    425-00-01 - Returns & allowances - East             $   19,017.00
    425-00-02 - Returns & allowances - West             $    6,538.00
    450-01-00 - Purchases                               $  163,177.00
    450-01-01 - Purchases - East                        $  308,322.00
    450-01-02 - Purchases - West                        $  294,984.00
    450-02-00 - Freight                                 $      555.00
    450-02-01 - Freight - East                          $   13,324.00
    450-02-02 - Freight - West                          $    5,927.00
    450-03-00 - Warehouseman payroll                    $        0.00
    450-03-01 - Warehouseman payroll - East             $  157,836.00
    450-03-02 - Warehouseman payroll - West             $   37,100.00
    450-04-00 - Serviceman payroll                      $        0.00
    450-04-01 - Serviceman payroll - East               $    9,646.00
    450-04-02 - Serviceman payroll - West               $    5,489.00
    450-10-00 - Cost of Goods Sold                      $      945.00
    500-00-00 - Other expenses (history)                $    2,573.00
    500-00-03 - Warehouse payroll                       $    6,930.00
    505-00-03 - Clerical salaries                       $    6,545.00
    507-00-03 - Sick pay                                $      195.00
    508-00-03 - Holiday pay                             $      263.00
    509-00-03 - Vacation pay                            $        0.00
    510-00-03 - Payroll taxes                           $   18,905.00
    515-00-03 - Building maintenance                    $    9,192.00
    518-00-00 - Accrued Credit Card Expense             $        0.00
    520-00-03 - Depreciation expense                    $    2,453.00
    525-00-03 - Equipment maintenance                   $    1,872.00
    530-00-03 - Insurance expense                       $      480.00
    530-10-03 - Insurance expense: SA&MK-CENTR          $      568.00
    530-20-03 - Insurance expense: ACCTG-CENTR          $      284.00
    530-30-03 - Insurance expense: CSERV-CENTR          $      426.00
    530-60-03 - Insurance expense: SH&RC-CENTR          $      640.00
    535-00-03 - Warehouse supplies                      $    3,406.00
    540-00-03 - Telephone expense                       $    1,154.00
    540-10-03 - Telephone expense: SA&MK-CENTR          $      936.00
    540-20-03 - Telephone expense: ACCTG-CENTR          $      312.00
    540-30-03 - Telephone expense: CSERV-CENTR          $      936.00
    540-60-03 - Telephone expense: SH&RC-CENTR          $      624.00
    545-00-03 - Utilities                               $    2,715.00
    545-10-03 - Utilities: SA&MK-CENTR                  $      288.00
    545-20-03 - Utilities: ACCTG-CENTR                  $       96.00
    545-30-03 - Utilities: CSERV-CENTR                  $      288.00
    545-60-03 - Utilities: SH&RC-CENTR                  $      192.00
    555-00-03 - Rent                                    $   11,077.00
    555-10-03 - Rent: SA&MK-CENTR                       $    9,186.00
    555-20-03 - Rent: ACCTG-CENTR                       $    1,941.00
    555-30-03 - Rent: CSERV-CENTR                       $    5,824.00
    555-60-03 - Rent: SH&RC-CENTR                       $    5,320.00
    560-00-03 - Truck expenses                          $    9,922.00
    560-10-01 - Truck expenses: SA&MK-EAST              $        0.00
    560-10-02 - Truck expenses: SA&MK-WEST              $        0.00
    560-10-03 - Truck expenses: SA&MK-CENTR             $        0.00
    560-20-01 - Truck expenses: ACCTG-EAST              $        0.00
    560-20-02 - Truck expenses: ACCTG-WEST              $        0.00
    560-20-03 - Truck expenses: ACCTG-CENTR             $        0.00
    560-30-01 - Truck expenses: CSERV-EAST              $        0.00
    560-30-02 - Truck expenses: CSERV-WEST              $        0.00
    560-30-03 - Truck expenses: CSERV-CENTR             $        0.00
    560-40-01 - Truck expenses: HR-EAST                 $        0.00
    560-40-02 - Truck expenses: HR-WEST                 $        0.00
    560-40-03 - Truck expenses: HR-CENTR                $        0.00
    560-50-01 - Truck expenses: ENGR-EAST               $        0.00
    560-50-02 - Truck expenses: ENGR-WEST               $        0.00
    560-50-03 - Truck expenses: ENGR-CENTR              $        0.00
    560-60-01 - Truck expenses: SH&RC-EAST              $        0.00
    560-60-02 - Truck expenses: SH&RC-WEST              $        0.00
    560-60-03 - Truck expenses: SH&RC-CENTR             $        0.00
    565-00-03 - Miscellaneous expense                   $      688.00
    600-01-01 - Driver payroll - East                   $  132,035.00
    600-01-02 - Driver payroll - West                   $   49,882.00
    605-01-00 - Clerical salaries                       $        0.00
    605-01-01 - Clerical salaries - East                $   28,263.00
    605-01-02 - Clerical salaries - West                $   25,781.00
    607-01-00 - Sick pay                                $        0.00
    607-01-01 - Sick pay - East                         $    1,008.00
    607-01-02 - Sick pay - West                         $        0.00
    608-01-00 - Holiday pay                             $        0.00
    608-01-01 - Holiday pay - East                      $    5,884.00
    608-01-02 - Holiday pay - West                      $    2,214.00
    609-01-00 - Vacation pay                            $        0.00
    609-01-01 - Vacation pay - East                     $      854.00
    609-01-02 - Vacation pay - West                     $      540.00
    610-01-00 - Payroll taxes                           $        0.00
    610-01-01 - Payroll taxes - East                    $   66,271.00
    610-01-02 - Payroll taxes - West                    $   18,027.00
    620-01-00 - Depreciation expense                    $        0.00
    620-01-01 - Depreciation expense - East             $    4,564.00
    620-01-02 - Depreciation expense - West             $      342.00
    625-01-00 - Equipment maintenance                   $        0.00
    625-01-01 - Equipment maintenance - East            $    7,138.00
    625-01-02 - Equipment maintenance - West            $    6,671.00
    630-01-00 - Insurance                               $        0.00
    630-01-01 - Insurance - East                        $   15,947.00
    630-01-02 - Insurance - West                        $    6,526.00
    635-01-00 - Warehouse supplies                      $        0.00
    635-01-01 - Warehouse supplies - East               $   18,853.00
    635-01-02 - Warehouse supplies - West               $    8,369.00
    640-01-00 - Telephone expense                       $        0.00
    640-01-01 - Telephone expense - East                $   25,884.00
    640-01-02 - Telephone expense - West                $   19,416.00
    645-01-00 - Utilities                               $        0.00
    645-01-01 - Utilities - East                        $   21,910.00
    645-01-02 - Utilities - West                        $    8,975.00
    660-01-00 - Truck expenses                          $      375.00
    660-01-01 - Truck expenses - East                   $   17,399.00
    660-01-02 - Truck expenses - West                   $    7,674.00
    665-01-00 - Miscellaneous expense                   $       79.00
    665-01-01 - Miscellaneous expense - East            $    6,186.00
    665-01-02 - Miscellaneous expense - West            $    2,686.00
    700-00-04 - Officer salaries                        $   74,562.00
    705-00-04 - Clerical salaries                       $   54,391.00
    706-00-04 - Bonus pay                               $        0.00
    707-00-04 - Sick pay                                $      184.00
    708-00-04 - Holiday pay                             $      909.00
    709-00-04 - Vacation pay                            $      854.00
    710-00-04 - Payroll taxes                           $   43,081.00
    715-00-04 - Hospitalization                         $   13,812.00
    720-00-04 - Depreciation                            $   11,980.00
    722-00-04 - Amortization                            $      297.00
    725-00-04 - Professional fees                       $   10,606.00
    730-00-04 - Insurance expense                       $   12,088.00
    735-00-04 - Advertising expenses                    $    7,971.00
    740-00-04 - Telephone expenses                      $    9,638.00
    745-00-04 - Utilities                               $    6,356.00
    750-00-04 - Interest expense                        $    3,410.00
    755-00-04 - Travel and entertainment                $   22,792.00
    760-00-04 - Office supplies                         $    6,507.00
    765-00-04 - Miscellaneous expense                   $      522.00
    770-00-04 - Postage & other freight                 $    4,523.00
    950-01-04 - Discounts earned                        $    8,260.00-
    950-02-04 - Discounts allowed                       $    2,391.00
    955-00-04 - Interest income                         $   35,242.00-
    960-00-04 - Miscellaneous                           $   35,636.00-
    990-00-00 - Provision for income taxes              $   67,050.00
    105-00-00 - Accts. receiv.                          $        0.00
    235-00-00 - Sales tax payable                       $        0.00
    950-03-04 - Nonrecoverable                          $        0.00
    240-01-00 - Use tax - East Cnty                     $        0.00
    240-00-00 - Use tax                                 $        0.00
    240-02-00 - Use tax - West Cnty                     $        0.00
    200-00-00 - Accounts payable                        $        0.00
    
    C:\OpenSage\test>

  • 0 in reply to FormerMember

    SetProgram and LookupTask are not unneeded.  LookupTask returns the task id that SetProgram expects based on the class you pass to it.  SetProgram does several things, it sets the program name you see in Master Console for the process' session and it checks security to make sure the currently set user has appropriate role permissions assigned to access a task.  If you are always using a user with an "admin" role then you won't see the impact but for user defined scripts that operate in multi-company and multi-user environments, it is important to check to avoid potential hard errors.

  • 0 in reply to David Speck

    I've also found that the value returned form SetProgram will return 0 when the user is not authorized, but the program will continue and return data even though the user is not authorized.

    For me, I've determined the best practice is to test the returned value from nSetUser, nSetDate, and nSetProgram. If the returned value is 0, then throw an exception to indicate the user is not authorized to prevent the execution of later code.