SDK Live

Introduction

This is the "Bridge" direct live linking between External Program with SQL Accounting

Pros

1. Can Live or Batch Update.
2. Can do action such as New, Edit & Delete.
3. About 95% of modules is covered.
4. Can direct Retrieve/Get Information From Connected Database.
5. Support GST.
6. Free of Charge for End User. (Freeware)

Cons

1. Must had SQL Accounting installed & running.
2. Must self do checking else error prompt will unable to “capture”.
3. Only supported in IE for JScript

[top]

Documentation

• http://www.estream.com.my/document/SQLLink/SQLSDK.zip
• http://www.estream.com.my/document/SQLLink/Video-SDK.exe
• http://www.estream.com.my/document/SQLLink/TaxPostSeq.jpg

Linking Flow

• http://www.estream.com.my/document/SQLLink/LinkFlow.pdf

Steps

CHM File

1. Install sqlaccounting
2. Create Sample Data
3. Login (username & password is Admin)
4. Double click the SQLSDK.chm file
5. Click Application Object | Business Objects
6. Click the word "here"
7. Click Yes
8. In the list Look for (example)

- AR_IV -> Customer Invoice

- AR_CN -> Customer Credit Note

- AP_PI -> Supplier Invoice

- SL_ID -> Sales Invoice

- ST_AJ -> Stock Adjustment

External Application Linking

1. Login SQL Accounting
2. Login External Application
3. Click Post to SQL Accounting button or Click Save button from External Application.

----------------------------------------------------------------------------------------------------------------------------------------------------

Can try start with Add Agent under the SDK file to make sure you Application can communicate with SQL Accounting

----------------------------------------------------------------------------------------------------------------------------------------------------

[top]

Extra Notes

Login Database Profile

• Can use the Common.Agent.RO Report Objects to get the Profile info.
• Then use the cdsProfile Dataset

- CompanyName Field

- Remark Field

• Refer to How to retrieve data from Customer Invoice Listing Report? on chm

- Can by pass the Steps 5 Spool Parameters

Example External Program

• http://www.estream.com.my/document/SQLLink/Accountinglink-VBDotNet.zip (Using VB.Net)
• http://www.estream.com.my/document/SQLLink/Customer_RO-VBScipt.zip (Using VB Script)
• http://www.estream.com.my/document/SQLLink/AR_PM-JScript.zip (Using JScript)

Delphi

The below example is assuming after you had check the login status.

Example-AR_Customer

AR_Customer Script

procedure TfmMain.dxbb_CustomerClick(Sender: TObject); var lMain, lDtl, lBizObj, lBizApp : Variant; lTime : TDateTime; begin lTime := Now; lBizApp := CreateOleObject('SQLAcc.BizApp'); lBizObj := lBizApp.BizObjects.Find('AR_Customer'); lMain := lBizObj.DataSets.Find('MainDataSet'); lDtl := lBizObj.DataSets.Find('cdsBranch'); try lBizObj.New; lMain.FindField('Code').value := '300-Test'; lMain.FindField('CompanyName').value := 'Testing 123'; lDtl.Edit; //For 1st Branch lDtl.FindField('BranchName').AsString := 'BILLING'; lDtl.FindField('Address1').AsString := 'Address1'; lDtl.FindField('Address2').AsString := 'Address2'; lDtl.FindField('Address3').AsString := 'Address3'; lDtl.FindField('Address4').AsString := 'Address4'; lDtl.FindField('Attention').AsString := 'Attention'; lDtl.FindField('Phone1').AsString := 'Phone1'; lDtl.FindField('Fax1').AsString := 'Fax1'; lDtl.FindField('Email').AsString := 'EmailAddress'; lDtl.Post; lDtl.Append; //For 2nd Branch lDtl.FindField('BranchName').AsString := 'Branch1'; lDtl.FindField('Address1').AsString := 'DAddress1'; lDtl.FindField('Address2').AsString := 'DAddress2'; lDtl.FindField('Address3').AsString := 'DAddress3'; lDtl.FindField('Address4').AsString := 'DAddress4'; lDtl.FindField('Attention').AsString := 'DAttention'; lDtl.FindField('Phone1').AsString := 'DPhone1'; lDtl.FindField('Fax1').AsString := 'DFax1'; lDtl.FindField('Email').AsString := 'DEmailAddress'; lDtl.Post; lBizObj.Save; lTime := Now - lTime; MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]), mtInformation, [mbOk], 0); finally lBizObj.Close; end; end;

Example-AR_PM

AR_PM Script

procedure TfmMain.dxbb_ARPMClick(Sender: TObject); var lMain, lKO, lBizApp, lBizObj, lDocKey, V : Variant; begin lBizApp := CreateOleObject('SQLAcc.BizApp'); try lBizObj := lBizApp.BizObjects.Find('AR_PM'); lMain := lBizObj.DataSets.Find('MainDataSet'); lKO := lBizObj.DataSets.Find('cdsKnockOff'); lBizObj.New; lMain.FindField('DOCKEY').Value := -1; lMain.FindField('DocNo').Value := '--PM Test--'; lMain.FindField('CODE').Value := '300-A0001'; lMain.FindField('DocDate').Value := '28 Aug 2016'; lMain.FindField('PostDate').Value := '28 Aug 2016'; lMain.FindField('Description').Value := 'Payment for A/c'; lMain.FindField('PaymentMethod').Value := '320-000'; lMain.FindField('DocAmt').AsFloat := 1060; lMain.FindField('Cancelled').Value := 'F'; lMain.FindField('CHANGED').Value := 'F'; V := lBizApp.CreateOleVariantArray(2); V.SetItem(0, 'IV'); //Document Type is either is IV or DN for Sales & PI or SD for Purchase V.SetItem(1, '--IV Test--'); //Document Number if lKO.Locate('DocType;DocNo', V.AsOleVariant, False, False) then begin lKO.Edit; lKO.FindField('KOAmt').value := 500; lKO.FindField('KnockOff').AsString := 'T'; lKO.Post; end; lBizObj.Save; MessageDlg('AR_PM-DocNo : ''--PM Test--'' Posted...', mtInformation, [mbOK],0); finally lBizObj.Close; end; end;

Example-ST_Item

ST_Item Script

procedure TfmMain.dxbb_ST_ItemClick(Sender: TObject); var lMain, lDtl, lBizObj, lBizApp : Variant; lTime : TDateTime; begin lTime := Now; lBizApp := CreateOleObject('SQLAcc.BizApp'); lBizObj := lBizApp.BizObjects.Find('ST_ITEM'); lMain := lBizObj.DataSets.Find('MainDataSet'); lDtl := lBizObj.DataSets.Find('cdsUOM'); try lBizObj.New; lMain.FindField('Code').value := 'Test-1'; lMain.FindField('Description').value := 'Testing - Description'; lDtl.Edit; //For 1st UOM lDtl.FindField('UOM').value := 'PCS'; lDtl.FindField('Rate').AsFloat := 1; lDtl.FindField('RefCost').AsFloat := 10.20; lDtl.FindField('RefPrice').AsFloat := 25.00; lDtl.Post; lDtl.Append; //For 2nd UOM lDtl.FindField('UOM').value := 'CTN'; lDtl.FindField('Rate').AsFloat := 12; lDtl.FindField('RefCost').AsFloat := 102.00; lDtl.FindField('RefPrice').AsFloat := 240.00; lDtl.Post; lBizObj.Save; lTime := Now - lTime; MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]), mtInformation, [mbOk], 0); finally lBizObj.Close; end; end;

Example-SL_IV

SL_IV Script

procedure TfmMain.dxbb_SL_IVClick(Sender: TObject); var lMain, lDtl, lSN, lBizObj, lBizApp : Variant; i : integer; lTime : TDateTime; begin lTime := Now; lBizApp := CreateOleObject('SQLAcc.BizApp'); lBizObj := lBizApp.BizObjects.Find('SL_IV'); lMain := lBizObj.DataSets.Find('MainDataSet'); lDtl := lBizObj.DataSets.Find('cdsDocDetail'); lSN := lBizObj.DataSets.Find('cdsSerialNumber'); try lBizObj.New; lMain.FindField('DOCKEY').Value := -1; lMain.FindField('DocNo').Value := '--IV Test--'; lMain.FindField('CODE').Value := '300-A0001'; lMain.FindField('DocDate').Value := '28 Aug 2015'; lMain.FindField('PostDate').Value := '28 Aug 2015'; lMain.FindField('TAXDATE').Value := '28 Aug 2015'; lMain.FindField('Description').Value := 'Sales'; lMain.FindField('DocAmt').AsFloat := 735.28; lMain.FindField('Cancelled').Value := 'F'; lMain.FindField('CHANGED').Value := 'F'; lMain.Post; lDtl.Append; // For Tax Inclusive = True with override Tax Amount lDtl.FindField('DTLKEY').Value := -1; lDtl.FindField('DOCKEY').Value := -1; lDtl.FindField('SEQ').Value := 1; lDtl.FindField('ItemCode').Value := 'ITEM A'; lDtl.FindField('ACCOUNT').Value := '500-000'; lDtl.FindField('DESCRIPTION').Value := 'Sales Item A'; lDtl.FindField('QTY').AsFloat := 1; lDtl.FindField('TAX').Value := 'SR'; lDtl.FindField('TAXINCLUSIVE').Value := 0; lDtl.FindField('UNITPRICE').AsFloat := 410.37; //Must Excluding GST lDtl.FindField('AMOUNT').AsFloat := 410.37; //Must Excluding GST (Qty * UnitPrice) lDtl.FindField('TAXAMT').AsFloat := 24.63; lDtl.DisableControls; //For Tax Inclusive Only where TaxAmt is not as Calc try lDtl.FindField('TAXINCLUSIVE').Value := 1; finally lDtl.EnableControls; end; lDtl.FindField('CHANGED').Value := 'F'; lDtl.Post; lDtl.Append; // For Tax Inclusive = False with override Tax Amount lDtl.FindField('DTLKEY').Value := -1; lDtl.FindField('DOCKEY').Value := -1; lDtl.FindField('SEQ').Value := 2; lDtl.FindField('ItemCode').Value := 'ITEM B'; lDtl.FindField('ACCOUNT').Value := '500-000'; lDtl.FindField('DESCRIPTION').Value := 'Sales Item B'; lDtl.FindField('QTY').AsFloat := 1; lDtl.FindField('TAX').Value := 'SR'; lDtl.FindField('TAXINCLUSIVE').Value := 0; lDtl.FindField('UNITPRICE').AsFloat := 94.43; lDtl.FindField('AMOUNT').AsFloat := 94.43; lDtl.FindField('TAXAMT').AsFloat := 5.66; lDtl.FindField('CHANGED').Value := 'F'; lDtl.Post; lDtl.Append; // For Serial Number lDtl.FindField('DTLKEY').Value := -1; lDtl.FindField('DOCKEY').Value := -1; lDtl.FindField('SEQ').Value := 3; lDtl.FindField('ItemCode').Value := 'SN'; lDtl.FindField('ACCOUNT').Value := '500-000'; lDtl.FindField('DESCRIPTION').Value := 'Sales Serial Number Item'; lSN.Append; lSN.FindField('SERIALNUMBER').Value := 'SN-00001'; lSN.Post; lSN.Append; lSN.FindField('SERIALNUMBER').Value := 'SN-00002'; lSN.Post; lDtl.FindField('QTY').AsFloat := 2; lDtl.FindField('TAX').Value := 'SR'; lDtl.FindField('TAXINCLUSIVE').Value := 0; lDtl.FindField('UNITPRICE').AsFloat := 94.43; lDtl.FindField('TAXAMT').AsFloat := 11.33; lDtl.FindField('CHANGED').Value := 'F'; lDtl.Post; lBizObj.Save; lTime := Now - lTime; MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]), mtInformation, [mbOk], 0); finally lBizObj.Close; end; end;

Example-Customer.IV.RO

Customer.IV.RO Report Object Script

procedure TfmMain.dxbb_RptObjClick(Sender: TObject); var lBizApp, lRptVar : Variant; lTime : TDateTime; cdsMain, cdsDtl : TClientDataset; begin lTime := Now; cdsMain := TClientDataset.Create(nil); cdsDtl := TClientDataset.Create(nil); lBizApp := CreateOleObject('SQLAcc.BizApp'); try lRptVar := null; lRptVar := lBizApp.RptObjects.Find('Customer.IV.RO'); lRptVar.Params.Find('SelectDate').AsBoolean := True; lRptVar.Params.Find('DateFrom').AsDate := '01/01/2015'; lRptVar.Params.Find('DateTo').AsDate := '31/12/2015'; lRptVar.Params.Find('AllAgent').AsBoolean := False; lRptVar.Params.Find('AgentData').AsBlob := '----'#13#10'HALIM'; //Filter by Agent code ---- & HALIM lRptVar.Params.Find('AllArea').AsBoolean := True; // lRptVar.Params.Find('AreaData').AsBlob  := s; //Not use if AllArea is True lRptVar.Params.Find('AllCompany').AsBoolean := True; // lRptVar.Params.Find('CompanyData').AsBlob  := s; //Not use if AllCompany is True lRptVar.Params.Find('AllCurrency').AsBoolean := True; // lRptVar.Params.Find('CurrencyData').AsBlob  := s; //Not use if AllCurrenty is True lRptVar.Params.Find('AllDocProject').AsBoolean := True; // lRptVar.Params.Find('DocProjectData').AsBlob  := s; //Not use if AllDocProject is True lRptVar.Params.Find('AllCompanyCategory').AsBoolean := True; // lRptVar.Params.Find('CompanyCategoryData').AsBlob  := s; //Not use if AllCompanyCategory is True lRptVar.Params.Find('AllDocument').AsBoolean := True; // lRptVar.Params.Find('DocumentData').AsBlob  := s; //Not use if AllDocument is True lRptVar.Params.Find('IncludeCancelled').AsBoolean := True; lRptVar.Params.Find('PrintDocumentStyle').AsBoolean := False; lRptVar.Params.Find('AllItemProject').AsBoolean := True; lRptVar.Params.Find('AllPaymentMethod').AsBoolean := True; lRptVar.CalculateReport; cdsMain.XMLData := lRptVar.DataSets.Find('cdsMain').XMLData; //Master Data cdsDtl.XMLData := lRptVar.DataSets.Find('cdsDocDetail').XMLData; //Detail Data - To link Master Data use Dockey field cdsMain.SaveToFile('C:\Temp\_ListM.xml'); //Export To XML file cdsDtl.SaveToFile('C:\Temp\_ListD.xml'); //Export To XML file lTime := Now - lTime; MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]), mtInformation, [mbOk], 0); finally lRptVar := null; cdsMain.Free; cdsDtl.Free; end; end;

[top]

FAQ

Problem with opening CHM Help files?

1. Right click the chm file & select Properties
2. At General tab Click Unblock button.
3. Click Apply | Ok.

Still can't open CHM file?

1. Login SQL Accounting
2. Click Tools | Options
3. Click Register & follow wizard
4. Exit SQL Accounting
5. Login SQL Accounting
6. Run CHM File

Why when I compile my program with stated unknown method or unknown identifier?

If you compiler had error may try add the esfw10.dll file under the SQL Accounting\bin folder

Why after post to SQL Accounting the Description3 (More Description)/Notes field it show like this "M y D e s c r i p t i o n" instead of "My Description"?

Make sure you use .AsString in your code (see example below)

Description3 Field Script/Code
... lDtl.FindField('DESCRIPTION3').AsString  := 'Sales Item A More Description'+ #13 + 'I in 2nd line'; ...

Can SQL Acc accept negative amount?

No all document amount(DocAmt field) must not below 0

See also

• SQL Accounting Linking
• SQL XML Import
• SQL Text Import
• SQL Acc XLS n MDB Import
• Others Customisation