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. Able to do Automation (i.e. auto login & update data).
6. Support GST.
7. Free of Charge (Freeware) for End User on SDK Linking.

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

• Highlight in Yellow Color is the Application/Function you need to Develop/Create.

Example 1 - POS System

Example 2 - Cloud System

[top]

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]

Example External Program

VB. Net

• http://www.estream.com.my/document/SQLLink/Accountinglink-VBDotNet.zip

[top]

VB Script

Example-Get Company Profile

Company Profile Script

'Copy below script & paste to notepad & name it as eg Common.Agent.RO.vbs Call GetCoInfo Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function GetCoInfo Dim ComServer, RptObject, lDataSet '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("Common.Agent.RO") 'Step 3: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsProfile") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox "Company Name : " & lDataSet.FindField("CompanyName").AsString MsgBox "Remark : " & lDataSet.FindField("Remark").AsString MsgBox "ROC No : " & lDataSet.FindField("RegisterNo").AsString MsgBox "GST No : " & lDataSet.FindField("GSTNo").AsString MsgBox "Address 1 : " & lDataSet.FindField("Address1").AsString MsgBox "Address 2 : " & lDataSet.FindField("Address2").AsString MsgBox "Address 3 : " & lDataSet.FindField("Address3").AsString MsgBox "Address 4 : " & lDataSet.FindField("Address4").AsString MsgBox "Attention : " & lDataSet.FindField("Attention").AsString MsgBox "Phone : " & lDataSet.FindField("Phone1").AsString MsgBox "Fax : " & lDataSet.FindField("Fax1").AsString MsgBox "E-Mail : " & lDataSet.FindField("EMail").AsString lDataSet.Next Wend End Function

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

Example-AR_PM

AR_PM Script

'Copy below script & paste to notepad & name it as eg AR_PM.vbs Call PostData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function PostData Dim ComServer, BizObject, lIVNo, v(1), 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_PM") 'Step 3: Set Dataset Set lMain = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data Set lDetailDataSet = BizObject.DataSets.Find("cdsKnockOff") 'lDetailDataSet contains detail data 'Step 4 : Posting lDate = CDate("January 23, 2017") BizObject.New lMain.FindField("DOCKEY").Value = -1 lMain.FindField("DocNo").AsString = "--PM Test--" lMain.FindField("CODE").AsString = "300-A001" lMain.FindField("DocDate").Value = lDate lMain.FindField("PostDate").Value = lDate lMain.FindField("Description").AsString = "Payment for A/c" lMain.FindField("PaymentMethod").AsString = "310-000" lMain.FindField("DocAmt").AsFloat = 65.57 lMain.FindField("Cancelled").AsString = "F" 'Step 5: Knock Off IV lIVNO = "IV-16550" v(0) = "IV" v(1) = lIVNO if (lDetailDataSet.Locate("DocType;DocNo", v, false, false)) then lDetailDataSet.Edit lDetailDataSet.FindField("KOAmt").value = 65.57 lDetailDataSet.FindField("KnockOff").AsString = "T" lDetailDataSet.Post End IF 'Step 6: Save Document BizObject.Save BizObject.Close MsgBox "Done" 'END IF 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 BizObject.Close 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

Example-GL.CB.RO

GL.CB.RO Report Object Script

'Copy below script & paste to notepad & name it as eg GL.CB.RO.vbs Call GetCLCBData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function GetCLCBData Dim ComServer, RptObject, lDataSet, lDataSet2, lDataSet3, 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("GL.CB.RO") 'Step 3: Spool parameters RptObject.Params.Find("AllCurrency").Value = true RptObject.Params.Find("AllDocProject").Value = true RptObject.Params.Find("AllDocument").Value = true RptObject.Params.Find("AllItemProject").Value = true RptObject.Params.Find("AllItemArea").Value = true RptObject.Params.Find("AllItemAgent").Value = true RptObject.Params.Find("AllDocAgent").Value = true RptObject.Params.Find("AllDocArea").Value = true RptObject.Params.Find("AllPaymentMethod").Value = false 'RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is true lDateFrom = CDate("October 27, 2016") lDateTo = CDate("December 31, 2016") 'For Post Date From & Date To' 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("DocAreaData").Value = 'Not use if AllDocArea is true 'RptObject.Params.Find("DocAgentData").Value = 'Not use if AllDocAgent is true 'RptObject.Params.Find("ItemAgentData").Value = 'Not use if AllItemAgent is true RptObject.Params.Find("DocType").Value = "PV" 'PV for Payment Voucher & OR for Official Receipt 'RptObject.Params.Find("DocumentData").Value = 'Not use if AllDocument is true 'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data RptObject.Params.Find("IncludeCancelled").Value = false 'RptObject.Params.Find("ItemAreaData").Value = 'Not use if AllItemArea is true 'RptObject.Params.Find("ItemProjectData").Value = 'Not use if AllItemProject is true RptObject.Params.Find("PaymentMethodData").Value = "310-000" RptObject.Params.Find("PrintDocumentStyle").Value = false RptObject.Params.Find("SelectDate").Value = true 'For Post Date 'RptObject.Params.Find("SelectDocDate").Value = 'For Doc Date RptObject.Params.Find("SortBy").Value = "PostDate;DocNo" 'Step 4: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsMain") Set lDataSet2 = RptObject.DataSets.Find("cdsDetail") Set lDataSet3 = RptObject.DataSets.Find("cdsTax") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox lDataSet.FindField("DocNo").AsString & " " & lDataSet.FindField("Description").AsString lDataSet2.First While (not lDataSet2.eof) MsgBox lDataSet2.FindField("Code").AsString & " " & lDataSet2.FindField("Description").AsString lDataSet2.Next Wend lDataSet.Next Wend 'For GST List MsgBox "GST Code Count " & lDataSet3.RecordCount lDataSet3.First While (not lDataSet3.eof) MsgBox lDataSet3.FindField("Code").AsString lDataSet3.Next Wend End Function

Example-AR_Customer-Edit

AR_Customer - Edit Script

'Copy below script & paste to notepad & name it as eg AR_Customer.vbs Call EditARCustomer Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function EditARCustomer Dim ComServer, BizObject, lDataSet, lDataSet2 '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_Customer") 'Step 3 Set Dataset Set lMainDataSet = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data Set lDetailDataSet = BizObject.DataSets.Find("cdsBranch") 'lDetailDataSet contains detail data 'Step 4 Find Customer Code BizObject.Params.Find("Code").Value = "300-Test" BizObject.Open BizObject.Edit lMainDataSet.Edit lMainDataSet.FindField("CompanyName").value = "Testing 123-Changed" 'Step 5 For 1st Branch v = "BILLING" if (lDetailDataSet.Locate("BranchName", v, false, false)) then lDetailDataSet.Edit lDetailDataSet.FindField("Address1").AsString = "Address1-Changed" lDetailDataSet.FindField("Address2").AsString = "Address2-Changed" lDetailDataSet.FindField("Address3").AsString = "Address3-Changed" lDetailDataSet.FindField("Address4").AsString = "Address4-Changed" lDetailDataSet.FindField("Attention").AsString = "Attention-Changed" lDetailDataSet.FindField("Phone1").AsString = "Phone1-Changed" lDetailDataSet.FindField("Fax1").AsString = "Fax1-Changed" lDetailDataSet.FindField("Email").AsString = "EmailAddress-Changed" lDetailDataSet.Post end if 'Step 5 For 2nd Branch v = "Branch1" if (lDetailDataSet.Locate("BranchName", v, false, false)) then lDetailDataSet.Edit lDetailDataSet.FindField("Address1").AsString = "DAddress1-Changed" lDetailDataSet.FindField("Address2").AsString = "DAddress2-Changed" lDetailDataSet.FindField("Address3").AsString = "DAddress3-Changed" lDetailDataSet.FindField("Address4").AsString = "DAddress4-Changed" lDetailDataSet.FindField("Attention").AsString = "DAttention-Changed" lDetailDataSet.FindField("Phone1").AsString = "DPhone1-Changed" lDetailDataSet.FindField("Fax1").AsString = "DFax1-Changed" lDetailDataSet.FindField("Email").AsString = "DEmailAddress-Changed" lDetailDataSet.Post end IF 'Step 6 Save Customer BizObject.Save BizObject.Close MsgBox "Done" End Function

Example-GL.TrialBalance.RO

GL Trial Balance Report Object Script

'Copy below script & paste to notepad & name it as eg GL.TrialBalance.RO.vbs Call GetGLTBData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function GetGLTBData 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("GL.TrialBalance.RO") 'Step 3: Spool parameters RptObject.Params.Find("AllProject").Value = true RptObject.Params.Find("AllAgent").Value = true RptObject.Params.Find("AllArea").Value = true RptObject.Params.Find("LedgerType").Value = "G" 'G = Legder, S = Debtor, P = Supplier 'RptObject.Params.Find("ProjectData").Value = 'Not use if AllArea is true RptObject.Params.Find("ShowZeroBalance").Value = false RptObject.Params.Find("SortBy").Value = "PostDate;DocNo" RptObject.Params.Find("UseDescription2").Value = false lDateFrom = CDate("October 27, 2016") lDateTo = CDate("December 31, 2016") 'For Post Date From & Date To' RptObject.Params.Find("DateFrom").Value = lDateFrom RptObject.Params.Find("DateTo").Value = lDateTo 'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true 'RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is true RptObject.Params.Find("SortBy").Value = "Code;Description" 'Step 4: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsMain") Set lDataSet2 = RptObject.DataSets.Find("cdsGLAccount") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox "Month To Date" MsgBox lDataSet.FindField("Code").AsString & " DR=>" & lDataSet.FindField("MTDDR").AsString & " CR=>" & lDataSet.FindField("MTDCR").AsString MsgBox "Year To Date" MsgBox lDataSet.FindField("Code").AsString & " DR=>" & lDataSet.FindField("YTDDR").AsString & " CR=>" & lDataSet.FindField("YTDCR").AsString lDataSet.Next Wend 'For Account List lDataSet2.First While (not lDataSet2.eof) MsgBox lDataSet2.FindField("Code").AsString & " " & lDataSet2.FindField("Description").AsString lDataSet2.Next Wend End Function

Example-Customer.Aging.RO

Customer Aging Report Object Script

'Copy below script & paste to notepad & name it as eg customer_aging_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.Aging.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("ActualGroupBy").Value = "Code;CompanyName" 'Fixed ' RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is True RptObject.Params.Find("AgingData").Value = lAgeData 'Fixed lDateTo = CDate("August 31, 2016") RptObject.Params.Find("AgingDate").Value = lDateTo 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 ' RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is True RptObject.Params.Find("FilterPostDate").Value = false ' RptObject.Params.Find("GroupBy").Value = 'Not use RptObject.Params.Find("IncludePDC").Value = false RptObject.Params.Find("IncludeZeroBalance").Value = false RptObject.Params.Find("SortBy").Value = "Code;CompanyName" lDateTo = CDate("August 31, 2016") RptObject.Params.Find("DateTo").Value = lDateTo '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 lDataSet2.First While (not lDataSet2.eof) MsgBox lDataSet2.FindField("DocNo").AsString lDataSet2.Next Wend lDataSet.Next Wend End Function

Example-GL.JE.RO

GL Journal Voucher Report Object Script

'Copy below script & paste to notepad & name it as eg GLJE_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("GL.JE.RO") 'Step 3: Spool parameters RptObject.Params.Find("AllCurrency").Value = true RptObject.Params.Find("AllDocProject").Value = true RptObject.Params.Find("AllDocument").Value = true RptObject.Params.Find("AllItemProject").Value = true RptObject.Params.Find("AllItemArea").Value = true RptObject.Params.Find("AllItemAgent").Value = true RptObject.Params.Find("AllDocAgent").Value = true RptObject.Params.Find("AllDocArea").Value = true RptObject.Params.Find("AllPaymentMethod").Value = true lDateFrom = CDate("December 1, 2017") lDateTo = CDate("December 31, 2017") RptObject.Params.Find("DateFrom").Value = lDateFrom RptObject.Params.Find("DocDateFrom").Value = lDateFrom RptObject.Params.Find("DateTo").Value = lDateTo RptObject.Params.Find("DocDateTo").Value = lDateTo RptObject.Params.Find("IncludeCancelled").Value = false RptObject.Params.Find("PrintDocumentStyle").Value = false RptObject.Params.Find("SelectDate").Value = true 'Post Date RptObject.Params.Find("SelectDocDate").Value = false 'Doc Date 'Step 4: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsMain") Set lDataSet2 = RptObject.DataSets.Find("cdsDetail") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox lDataSet.FindField("DocNo").AsString & " " & lDataSet.FindField("Description").AsString lDataSet2.First While (not lDataSet2.eof) MsgBox lDataSet2.FindField("Code").AsString & " " & lDataSet2.FindField("Description").AsString lDataSet2.Next Wend lDataSet.Next Wend End Function

Example-SL_CS

SL_CS Script

'Copy below script & paste to notepad & name it as eg SL_CS.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("SL_CS") '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-C0001" 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("Qty").value = 1 lDetailDataSet.FindField("Tax").value = "SR" lDetailDataSet.FindField("TaxInclusive").value = 0 lDetailDataSet.FindField("UnitPrice").value = 410.37 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("Qty").value = 1 lDetailDataSet.FindField("Tax").value = "SR" lDetailDataSet.FindField("TaxInclusive").value = 0 lDetailDataSet.FindField("UnitPrice").value = 94.43 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 BizObject.Close MsgBox "Done" End Function

Example-Customer.PM.RO

Customer.PM.RO Script

'Copy below script & paste to notepad & name it as eg Customer.PM.RO.vbs Call GetARPMData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function GetARPMData Dim ComServer, RptObject, lDataSet, lDataSet2, lDataSet3, 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.PM.RO") 'Step 3: Spool parameters 'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true 'RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is true RptObject.Params.Find("AllAgent").Value = true RptObject.Params.Find("AllCompanyCategory").Value = true RptObject.Params.Find("AllArea").Value = true RptObject.Params.Find("AllCompany").Value = true RptObject.Params.Find("AllCurrency").Value = true RptObject.Params.Find("AllDocument").Value = false RptObject.Params.Find("AllPaymentMethod").Value = true 'RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is true 'RptObject.Params.Find("CompanyData").Value = 'Not use if AllCompany is true 'RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is true lDateFrom = CDate("January 01, 2017") lDateTo = CDate("December 31, 2017") RptObject.Params.Find("DateFrom").Value = lDateFrom RptObject.Params.Find("DateTo").Value = lDateTo RptObject.Params.Find("DocumentData").Value = "VCOR-00049" 'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data RptObject.Params.Find("IncludeCancelled").Value = false 'RptObject.Params.Find("PaymentMethodData").Value = 'Not use if AllPaymentMethod is true RptObject.Params.Find("PrintDocumentStyle").Value = false RptObject.Params.Find("SelectDate").Value = true RptObject.Params.Find("ShowUnappliedAmountOnly").Value = false RptObject.Params.Find("SortBy").Value = "PostDate;DocNo;Code" RptObject.Params.Find("AllDocProject").Value = true RptObject.Params.Find("AllItemProject").Value = true 'RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is true 'RptObject.Params.Find("ItemProjectData").Value = 'Not use if AllItemProject is true 'Step 4: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsMain") Set lDataSet2 = RptObject.DataSets.Find("cdsKnockOff") Set lDataSet3 = RptObject.DataSets.Find("cdsKnockOffDetail") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox lDataSet.FindField("DocKey").AsString & " " & lDataSet.FindField("DocNo").AsString & " " & lDataSet.FindField("Code").AsString MsgBox "cdsKnockOff Count " & lDataSet2.RecordCount lDataSet2.First While (not lDataSet2.eof) MsgBox lDataSet2.FindField("FromDocKey").AsString & " " & lDataSet2.FindField("DocNo").AsString & " " & lDataSet2.FindField("KOAmt").AsString lDataSet2.Next Wend MsgBox "cdsKnockOffDetail Count " & lDataSet3.RecordCount lDataSet3.First While (not lDataSet3.eof) MsgBox lDataSet3.FindField("FromDocKey").AsString & " " & lDataSet3.FindField("DocNo").AsString & " " & lDataSet3.FindField("KOAmt").AsString lDataSet3.Next Wend lDataSet.Next Wend End Function

Example-Sales.IV.RO

Sales.IV.RO Script

'Copy below script & paste to notepad & name it as eg Sales.IV.RO.vbs Call GetSalesIVData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function GetSalesIVData Dim ComServer, RptObject, lDataSet, lDataSet2, lDataSet3, 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("Sales.IV.RO") 'Step 3: Spool parameters 'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true RptObject.Params.Find("AllAgent").Value = true RptObject.Params.Find("AllArea").Value = true RptObject.Params.Find("AllCompany").Value = false RptObject.Params.Find("AllCurrency").Value = true RptObject.Params.Find("AllDocProject").Value = true RptObject.Params.Find("AllDocument").Value = true RptObject.Params.Find("AllItem").Value = true RptObject.Params.Find("AllItemProject").Value = true RptObject.Params.Find("AllLocation").Value = true RptObject.Params.Find("AllStockGroup").Value = true RptObject.Params.Find("AllCompanyCategory").Value = true RptObject.Params.Find("AllBatch").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-C0001" 'RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is true lDateFrom = CDate("January 01, 2017") lDateTo = CDate("December 31, 2017") 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("DocumentData").Value = 'Not use if AllDocument is true 'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data 'RptObject.Params.Find("CategoryData").Value = 'Not use if HasCategory is false 'RptObject.Params.Find("CategoryTpl").Value = 'For Internal use only RptObject.Params.Find("IncludeCancelled").Value = false RptObject.Params.Find("HasCategory").Value = false 'RptObject.Params.Find("ItemData").Value = 'Not use if AllItem is true 'RptObject.Params.Find("ItemProjectData").Value = 'Not use if AllItemProject is true 'RptObject.Params.Find("LocationData").Value = 'Not use if AllLocation is true 'RptObject.Params.Find("ItemCategoryData").Value = 'For Internal use only 'RptObject.Params.Find("BatchData").Value = 'Not use if AllBatch is true RptObject.Params.Find("PrintDocumentStyle").Value = false RptObject.Params.Find("SelectDate").Value = true RptObject.Params.Find("SortBy").Value = "PostDate;DocNo;Code" 'RptObject.Params.Find("StockGroupData").Value = 'Not use if AllStockGroup is true 'Step 4: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsMain") Set lDataSet2 = RptObject.DataSets.Find("cdsDocDetail") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox lDataSet.FindField("DocKey").AsString & " " & lDataSet.FindField("DocNo").AsString & " " & lDataSet.FindField("Code").AsString MsgBox "cdsDocDetail Count " & lDataSet2.RecordCount lDataSet2.First While (not lDataSet2.eof) MsgBox lDataSet2.FindField("DocKey").AsString & " " & lDataSet2.FindField("ItemCode").AsString & " " & lDataSet2.FindField("Amount").AsString lDataSet2.Next Wend lDataSet.Next Wend End Function

Example-Customer.DueDocument.RO

Customer.DueDocument.RO Script

'Copy below script & paste to notepad & name it as eg Customer.DueDocument.RO.vbs Call GetData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function GetData Dim ComServer, RptObject, lDataSet, 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.DueDocument.RO") 'Step 3: Spool parameters 'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true 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("AllDocProject").Value = true RptObject.Params.Find("AllItemProject").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-A0003" 'RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is true 'RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is true 'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data 'RptObject.Params.Find("ItemProjectData").Value = 'Not use if AllItemProject is true RptObject.Params.Find("PrintContra").Value = true RptObject.Params.Find("PrintCreditNote").Value = true RptObject.Params.Find("PrintDebitNote").Value = true RptObject.Params.Find("PrintInvoice").Value = true RptObject.Params.Find("PrintOverDue").Value = true RptObject.Params.Find("PrintUnDue").Value = true RptObject.Params.Find("ShowForeignCurrency").Value = true RptObject.Params.Find("ShowLocalCurrency").Value = true RptObject.Params.Find("SortBy").Value = "PostDate;DocNo;Code" lDateTo = CDate("April 12, 2018") RptObject.Params.Find("DateTo").Value = lDateTo RptObject.Params.Find("IncludePDCheque").Value = true 'Step 4: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsMain") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox "Dockey " & lDataSet.FindField("DocKey").AsString MsgBox "DocNo " & lDataSet.FindField("DocNo").AsString MsgBox "DocAmt " & lDataSet.FindField("DocAmt").AsString MsgBox "Outstanding " & lDataSet.FindField("Outstanding").AsString MsgBox "Code " & lDataSet.FindField("Code").AsString lDataSet.Next Wend End Function

Example-Customer.IV.RO

Customer.IV.RO Script

'Copy below script & paste to notepad & name it as eg Customer.IV.RO.vbs Call GetData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function GetData Dim ComServer, RptObject, lDataSet, lDataSet2, lDataSet3, 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.IV.RO") 'Step 3: Spool parameters 'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true 'RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is true RptObject.Params.Find("AllAgent").Value = true RptObject.Params.Find("AllCompanyCategory").Value = true RptObject.Params.Find("AllArea").Value = true RptObject.Params.Find("AllCompany").Value = true RptObject.Params.Find("AllCurrency").Value = true RptObject.Params.Find("AllDocument").Value = false RptObject.Params.Find("AllPaymentMethod").Value = true 'RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is true 'RptObject.Params.Find("CompanyData").Value = 'Not use if AllCompany is true 'RptObject.Params.Find("CurrencyData").Value = 'Not use if AllCurrency is true lDateFrom = CDate("January 01, 2017") lDateTo = CDate("December 31, 2017") RptObject.Params.Find("DateFrom").Value = lDateFrom RptObject.Params.Find("DateTo").Value = lDateTo RptObject.Params.Find("DocumentData").Value = "IV-00012" 'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data RptObject.Params.Find("IncludeCancelled").Value = true 'RptObject.Params.Find("PaymentMethodData").Value = 'Not use if AllPaymentMethod is true RptObject.Params.Find("PrintDocumentStyle").Value = false RptObject.Params.Find("SelectDate").Value = true RptObject.Params.Find("ShowUnappliedAmountOnly").Value = true RptObject.Params.Find("SortBy").Value = "DocDate;DocNo;Code" RptObject.Params.Find("AllDocProject").Value = true RptObject.Params.Find("AllItemProject").Value = true 'RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is true 'RptObject.Params.Find("ItemProjectData ").Value = 'Not use if AllItemProject is true 'Step 4: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsMain") Set lDataSet2 = RptObject.DataSets.Find("cdsDocDetail") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox "DocNo " & lDataSet.FindField("DocNo").AsString MsgBox "DocAmt " & lDataSet.FindField("DocAmt").AsString MsgBox "PaymentAmt " & lDataSet.FindField("PaymentAmt").AsString MsgBox "cdsDocDetail Count " & lDataSet2.RecordCount lDataSet2.First While (not lDataSet2.eof) MsgBox "Account " & lDataSet2.FindField("Account").AsString MsgBox "Description " & lDataSet2.FindField("Description").AsString MsgBox "Amount " & lDataSet2.FindField("Amount").AsString lDataSet2.Next Wend lDataSet.Next Wend End Function

Example-Sales.OutstandingSO.RO

Sales.OutstandingSO.RO Script

'Copy below script & paste to notepad & name it as eg Sales.OutstandingSO.RO.vbs Call GetData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function GetData Dim ComServer, RptObject, lDataSet, lDataSet2, lDataSet3, 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("Sales.OutstandingSO.RO") 'Step 3: Spool parameters 'RptObject.Params.Find("AgentData").Value = 'Not use if AllAgent is true 'RptObject.Params.Find("CompanyCategoryData").Value = 'Not use if AllCompanyCategory is true 'RptObject.Params.Find("LocationData").Value = 'Not use if AllLocation is true 'RptObject.Params.Find("StockGroupData").Value = 'Not use if AllStockGroup is true RptObject.Params.Find("AllAgent").Value = true RptObject.Params.Find("AllArea").Value = true RptObject.Params.Find("AllCompany").Value = true RptObject.Params.Find("AllDocument").Value = false RptObject.Params.Find("AllItem").Value = true RptObject.Params.Find("AllItemProject").Value = true 'RptObject.Params.Find("AreaData").Value = 'Not use if AllArea is true 'RptObject.Params.Find("CompanyData").Value = 'Not use if AllCompany is true lDateFrom = CDate("January 01, 2017") lDateTo = CDate("December 31, 2017") 'RptObject.Params.Find("DateFrom").Value = lDateFrom 'RptObject.Params.Find("DateTo").Value = lDateTo 'RptObject.Params.Find("DeliveryDateFrom").Value = lDateFrom 'RptObject.Params.Find("DeliveryDateTo").Value = lDateTo RptObject.Params.Find("DocumentData").Value = "SO-00126"& vbCRLF & "SO-00127" 'RptObject.Params.Find("GroupBy").Value = 'If you wanted to grouping the data RptObject.Params.Find("IncludeCancelled").Value = false 'RptObject.Params.Find("ItemData").Value = 'Not use if AllItem is true RptObject.Params.Find("PrintFulfilledItem").Value = true 'Print transfered info RptObject.Params.Find("PrintOutstandingItem").Value = true 'Print untransfer info 'RptObject.Params.Find("ItemProjectData").Value = 'Not use if AllItemProject is true RptObject.Params.Find("SelectDate").Value = false RptObject.Params.Find("SelectDeliveryDate").Value = false RptObject.Params.Find("SortBy").Value = "DocDate;DocNo;Code" RptObject.Params.Find("AllDocProject").Value = true RptObject.Params.Find("AllLocation").Value = true RptObject.Params.Find("AllCompanyCategory").Value = true RptObject.Params.Find("AllBatch").Value = true RptObject.Params.Find("HasCategory").Value = false RptObject.Params.Find("AllStockGroup").Value = true 'RptObject.Params.Find("CategoryData").Value = 'For Internal use only 'RptObject.Params.Find("CategoryTpl").Value = 'For Internal use only 'RptObject.Params.Find("ItemCategoryData").Value = 'For Internal use only 'RptObject.Params.Find("DocProjectData").Value = 'Not use if AllDocProject is true 'RptObject.Params.Find("BatchData").Value = 'Not use if AllBatch is true RptObject.Params.Find("TranferDocFilterDate").Value = false 'Step 4: Perform Report calculation RptObject.CalculateReport() Set lDataSet = RptObject.DataSets.Find("cdsMain") Set lDataSet2 = RptObject.DataSets.Find("cdsTransfer") MsgBox "Count " & lDataSet.RecordCount 'Step 5 Retrieve the output lDataSet.First While (not lDataSet.eof) MsgBox "DocKey " & lDataSet.FindField("DocKey").AsString MsgBox "DtlKey " & lDataSet.FindField("Dtlkey").AsString MsgBox "DocNo " & lDataSet.FindField("DocNo").AsString MsgBox "Item Code " & lDataSet.FindField("ItemCode").AsString MsgBox "Org Qty " & lDataSet.FindField("SQty").AsString MsgBox "Outstanding Qty " & lDataSet.FindField("OutstandingQty").AsString MsgBox "cdsDocDetail Count " & lDataSet2.RecordCount lDataSet2.First While (not lDataSet2.eof) MsgBox "FromDocKey " & lDataSet2.FindField("FromDocKey").AsString MsgBox "FromDtlKey " & lDataSet2.FindField("FromDtlkey").AsString MsgBox "DocType " & lDataSet2.FindField("DocType").AsString MsgBox "DocNo " & lDataSet2.FindField("DocNo").AsString MsgBox "DocDate " & lDataSet2.FindField("DocDate").AsString MsgBox "Transfer Qty " & lDataSet2.FindField("TransferQty").AsString lDataSet2.Next Wend lDataSet.Next Wend End Function

[top]

JScript/JavaScript

This Script only can run using Internet Explorer

Example-AR_IV

AR_IV Script

<html> <head> <title>Add Customer Invoice</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 AutoInvoice() { var BizObject, lMainDataSet, lDetailDataSet; CreateSQLAccServer(); BizObject = ComServer.BizObjects.Find('AR_IV'); lMainDataSet = BizObject.DataSets.Find('MainDataSet'); lDetailDataSet = BizObject.DataSets.Find('cdsDocDetail'); BizObject.New(); lMainDataSet.FindField('DocKey').value = -1; lMainDataSet.FindField('DocNo').value = "--IV Test--"; lMainDataSet.FindField('DocDate').value = "01/10/2016"; lMainDataSet.FindField('PostDate').value = "01/10/2016"; lMainDataSet.FindField('Code').value = "300-A0003"; lMainDataSet.FindField('Description').value = "Sales"; /*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(); /* Save document */ BizObject.Save(); BizObject.Close(); alert('Customer Invoice - has been saved.'); } </script> </head> <body> <input type="submit" value="SYNC" name="btnSubmit" onclick="AutoInvoice()"> </body> </html>

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('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(); BizObject.Close(); 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-AR_PM-Edit

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'); lDocKey := lBizObj.FindKeyByRef('DocNo', '--PM Test--'); lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey); If not VarIsNull(lDocKey) Then begin lBizObj.Open; lBizObj.Edit; lMain.FindField('DOCKEY').Value := -1; 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 := '310-000'; lMain.FindField('DocAmt').AsFloat := 800.28; lMain.FindField('UnappliedAmt').AsFloat := 100.28; 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--'); //Document Number if lKO.Locate('DocType;DocNo', V.AsOleVariant, False, False) then begin lKO.Edit; lKO.FindField('KOAmt').AsFloat := 700.28; // IV Knock Off/Offset Amount lKO.FindField('KnockOff').AsString := 'T'; lKO.Post; end; lBizObj.Save; MessageDlg('AR_PM-DocNo : ''--PM Test--'' Updated...', mtInformation, [mbOK],0); end; 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-ST_Item-Edit

ST_Item Script

procedure TfmMain.dxbb_ST_ItemClick(Sender: TObject); var lMain, lDtl, lBizObj, lBizApp, lDocKey, V : Variant; begin lBizApp := CreateOleObject('SQLAcc.BizApp'); lBizObj := lBizApp.BizObjects.Find('ST_ITEM'); lMain := lBizObj.DataSets.Find('MainDataSet'); lDtl := lBizObj.DataSets.Find('cdsUOM'); lDocKey := lBizObj.FindKeyByRef('Code', '--Stock Item Test--'); lBizObj.Params.Find('DocKey').Value := VarToStr(lDocKey); try If not VarIsNull(lDocKey) Then begin lBizObj.Open; lBizObj.Edit; lMain.FindField('Description').value := 'Testing - Description'; V := lBizApp.CreateOleVariantArray(2); V.SetItem(0, 'CTN'); //UOM V.SetItem(1, '12'); //Rate if lDtl.Locate('UOM;Rate', V.AsOleVariant, False, False) then begin lDtl.Edit; lDtl.FindField('RefCost').AsFloat := 10.20; lDtl.FindField('RefPrice').AsFloat := 25.00; lDtl.Post; end; lBizObj.Save; MessageDlg('ST_Item-ItemCode : ''--Stock Item Test--'' Updated...', mtInformation, [mbOK],0); end; 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;

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;

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;

Example-Customer.Aging.RO

Customer Aging Report Object Script

procedure TfmMain.dxbb_AgingClick(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.Aging.RO'); lRptVar.Params.Find('ActualGroupBy').AsString := 'Code;CompanyName'; //Fixed // lRptVar.Params.Find('AgentData').AsBlob  := ; //Not use if AllAgent is True lRptVar.Params.Find('AgingData').AsString := s; lRptVar.Params.Find('AgingDate').AsDate := '31 Aug 2016'; lRptVar.Params.Find('AgingOn').AsString := 'I'; //Fixed lRptVar.Params.Find('AllAgent').AsBoolean := True; lRptVar.Params.Find('AllArea').AsBoolean := True; lRptVar.Params.Find('AllCompany').AsBoolean := True; 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  := ; //Not use if AllCompany is True // 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('DocProjectData').AsBlob  := ; //Not use if AllDocProject is True lRptVar.Params.Find('FilterPostDate').AsBoolean := True; // lRptVar.Params.Find('GroupBy lRptVar.Params.Find('IncludePDC').AsBoolean := False; lRptVar.Params.Find('IncludeZeroBalance').AsBoolean := False; lRptVar.Params.Find('SortBy').AsString := 'Code;CompanyName'; lRptVar.Params.Find('DateTo').AsDate := '31 Aug 2016'; lRptVar.Params.Find('IncludeKnockedOffDetail').AsBoolean := False; 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

How to make sure I login to correct database?

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

- CompanyName Field

- Remark Field

• Refer to Example - Get Company Profile

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 I Ignore Doc No Field & let SQL Accounting to Auto Assign?

Yes but we not recommended as you will lost track when user wanted to know which Doc No in SQL Acc is posted.

To Set Auto Assign set it as below

DocNo Field Script/Code
... lMain.FindField('DocNo').AsString  := '<<New>>'; ...

Can I Auto Login & Logout SQL Accounting when doing & after Posting?

Yes can below is example script

Delphi

procedure TForm1.Button2Click(Sender: TObject); var lMain, lBizObj, lBizApp : Variant; lTime : TDateTime; lFName, lFileName, lFilter : string; begin lFilter := 'DCF File (*.DCF)|*.DCF|All Files (*.*)|*.*'; lFName := 'ACC-0003.FDB'; //Default Database file lFileName := 'C:\eStream\SQLAccounting\Share\Default.DCF'; //Default DCF File if PromptForFileName(lFileName, lFilter, 'DCF', 'Open DCF File...', '.', False) then begin if inputQuery('Enter DB file Name', 'FDB Name', lFName) then begin lTime := Now; lBizApp := CreateOleObject('SQLAcc.BizApp'); sleep(1000); //Wait 1 second for system loading before proceed if lBizApp.IsLogin then lBizApp.Logout; //Force Logout to make sure login correct database lBizApp.Login('ADMIN', 'ADMIN', lFileName, lFName); //Login lBizObj := lBizApp.BizObjects.Find('Agent'); //Start Posting lMain := lBizObj.DataSets.Find('MainDataSet'); try lBizObj.New; lMain.FindField('CODE').AsString := '_Test_'; lMain.FindField('Description').AsString := 'Testing 123'; lBizObj.Save; //Save Posting finally lBizObj.Close; lTime := Now - lTime; MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]), mtInformation, [mbOk], 0); lBizApp.Logout; //Logout lMain := Null; lBizObj := Null; lBizApp := Null; end; end; end; end;

VB Script

'Copy below script & paste to notepad & name it as eg Agent.vbs Call PostData Function CreateSQLAccServer Set CreateSQLAccServer = CreateObject("SQLAcc.BizApp") End Function Function PostData Dim ComServer, BizObject 'Step 1: Create Com Server object Set ComServer = CreateSQLAccServer 'Create Com Server 'Step 2: Force Logout to make sure login correct database If ComServer.IsLogin Then 'if user hasn't logon to SQL application ComServer.Logout END IF 'Step 3: Login ComServer.Login "ADMIN", "ADMIN", "C:\estream\SQLAccounting\Share\Default.DCF", "ACC-0015.FDB" 'Step 4: Find and Create the Biz Objects Set BizObject = ComServer.BizObjects.Find("Agent") 'Step 5: Set Dataset Set lMain = BizObject.DataSets.Find("MainDataSet") 'lMainDataSet contains master data 'Step 6 : Posting BizObject.New lMain.FindField("CODE").AsString = "_Test_" lMain.FindField("Description").AsString = "Testing 123" 'Step 7: Save Document BizObject.Save BizObject.Close 'Step 8: Logout ComServer.Logout MsgBox "Done" End Function

[top]

See also

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