ODBC Export Vouchers with required fields

Discussion in 'Tally Integration' started by Sanket Patel, Jul 29, 2019.

    
  1. Sanket Patel

    Sanket Patel New Member


    Hello
    Please help with TDL file which can export all vouchers with following information.

    Date, Voucher Type, Voucher Number, Reference Number, Narration, Amount

    Thanks
     


  2. Sanjeev S

    Sanjeev S Active Member


    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()
     


  3. Sanket Patel

    Sanket Patel New Member


    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
     


  4. Sanket Patel

    Sanket Patel New Member


    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)
     


  5. Bipin Damania

    Bipin Damania Active Member


    Sanjeevji

    Superb
    Thank you for sharing...
     


  6. Jay kumar tailor

    Jay kumar tailor Well-Known Member


    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"
     


  7. Sanket Patel

    Sanket Patel New Member


    tally1.jpg tally2.jpg

    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

    [​IMG]
     


  8. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    [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
     
    ranabhilai likes this.


  9. Sanket Patel

    Sanket Patel New Member


    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'

    tally3.jpg

    tally4.jpg
     


  10. Bipin Damania

    Bipin Damania Active Member


    Amitji

    Thank you
    And
    How to get DrLed, CrLed Amount ?
     


  11. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    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.
     


  12. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    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.......
     


  13. Sanket Patel

    Sanket Patel New Member


    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.
     


  14. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    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"
     
    Sanjeev S likes this.


  15. Sanket Patel

    Sanket Patel New Member


    tried your suggestion, in TDL file but did not work.
    can you pls help with that TDL
     


  16. Sanket Patel

    Sanket Patel New Member


    Hi Amit ji,
    Can you please help with error as shown in above screenshot Post#9.

    Thanks
     


  17. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    [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
     


  18. Sanket Patel

    Sanket Patel New Member


    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.
     

    Attached Files:



  19. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    Then Compute the Amount also.......Amt1 to Amt8 in your collection.
     


  20. Sanket Patel

    Sanket Patel New Member


    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
     


  21. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    Not that I am aware of............in row-wise.
     


  22. NSA

    NSA Member


    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
     


  23. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    Do not understand your query ??
     


  24. NSA

    NSA Member


    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 :pvTfmt 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?
     


  25. NSA

    NSA Member


    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
     


Share This Page