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-GL_JE

GL_JE Script

'Copy below script & paste to notepad & name it as eg GL_JE.vbs Call InsertGLJE Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function InsertGLJE 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("GL_JE") '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 23, 2017") BizObject.New lMainDataSet.FindField("DocKey").value = -1 lMainDataSet.FindField("DocNo").value = "--JV Test--" lMainDataSet.FindField("DocDate").value = lDate lMainDataSet.FindField("PostDate").value = lDate lMainDataSet.FindField("Description").value = "testing desc header" lMainDataSet.Post 'Step 5: Insert Data - Detail 'For Tax Inclusive lDetailDataSet.Append lDetailDataSet.FindField("DtlKey").value = -1 lDetailDataSet.FindField("DocKey").value = -1 lDetailDataSet.FindField("SEQ").value = 1 lDetailDataSet.FindField("Code").value = "610-1000" lDetailDataSet.FindField("Project").value = "----" lDetailDataSet.FindField("Description").value = "testing desc1" lDetailDataSet.FindField("Tax").value = "TX" 'lDetailDataSet.FindField("LOCALTAXAMT").value = 17.32 //For JE you can't override (i.e. system auto calc) lDetailDataSet.FindField("TaxInclusive").Value = 1 lDetailDataSet.FindField("LocalDR").value = 306 '//Including GST if TAXINCLUSIVE = 1 lDetailDataSet.FindField("DR").value = 306 '//If wanted DR <> LocalDR lDetailDataSet.FindField("Changed").value = "F" 'For Tax Exclusive lDetailDataSet.Append lDetailDataSet.FindField("DtlKey").value = -1 lDetailDataSet.FindField("DocKey").value = -1 lDetailDataSet.FindField("SEQ").value = 2 lDetailDataSet.FindField("Code").value = "605-200" lDetailDataSet.FindField("Project").value = "----" lDetailDataSet.FindField("Description").value = "testing desc2" lDetailDataSet.FindField("Tax").value = "TX" lDetailDataSet.FindField("TaxInclusive").value = 0 lDetailDataSet.FindField("LocalCR").value = 100 lDetailDataSet.FindField("Changed").value = "F" lDetailDataSet.Post 'For No GST lDetailDataSet.Append lDetailDataSet.FindField("DtlKey").value = -1 lDetailDataSet.FindField("DocKey").value = -1 lDetailDataSet.FindField("SEQ").value = 3 lDetailDataSet.FindField("Code").value = "610-002" lDetailDataSet.FindField("Project").value = "----" lDetailDataSet.FindField("Description").value = "testing desc3" lDetailDataSet.FindField("Tax").value = "" lDetailDataSet.FindField("TaxInclusive").value = 0 lDetailDataSet.FindField("LocalCR").value = 200 lDetailDataSet.FindField("Changed").value = "F" lDetailDataSet.Post 'Step 6: Save Document BizObject.Save MsgBox "Done" End Function

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 MsgBox "Done" End Function

Example-AR_PM-Edit

AR_PM Edit Script

'Copy below script & paste to notepad & name it as eg AR_IV.vbs Call EditARPM Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function EditARPM Dim ComServer, BizObject, lDataSet, lDataSet2, lDocKey, lDocNo, lIVNo, v(1) '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_PM") 'Step 3: Set Dataset Set lMainDataSet = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data Set lDetailDataSet = BizObject.DataSets.Find("cdsKnockOff") 'lDetailDataSet contains detail data 'Step 4 : Find OR Number lDocNo = "OR-00022" lDocKey = BizObject.FindKeyByRef("DocNo", lDocNo) BizObject.Params.Find("DocKey").Value = lDocKey If not IsNull(lDocKey) Then BizObject.Open BizObject.Edit lMainDataSet.Edit lMainDataSet.FindField("Description").AsString = "testing" 'Step 5: Knock Off IV lIVNO = "IV-00006" v(0) = "IV" v(1) = lIVNO if (lDetailDataSet.Locate("DocType;DocNo", v, false, false)) then lDetailDataSet.Edit lDetailDataSet.FindField("KOAmt").value = 50.12 lDetailDataSet.FindField("KnockOff").AsString = "T" lDetailDataSet.Post MsgBox "yes" End IF 'Step 6: Save Document BizObject.Save MsgBox "Done" END IF 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

[top]

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>

[top]

Delphi

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

Example-GL_JE

GL_JE Script

procedure TfmMain.dxbb_GL_JEClick(Sender: TObject); var BizApp, lMain, lDtl, lBizObj, lDocKey : Variant; i : integer; begin BizApp := CreateOleObject('SQLAcc.BizApp'); try lBizObj := BizApp.BizObjects.Find('GL_JE'); lMain := lBizObj.DataSets.Find('MainDataSet'); lDtl := lBizObj.DataSets.Find('cdsDocDetail'); lBizObj.New; lMain.FindField('DOCKEY').Value := -1; lMain.FindField('DocNo').Value := '--JE Test--'; lMain.FindField('DocDate').Value := '28 Aug 2017'; lMain.FindField('PostDate').Value := '28 Aug 2017'; lMain.FindField('TAXDATE').Value := '28 Aug 2017'; lMain.FindField('Description').Value := 'Postage'; lMain.FindField('Cancelled').Value := 'F'; lMain.FindField('DOCNOSETKEY').Value := 0; lMain.FindField('CHANGED').Value := 'F'; lMain.Post; //For Tax Inclusive lDtl.Append; lDtl.FindField('DTLKEY').Value := -1; lDtl.FindField('DOCKEY').Value := -1; lDtl.FindField('SEQ').Value := 1; lDtl.FindField('CODE').Value := '610-1000'; lDtl.FindField('DESCRIPTION').Value := 'Item Desc 1'; lDtl.FindField('TAX').Value := 'TX'; // lDtl.FindField('LOCALTAXAMT').AsFloat := 17.32;//For JE you can't override (i.e. system auto calc) lDtl.FindField('TAXINCLUSIVE').Value := 1; lDtl.FindField('LocalDR').AsFloat := 306;//Including GST if TAXINCLUSIVE = 1 lDtl.FindField('DR').AsFloat := 306;//if wanted DR <> LocalDR lDtl.FindField('CHANGED').Value := 'F'; lDtl.Post; //For Tax Exclusive lDtl.Append; lDtl.FindField('DTLKEY').Value := -1; lDtl.FindField('DOCKEY').Value := -1; lDtl.FindField('SEQ').Value := 2; lDtl.FindField('CODE').Value := '605-200'; lDtl.FindField('DESCRIPTION').Value := 'Item Desc 2'; lDtl.FindField('TAX').Value := 'TX'; lDtl.FindField('TAXINCLUSIVE').Value := 0; lDtl.FindField('LocalCR').AsFloat := 100; lDtl.FindField('CHANGED').Value := 'F'; lDtl.Post; //For No GST lDtl.Append; lDtl.FindField('DTLKEY').Value := -1; lDtl.FindField('DOCKEY').Value := -1; lDtl.FindField('SEQ').Value := 3; lDtl.FindField('CODE').Value := '610-002'; lDtl.FindField('DESCRIPTION').Value := 'Item Desc 3'; lDtl.FindField('TAX').Value := ''; lDtl.FindField('TAXINCLUSIVE').Value := 0; lDtl.FindField('LocalCR').AsFloat := 200; lDtl.FindField('CHANGED').Value := 'F'; lDtl.Post; lBizObj.Save; MessageDlg('GL_JE-DocNo : ''--JE Test--'' Posted...', mtInformation, [mbOK],0); finally lBizObj.Close; end; end;

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, cdsCat : TClientDataset; lSQL : string; begin lTime := Now; cdsMain := TClientDataset.Create(nil); cdsDtl := TClientDataset.Create(nil); cdsCat := TClientDataset.Create(nil); lBizApp := CreateOleObject('SQLAcc.BizApp'); lSQL := 'SELECT * FROM ST_ITEM_CATEGORY ' + 'WHERE Category IN (''B-MOTOLORA'',''B-MAXIS'')'; cdsCat.Data := lBizApp.DBManager.Execute(lSQL); //For filtering Category only try lRptVar := null; lRptVar := lBizApp.RptObjects.Find('Stock.Item.RO'); lRptVar.Params.Find('AllItem').AsBoolean := True; 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 // lRptVar.Params.Find('CategoryTpl').AsBlob  := ; //Not use // 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  := cdsCat.XMLData; //Not use if SelectCategory is False 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; cdsCat.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]

Example-GL Account List For Sales & Purchase 1

GL_Acc Object Script

procedure TfmMain.dxbb_GLAccClick(Sender: TObject); var BizApp : Variant; lDataSet : TClientDataSet; ASQL : String; begin //For Debtor List => SpecialAccType = DR //For Creditor List => SpecialAccType = CR BizApp := CreateOleObject('SQLAcc.BizApp'); lDataSet := TClientDataSet.Create(Self); try ASQL := 'SELECT A.Code, A.Description FROM GL_ACC A '+ 'LEFT OUTER JOIN GL_ACC B ON (A.Dockey=B.Parent) '+ 'WHERE A.Parent<>-1 ' + 'AND B.Dockey IS NULL ' + 'AND A.SpecialAccType = '''' '+ 'ORDER BY A.CODE '; lDataSet.Data := BizApp.DBManager.Execute(ASQL); lDataset.SaveToFile('C:\Temp\_List.xml', dfXML); //Export To XML file finally lDataSet.Free; end; end;

[top]

Example-GL Account List For Sales & Purchase 2

GL_Acc Object Script

procedure TfmMain.dxbb_GLAccClick(Sender: TObject); var BizApp, BizObj : Variant; lDataSet1, lDataSet2 : TClientDataSet; ASQL : String; begin //For Debtor List => SpecialAccType = DR //For Creditor List => SpecialAccType = CR BizApp := CreateOleObject('SQLAcc.BizApp'); BizObj := BizApp.BizObjects.Find('GL_Acc'); lDataSet1 := TClientDataSet.Create(Self); lDataSet2 := TClientDataSet.Create(Self); try ASQL :='Parent<>-1 ' + 'AND (SpecialAccType IS NULL OR SpecialAccType = '''' ' + 'OR SpecialAccType = '''') '; lDataSet1.XMLData:= BizObj.Select('Dockey,Code,Description', ASQL, 'Code', 'SX', '', ''); lDataSet2.XMLData:= BizObj.Select('Parent', '', 'Code', 'SX', '', ''); lDataset2.First; // Remove Parent Account while not lDataset2.Eof do begin if lDataset1.Locate('Dockey', lDataSet2.FindField('Parent').AsString, []) then lDataset1.Delete; lDataset2.Next; end; lDataset1.MergeChangeLog; lDataset1.SaveToFile('C:\Temp\_List.xml', dfXML); //Export To XML file finally lDataSet1.Free; lDataSet2.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'; ...

See also

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