Introduction
This is the "Bridge" direct live linking between External Program with SQL Accounting
Pros
- Can Live or Batch Update.
- Can do action such as New, Edit & Delete.
- About 95% of modules is covered.
- Can direct Retrieve/Get Information From Connected Database.
- Support GST.
- Free of Charge for End User. (Freeware)
Cons
- Must had programming knowledge.
- Must had SQL Accounting installed & running.
- Must self do checking else error prompt will unable to “capture”.
- Only supported in IE for JScript
Documentation
Linking Flow
Steps
CHM File
- Install sqlaccounting
- Create Sample Data
- Login (username & password is Admin)
- Double click the SQLSDK.chm file
- Click Application Object | Business Objects
- Click the word "here"
- Click Yes
- 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
- Login SQL Accounting
- Login External Application
- 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
- Can try start with Add Agent under the SDK file to make sure you
- ----------------------------------------------------------------------------------------------------------------------------------------------------
Extra Notes
Login Database Profile
- Can use the Common.Agent.RO Report Objects to get the Profile info.
- Then use the cdsProfile Dataset
- - CompanyName Field
- - Remark Field
- Refer to How to retrieve data from Customer Invoice Listing Report? on chm
- - Can by pass the Steps 5 Spool Parameters
Example External Program
VBScript
- http://www.estream.com.my/document/SQLLink/Customer_RO-VBScipt.zip (Using VB Script)
Example-AR_IV
| AR_IV Script |
|---|
JScript/JavaScript
This Script only can run using Internet Explorer
Example-AR_PM
| AR_PM Script |
|---|
<html>
<head>
<title>Add Customer Payment</title>
<script language="JScript">
var ComServer;
function CreateSQLAccServer() {
ComServer = new ActiveXObject("SQLAcc.BizApp");
if (!ComServer.IsLogin) { /* check whether user has logon */
ComServer.Login('ADMIN', 'ADMIN', 'C\:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF', 'SAMPLE.FDB');
/* UserName, Password */
}
}
function AutoPayment() {
var BizObject, lMainDataSet, lKnockOffDataSet, V;
CreateSQLAccServer();
BizObject = ComServer.BizObjects.Find('AR_PM');
lMainDataSet = BizObject.DataSets.Find('MainDataSet');
lKnockOffDataSet = BizObject.DataSets.Find('cdsKnockOff');
BizObject.New();
lMainDataSet.FindField('DocKey').value = -1;
lMainDataSet.FindField('DocNo').value = "Test";
lMainDataSet.FindField('Code').value = "888";
lMainDataSet.FindField('DocDate').value = "01/09/2015";
lMainDataSet.FindField('PostDate').value = "01/09/2015";
lMainDataSet.FindField('Project').value = "----";
lMainDataSet.FindField('Description').value = "";
lMainDataSet.FindField('PaymentMethod').value = "320-000";
lMainDataSet.FindField('Journal').value = "CASH";
lMainDataSet.FindField('ChequeNumber').value = "";
lMainDataSet.FindField('CurrencyCode').value = "----";
lMainDataSet.FindField('CurrencyRate').value = "1.0000000000";
lMainDataSet.FindField('DocAmt').value = "106.00";
lMainDataSet.FindField('LocalDocAmt').value = "0.00";
lMainDataSet.FindField('BankCharge').value = "0";
lMainDataSet.FindField('UnappliedAmt').value = "0";
lMainDataSet.FindField('Cancelled').value = 'F';
v = ComServer.CreateOleVariantArray(2);
v.SetItem(0, 'IV'); /*Knock Off DocType*/
v.SetItem(1, 'IV-00111'); /*Knock Off DocNo*/
if (lKnockOffDataSet.Locate('DocType;DocNo', v.AsOleVariant(), false, false)){
lKnockOffDataSet.Edit();
lKnockOffDataSet.FindField('DocAmt').value = "106";
lKnockOffDataSet.FindField('KnockOff').AsString = 'T';
lKnockOffDataSet.Post(); }
/* Save document */
BizObject.Save();
alert("Customer Payment - has been saved.");
}
</script>
</head>
<body>
<input type="submit" value="SYNC" name="btnSubmit" onclick="AutoPayment()">
</body>
</html>
|
Example-Customer.IV.RO
| Customer.IV.RO Report Object Script |
|---|
<html>
<head>
<title>Customer.IV.RO</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<script type="text/javascript">
var ComServer;
function CreateSQLAccServer() {
ComServer = new ActiveXObject("SQLAcc.BizApp");
if (!ComServer.IsLogin) { /* check whether user has logon */
ComServer.Login('ADMIN', 'ADMIN', 'C\:\\eStream\\SQLAccounting\\Share\\DEFAULT.DCF', 'SAMPLE.FDB');
/* UserName, Password */
}
}
function GetData() {
var lMain, lDetail, lRptVar, lDateFrom, lDateTo;
elem = document.getElementById('txt1');
CreateSQLAccServer();
lDateFrom = new Date("January 1 2015");
lDateTo = new Date("December 31, 2015");
lRptVar = ComServer.RptObjects.Find('Customer.IV.RO');
lRptVar.Params.Find('SelectDate').Value = true;
lRptVar.Params.Find('DateFrom').Value = lDateFrom.valueOf();
lRptVar.Params.Find('DateTo').Value = lDateTo.valueOf();
lRptVar.Params.Find('AllAgent').Value = false;
lRptVar.Params.Find('AgentData').Value = "----\nLF"; /*Filter by Agent code ---- & HALIM */
lRptVar.Params.Find('AllArea').Value = true;
lRptVar.Params.Find('AllCompany').Value = true;
lRptVar.Params.Find('AllCurrency').Value = true;
lRptVar.Params.Find('AllDocProject').Value = true;
lRptVar.Params.Find('AllCompanyCategory').Value = true;
lRptVar.Params.Find('AllDocument').Value = true;
lRptVar.Params.Find('IncludeCancelled').Value = false;
lRptVar.Params.Find('PrintDocumentStyle').Value = false;
lRptVar.Params.Find('AllItemProject').Value = true;
lRptVar.Params.Find('AllPaymentMethod').Value = true;
lRptVar.CalculateReport();
lMain = lRptVar.DataSets.Find('cdsMain');
lDetail = lRptVar.DataSets.Find('cdsDocDetail');
/*alert("Count -" + lRptVar.DataSets.Find('cdsDocDetail').RecordCount);*/
elem.innerHTML += 'From Main'+'\n';
lMain.First();
while (!lMain.eof) {
elem.innerHTML += lMain.FindField('DocKey').AsString + ' ' +
lMain.FindField('DocNo').AsString + ' ' + lMain.FindField('DocDate').AsString + ' '+
lMain.FindField('CompanyName').AsString + ' ' + lMain.FindField('DocAmt').AsString + '\n';
lMain.Next();
}
elem.innerHTML += 'End Main'+'\n\n';
elem.innerHTML += 'From Detail'+'\n';
lDetail.First();
while (!lDetail.eof) {
elem.innerHTML += lDetail.FindField('Dockey').AsString + ' ' + lDetail.FindField('Account').AsString + ' ' +
lDetail.FindField('Description').AsString + ' ' + lDetail.FindField('Amount').AsString + '\n';
lDetail.Next();
}
elem.innerHTML += 'End Detail'+'\n\n';
}
</script>
</head>
<body>
<form>
<textarea id="txt1" rows="30" cols="116"></textarea>
<input type="button" value="Calculate" onclick="GetData()">
</form>
</body>
</html>
|
Delphi
The below example is assuming after you had check the login status.
Example-GL_PV
| GL_PV Script |
|---|
procedure TfmMain.dxbb_GLPVClick(Sender: TObject);
var lMain, lDtl, lBizObj, BizApp : Variant;
lTime : TDateTime;
begin
lTime := Now;
BizApp := CreateOleObject('SQLAcc.BizApp');
try
lBizObj := BizApp.BizObjects.Find('GL_PV');
lMain := lBizObj.DataSets.Find('MainDataSet');
lDtl := lBizObj.DataSets.Find('cdsDocDetail');
lBizObj.New;
lMain.FindField('DOCKEY').Value := -1;
lMain.FindField('DocNo').AsString := '--CB PV Test1--';
lMain.FindField('DocDate').Value := '18 Aug 2015';
lMain.FindField('PostDate').Value := '18 Aug 2015';
lMain.FindField('TAXDATE').Value := '18 Aug 2015';
lMain.FindField('Description').AsString := 'Compacc System';
lMain.FindField('PAYMENTMETHOD').AsString := '310-001';
lMain.FindField('CHEQUENUMBER').AsString := 'MBB 213245';
lMain.FindField('DocAmt').AsFloat := 2019;
lMain.FindField('Cancelled').AsString := 'F';
lMain.FindField('CHANGED').AsString := 'F';
lMain.Post;
lDtl.Append;
lDtl.FindField('DTLKEY').Value := -1;
lDtl.FindField('DOCKEY').Value := -1;
lDtl.FindField('Code').AsString := '200-300';
lDtl.FindField('DESCRIPTION').AsString := 'Maybank - Asus A555LD-xx313H';
lDtl.FindField('TAX').AsString := 'TX';
lDtl.FindField('TAXAMT').AsFloat := 114.28;
lDtl.FindField('TAXINCLUSIVE').Value := 1;
lDtl.FindField('AMOUNT').AsFloat := 2019;
lDtl.FindField('CHANGED').AsString := 'F';
lDtl.Post;
lBizObj.Save;
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
finally
lBizObj.Close;
end;
end;
|
Example-AR_Customer
| AR_Customer Script |
|---|
procedure TfmMain.dxbb_CustomerClick(Sender: TObject);
var lMain, lDtl, lBizObj, lBizApp : Variant;
lTime : TDateTime;
begin
lTime := Now;
lBizApp := CreateOleObject('SQLAcc.BizApp');
lBizObj := lBizApp.BizObjects.Find('AR_Customer');
lMain := lBizObj.DataSets.Find('MainDataSet');
lDtl := lBizObj.DataSets.Find('cdsBranch');
try
lBizObj.New;
lMain.FindField('Code').value := '300-Test';
lMain.FindField('CompanyName').value := 'Testing 123';
lDtl.Edit; //For 1st Branch
lDtl.FindField('BranchName').AsString := 'BILLING';
lDtl.FindField('Address1').AsString := 'Address1';
lDtl.FindField('Address2').AsString := 'Address2';
lDtl.FindField('Address3').AsString := 'Address3';
lDtl.FindField('Address4').AsString := 'Address4';
lDtl.FindField('Attention').AsString := 'Attention';
lDtl.FindField('Phone1').AsString := 'Phone1';
lDtl.FindField('Fax1').AsString := 'Fax1';
lDtl.FindField('Email').AsString := 'EmailAddress';
lDtl.Post;
lDtl.Append; //For 2nd Branch
lDtl.FindField('BranchName').AsString := 'Branch1';
lDtl.FindField('Address1').AsString := 'DAddress1';
lDtl.FindField('Address2').AsString := 'DAddress2';
lDtl.FindField('Address3').AsString := 'DAddress3';
lDtl.FindField('Address4').AsString := 'DAddress4';
lDtl.FindField('Attention').AsString := 'DAttention';
lDtl.FindField('Phone1').AsString := 'DPhone1';
lDtl.FindField('Fax1').AsString := 'DFax1';
lDtl.FindField('Email').AsString := 'DEmailAddress';
lDtl.Post;
lBizObj.Save;
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
finally
lBizObj.Close;
end;
end;
|
Example-AR_PM
| AR_PM Script |
|---|
procedure TfmMain.dxbb_ARPMClick(Sender: TObject);
var lMain, lKO, lBizApp, lBizObj, V : Variant;
begin
lBizApp := CreateOleObject('SQLAcc.BizApp');
try
lBizObj := lBizApp.BizObjects.Find('AR_PM');
lMain := lBizObj.DataSets.Find('MainDataSet');
lKO := lBizObj.DataSets.Find('cdsKnockOff');
lBizObj.New;
lMain.FindField('DOCKEY').Value := -1;
lMain.FindField('DocNo').AsString := '--PM Test--'; //Payment Document Number
lMain.FindField('CODE').AsString := '300-A0001';
lMain.FindField('DocDate').Value := '28 Aug 2016';
lMain.FindField('PostDate').Value := '28 Aug 2016';
lMain.FindField('Description').AsString := 'Payment for A/c';
lMain.FindField('PaymentMethod').AsString := '320-000';
lMain.FindField('DocAmt').AsFloat := 1060;
lMain.FindField('Cancelled').AsString := 'F';
lMain.FindField('CHANGED').AsString := 'F';
V := lBizApp.CreateOleVariantArray(2);
V.SetItem(0, 'IV'); //Document Type is either is IV or DN for Sales & PI or SD for Purchase
V.SetItem(1, '--IV Test--'); //Invoice Document Number
if lKO.Locate('DocType;DocNo', V.AsOleVariant, False, False) then
begin
lKO.Edit;
lKO.FindField('KOAmt').ASFloat := 500;
lKO.FindField('KnockOff').AsString := 'T';
lKO.Post;
end;
lBizObj.Save;
MessageDlg('AR_PM-DocNo : ''--PM Test--'' Posted...', mtInformation, [mbOK],0);
finally
lBizObj.Close;
end;
end;
|
Example-ST_Item
| ST_Item Script |
|---|
procedure TfmMain.dxbb_ST_ItemClick(Sender: TObject);
var lMain, lDtl, lBizObj, lBizApp : Variant;
lTime : TDateTime;
begin
lTime := Now;
lBizApp := CreateOleObject('SQLAcc.BizApp');
lBizObj := lBizApp.BizObjects.Find('ST_ITEM');
lMain := lBizObj.DataSets.Find('MainDataSet');
lDtl := lBizObj.DataSets.Find('cdsUOM');
try
lBizObj.New;
lMain.FindField('Code').AsString := 'Test-1';
lMain.FindField('Description').AsString := 'Testing - Description';
lDtl.Edit; //For 1st UOM
lDtl.FindField('UOM').AsString := 'PCS';
lDtl.FindField('Rate').AsFloat := 1;
lDtl.FindField('RefCost').AsFloat := 10.20;
lDtl.FindField('RefPrice').AsFloat := 25.00;
lDtl.Post;
lDtl.Append; //For 2nd UOM
lDtl.FindField('UOM').AsString := 'CTN';
lDtl.FindField('Rate').AsFloat := 12;
lDtl.FindField('RefCost').AsFloat := 102.00;
lDtl.FindField('RefPrice').AsFloat := 240.00;
lDtl.Post;
lBizObj.Save;
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
finally
lBizObj.Close;
end;
end;
|
Example-SL_IV
| SL_IV Script |
|---|
procedure TfmMain.dxbb_SL_IVClick(Sender: TObject);
var lMain, lDtl, lSN, lBizObj, lBizApp : Variant;
lTime : TDateTime;
begin
lTime := Now;
lBizApp := CreateOleObject('SQLAcc.BizApp');
lBizObj := lBizApp.BizObjects.Find('SL_IV');
lMain := lBizObj.DataSets.Find('MainDataSet');
lDtl := lBizObj.DataSets.Find('cdsDocDetail');
lSN := lBizObj.DataSets.Find('cdsSerialNumber');
try
lBizObj.New;
lMain.FindField('DOCKEY').Value := -1;
lMain.FindField('DocNo').AsString := '--IV Test--';
lMain.FindField('CODE').AsString := '300-A0001';
lMain.FindField('DocDate').Value := '28 Aug 2015';
lMain.FindField('PostDate').Value := '28 Aug 2015';
lMain.FindField('TAXDATE').Value := '28 Aug 2015';
lMain.FindField('Description').AsString := 'Sales';
lMain.FindField('DocAmt').AsFloat := 735.28;
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 := 410.37; //Must Excluding GST
lDtl.FindField('AMOUNT').AsFloat := 410.37; //Must Excluding GST (Qty * UnitPrice)
lDtl.FindField('TAXAMT').AsFloat := 24.63;
lDtl.DisableControls; //For Tax Inclusive Only where TaxAmt is not as Calc
try
lDtl.FindField('TAXINCLUSIVE').Value := 1;
finally
lDtl.EnableControls;
end;
lDtl.FindField('CHANGED').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;
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 := 'SN';
lDtl.FindField('ACCOUNT').AsString := '500-000';
lDtl.FindField('DESCRIPTION').AsString := 'Sales Serial Number Item';
lSN.Append;
lSN.FindField('SERIALNUMBER').AsString := 'SN-00001';
lSN.Post;
lSN.Append;
lSN.FindField('SERIALNUMBER').AsString := 'SN-00002';
lSN.Post;
lDtl.FindField('QTY').AsFloat := 2;
lDtl.FindField('TAX').AsString := 'SR';
lDtl.FindField('TAXINCLUSIVE').Value := 0;
lDtl.FindField('UNITPRICE').AsFloat := 94.43;
lDtl.FindField('TAXAMT').AsFloat := 11.33;
lDtl.FindField('CHANGED').AsString := 'F';
lDtl.Post;
lBizObj.Save;
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
finally
lBizObj.Close;
end;
end;
|
Example-Customer.IV.RO
| Customer.IV.RO Report Object Script |
|---|
procedure TfmMain.dxbb_RptObjClick(Sender: TObject);
var lBizApp, lRptVar : Variant;
lTime : TDateTime;
cdsMain, cdsDtl : TClientDataset;
begin
lTime := Now;
cdsMain := TClientDataset.Create(nil);
cdsDtl := TClientDataset.Create(nil);
lBizApp := CreateOleObject('SQLAcc.BizApp');
try
lRptVar := null;
lRptVar := lBizApp.RptObjects.Find('Customer.IV.RO');
lRptVar.Params.Find('SelectDate').AsBoolean := True;
lRptVar.Params.Find('DateFrom').AsDate := '01/01/2015';
lRptVar.Params.Find('DateTo').AsDate := '31/12/2015';
lRptVar.Params.Find('AllAgent').AsBoolean := False;
lRptVar.Params.Find('AgentData').AsBlob := '----'#13#10'HALIM'; //Filter by Agent code ---- & HALIM
lRptVar.Params.Find('AllArea').AsBoolean := True;
// lRptVar.Params.Find('AreaData').AsBlob := ; //Not use if AllArea is True
lRptVar.Params.Find('AllCompany').AsBoolean := True;
// lRptVar.Params.Find('CompanyData').AsBlob := ; //Not use if AllCompany is True
lRptVar.Params.Find('AllCurrency').AsBoolean := True;
// lRptVar.Params.Find('CurrencyData').AsBlob := ; //Not use if AllCurrenty is True
lRptVar.Params.Find('AllDocProject').AsBoolean := True;
// lRptVar.Params.Find('DocProjectData').AsBlob := ; //Not use if AllDocProject is True
lRptVar.Params.Find('AllCompanyCategory').AsBoolean := True;
// lRptVar.Params.Find('CompanyCategoryData').AsBlob := ; //Not use if AllCompanyCategory is True
lRptVar.Params.Find('AllDocument').AsBoolean := True;
// lRptVar.Params.Find('DocumentData').AsBlob := ; //Not use if AllDocument is True
lRptVar.Params.Find('IncludeCancelled').AsBoolean := True;
lRptVar.Params.Find('PrintDocumentStyle').AsBoolean := False;
lRptVar.Params.Find('AllItemProject').AsBoolean := True;
lRptVar.Params.Find('AllPaymentMethod').AsBoolean := True;
lRptVar.CalculateReport;
cdsMain.XMLData := lRptVar.DataSets.Find('cdsMain').XMLData; //Master Data
cdsDtl.XMLData := lRptVar.DataSets.Find('cdsDocDetail').XMLData; //Detail Data - To link Master Data use Dockey field
cdsMain.SaveToFile('C:\Temp\_ListM.xml'); //Export To XML file
cdsDtl.SaveToFile('C:\Temp\_ListD.xml'); //Export To XML file
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
finally
lRptVar := null;
cdsMain.Free;
cdsDtl.Free;
end;
end;
|
Example-Stock.Item.RO
| Stock.Item.RO Report Object Script |
|---|
procedure TfmMain.dxbb_ST_ITEM_ROClick(Sender: TObject);
var lBizApp, lRptVar : Variant;
lTime : TDateTime;
cdsMain, cdsDtl : TClientDataset;
begin
lTime := Now;
cdsMain := TClientDataset.Create(nil);
cdsDtl := TClientDataset.Create(nil);
lBizApp := CreateOleObject('SQLAcc.BizApp');
try
lRptVar := null;
lRptVar := lBizApp.RptObjects.Find('Stock.Item.RO');
lRptVar.Params.Find('AllItem').AsBoolean := False;
lRptVar.Params.Find('AllStockGroup').AsBoolean := True;
lRptVar.Params.Find('AllCustomerPriceTag').AsBoolean := True;
lRptVar.Params.Find('AllSupplierPriceTag').AsBoolean := True;
// lRptVar.Params.Find('CategoryData').AsBlob := ; //Not use if SelectCategory is False
// lRptVar.Params.Find('CategoryTpl').AsBlob := ; //Not use if AllArea is True
// lRptVar.Params.Find('CustomerPriceTagData').AsBlob := ; //Not use if AllCustomerPriceTag is True
// lRptVar.Params.Find('SupplierPriceTagData').AsBlob := ; //Not use if AllSupplierPriceTag is True
// lRptVar.Params.Find('DateFrom').AsDate := '01/01/2015'; //Not use if SelectDate is False
// lRptVar.Params.Find('DateTo').AsDate := '31/12/2015';
lRptVar.Params.Find('HasAltStockItem').AsBoolean := False;
lRptVar.Params.Find('HasBarcode').AsBoolean := False;
lRptVar.Params.Find('HasBOM').AsBoolean := False;
lRptVar.Params.Find('HasCategory').AsBoolean := False;
lRptVar.Params.Find('HasCustomerItem').AsBoolean := False;
lRptVar.Params.Find('HasOpeningBalance').AsBoolean := False;
lRptVar.Params.Find('HasPurchasePrice').AsBoolean := False;
lRptVar.Params.Find('HasSellingPrice').AsBoolean := False;
lRptVar.Params.Find('HasSupplierItem').AsBoolean := False;
lRptVar.Params.Find('ItemData').AsBlob := 'ANT 1.0'#13#10'COVER'; //Filter by Item code ANT 1.0 & COVER
// lRptVar.Params.Find('ItemCategoryData').AsBlob := ; //Not use if AllArea is True
lRptVar.Params.Find('PrintActive').AsBoolean := True;
lRptVar.Params.Find('PrintInActive').AsBoolean := True;
lRptVar.Params.Find('PrintNonStockControl').AsBoolean := True;
lRptVar.Params.Find('PrintStockControl').AsBoolean := True;
lRptVar.Params.Find('SelectCategory').AsBoolean := False;
lRptVar.Params.Find('SelectDate').AsBoolean := False;
lRptVar.Params.Find('SortBy').AsString := 'Code';
// lRptVar.Params.Find('StockGroupData').AsBlob := ; //Not use if AllStockGroup is True
lRptVar.CalculateReport;
cdsMain.XMLData := lRptVar.DataSets.Find('cdsMain').XMLData; //Master Data
cdsDtl.XMLData := lRptVar.DataSets.Find('cdsUOM').XMLData; //Detail Data - To link Master Data use Code field
cdsMain.SaveToFile('C:\Temp\_ListM.xml'); //Export To XML file
cdsDtl.SaveToFile('C:\Temp\_ListD.xml'); //Export To XML file
lTime := Now - lTime;
MessageDlg(Format('[Elapsed Time: %s ]',[FormatDateTime ('hh:nn:ss:zzz', lTime)]),
mtInformation, [mbOk], 0);
finally
lRptVar := null;
cdsMain.Free;
cdsDtl.Free;
end;
end;
|
FAQ
Problem with opening CHM Help files?
- Right click the chm file & select Properties
- At General tab Click Unblock button.
- Click Apply | Ok.
Still can't open CHM file?
- Login SQL Accounting
- Click Tools | Options
- Click Register & follow wizard
- Exit SQL Accounting
- Login SQL Accounting
- Run CHM File
Why when I compile my program with stated unknown method or unknown identifier?
- If you compiler had error may try add the esfw10.dll file under the SQL Accounting\bin folder
Why after post to SQL Accounting the Description3 (More Description)/Notes field it show like this "M y D e s c r i p t i o n" instead of "My Description"?
- Make sure you use .AsString in your code (see example below)
| Description3 Field Script/Code |
|---|
|
... lDtl.FindField('DESCRIPTION3').AsString := 'Sales Item A More Description'+ #13 + 'I in 2nd line';
... |
Can SQL Acc accept negative amount?
- No all document amount(DocAmt field) must not below 0