Hello Please help with TDL file which can export all vouchers with following information. Date, Voucher Type, Voucher Number, Reference Number, Narration, Amount Thanks
Simplest Method as per my knowledge..... Create a simple collection tdl and run the tdl select a company & in Calculator panel just type select $Date, $VoucherTypeName, $VoucherNumber, $Referencer, $Narration, $Amount from AllVouchers & press <Enter> The list of all vouchers since starting of the financial year shall appear. Export it to the excel,xml,pdf ---- Add Fetches and filters if you need additional information ;;The Collection TDL [Collection : All Vouchers] Type : Voucher Fetch : Date, VoucherNumber, Reference, VoucherTypeName, LedgerName, Narration, PartyLedgerName, Amount ;;EOF()
Thanks a lot, Boss. It worked. I appreciate your help Sanjeev One ques: I executed following query and it works well, but can add count column in it? select $VoucherTypeName, $Amount from AllVouchers group by $VoucherTypeName Above Group by query gives me sum of Amount by VoucherType. But i also want count of VoucherType. Sales 5 Rs.45129 Journal 11 Rs. 239830 Receipt 7 Rs. 21239 Thanks
I am having one issue with above TDL. Lets say there is one Journal Entry recorded in Tally. Journal By = Ravikant Rs.20000 To = Manish Rs.20000 Both Ledgers are Sundry Debtors. Now, when I run query as above advised TDL, " select $Date, $LedgerName, $PartyLedgerName, $VoucherTypeName, $VoucherNumber, $Amount from AllVouchers where $LedgerName = 'Manish' It does not return anything. but if pass first ledger name (Ravikant) in where condition then it shows the record. select $Date, $LedgerName, $PartyLedgerName, $VoucherTypeName, $VoucherNumber, $Amount from AllVouchers where $LedgerName = 'Ravikant' This is happening when both Ledgers of this voucher are Sundry Debtors. If "By" Ledger is Current Asset or Indirect Expense and "To" Ledger is Sundry Debtors, then it shows Both Ledger Name in result. If i do not pass where condition, select $Date, $LedgerName, $PartyLedgerName, $VoucherTypeName, $VoucherNumber, $Amount from AllVouchers then in result, it shows same name (Ravikant) in both column [$LedgerName] [$PartyLedgerName] Please help me where it can show me both Ledger Name for that record (in case where both ledgers are under same account sundry debtors)
Try This...... 1. select $ledgername,$Date, $VoucherTypeName, $VoucherNumber, $Referencer, $Narration, $Amount from AllVouchers where $Vouchertypename = "payment" 2. select $ledgername,$Date, $VoucherTypeName, $VoucherNumber, $Referencer, $Narration, $Amount from AllVouchers where $Vouchernumber Contains "2"
Hi Jay, I Appriciate your help. Issue is not i am not getting particular journal voucher. I do see all vouchers record. But Issue is, it does not show me both party's Ledger Name in result. It shows me only one party ledger name. Problem is as follow: Any journal voucher will have two party (By and To) Even if type in query what you suggested, it shows me only party name (same name) in both the columns. For Journal voucher it should show me both Ledger Names for one particular record in two columns. See attached screenshot
[Collection : All Vouchers] Type : Voucher Fetch : Date, VoucherNumber, Reference, VoucherTypeName, LedgerName, Narration, PartyLedgerName, Amount Fetch : DrLed, CrLed Compute : DrLed : $$CollectionField:$LedgerName:1:AllLedgerEntries Compute : CrLed : $$CollectionField:$LedgerName:2:AllLedgerEntries Then type this in Calculator panel........ select $Date, $DrLed, $CrLed, $VoucherTypeName, $VoucherNumber, $Amount from AllVouchers
Thanks a lot Amit ji, but i am having some issue when voucher is more than one ledgers in it. See the example below. is it possible to show all (dr or cr) transaction of particular ledger ? i.e select $date,$VoucherTypename,$Vouchernumber, $amount(dr/cr) from Allvouchers where $name = 'Jignesh'
Since this is a text based query (calculator panel) it will only get the basic details from the object (voucher). Any other details one requires, needs to be computed in the COLLECTION, as exemplified by me in the above code. So whatever you need, compute it and then use it in the Line query.
I have given an example for 1 single debit and 1 single credit......... now what if there are 2 debits and 2 credits............ and then what if the entry is passed as per below demo..... Dr ABC A/c .............................5000/- Cr Cash A/c ..........................................11250/- Dr Imprest recovery .............15000/- Cr Loan A/c (recovery)........................ 8750/- When Dr/Cr are not in particular order........as shown in above entry. To have proper control and display report, then you need to write a report, wherein the collections compute the different values you need to display. For multiple Dr/Cr entries you need to collect/compute the Names/Amounts etc.... make a aggregate and then use $$FullList to display all.......
is there any query which can show all dr/cr transactions for particular Ledger? e.g show all entries of ABC A/c (which includes Sales, Receipt, Journal, Payment etc) with Voucher Type, Number and date. Thanks for your nice guidance.
Yes..... for all entries of ABC A/c............ change your collection accordingly........... like.... Child Of : ##Selectedledger here Selectedledger is a variable which you need to define..........and contains the value "ABC A/c"
[Collection : All Vouchers] Type : Voucher Fetch : Date, VoucherNumber, Reference, VoucherTypeName, LedgerName, Narration, PartyLedgerName, Amount ;Fetch : DrLed, CrLed Compute : Led1 : $$CollectionField:$LedgerName:1:AllLedgerEntries Compute : Led2 : $$CollectionField:$LedgerName:2:AllLedgerEntries Compute : Led3 : $$CollectionField:$LedgerName:3:AllLedgerEntries Compute : Led4 : $$CollectionField:$LedgerName:4:AllLedgerEntries Compute : Led5 : $$CollectionField:$LedgerName:5:AllLedgerEntries Compute : Led6 : $$CollectionField:$LedgerName:6:AllLedgerEntries Compute : Led7 : $$CollectionField:$LedgerName:7:AllLedgerEntries Compute : Led8 : $$CollectionField:$LedgerName:8:AllLedgerEntries Calculator Panel : select $Date, $Led1, $Led2, $Led3, $Led4, $Led5, $Led6, $Led7, $Led8, $VoucherTypeName, $VoucherNumber, $Amount from AllVouchers
Great support Amit ji, Appreciate your help. I run above mentioned collection, it works well without error. Unfortunately output format is different then what I am expecting. Above process gives, Ledger name in individual columns, and then Voucher Number and voucher total. Where as I am looking for Raw wise ledgers, Amount for that particular Ledger in that voucher, Date, Voucher Number and RemoteID. Following is the example what I am looking.
Thank for your reply. Yes this returns the Amount but said, issue is that it returns in column format. Is it possible to get Ledgers in row (lines)? As shown in screenshot above! Pls advise. Thanks
Sir, In this case, how can I get the values to filed? I mean, I need like Party AMOUNT ledger1 AMT1 Ledger2 AMT2 Ledger3 AMT3
Line: LedgerReportCustom Field : PvTfmt DateFld, PvTfmt ParticularFld Right Field : PvTfmt VchTypeFld, PvTfmt VchNumberFld, /*PvTfmt SupplierInvNoFld,*/ PvTfmt DebitAmountFld Explode : MyInvExplode [Field : PvTfmt DateFld] Use : Short Date Field Set as :$Date Set Always : Yes Width : 5% Screen Alter : Voucher Style : Small [Field : PvTfmt ParticularFld] Use : Name Field Set as : $LedgerEntries[2].LedgerName Set Always : Yes FullWidth : Yes Style : Small Bold [Field : PvTfmt VchTypeFld] Use : Name Field Set as : $AdditionalName ;;$VoucherTypeName Set Always : Yes Width : 5% Screen Style : Small Bold [Field : PvTfmt VchNumberFld] Use : Name Field Set as : $VoucherNumber Width : 5% Screen Set Always : Yes Align : Right Style : Small [Field : PvTfmt CreditAmountFld] Use : Amount Forex Field Set as : $LedgerEntries[2].Amount*-1 Set Always : Yes Width : @@AmountWidth Style : Small Bold Format : "DrCr" [Part : MyInvExplode] Line : MyInvExplode Repeat : MyInvExplode : AllInventoryEntries [Line : MyInvExplode] Field : PvTfmt DateFld, PvTfmt ParticularFld Right Field : PvTfmt VchTypeFld, PvTfmt VchNumberFld, /*PvTfmt SupplierInvNoFld,*/ PvTfmt DebitAmountFld Remove If : $$IsEmpty:$StockItemName Local : Field : PvTfmt DateFld : Set As : $Date Local : Field : PvTfmt ParticularFld: Set As : $StockItemName Local : Field : PvTfmt DebitAmountFld: Set As : $Amount*-1 Local : Field : PvTfmt VchNumberFld :â€â€ Local : Field vTfmt VchTypeFld: Set As :â€â€ [Collection : All Vouchers] Type : Voucher Filter: Ledfil Fetch : Date, VoucherNumber, Reference, VoucherTypeName, LedgerName, Narration, PartyLedgerName, Amount ;Fetch : DrLed, CrLed Compute : Led1 : $$CollectionField:$LedgerName:1:AllLedgerEntries Compute : Led2 : $$CollectionField:$LedgerName:2:AllLedgerEntries Compute : Led3 : $$CollectionField:$LedgerName:3:AllLedgerEntries Compute : Led4 : $$CollectionField:$LedgerName:4:AllLedgerEntries Compute : Led5 : $$CollectionField:$LedgerName:5:AllLedgerEntries Compute : Led6 : $$CollectionField:$LedgerName:6:AllLedgerEntries Compute : Led7 : $$CollectionField:$LedgerName:7:AllLedgerEntries Compute : Led8 : $$CollectionField:$LedgerName:8:AllLedgerEntries [System: Formula] LedFil: $PartyLedgerName = ##LedgerName Sir, the above is the abstract of my current coding. If you see [Field : PvTfmt ParticularFld], [Field : PvTfmt CreditAmountFld] ... These fields uses reference. But sometimes, $LedgerEntries[2].Name shows the different account and I need $LedgerEntries[1].Name So How can I get all these $LedgerEntreies.[]Name? Is there any code?
I know this formu is for developing people, so I very well know that you will not refer the code, can you please guide me ? I mean where should I focucs? Is it a function? Since I am new, I wanna study it properly