ODBC get all ledger details

Discussion in 'Tally Integration' started by Satwaki Sarkar, Mar 22, 2021.

    
  1. Satwaki Sarkar

    Satwaki Sarkar New Member


    I am trying to get all ledger details through ODBC. In few cases, a voucher has multiple ledger entries with the same name. Whats happening is ODBC is only able to pick the first entry for that ledger and ignoring the others as you can see in the screenshot below. How do i get all the entries along with their bill transaction details?

    Any help is much appreciated

    [Collection : RTSAllVouchers2]
    Type: Voucher
    IsODBCTable: Yes
    Fetch: Vouchernumber,date,vouchertypename

    [Collection : Reccoll]
    Source Collection: RTSAllVouchers2
    Walk : LedgerEntries
    by : Vouchernumber:$Vouchernumber
    by : Date:$Date
    by : VoucherTypeName:$VoucherTypeName
    by : PartyLedgerName:$PartyLedgerName
    by : PartyName:$PartyName
    by : LedgerName:$LedgerName
    by : BillRef : $.Ledgerentries[1].BILLALLOCATIONS[1].Name
    by : BillType : $.Ledgerentries[1].BILLALLOCATIONS[1].BillType
    Compute:Amount:$Amount
    ISODBCTable : Yes
     

    Attached Files:



  2. Sudhir83411

    Sudhir83411 New Member


    [Collection : RTSAllVouchers2]
    Type: Voucher


    [Collection : Reccoll]
    Source Collection: RTSAllVouchers2
    Walk : LedgerEntries
    Fetch: date,vouchertypename,Vouchernumber,PartyLedgerName,PartyName
    Compute:LedgerName:$LedgerName
    Compute:BillRef :$.Ledgerentries[1].BILLALLOCATIONS[1].Name
    Compute: BillType: $.Ledgerentries[1].BILLALLOCATIONS[1].BillType
    Compute:Amount:$Amount
    ISODBCTable : Yes
     


  3. Satwaki Sarkar

    Satwaki Sarkar New Member


    Thanks a lot Sudhir, it works now!
    However bill ref is still not showing the correct value as attached. Any way to fix that?
     

    Attached Files:



  4. Sudhir83411

    Sudhir83411 New Member


    59814+213438+1765+75526=350543
    Please check total once ....it might be right
     


  5. Satwaki Sarkar

    Satwaki Sarkar New Member


    Total is fine. I was referring to the bill reference numbers. It is showing the value as of the first entry for all the entries.

    MNF/MH/2021/0279
    MNF/MH/2021/0279
    MNF/MH/2021/0279
    MNF/MH/2021/0279
    MNF/MH/2021/0279
     


  6. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    BILLALLOCATIONS[$$Line]
     


  7. Satwaki Sarkar

    Satwaki Sarkar New Member


    Thanks for your reply!
    I tried the below code but still getting the same value repeated as in screenshot shared above

    [Collection : Reccoll]
    Source Collection: RTSAllVouchers2
    Walk : LedgerEntries
    Fetch: date,vouchertypename,Vouchernumber,PartyLedgerName,PartyName
    Compute:LedgerName:$LedgerName
    Compute:BillRef :$.Ledgerentries[$$Line].BILLALLOCATIONS[$$Line].Name
    Compute: BillType: $.Ledgerentries[$$Line].BILLALLOCATIONS[$$Line].BillType
    Compute:Amount:$Amount
    ISODBCTable : Yes
     


  8. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    Why did you add $$Line to LedgerEntries.......... I only suggested Bill Allocations...............
     


  9. Satwaki Sarkar

    Satwaki Sarkar New Member


    I tried this as well. Still doesn't work
    Compute:BillRef :$.Ledgerentries[1].BILLALLOCATIONS[$$Line].Name
    Compute: BillType: $.Ledgerentries[1].BILLALLOCATIONS[$$Line].BillType
     


  10. Satwaki Sarkar

    Satwaki Sarkar New Member


    @Amit Kamdar Any idea how i can get this? My guess is bill reference details are coming from pending bills. How do we fetch that?
     


  11. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    $Ledgerentries[1].AllLedgerEntries[1].BILLALLOCATIONS[$$Line].Name
     


  12. Satwaki Sarkar

    Satwaki Sarkar New Member


    @Amit Kamdar
    Still doesn't seem to work. See attached screenshot. Is there a way to see the bill allocations table alone?
     

    Attached Files:



  13. Samiksha

    Samiksha New Member


    Make a Function to collect the Values..........

    Then compute the same in your collection.
     
    Satwaki Sarkar likes this.


  14. Satwaki Sarkar

    Satwaki Sarkar New Member


    Hey Samiksha,

    Thanks for your response. Can you please elaborate? I am not following. I am very new to this
     


  15. Samiksha

    Samiksha New Member


    Am in learning mode too....... but from what I know and by logic........

    Try to get / extract your value from Vouchers with a Function.......... then compute that in your required collection.
     


  16. Satwaki Sarkar

    Satwaki Sarkar New Member


    I changed my code to below and now it works for target 1, however target 2 i am still getting only the top row.

    [Collection : Reccoll]
    Source Collection: RTSAllVouchers2
    Walk : LedgerEntries
    Compute: date:$date
    Compute: vouchertypename :$vouchertypename
    Compute: vouchernumber :$vouchernumber
    Compute: partyledgername :$partyledgername
    Compute: partyname :$partyname
    Compute:LedgerName:$LedgerName
    Compute:BillRef :$BILLALLOCATIONS.Name
    Compute: BillType: $BILLALLOCATIONS.BillType
    Compute:Amount:$Amount
    ISODBCTable : Yes

    For target 2, i wrote a code as below but still not getting the sub allocations bill wise according to the target2 screenshot. DO I NEED TO SPECIFY A DIFFERENT CHILD OF/USE BATCH ALLOCATIONS? Please advise

    [Collection : PurOrd_VchSrc]
    Type : Vouchers : Voucher type
    Child of : $$VchTypeReceipt
    Belongs To : Yes
    Fetch: date,vouchertypename,Vouchernumber,PartyLedgerName,PartyName
    Compute:LedgerName:$LedgerName
    Compute:BillRef :$BILLALLOCATIONS.Name
    Compute: BillType: $BILLALLOCATIONS.BillType
    Compute : Led1 : $$CollectionField:$BILLALLOCATIONS.Name:1:AllLedgerEntries
    Compute : Led2 : $$CollectionField:$BILLALLOCATIONS[$$Line].Name:2:AllLedgerEntries
    Compute:Amount:$Amount
    ISODBCTable : Yes
     

    Attached Files:



  17. ramajayam2022

    ramajayam2022 New Member


    Fixed this issue....

    The solution is Walk and fetch billallocations must be in the same collection
     


  18. ramajayam2022

    ramajayam2022 New Member


    [Collection : Reccoll]
    Source Collection: allvouchers
    Walk : LedgerEntries,billallocations
    ; [Collection : Reccoll1]
    ;Source Collection: reccoll
    Fetch :LedgerEntries.billallocations.*

    Compute: date:$date
    Compute: vouchertypename :$vouchertypename
    Compute: vouchernumber :$vouchernumber
    Compute: partyledgername :$partyledgername
    Compute: partyname :$partyname
    Compute:LedgerName:$LedgerName
    Fetch:Isdeemedpositive,Masterid,Alterid
    Compute:BillRef :$name
    Compute: BillType: $BillType
    Compute:Amount:$$AsCrAmt:$Amount
    Compute: billcreditper:$BILLCREDITPERIOD
     


  19. Sudhir83411

    Sudhir83411 New Member


    Keep up the hard work!
     
    ramajayam2022 likes this.


  20. ramajayam2022

    ramajayam2022 New Member



    Walk is the problem here ...
     
    Sudhir83411 likes this.


  21. Sudhir83411

    Sudhir83411 New Member


    I am trying to get ledger-filleted voucher details through ODBC.

    In “MasterCollection” I can extract the “ABC Company” ledger voucher in a single line with the GUID

    In “VoucherCollection” I can extract all ledger vouchers with multiple lines with GUID

    Now how can I filter “MasterCollection” GUID with VoucherCollection GUID

    Here is my code


    [Collection:CompanyColl]
    Type:Company
    Fetch:Name
    [Collection: MySourceCollection]
    Type : Voucher
    **********************************************************************************************************************************************************************
    [Collection: MasterCollection]
    Source Collection : MySourceCollection
    Keep Source:NO
    Walk :AllLedgerEntries
    Compute :MGUID:$GUID
    Compute :LedName:$LedgerName
    Compute:CmpName:$$LoopCollObj:$Name
    Filter:ledfilter

    [System: Formula]
    ledfilter:$LedName = "ABC Company"
    ***********************************************************************************************************************************************************************
    [Collection: VoucherCollection]
    Source Collection :MySourceCollection
    Keep Source:NO
    Walk :AllLedgerEntries
    Fetch:GUID,Date,VouchertypeName,VoucherNumber,ISDEEMEDPOSITIVE
    Compute :LedName:$LedgerName
    Compute :LedAmount:$Amount
    Compute :Narration:$().Narration
    Compute:CmpName:$$LoopCollObj:$Name
    ***********************************************************************************************************************************************************************
    [Collection: MasterDataCollection]
    Collection:MasterCollection:CompanyColl

    [Collection: VoucherDataCollection]
    Collection:VoucherCollection:CompanyColl


    ;;Select $CmpName,$MGUID,$Date,$VouchertypeName,$VoucherNumber,$LedName,$LedName,$LedAmount,$ISDEEMEDPOSITIVE,$Narration from MasterDataCollection
    ;;Select $CmpName,$GUID,$Date,$VouchertypeName,$VoucherNumber,$LedName,$LedName,$LedAmount,$ISDEEMEDPOSITIVE,$Narration from VoucherDataCollection
     

    Attached Files:



Share This Page