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 programming knowledge.
2. Must had SQL Accounting installed & running.
3. Must self do checking else error prompt will unable to “capture”.
4. 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_IV -> Sales Invoice

- PH_PI -> Purchase 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)

VBScript

Example-AR_IV

AR_IV Script

'Copy below script & paste to notepad & name it as eg AR_IV.vbs Call InsertARIV Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function InsertARIV Dim ComServer, BizObject, lDataSet, lDataSet2, lDate 'Step 1: Create Com Server object Set ComServer = CreateSQLAccServer 'Create Com Server If not ComServer.IsLogin Then 'if user hasn't logon to SQL application ComServer.Login "ADMIN", "ADMIN", "C:\estream\SQLAccounting\Share\Default.DCF", "ACC-0020.FDB" END IF 'Step 2: Find and Create the Biz Objects Set BizObject = ComServer.BizObjects.Find("AR_IV") 'Step 3: Set Dataset Set lMainDataSet = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data Set lDetailDataSet = BizObject.DataSets.Find("cdsDocDetail") 'lDetailDataSet contains detail data 'Step 4 : Insert Data - Master lDate = CDate("January 1, 2016") BizObject.New lMainDataSet.FindField("DocKey").value = -1 lMainDataSet.FindField("DocNo").value = "--IV Test--" lMainDataSet.FindField("DocDate").value = lDate lMainDataSet.FindField("PostDate").value = lDate lMainDataSet.FindField("Code").value = "300-A0003" lMainDataSet.FindField("Description").value = "Sales" 'Step 5: Insert Data - Detail 'For Tax Inclusive = True with override Tax Amount lDetailDataSet.Append lDetailDataSet.FindField("DtlKey").value = -1 lDetailDataSet.FindField("DocKey").value = -1 lDetailDataSet.FindField("Account").value = "500-000" lDetailDataSet.FindField("Description").value = "Sales Item A" lDetailDataSet.FindField("Tax").value = "SR" lDetailDataSet.FindField("TaxInclusive").value = 0 lDetailDataSet.FindField("Amount").value = 410.37 lDetailDataSet.FindField("TaxAmt").value = 24.63 lDetailDataSet.DisableControls lDetailDataSet.FindField("TaxInclusive").value = 1 lDetailDataSet.EnableControls lDetailDataSet.FindField("Changed").value = "F" lDetailDataSet.Post 'For Tax Inclusive = False with override Tax Amount lDetailDataSet.Append lDetailDataSet.FindField("DtlKey").value = -1 lDetailDataSet.FindField("DocKey").value = -1 lDetailDataSet.FindField("Account").value = "500-000" lDetailDataSet.FindField("Description").value = "Sales Item B" lDetailDataSet.FindField("Tax").value = "SR" lDetailDataSet.FindField("TaxInclusive").value = 0 lDetailDataSet.FindField("Amount").value = 94.43 lDetailDataSet.FindField("TaxAmt").value = 5.66 lDetailDataSet.FindField("Changed").value = "F" lDetailDataSet.Post 'Step 6: Save Document BizObject.Save End Function

Example-Customer.RO

Customer.RO Report Object Script

'Copy below script & paste to notepad & name it as eg customer_ro.vbs Call GetCustomerData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function GetCustomerData Dim ComServer, RptObject, lDataSet, lDataSet2, lDateFrom, lDateTo 'Step 1: Create Com Server object Set ComServer = CreateSQLAccServer 'Create Com Server If not ComServer.IsLogin Then 'if user hasn't logon to SQL application ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB" END IF 'Step 2: Find and Create the Report Objects Set RptObject = ComServer.RptObjects.Find("Customer.RO") 'Step 3: Spool parameters RptObject.Params.Find("AllAgent").Value = true RptObject.Params.Find("AllArea").Value = true RptObject.Params.Find("AllCompany").Value = false RptObject.Params.Find("AllCompanyCategory").Value = true RptObject.Params.Find("AllCurrency").Value = true RptObject.Params.Find("AllTerms").Value = true RptObject.Params.Find("SelectDate").Value = true RptObject.Params.Find("PrintActive").Value = true RptObject.Params.Find("PrintInactive").Value = false RptObject.Params.Find("PrintPending").Value = false RptObject.Params.Find("PrintProspect").Value = false RptObject.Params.Find("PrintSuspend").Value = false lDateFrom = CDate("January 1, 2000") lDateTo = CDate("December 31, 2014") RptObject.Params.Find("DateFrom").Value = lDateFrom RptObject.Params.Find("DateTo").Value = lDateTo RptObject.Params.Find("CompanyData").Value = "300-A0003" 'Step 4: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsMain") Set lDataSet2 = RptObject.DataSets.Find("cdsBranch") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox lDataSet.FindField("Code").AsString & " " & lDataSet.FindField("CompanyName").AsString lDataSet2.First While (not lDataSet2.eof) MsgBox lDataSet2.FindField("Address1").AsString lDataSet2.Next Wend lDataSet.Next Wend End Function

Example-Customer.Statement.RO

Customer.Statement.RO Report Object Script

'Copy below script & paste to notepad & name it as eg customer_statement_ro.vbs Call GetCustStatementData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function GetCustStatementData Const Quote = """" Dim ComServer, RptObject, lDataSet, lDataSet2, lDateFrom, lDateTo, lAgeData 'Step 1: Create Com Server object Set ComServer = CreateSQLAccServer 'Create Com Server If not ComServer.IsLogin Then 'if user hasn't logon to SQL application ComServer.Login "ADMIN", "ADMIN", "C:\DB\SQLAcc.DCF", "ACC-0002.FDB" END IF 'Step 2: Find and Create the Report Objects Set RptObject = ComServer.RptObjects.Find("Customer.Statement.RO") 'Step 3: Spool parameters lAgeData = "<?xml version=" & Quote & "1.0" & Quote & " standalone=" & Quote & "yes" & Quote & "?> <DATAPACKET Version=" & Quote & "2.0" & Quote & "><METADATA><FIELDS>" &_ "<FIELD attrname=" & Quote & "ColumnNo" & Quote & " fieldtype=" & Quote & "i4" & Quote & " required=" & Quote & "true" & Quote & "/><FIELD attrname=" & Quote & "ColumnType" & Quote & " fieldtype=" & Quote & "string" & Quote & " WIDTH=" & Quote & "1" & Quote & "/>" &_ "<FIELD attrname=" & Quote & "Param1" & Quote & " fieldtype=" & Quote & "i4" & Quote & " required=" & Quote & "true" & Quote & "/><FIELD attrname=" & Quote & "Param2" & Quote & " fieldtype=" & Quote & "i4" & Quote & " required=" & Quote & "true" & Quote & "/>" &_ "<FIELD attrname=" & Quote & "IsLocal" & Quote & " fieldtype=" & Quote & "boolean" & Quote & "/><FIELD attrname=" & Quote & "HeaderScript" & Quote & " fieldtype=" & Quote & "bin.hex" & Quote & " SUBTYPE=" & Quote & "Text" & Quote & " WIDTH=" & Quote & "1" & Quote & "/>" &_ "</FIELDS><PARAMS/></METADATA><ROWDATA><ROW ColumnNo=" & Quote & "0" & Quote & " ColumnType=" & Quote & "" & Quote & " Param1=" & Quote & "0" & Quote & " Param2=" & Quote & "0" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & "/>" &_ "<ROW ColumnNo=" & Quote & "1" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "0" & Quote & " Param2=" & Quote & "0" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal&#013;begin&#013;Value:= &apos;Current Mth&apos;&#013;end;" & Quote & "/>" &_ "<ROW ColumnNo=" & Quote & "2" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-1" & Quote & " Param2=" & Quote & "-1" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal&#013;begin&#013;Value:= &apos;1 Months&apos;&#013;end;" & Quote & "/>" &_ "<ROW ColumnNo=" & Quote & "3" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-2" & Quote & " Param2=" & Quote & "-2" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal&#013;begin&#013;Value:= &apos;2 Months&apos;&#013;end;" & Quote & "/>" &_ "<ROW ColumnNo=" & Quote & "4" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-3" & Quote & " Param2=" & Quote & "-3" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal&#013;begin&#013;Value:= &apos;3 Months&apos;&#013;end;" & Quote & "/>" &_ "<ROW ColumnNo=" & Quote & "5" & Quote & " ColumnType=" & Quote & "A" & Quote & " Param1=" & Quote & "-4" & Quote & " Param2=" & Quote & "-4" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal&#013;begin&#013;Value:= &apos;4 Months&apos;&#013;end;" & Quote & "/>" &_ "<ROW ColumnNo=" & Quote & "6" & Quote & " ColumnType=" & Quote & "B" & Quote & " Param1=" & Quote & "-999999" & Quote & " Param2=" & Quote & "-5" & Quote & " IsLocal=" & Quote & "FALSE" & Quote & " HeaderScript=" & Quote & "ObjectPascal&#013;begin&#013;Value:= &apos;5 Month &amp; above&apos;&#013;end;" & Quote & "/>" &_ "</ROWDATA></DATAPACKET>" ' RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is True RptObject.Params.Find("AgingData").Value = lAgeData 'Fixed RptObject.Params.Find("AgingOn").Value = "I" 'Fixed RptObject.Params.Find("AllAgent").Value = true RptObject.Params.Find("AllArea").Value = true RptObject.Params.Find("AllCompany").Value = false RptObject.Params.Find("AllCompanyCategory").Value = true RptObject.Params.Find("AllControlAccount").Value = true RptObject.Params.Find("AllCurrency").Value = true RptObject.Params.Find("AllDocProject").Value = true ' RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is True ' RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is True RptObject.Params.Find("CompanyData").Value = "300-A0001"& vbCRLF & "300-C0001" 'Filter by Customer Code 300-A0001 & 300-C0001 ' RptObject.Params.Find("ControlAccountData").Value = 'Not use if AllControlAccount is True ' RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is True lDateFrom = CDate("August 1, 2016") lDateTo = CDate("August 31, 2016") RptObject.Params.Find("DateFrom").Value = lDateFrom RptObject.Params.Find("DateTo").Value = lDateTo ' RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is True RptObject.Params.Find("IncludeZeroBalance").Value = false RptObject.Params.Find("SelectDate").Value = true RptObject.Params.Find("SortBy").Value = "CompanyCategory;Code;CompanyName;Agent;Area;CurrencyCode;ControlAccount" RptObject.Params.Find("StatementDate").Value = lDateTo ' Norm same as DateTo RptObject.Params.Find("StatementType").Value = "O" 'O = Open Item, B = B/F 'Step 4: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsMain") Set lDataSet2 = RptObject.DataSets.Find("cdsDocument") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox lDataSet.FindField("Code").AsString & " " & lDataSet.FindField("CompanyName").AsString & " " & lDataSet.FindField("Balance").AsString lDataSet2.First While (not lDataSet2.eof) MsgBox lDataSet2.FindField("DocNo").AsString lDataSet2.Next Wend lDataSet.Next Wend End Function

JScript/JavaScript

This Script only can run using Internet Explorer

Example-AR_PM

AR_PM Script

<html> <head> <title>Add Customer Payment</title> <script language="JScript"> var ComServer; function CreateSQLAccServer() { ComServer = new ActiveXObject("SQLAcc.BizApp"); if (!ComServer.IsLogin) { /* check whether user has logon */ ComServer.Login('ADMIN', 'ADMIN', 'C\:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF', 'SAMPLE.FDB'); /* UserName, Password */ } } function AutoPayment() { var BizObject, lMainDataSet, lKnockOffDataSet, V; CreateSQLAccServer(); BizObject = ComServer.BizObjects.Find('AR_PM'); lMainDataSet = BizObject.DataSets.Find('MainDataSet'); lKnockOffDataSet = BizObject.DataSets.Find('cdsKnockOff'); BizObject.New(); lMainDataSet.FindField('DocKey').value = -1; lMainDataSet.FindField('DocNo').value = "Test"; lMainDataSet.FindField('Code').value = "888"; lMainDataSet.FindField('DocDate').value = "01/09/2015"; lMainDataSet.FindField('PostDate').value = "01/09/2015"; lMainDataSet.FindField('Project').value = "----"; lMainDataSet.FindField('Description').value = ""; lMainDataSet.FindField('PaymentMethod').value = "320-000"; lMainDataSet.FindField('Journal').value = "CASH"; lMainDataSet.FindField('ChequeNumber').value = ""; lMainDataSet.FindField('CurrencyCode').value = "----"; lMainDataSet.FindField('CurrencyRate').value = "1.0000000000"; lMainDataSet.FindField('DocAmt').value = "106.00"; lMainDataSet.FindField('LocalDocAmt').value = "0.00"; lMainDataSet.FindField('BankCharge').value = "0"; lMainDataSet.FindField('UnappliedAmt').value = "0"; lMainDataSet.FindField('Cancelled').value = 'F'; v = ComServer.CreateOleVariantArray(2); v.SetItem(0, 'IV'); /*Knock Off DocType*/ v.SetItem(1, 'IV-00111'); /*Knock Off DocNo*/ if (lKnockOffDataSet.Locate('DocType;DocNo', v.AsOleVariant(), false, false)){ lKnockOffDataSet.Edit(); lKnockOffDataSet.FindField('DocAmt').value = "106"; lKnockOffDataSet.FindField('KnockOff').AsString = 'T'; lKnockOffDataSet.Post(); } /* Save document */ BizObject.Save(); alert("Customer Payment - has been saved."); } </script> </head> <body> <input type="submit" value="SYNC" name="btnSubmit" onclick="AutoPayment()"> </body> </html>

Example-Customer.IV.RO

Customer.IV.RO Report Object Script

<html> <head> <title>Customer.IV.RO</title> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <script type="text/javascript"> var ComServer; function CreateSQLAccServer() { ComServer = new ActiveXObject("SQLAcc.BizApp"); if (!ComServer.IsLogin) { /* check whether user has logon */ ComServer.Login('ADMIN', 'ADMIN', 'C\:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF', 'SAMPLE.FDB'); /* UserName, Password */ } } function GetData() { var lMain, lDetail, lRptVar, lDateFrom, lDateTo; elem = document.getElementById('txt1'); CreateSQLAccServer(); lDateFrom = new Date("January 1 2015"); lDateTo = new Date("December 31, 2015"); lRptVar = ComServer.RptObjects.Find('Customer.IV.RO'); lRptVar.Params.Find('SelectDate').Value = true; lRptVar.Params.Find('DateFrom').Value = lDateFrom.valueOf(); lRptVar.Params.Find('DateTo').Value = lDateTo.valueOf(); lRptVar.Params.Find('AllAgent').Value = false; lRptVar.Params.Find('AgentData').Value = "----\nLF"; /*Filter by Agent code ---- & HALIM */ lRptVar.Params.Find('AllArea').Value = true; lRptVar.Params.Find('AllCompany').Value = true; lRptVar.Params.Find('AllCurrency').Value = true; lRptVar.Params.Find('AllDocProject').Value = true; lRptVar.Params.Find('AllCompanyCategory').Value = true; lRptVar.Params.Find('AllDocument').Value = true; lRptVar.Params.Find('IncludeCancelled').Value = false; lRptVar.Params.Find('PrintDocumentStyle').Value = false; lRptVar.Params.Find('AllItemProject').Value = true; lRptVar.Params.Find('AllPaymentMethod').Value = true; lRptVar.CalculateReport(); lMain = lRptVar.DataSets.Find('cdsMain'); lDetail = lRptVar.DataSets.Find('cdsDocDetail'); /*alert("Count -" + lRptVar.DataSets.Find('cdsDocDetail').RecordCount);*/ elem.innerHTML += 'From Main'+'\n'; lMain.First(); while (!lMain.eof) { elem.innerHTML += lMain.FindField('DocKey').AsString + ' ' + lMain.FindField('DocNo').AsString + ' ' + lMain.FindField('DocDate').AsString + ' '+ lMain.FindField('CompanyName').AsString + ' ' + lMain.FindField('DocAmt').AsString + '\n'; lMain.Next(); } elem.innerHTML += 'End Main'+'\n\n'; elem.innerHTML += 'From Detail'+'\n'; lDetail.First(); while (!lDetail.eof) { elem.innerHTML += lDetail.FindField('Dockey').AsString + ' ' + lDetail.FindField('Account').AsString + ' ' + lDetail.FindField('Description').AsString + ' ' + lDetail.FindField('Amount').AsString + '\n'; lDetail.Next(); } elem.innerHTML += 'End Detail'+'\n\n'; } </script> </head> <body> <form> <textarea id="txt1" rows="30" cols="116"></textarea> <input type="button" value="Calculate" onclick="GetData()"> </form> </body> </html>

Delphi

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

Example-GL_PV

GL_PV Script

procedure TfmMain.dxbb_GLPVClick(Sender: TObject); var lMain, lDtl, lBizObj, BizApp : Variant; lTime : TDateTime; begin lTime := Now; BizApp := CreateOleObject('SQLAcc.BizApp'); try lBizObj := BizApp.BizObjects.Find('GL_PV'); lMain := lBizObj.DataSets.Find('MainDataSet'); lDtl := lBizObj.DataSets.Find('cdsDocDetail'); lBizObj.New; lMain.FindField('DOCKEY').Value := -1; lMain.FindField('DocNo').AsString := '--CB PV Test1--'; lMain.FindField('DocDate').Value := '18 Aug 2015'; lMain.FindField('PostDate').Value := '18 Aug 2015'; lMain.FindField('TAXDATE').Value := '18 Aug 2015'; lMain.FindField('Description').AsString := 'Compacc System'; lMain.FindField('PAYMENTMETHOD').AsString := '310-001'; lMain.FindField('CHEQUENUMBER').AsString := 'MBB 213245'; lMain.FindField('DocAmt').AsFloat := 2019; lMain.FindField('Cancelled').AsString := 'F'; lMain.FindField('CHANGED').AsString := 'F'; lMain.Post; lDtl.Append; lDtl.FindField('DTLKEY').Value := -1; lDtl.FindField('DOCKEY').Value := -1; lDtl.FindField('Code').AsString := '200-300'; lDtl.FindField('DESCRIPTION').AsString := 'Maybank - Asus A555LD-xx313H'; lDtl.FindField('TAX').AsString := 'TX'; lDtl.FindField('TAXAMT').AsFloat := 114.28; lDtl.FindField('TAXINCLUSIVE').Value := 1; lDtl.FindField('AMOUNT').AsFloat := 2019; lDtl.FindField('CHANGED').AsString := '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-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, 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').AsString := '--PM Test--'; //Payment Document Number lMain.FindField('CODE').AsString := '300-A0001'; lMain.FindField('DocDate').Value := '28 Aug 2016'; lMain.FindField('PostDate').Value := '28 Aug 2016'; lMain.FindField('Description').AsString := 'Payment for A/c'; lMain.FindField('PaymentMethod').AsString := '320-000'; lMain.FindField('DocAmt').AsFloat := 1060; lMain.FindField('Cancelled').AsString := 'F'; lMain.FindField('CHANGED').AsString := '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--'); //Invoice Document Number if lKO.Locate('DocType;DocNo', V.AsOleVariant, False, False) then begin lKO.Edit; lKO.FindField('KOAmt').ASFloat := 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').AsString := 'Test-1'; lMain.FindField('Description').AsString := 'Testing - Description'; lDtl.Edit; //For 1st UOM lDtl.FindField('UOM').AsString := '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').AsString := '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; 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').AsString := '--IV Test--'; lMain.FindField('CODE').AsString := '300-A0001'; lMain.FindField('DocDate').Value := '28 Aug 2016'; lMain.FindField('PostDate').Value := '28 Aug 2016'; lMain.FindField('TAXDATE').Value := '28 Aug 2016'; lMain.FindField('Description').AsString := 'Sales'; lMain.FindField('Cancelled').AsString := 'F'; lMain.FindField('CHANGED').AsString := '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').AsString := 'ITEM A'; lDtl.FindField('ACCOUNT').AsString := '500-000'; //GL Sales Account lDtl.FindField('DESCRIPTION').AsString := 'Sales Item A'; lDtl.FindField('QTY').AsFloat := 1; lDtl.FindField('TAX').AsString := 'SR'; lDtl.FindField('TAXINCLUSIVE').Value := 0; lDtl.FindField('UNITPRICE').AsFloat := 435;//Including GST UnitPrice lDtl.FindField('AMOUNT').AsFloat := 410.37; //Must Excluding GST (Qty * UnitPrice) // Less 0.01 lDtl.FindField('TAXAMT').AsFloat := 24.63; //Extra 0.01 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').AsString := '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').AsString := 'ITEM B'; lDtl.FindField('ACCOUNT').AsString := '500-000'; lDtl.FindField('DESCRIPTION').AsString := 'Sales Item B'; lDtl.FindField('QTY').AsFloat := 1; lDtl.FindField('TAX').AsString := 'SR'; lDtl.FindField('TAXINCLUSIVE').Value := 0; lDtl.FindField('UNITPRICE').AsFloat := 94.43; lDtl.FindField('AMOUNT').AsFloat := 94.43; lDtl.FindField('TAXAMT').AsFloat := 5.66; //Less 0.01 lDtl.FindField('CHANGED').AsString := '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').AsString := 'SN1'; lDtl.FindField('ACCOUNT').AsString := '500-000'; lDtl.FindField('DESCRIPTION').AsString := 'Sales Serial Number Item'; lSN.Append; lSN.FindField('SERIALNUMBER').AsString := 'SN-00001'; lSN.Post; lSN.Append; lSN.FindField('SERIALNUMBER').AsString := 'SN-00002'; lSN.Post; lDtl.FindField('QTY').AsFloat := 2; lDtl.FindField('TAX').AsString := 'SR'; lDtl.FindField('TAXINCLUSIVE').Value := 0; lDtl.FindField('UNITPRICE').AsFloat := 94.43; lDtl.FindField('TAXAMT').AsFloat := 11.33; lDtl.FindField('CHANGED').AsString := '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  := ; //Not use if AllArea is True lRptVar.Params.Find('AllCompany').AsBoolean := True; // lRptVar.Params.Find('CompanyData').AsBlob  := ; //Not use if AllCompany is True lRptVar.Params.Find('AllCurrency').AsBoolean := True; // lRptVar.Params.Find('CurrencyData').AsBlob  := ; //Not use if AllCurrenty is True lRptVar.Params.Find('AllDocProject').AsBoolean := True; // lRptVar.Params.Find('DocProjectData').AsBlob  := ; //Not use if AllDocProject is True lRptVar.Params.Find('AllCompanyCategory').AsBoolean := True; // lRptVar.Params.Find('CompanyCategoryData').AsBlob  := ; //Not use if AllCompanyCategory is True lRptVar.Params.Find('AllDocument').AsBoolean := True; // lRptVar.Params.Find('DocumentData').AsBlob  := ; //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;

Example-Stock.Item.RO

Stock.Item.RO Report Object Script

procedure TfmMain.dxbb_ST_ITEM_ROClick(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('Stock.Item.RO'); lRptVar.Params.Find('AllItem').AsBoolean := False; lRptVar.Params.Find('AllStockGroup').AsBoolean := True; lRptVar.Params.Find('AllCustomerPriceTag').AsBoolean := True; lRptVar.Params.Find('AllSupplierPriceTag').AsBoolean := True; // lRptVar.Params.Find('CategoryData').AsBlob  := ; //Not use if SelectCategory is False // lRptVar.Params.Find('CategoryTpl').AsBlob  := ; //Not use if AllArea is True // lRptVar.Params.Find('CustomerPriceTagData').AsBlob  := ; //Not use if AllCustomerPriceTag is True // lRptVar.Params.Find('SupplierPriceTagData').AsBlob  := ; //Not use if AllSupplierPriceTag is True // lRptVar.Params.Find('DateFrom').AsDate  := '01/01/2015'; //Not use if SelectDate is False // lRptVar.Params.Find('DateTo').AsDate  := '31/12/2015'; lRptVar.Params.Find('HasAltStockItem').AsBoolean := False; lRptVar.Params.Find('HasBarcode').AsBoolean := False; lRptVar.Params.Find('HasBOM').AsBoolean := False; lRptVar.Params.Find('HasCategory').AsBoolean := False; lRptVar.Params.Find('HasCustomerItem').AsBoolean := False; lRptVar.Params.Find('HasOpeningBalance').AsBoolean := False; lRptVar.Params.Find('HasPurchasePrice').AsBoolean := False; lRptVar.Params.Find('HasSellingPrice').AsBoolean := False; lRptVar.Params.Find('HasSupplierItem').AsBoolean := False; lRptVar.Params.Find('ItemData').AsBlob := 'ANT 1.0'#13#10'COVER'; //Filter by Item code ANT 1.0 & COVER // lRptVar.Params.Find('ItemCategoryData').AsBlob  := ; //Not use if AllArea is True lRptVar.Params.Find('PrintActive').AsBoolean := True; lRptVar.Params.Find('PrintInActive').AsBoolean := True; lRptVar.Params.Find('PrintNonStockControl').AsBoolean := True; lRptVar.Params.Find('PrintStockControl').AsBoolean := True; lRptVar.Params.Find('SelectCategory').AsBoolean := False; lRptVar.Params.Find('SelectDate').AsBoolean := False; lRptVar.Params.Find('SortBy').AsString := 'Code'; // lRptVar.Params.Find('StockGroupData').AsBlob  := ; //Not use if AllStockGroup is True lRptVar.CalculateReport; cdsMain.XMLData := lRptVar.DataSets.Find('cdsMain').XMLData; //Master Data cdsDtl.XMLData := lRptVar.DataSets.Find('cdsUOM').XMLData; //Detail Data - To link Master Data use Code 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;

Example-Customer.Statement.RO

Customer.Statement.RO Report Object Script

procedure TfmMain.dxbb_StatementClick(Sender: TObject); var lBizApp, lRptVar : Variant; lTime : TDateTime; cdsMain, cdsDtl : TClientDataset; s : string; begin lTime := Now; cdsMain := TClientDataset.Create(nil); cdsDtl := TClientDataset.Create(nil); lBizApp := CreateOleObject('SQLAcc.BizApp'); s := '<?xml version="1.0" standalone="yes"?> <DATAPACKET Version="2.0"><METADATA><FIELDS>'+ '<FIELD attrname="ColumnNo" fieldtype="i4" required="true"/><FIELD attrname="ColumnType" fieldtype="string" WIDTH="1"/>'+ '<FIELD attrname="Param1" fieldtype="i4" required="true"/><FIELD attrname="Param2" fieldtype="i4" required="true"/>'+ '<FIELD attrname="IsLocal" fieldtype="boolean"/><FIELD attrname="HeaderScript" fieldtype="bin.hex" SUBTYPE="Text" WIDTH="1"/>'+ '</FIELDS><PARAMS/></METADATA><ROWDATA><ROW ColumnNo="0" ColumnType="" Param1="0" Param2="0" IsLocal="FALSE"/>'+ '<ROW ColumnNo="1" ColumnType="A" Param1="0" Param2="0" IsLocal="FALSE" HeaderScript="ObjectPascal&#013;begin&#013;Value:= &apos;Current Mth&apos;&#013;end;"/>'+ '<ROW ColumnNo="2" ColumnType="A" Param1="-1" Param2="-1" IsLocal="FALSE" HeaderScript="ObjectPascal&#013;begin&#013;Value:= &apos;1 Months&apos;&#013;end;"/>'+ '<ROW ColumnNo="3" ColumnType="A" Param1="-2" Param2="-2" IsLocal="FALSE" HeaderScript="ObjectPascal&#013;begin&#013;Value:= &apos;2 Months&apos;&#013;end;"/>'+ '<ROW ColumnNo="4" ColumnType="A" Param1="-3" Param2="-3" IsLocal="FALSE" HeaderScript="ObjectPascal&#013;begin&#013;Value:= &apos;3 Months&apos;&#013;end;"/>'+ '<ROW ColumnNo="5" ColumnType="A" Param1="-4" Param2="-4" IsLocal="FALSE" HeaderScript="ObjectPascal&#013;begin&#013;Value:= &apos;4 Months&apos;&#013;end;"/>'+ '<ROW ColumnNo="6" ColumnType="B" Param1="-999999" Param2="-5" IsLocal="FALSE" HeaderScript="ObjectPascal&#013;begin&#013;Value:= &apos;5 Month &amp; above&apos;&#013;end;"/>'+ '</ROWDATA></DATAPACKET>'; try lRptVar := null; lRptVar := lBizApp.RptObjects.Find('Customer.Statement.RO'); // lRptVar.Params.Find('AgentData').AsBlob  := ; //Not use if AllAgent is True lRptVar.Params.Find('AgingData').AsString := s; //Fixed lRptVar.Params.Find('AgingOn').AsString := 'I'; //Fixed lRptVar.Params.Find('AllAgent').AsBoolean := True; lRptVar.Params.Find('AllArea').AsBoolean := True; lRptVar.Params.Find('AllCompany').AsBoolean := False; lRptVar.Params.Find('AllCompanyCategory').AsBoolean := True; lRptVar.Params.Find('AllControlAccount').AsBoolean := True; lRptVar.Params.Find('AllCurrency').AsBoolean := True; lRptVar.Params.Find('AllDocProject').AsBoolean := True; // lRptVar.Params.Find('AreaData').AsBlob  := ; //Not use if AllArea is True // lRptVar.Params.Find('CompanyCategoryData').AsBlob  := ; //Not use if AllCompanyCategory is True lRptVar.Params.Find('CompanyData').AsBlob := '300-A0001'#13#10'300-C0001'; //Filter by Customer Code 300-A0001 & 300-C0001 // lRptVar.Params.Find('ControlAccountData').AsBlob  := ; //Not use if AllControlAccount is True // lRptVar.Params.Find('CurrencyData').AsBlob  := ; //Not use if AllCurrency is True lRptVar.Params.Find('DateFrom').AsDate := '01 Aug 2016'; lRptVar.Params.Find('DateTo').AsDate := '31 Aug 2016'; // lRptVar.Params.Find('DocProjectData').AsBlob  := ; //Not use if AllDocProject is True lRptVar.Params.Find('IncludeZeroBalance').AsBoolean := False; lRptVar.Params.Find('SelectDate').AsBoolean := True; lRptVar.Params.Find('SortBy').AsString := 'CompanyCategory;Code;CompanyName;Agent;Area;CurrencyCode;ControlAccount'; lRptVar.Params.Find('StatementDate').AsDate := '31 Aug 2016'; // Norm same as DateTo lRptVar.Params.Find('StatementType').AsString := 'O'; //O := Open Item, B := B/F lRptVar.CalculateReport; cdsMain.XMLData := lRptVar.DataSets.Find('cdsMain').XMLData; //Master Data cdsDtl.XMLData := lRptVar.DataSets.Find('cdsDocument').XMLData; //Detail Data - To link Master Data use Code 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