To retrieve the ledger information from a group, use the SQL query in the below format:
Syntax
Select <Method Name> from <Collection or Table> where $Parent =<”Group Name”>
By using the above format, you can retrieve all ledger information from a specific group using the SQL query, Select * from Ledger where $Parent = “Sundry Debtors” .
Similarly, the SQL query to retrieve the required information from a group is Select $Name, $Parent,$OpeningBalance from Ledger where $Parent = “Sundry Debtors” . By using this query, it returns the ledgers from the group Sundry Debtors, but not from the sub-groups.
In order to retrieve all the direct as well as indirect ledgers falling under the group Sundry Debtors, we can use an external method _PrimaryGroup which is available in default TDL.
As a result, the revised SQL query is Select $Name, $Parent, $OpeningBalance from Ledger where $_PrimaryGroup = ”Sundry Debtors” . Underscore (_) is prefixed to the external method in order to expose the same to ODBC.
The above mentioned query works for group created by Tally.ERP 9 during company creation, which are called Primary Groups.
To retrieve the direct and indirect ledgers from a specified user created group as shown below:
● Write a collection with the type Ledger .
● Specify the attribute Child Of with the group name.
● Set the value of the attributes Belongs To and IsODBCTable to Yes .
Example
[Collection: LedgersofSouthDebtor s]
Type : Ledger
Child Of : "South Debtors"
Belongs To : Yes
IsODBCTable : Yes
The SQL query for retrieving the information from the above collection is Select $Name, $Parent, $OpeningBalance from LedgersofSouthDebtors .
SQL procedure is a sequence of SQL statements that executed for specific purpose. Also, use variables within SQL procedures to make the SQL procedure generic. So, you can accept the parameters from the calling program.
To invoke SQL Procedure from Tally.ERP 9
● Create any third-party application or scripts, like VBScript file, by passing appropriate parameters.
● Use the action Exec Command in TDL, and invoke the script file ( VBScript file).