Reportman Writing SQL

Parent Previous Next

To write SQL is actually very simple, for example, “select data from table”,  where data is a valid field name (e.g. ScompanyName Company Name) in the table and table is a valid table name (e.g. Sysvars).

To call the company name and Fax number (as entered in the Setup → System parameters screen (it is stored in the sysvars table)), you may simply write select SCompanyName CompanyName, SFaxNumber Fax from Sysvars.  

Select YOUR DATA FIELDS  from sysvars

An example of the SQL for the COMPANYINFO dataset is as follows:

SQL Text

Data on Screens

select


SCompanyName CompanyName,

BlobLogo Logo,

SAddress1 Address1,

SAddress2 Address2,

SAddress3 Address3,

SPostCode Postcode,

SPhoneNumber Telephone,

SFaxNumber Fax,

SEmailAddress EMailAddress,

SCompanyRegNo CompanyRegNo,

STaxRegNo TaxRegNo,

SBANKNAME1 BankName1,

SBANKNAME2 BankName2,

SBANKNUMBER1 BankNo1,

SBANKNUMBER2 BankNo2,

SBANKSWIFT1 BankSwift1,

SBANKSWIFT2 BankSwift2,

SBANKIBAN1 BankIBAN1,

SBANKIBAN2 BankIBAN2,

SBANKACCOUNTNAME1 BankAccountName1,

SBANKACCOUNTNAME2 BankAccountName2,

SInvoiceHeading InvoceHead,

SInvoicesMessage1 InvoiceMessage1,

SInvoicesMessage2 InvoiceMessage2,

SInvoicesMessage3 InvoiceMessage3,

SCreditNoteHeading CreditNoteHead,

SCreditNoteMessage1 CreditNoteMessage1,

SCreditNoteMessage2 CreditNoteMessage2,

SCreditNoteMessage3 CreditNoteMessage3,

SQuoteHeading QuoteHead,

SQuoteMessage1 QuoteMessage1,

SQuoteMessage2 QuoteMessage2,

SQuoteMessage3 QuoteMessage3,

SPurchaseHeading PurchaseHead,

SPurchasesMessage1 PurchaseMessage1,

SPurchasesMessage2 PurchaseMessage2,

SPurchasesMessage3 PurchaseMessage3,

SGoodsReturnedHeading SupplierReturnHead,

SGoodsReturnedMessage1 SupplierReturnMessage1,

SGoodsReturnedMessage2 SupplierReturnMessage2,

SGoodsReturnedMessage3 SupplierReturnMessage3,

SOrderHeading OrderHead,

SOrderMessage1 OrderMessage1,

SOrderMessage2 OrderMessage2,

SOrderMessage3 OrderMessage3,

SStatementMessage1 StatementMessage1,

SStatementMessage2 StatementMessage2,

SStatementMessage3 StatementMessage3,

SSellingPriceName1 SellingPrice1,

SSellingPriceName2 SellingPrice2,

SSellingPriceName3 SellingPrice3,

SAccountReportName1 LedgerRepGroup1,

SAccountReportName1 LedgerRepGroup2,

SDebtorReportName1 DebtorRepGroup1,

SDebtorReportName2 DebtorRepGroup2,

SCreditorReportName1 CreditorRepGroup1,

SCreditorReportName2 CreditorRepGroup2,

SStockReportName1 StockRepGroup1,

SStockReportName1  StockRepGroup2,

WRetainedIncomeID RetainedIncomeAccId,

WDebtorsControlID DebtorsControlAccId,

WCreditorsControlID CreditorControlAccId


from sysvars



Setup Company Info






















Setup Documents (Invoices)




Setup Documents (Credit Notes)




Setup Documents (Quotes)




Setup Documents (Purchases)




Setup Documents (Supplier Returns)




Setup Documents (Orders)




Setup Statements



Setup Stock Information



Setup Groups








Control Accounts linked to the books (default per Set of Books template selected or specified in the Create Set of Books (Advanced) option on the creation wizard.


In older versions of TurboCASH4 (before version 4.3.0.1 Update) you will receive an error message “No available data to print.” when you click on the Preview icon on the icon toolbar of the Report manager. To enable you to preview the report (document layout file) at any time while editing or adding objects to your report, you need to do the following few steps on the “Database connections and datasets” screen.  

To add a dataset, click on the icon. To create or edit a document in Reportmanager and test the document, you need an existing document number (or create a new invoice in TurboCASH4).

These steps are not necessary in TurboCASH4 3.0.1 or later versions, since TurboCASH4 will automatically find and use the latest Invoice.

1.Click on the icon.

2.On the New dataset screen, enter the alias (name) “Dummy” and click on the OK button.

3.Enter the following SQL text in the memo field:
               select max(Wdocid) from dochead

4.Click on the Show data button.

5.Remember the number displayed on the MAX column. This is the DocumentID number of the last document created in the Set of Books.  

6.Close the data screen.

7.Select the “Dummy” query and click on the to remove it.

8.Click on the Parameters button.

9.On the value field, enter the value of the document number.

10.Click on the OK button to close the Parameter definition screen.

After you have followed these setting and data configurations, you may select the DOCUMENTHEAD or any dataset and click on the Show data button to view the details of the Invoice.


Created with the Personal Edition of HelpNDoc: News and information about help authoring tools and software