How to get VoucherTypeName and Amount of the last voucher entered in a ledger.

Discussion in 'Free Source Codes' started by ABDUL AZIZ, Apr 29, 2019.

    
  1. ABDUL AZIZ

    ABDUL AZIZ Member


    [Collection:TravelImpLedgerColl]

    Title : "List of Travel Imperest Ledgers"
    Type : Group
    Align : Center
    Format : $Name , 25
    Format : $Parent, 15
    Format : $OpeningBalance, 10
    Format : $ClosingBalance, 12
    Sub Title : "Group Name", " Parent", "Open. Balance","Closing Balance"


    Trigger : TrigerGrpItemColl
    Variable: VarGrpItem ;; to bridge between collection and display report
    Report : DisplayGrpItem

    [Report:TrigerGrpItemColl]

    Form:TrigerGrpItemColl
    Title: "Select Stock Item Name"

    [Form:TrigerGrpItemColl]

    Part:TrigerGrpItemColl
    Vertical Align : Top
    Horizontal Align : Center

    [Part:TrigerGrpItemColl]

    Line:TrigerGrpItemColl

    [Line:TrigerGrpItemColl]

    Field:TrigerGrpItemColl

    [Field:TrigerGrpItemColl]

    Table : TravelImpLedgerColl
    Modifies : VarGrpItem
    Show Table : Always ;;show the table always
    Width : 45
    Max : 256 ;;Allows to type 50 charactor, by defualt it is only 1 charactor

    ;;End of TrigerReport


    ;;Start Final Display Report
    [Report:DisplayGrpItem]

    Form : DisplayGrpItem
    ;Object: Ledger : ##VarGrpItem ;; to bridge between collection and display report
    Title : "Ledger Under : " + ##VarGrpItem
    Variable : SVFromDate, SVToDate,
    Set : SVfromDate : $$YearOfDate:##SvCurrentdate ;$$Monthstart:##SvCurrentdate
    ;Set : LedgerName : ##LedgerName
    Variable : IsMultiPage, InNewPages, ButtonTitleChange
    Set : IsMultiPage : yes
    Set : InNewPages : yes
    Set : SVToDate : ##SVCurrentdate


    [Form:DisplayGrpItem]
    Use : DSP Template
    Part : HeadingPart, DisplayGrpItem, DevBottPart
    Width : 100% screen
    Height : 100% screen

    [Part:DisplayGrpItem]
    Line : DisplayGrpItemTitle,DisplayGrpItem
    Bottom Line : DisplayGrpItemTotal
    Repeat : DisplayGrpItem : MyLedgerColl
    Scroll : Vertical
    Common Border : Yes
    Total : DisplayGrpItem, DisplayGrpItemLastPymtDate, DisplayGrpItemClBal

    [Line:DisplayGrpItemTitle]
    Use : DisplayGrpItem
    Local : Field : DisplayGrpItem : Info: "Ledger Name"
    Local : Field : DisplayGrpItemLastPymtDate : Info: "Last Payment Date"
    Local : Field : DisplayGrpItemLastBillDate : Info: "Last Bill Date"
    Local : Field : DisplayGrpItemLastVchType : Info: "Last Vch Type Name"
    Local : Field : DisplayGrpItemClBal : Info: "Closing Balance"
    Local : Field : Default : Style : Normal Bold
    Local : Field : Default : Line : 0
    Local : Field : Default : Align : Center
    Border : Thick Top Bottom


    [Line:DisplayGrpItemTotal]

    use : DisplayGrpItem
    Border : Totals
    Local : Field : Default : Style : Normal Bold
    Local : Field : Default : Type : String
    Local : Field : DisplayGrpItem : Set As : "Total"
    Local : Field : DisplayGrpItemLastPymtDate : Set As : ""
    Local : Field : DisplayGrpItemClBal : Set As : $$Total:DisplayGrpItemClBal


    [Line:DisplayGrpItem]
    Field : DisplayGrpItem, DisplayGrpItemLastPymtDate, DisplayGrpItemLastBillDate, DisplayGrpItemLastVchType
    Right Field : DisplayGrpItemClBal

    [Field:DisplayGrpItem]
    Use : Name Field
    Set as : $Name
    Width : 65
    Border : Thin Left

    [Field:DisplayGrpItemLastPymtDate]

    Use : Uni Date Field
    Set as : $LastVoucherDate
    Border : Thin Left Right
    Width : 15

    [Field:DisplayGrpItemLastBillDate]
    Use : Uni Date Field
    Set as : $LastVoucherDate
    Border : Thin Left Right
    Width : 15

    [Field:DisplayGrpItemLastVchType]
    Use : Name Field
    Set as : $VoucherTypeName
    Border : Thin Left Right
    Width : 15
    PartyLedger : $Name

    [Field:DisplayGrpItemClBal]
    Use : Amount Field
    Set as : $ClosingBalance
    Border : Thin Left
    Width : 20


    ;;;;;;;;;;;;;;;;
    [Collection:MyLedgerColl]
    Type : Ledger
    Filter : MyLedFilter
    Fetch : VoucherTypeName, *.*



    [Variable:VarGrpItem]
    Type:String

    [System: Variables]
    VarGrpItem: ""


    [System: Formulae]
    MyLedFilter : $ClosingBalance > 0 AND $Parent = ##VarGrpItem
    MyLastPymtDate : $VoucherTypeName Contains "Payment"
    MyLastBillDate : ($VoucherTypeName Contains "Journal") OR ($VouchTypeName Contains "Receipt")

    ;;End Final Display Report
     


  2. ABDUL AZIZ

    ABDUL AZIZ Member


    Please help me to get the Last VoucherTypeName and Last paid amount.
     


  3. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    Make a Collection of All Vouchers...
    Sort the collection in decreasing date-wise..... so that the last voucher is on top

    then Compute in the collection..........
    e.g. LastVchType : $Vouchertype
    LastVchAmt : $Amount

    Then in your field level........... use function $$CollectionField to call these values in your report.
     


  4. ABDUL AZIZ

    ABDUL AZIZ Member


    Sir,

    Thanks a lot for your quick reply, I tried the below code as suggested but I think I missed something..

    Same Voucher Type and Amount is showing for all ledgers

    It will be a great help if you could recheck my code and correct please...



    [Collection:TravelImpLedgerColl]

    Title : "List of Travel Imperest Ledgers"
    Type : Group
    Align : Center
    Format : $Name , 25
    Format : $Parent, 15
    Format : $OpeningBalance, 10
    Format : $ClosingBalance, 12
    Sub Title : "Group Name", " Parent", "Open. Balance","Closing Balance"


    Trigger : TrigerGrpItemColl
    Variable: VarGrpItem ;; to bridge between collection and display report
    Report : DisplayGrpItem

    [Report:TrigerGrpItemColl]

    Form:TrigerGrpItemColl
    Title: "Select Stock Item Name"

    [Form:TrigerGrpItemColl]

    Part:TrigerGrpItemColl
    Vertical Align : Top
    Horizontal Align : Center

    [Part:TrigerGrpItemColl]

    Line:TrigerGrpItemColl

    [Line:TrigerGrpItemColl]

    Field:TrigerGrpItemColl

    [Field:TrigerGrpItemColl]

    Table : TravelImpLedgerColl
    Modifies : VarGrpItem
    Show Table : Always ;;show the table always
    Width : 45
    Max : 256 ;;Allows to type 50 charactor, by defualt it is only 1 charactor

    ;;End of TrigerReport


    ;;Start Final Display Report
    [Report:DisplayGrpItem]

    Form : DisplayGrpItem
    ;Object: Ledger : ##VarGrpItem ;; to bridge between collection and display report
    Title : "Ledger Under : " + ##VarGrpItem
    Variable : SVFromDate, SVToDate,
    Set : SVfromDate : $$YearOfDate:##SvCurrentdate ;$$Monthstart:##SvCurrentdate
    ;Set : LedgerName : ##LedgerName
    Variable : IsMultiPage, InNewPages, ButtonTitleChange
    Set : IsMultiPage : yes
    Set : InNewPages : yes
    Set : SVToDate : ##SVCurrentdate


    [Form:DisplayGrpItem]
    Use : DSP Template
    Part : HeadingPart, DisplayGrpItem, DevBottPart
    Width : 100% screen
    Height : 100% screen

    [Part:DisplayGrpItem]
    Line : DisplayGrpItemTitle,DisplayGrpItem
    Bottom Line : DisplayGrpItemTotal
    Repeat : DisplayGrpItem : MyLedgerColl
    Scroll : Vertical
    Common Border : Yes
    Total : DisplayGrpItem, DisplayGrpItemLastPymtDate, DisplayGrpItemClBal

    [Line:DisplayGrpItemTitle]
    Use : DisplayGrpItem
    Local : Field : DisplayGrpItem : Info: "Ledger Name"
    Local : Field : DisplayGrpItemLastPymtDate : Info: "Last Payment Date"
    Local : Field : DisplayGrpItemLastBillDate : Info: "Last Bill Date"
    Local : Field : DisplayGrpItemLastVchType : Info: "Last Vch Type Name"
    Local : Field : DisplayGrpItemLastVchAmt : Info: "Last Vch Amount"
    Local : Field : DisplayGrpItemClBal : Info: "Closing Balance"
    Local : Field : Default : Style : Normal Bold
    Local : Field : Default : Line : 0
    Local : Field : Default : Align : Center
    Border : Thick Top Bottom


    [Line:DisplayGrpItemTotal]

    use : DisplayGrpItem
    Border : Totals
    Local : Field : Default : Style : Normal Bold
    Local : Field : Default : Type : String
    Local : Field : DisplayGrpItem : Set As : "Total"
    Local : Field : DisplayGrpItemLastPymtDate : Set As : ""
    Local : Field : DisplayGrpItemClBal : Set As : $$Total:DisplayGrpItemClBal


    [Line:DisplayGrpItem]
    Field : DisplayGrpItem, DisplayGrpItemLastPymtDate, DisplayGrpItemLastBillDate, +
    DisplayGrpItemLastVchType, DisplayGrpItemLastVchAmt
    Right Field : DisplayGrpItemClBal

    [Field:DisplayGrpItem]
    Use : Name Field
    Set as : $Name
    Width : 65
    Border : Thin Left

    [Field:DisplayGrpItemLastPymtDate]

    Use : Uni Date Field
    Set as : $LastVoucherDate
    Border : Thin Left Right
    Width : 15

    [Field:DisplayGrpItemLastBillDate]
    Use : Uni Date Field
    Set as : $LastVoucherDate
    Border : Thin Left Right
    Width : 15

    [Field:DisplayGrpItemLastVchType]
    Use : Name Field
    Set as : $$CollectionField:$VoucherTypeName:1:AllVchrs
    Border : Thin Left Right
    Width : 15

    [Field:DisplayGrpItemLastVchAmt]
    Use : Amount Field
    Set as : $$CollectionField:$Amount:1:AllVchrs
    Border : Thin Left Right
    Width : 15

    [Field:DisplayGrpItemClBal]
    Use : Amount Field
    Set as : $ClosingBalance
    Border : Thin Left
    Width : 20


    ;;;;;;;;;;;;;;;;
    [Collection:MyLedgerColl]
    Type : Ledger
    Filter : MyLedFilter
    Fetch : VoucherTypeName, *.*

    [Variable:VarGrpItem]
    Type:String

    [System: Variables]
    VarGrpItem: ""


    [System: Formulae]
    MyLedFilter : $ClosingBalance > 0 AND $Parent = ##VarGrpItem
    MyLastPymtDate : $VoucherTypeName Contains "Payment"
    MyLastBillDate : ($VoucherTypeName Contains "Journal") OR ($VouchTypeName Contains "Receipt")
    ;VoucherTypeFilter : $LedgerName = DisplayGrpItem


    [Collection:AllVchrs]
    Type : Voucher
    Sort : $Date
    Compute : LastVchType : $VoucherTypeName
    Compute : LastVchAmt : $Amount
    ;Filter : VoucherTypeFilter


    ;;End Final Display Report
     

    Attached Files:

    Last edited: Apr 29, 2019


  5. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    Try $Amount:Ledger:$LedgerName and so on
     


  6. ABDUL AZIZ

    ABDUL AZIZ Member


    Sir,

    If possible please go through these codes. The problem I am facing is that, it give me the correct payment detail but not able to fetch the Journal Voucher details.



    [Collection:TravelImpLedgerColl]

    Title : "List of Travel Imperest Ledgers"
    Type : Group
    Align : Center
    Format : $Name , 25
    Format : $Parent, 15
    Format : $OpeningBalance, 10
    Format : $ClosingBalance, 12
    Sub Title : "Group Name", " Parent", "Open. Balance","Closing Balance"


    Trigger : TrigerGrpItemColl
    Variable: VarGrpItem ;; to bridge between collection and display report
    Report : DisplayGrpItem

    [Report:TrigerGrpItemColl]

    Form:TrigerGrpItemColl
    Title: "Select Stock Item Name"

    [Form:TrigerGrpItemColl]

    Part:TrigerGrpItemColl
    Vertical Align : Top
    Horizontal Align : Center

    [Part:TrigerGrpItemColl]

    Line:TrigerGrpItemColl

    [Line:TrigerGrpItemColl]

    Field:TrigerGrpItemColl

    [Field:TrigerGrpItemColl]

    Table : TravelImpLedgerColl
    Modifies : VarGrpItem
    Show Table : Always ;;show the table always
    Width : 45
    Max : 256 ;;Allows to type 50 charactor, by defualt it is only 1 charactor

    ;;End of TrigerReport


    ;;Start Final Display Report
    [Report:DisplayGrpItem]

    Form : DisplayGrpItem
    ;Object: Ledger : ##VarGrpItem ;; to bridge between collection and display report
    Title : "Ledger Under : " + ##VarGrpItem
    Variable : SVFromDate, SVToDate,
    Set : SVfromDate : $$YearOfDate:##SvCurrentdate ;$$Monthstart:##SvCurrentdate
    ;Set : LedgerName : ##LedgerName
    Variable : IsMultiPage, InNewPages, ButtonTitleChange
    Set : IsMultiPage : yes
    Set : InNewPages : yes
    Set : SVToDate : ##SVCurrentdate


    [Form:DisplayGrpItem]
    Use : DSP Template
    Part : HeadingPart, DisplayGrpItem, DevBottPart
    Width : 100% screen
    Height : 100% screen

    [Part:DisplayGrpItem]
    Line : DisplayGrpItemTitle,DisplayGrpItem
    Bottom Line : DisplayGrpItemTotal
    Repeat : DisplayGrpItem : MyLedgerColl
    Scroll : Vertical
    Common Border : Yes
    Total : DisplayGrpItem, DisplayGrpItemLastPymtDate, DisplayGrpItemClBalDr, DisplayGrpItemClBalCr

    [Line:DisplayGrpItemTitle]
    Use : DisplayGrpItem
    Local : Field : DisplayGrpItem : Info: "Ledger Name"
    Local : Field : DisplayGrpItemOpBal : Info: "Opening Balance"
    Local : Field : DisplayGrpItemLastPymtDate : Info: "Last Payment Date"
    Local : Field : DisplayGrpItemLastPytVchType : Info: "Last Pymt Vch Type Name"
    Local : Field : DisplayGrpItemLastPayVchAmt : Info: "Last Paid Amount"
    Local : Field : DisplayGrpItemLastBillDate : Info: "Last Bill/Rcpt. Date"
    Local : Field : DisplayGrpItemLastBillVchType : Info: "Last Bill/Rcpt. Vch Type Name"
    Local : Field : DisplayGrpItemLastBillVchAmt : Info: "Last Bill/Rcpt. Amount"
    Local : Field : DisplayGrpItemClBalDr : Info: "Closing Balance Dr"
    Local : Field : DisplayGrpItemClBalCr : Info: "Closing Balance Cr"
    Local : Field : Default : Style : Normal Bold
    Local : Field : Default : Line : 0
    Local : Field : Default : Align : Center
    Border : Thick Top Bottom


    [Line:DisplayGrpItemTotal]

    use : DisplayGrpItem
    Border : Totals
    Local : Field : Default : Style : Normal Bold
    Local : Field : Default : Type : String
    Local : Field : DisplayGrpItem : Set As : "Total"
    Local : Field : DisplayGrpItemLastPymtDate : Set As : ""
    Local : Field : DisplayGrpItemLastBillDate : Set As : ""
    Local : Field : DisplayGrpItemLastPytVchType : Set As : ""
    Local : Field : DisplayGrpItemLastVchAmt : Set As : ""
    Local : Field : DisplayGrpItemClBalDr : Set As : $$Total:DisplayGrpItemClBalDr
    Local : Field : DisplayGrpItemClBalCr : Set As : $$Total:DisplayGrpItemClBalCr


    [Line:DisplayGrpItem]
    Field : DisplayGrpItem, DisplayGrpItemOpBal, +
    DisplayGrpItemLastPymtDate, DisplayGrpItemLastPytVchType, DisplayGrpItemLastPayVchAmt, +
    DisplayGrpItemLastBillDate, DisplayGrpItemLastBillVchType, DisplayGrpItemLastBillVchAmt
    Right Field : DisplayGrpItemClBalDr, DisplayGrpItemClBalCr

    [Field:DisplayGrpItem]
    Use : Name Field
    Set as : $Name
    Width : 35
    Border : Thin Left

    ;;;;;;;PAYMENT SECTION;;;;;;;
    [Field:DisplayGrpItemOpBal]
    Use : Amount Field
    Set as : $OpeningBalance
    Width : 10
    Border : Thin Left
    Format : "DrCr"

    [Field:DisplayGrpItemLastPymtDate]
    Use : Uni Date Field
    Set as : $$CollectionField:$Date:1:AllPayVchrs ;$LastVoucherDate
    Border : Thin Left Right
    Width : 10


    [Field:DisplayGrpItemLastPytVchType]
    Use : Name Field
    Set as : $$CollectionField:$VoucherTypeName:1:AllPayVchrs
    Border : Thin Left Right
    Width : 12

    [Field:DisplayGrpItemLastPayVchAmt]
    Use : Amount Field
    Set as : $$CollectionField:$Amount:1:AllPayVchrs
    Border : Thin Left Right
    Width : 10
    ;Format : "DrCr"


    ;;;;;;;BILL/RECEIPT SECTION;;;;;;;
    [Field:DisplayGrpItemLastBillDate]
    Use : Uni Date Field
    Set as : $$CollectionField:$Date:1:AllRcptVchrs ;$LastVoucherDate
    Border : Thin Left Right
    Width : 10


    [Field:DisplayGrpItemLastBillVchType]
    Use : Name Field
    Set as : $$CollectionField:$VoucherTypeName:1:AllRcptVchrs
    Border : Thin Left Right
    Width : 12

    [Field:DisplayGrpItemLastBillVchAmt]
    Use : Amount Field
    Set as : $$CollectionField:$Amount:1:AllRcptVchrs
    Border : Thin Left Right
    Width : 10
    ;Format : "DrCr"

    ;;;CLOSING BALANCE;;;;;;;;;;;
    [Field:DisplayGrpItemClBalDr]
    Use : Amount Field
    Set as : If $$IsDr:$ClosingBalance Then $ClosingBalance Else $$AsAmount:0
    Border : Thin Left
    Width : 12
    ;Format : "DrCr"

    [Field:DisplayGrpItemClBalCr]
    Use : Amount Field
    Set as : If NOT $$IsDr:$ClosingBalance Then $ClosingBalance Else $$AsAmount:0

    Border : Thin Left
    Width : 12
    ;Format : "DrCr"
    ;;;;;;;;;;;;;;;;LEDGER COLL;;;;;;;;;
    [Collection:MyLedgerColl]
    Type : Ledger
    Filter : MyLedFilter
    Fetch : VoucherTypeName, *.*

    ;;;;PAYMENT COLL;;;;;;;;;;
    [Collection:AllPayVchrs]
    Type : Vouchers
    Sort : -$Date
    Filter : MyPayEntries
    Fetch : Amount, AllLedgerEntries.Amount, Date, AllLedgerEntries.Date, VoucherTypeName, AllLedgerEntries.VoucherTypeName

    ;;;;RECEIPT COLL;;;;;;;;;;
    [Collection:AllRcptVchrs]
    Type : Vouchers
    Sort : -$Date
    Filter : MyRcptEntries
    Fetch : Amount, AllLedgerEntries.Amount, Date, AllLedgerEntries.Date, VoucherTypeName, AllLedgerEntries.VoucherTypeName


    [Variable:VarGrpItem]
    Type:String

    [System: Variables]
    VarGrpItem: ""

    [System: Formulae]
    MyLedFilter : $ClosingBalance > 0 AND $Parent = ##VarGrpItem

    MyPayEntries : $LedgerName = #DisplayGrpItem AND $$IsPayment:$VoucherTypeName;$VoucherTypeName Contains "Payment"

    MyRcptEntries : $LedgerName = #DisplayGrpItem AND ($$IsReceipt:$VoucherTypeName OR $$IsJournal:$VoucherTypeName);$VoucherTypeName Not Contains "Payment"






    ;;End Final Display Report
     


  7. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    This collection will get you last voucher Amount and Voucher type for a given Ledger

    Code:
    [Collection : RBCPartyVouchersSrc]
    
    Type : Vouchers : Ledger                                            ; Because you want it for a ledger
    Child Of : ##vYourLedgerName                                 ;   your variable name or #fieldName
    
    
    [Collection : RBCPartyVouchers]
    
    Source Collection : RBCPartyVouchersSrc
    Fetch    :Date, VoucherTypeName, PartyLedgerName, Amount
    
    Sort    : @@Default    : -$Date
    
    
    [System : Forumlae]
    
    LastVchType : $$CollectionField:$VoucherTypeName:First:RBCPartyVouchers
    LastVchAmount : $$CollectionField:$Amount:First:RBCPartyVouchers
     
    Last edited: May 17, 2019


  8. ABDUL AZIZ

    ABDUL AZIZ Member


    Thanks a lot Ma'am for your reply. I have tried as per my best I am attaching TDL code and Screen shot of the report for your reference.

    In case of Payment Entries it is working fine, except in multi ledger voucher like if in a voucher entry, more that two or more ledger has been captured then information is not showing if the ledger is not at first position.

    In case of Receipt Entries it is showing one entry only which can be seen at screenshot, rest are not showing.

    In case of Journal no entry is showing at all.

    Please help and modify my TDL code if possible.

    Thanks & Regard
     

    Attached Files:



  9. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    Sir, Not Mam :mad::confused:

    Okay..

    I will try it.. by tomorrow
     


  10. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    o_O ........ Me too.......... :mad::confused: ....... :D:)

    I was gonna ask you, why everyone calls you ma'am ??? ;);)
     
    zakirhossain_diu and sattam like this.


  11. lumos

    lumos Member


    It's high time you change your display name .:D
     


  12. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    Garima is organisation name.. :(;)

    Dear Abdul

    Please attach tally data also.. As this report is meant for specific type of business.. I can not test it without data

    You should use Type : Ledger in Collection

    Voucher Type in filter otherwise it will take long time

    I am getting missing parts and buttons errors.. more I don't how to open your report.. you have not provided any menu for it in the given code.

    Always put code in order so that minimum inconvenience is caused to the person, who tries to help
     
    Last edited: May 21, 2019


  13. ABDUL AZIZ

    ABDUL AZIZ Member


    So sorry sir for addressing you as ma'am, and sorry for the inconvenience caused.

    I have added the button to open the report in F5.
    Also added the missing parts.
    I have Attached the data backup.

    What I want from this report: Summary of Last payment made and Last bills submitted or cash returned by them of all the ledgers of Travel Imprest Group having closing balance.

    Please have a look at the code.

    Thanks & Regards.
     

    Attached Files:



  14. Devendra_Rawat

    Devendra_Rawat Well-Known Member



  15. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    Not able to restore,

    Please provide Zip of Company folder
     


  16. ABDUL AZIZ

    ABDUL AZIZ Member


    Please find attached complete folder.
     

    Attached Files:



  17. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    You have given a different company or company with different data,(not as shown in the image file you uploaded)

    These things discourage me.

    Now the Question is,

    shall there be data in each columns for each ledger ??
     


  18. ABDUL AZIZ

    ABDUL AZIZ Member


    Sir, Actually the image was taken from the live data, due to some some reason I was unable to share the same data, but I created a new company and passed the entries to get the required report and then I sent you the backup which gives the desired result.
    Sorry for the inconvenience!

    Now what I want is that:

    1. Summary of a group ledgers which has closing balance.
    2. If they have closing balance then I want the Last debit entry and Last credit entry details like date, voucher type name, Amount etc.
    3. It is required to analyse that if someone has closing balance then, what is the last date when he was paid and when he submitted the last bill.
    4. I have attached new screenshot wherein I have written expected entries in red color (I am not good in using paint brush)

    Thanks & Regards.
     

    Attached Files:



  19. Devendra_Rawat

    Devendra_Rawat Well-Known Member



  20. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    Here it works
    ScreenAziz.jpg
    Code:
    [#Menu: Gateway of Tally]
        Add     : Button : MyVoucherTypeBook
       
    [Button: MyVoucherTypeBook]
       
        Title    : "Travel Imp Summary"
        Key        : F5
        Action  : Display Collection : TravelImpLedgerColl
    
    [Collection:TravelImpLedgerColl]
    
        Title        : "List of Travel Imprest Groups"
        Type        : Group
        Align        : Center
        Format        : $Name , 25 
        Format        : $Parent, 15
        Format        : $OpeningBalance, 10
        Format        : $ClosingBalance, 12
        Sub Title    : "Group Name", " Parent", "Open. Balance","Closing Balance"
        Trigger        : TrigerGrpItemColl
        Variable    : VarGrpItem ;; to bridge between collection and display report
        Report        : DisplayGrpItem
        Filter        : GrpFilter
       
        [Report:TrigerGrpItemColl]
           
            Form:TrigerGrpItemColl
            Title: "Select Stock Item Name"
    
            [Form:TrigerGrpItemColl]
               
                Part:TrigerGrpItemColl
                Vertical Align        : Top
                Horizontal Align    : Center
               
                [Part:TrigerGrpItemColl]
                   
                    Line:TrigerGrpItemColl
                   
                    [Line:TrigerGrpItemColl]
                       
                        Field:TrigerGrpItemColl
                       
                        [Field:TrigerGrpItemColl]
                           
                            Table        : TravelImpLedgerColl
                            Modifies    : VarGrpItem
                            Show Table    : Always ;;show the table always
                            Width        : 45
                            Max            : 256 ;;Allows to type 50 charactor, by defualt it is only 1 charactor
                   
    ;;End of TrigerReport
    
    
    ;;Start Final Display Report
    [Report:DisplayGrpItem]
           
            Form        : DisplayGrpItem
            ;Object: Ledger : ##VarGrpItem ;; to bridge between collection and display report
            Title        : "Ledger Under : " + ##VarGrpItem
            Variable     : SVFromDate, SVToDate,
            Set         : SVfromDate    : $$YearOfDate:##SvCurrentdate ;$$Monthstart:##SvCurrentdate
            ;Set         : LedgerName     : ##LedgerName
            Variable     : IsMultiPage, InNewPages,
            Set         : IsMultiPage     : yes
            Set         : InNewPages     : yes
            Set         : SVToDate         : ##SVCurrentdate
               
       
            [Form:DisplayGrpItem]
                Use        : DSP Template
                Part    : HeadingPart, DisplayGrpItem, DevBottPart
                Width    : 100% screen
                Height    : 100% screen
               
    
            [Part: HeadingPart]
                Line        : HeadingPart,
               
    
                [Line:HeadingPart]
                    Field:HeadingPart
                   
                    [Field:HeadingPart]
                       
                        Use            : Name field
                        Set as        : $$LocaleString:"Statement for the period: from " + $$String:##SVFromDate + " to " + $$String:##SVToDate
                        Align         : Center
                        Full Width     : Yes
                        Local        : Style        : Defualt    : Height    : 20
                        Color        : Black
                        Style        : large bold
                        Background    : Light Surf Green
                       
    [Part:DevBottPart]
        Line:DevBottPart
        Background: Dark Green
       
    [Line:DevBottPart]
        Field:DevBottPart
       
    [Field:DevBottPart]
        Info        : "(This is a customised report, developed by Mr. Abdul Aziz)"
        Align         : Center
        Full Width     : Yes
        Local        : Style    :Defualt    :Height    :20
        Color        : Black
        Style        : Tiny Small ;Small Prompt;large bold
        Background    : Light Surf Green;Light Sea Green;CalcBGClr
        ;Border        : Thin Top
        Invisible    : NOT ($$InPrintMode OR $$InExportMode OR $$InMailAction) OR ($$InCreateMode OR $$InAlterMode)
        Invisible    :  ($$InPrintMode OR $$InExportMode OR $$InMailAction) OR ($$InCreateMode OR $$InAlterMode)
    
    
    
    
    
                [Part:DisplayGrpItem]
                    Line            : DisplayGrpItemTitle,DisplayGrpItem
                    Bottom Line        : DisplayGrpItemTotal
                    Repeat            : DisplayGrpItem : MyLedgerColl
                    Scroll            : Vertical
                    Common Border    : Yes
                    Total            : DisplayGrpItem, DisplayGrpItemLastPymtDate, DisplayGrpItemClBalDr, DisplayGrpItemClBalCr
                   
                    [Line:DisplayGrpItemTitle]
                        Use        : DisplayGrpItem   
                        Local    : Field    : DisplayGrpItem                 : Info: "Ledger Name"
                        Local    : Field    : DisplayGrpItemOpBal            : Info: "Opening Balance"
                        Local    : Field    : DisplayGrpItemLastPymtDate    : Info: "Last Payment Date"
                        Local    : Field    : DisplayGrpItemLastPytVchType    : Info: "Last Pymt Vch Type Name"
                        Local    : Field    : DisplayGrpItemLastPayVchAmt    : Info: "Last Paid Amount"
                        Local    : Field    : DisplayGrpItemLastBillDate    : Info: "Last Bill/Rcpt. Date"
                        Local    : Field    : DisplayGrpItemLastBillVchType    : Info: "Last Bill/Rcpt. Vch Type Name"
                        Local    : Field    : DisplayGrpItemLastBillVchAmt    : Info: "Last Bill/Rcpt. Amount"                   
                        Local    : Field    : DisplayGrpItemClBalDr            : Info: "Closing Balance Dr"
                        Local    : Field    : DisplayGrpItemClBalCr            : Info: "Closing Balance Cr"
                        Local    : Field    : Default                        : Style    : Normal Bold
                        Local    : Field    : Default                        : Line    : 0
                        Local    : Field    : Default                        : Align    : Center
                        Border    : Thick Top Bottom
                       
    
                        [Line:DisplayGrpItemTotal]
    
                            use     : DisplayGrpItem
                            Border     : Totals
                            Local     : Field : Default                         : Style     : Normal Bold
                            Local     : Field : Default                         : Type         : String
                            Local     : Field : DisplayGrpItem                : Set As     : "Total"
                            Local     : Field : DisplayGrpItemLastPymtDate     : Set As     : ""
                            Local     : Field : DisplayGrpItemLastBillDate    : Set As     : ""
                            Local     : Field : DisplayGrpItemLastPytVchType    : Set As     : ""
                            Local     : Field : DisplayGrpItemLastVchAmt        : Set As     : ""
                            Local     : Field : DisplayGrpItemClBalDr            : Set As     : $$Total:DisplayGrpItemClBalDr
                            Local     : Field : DisplayGrpItemClBalCr            : Set As     : $$Total:DisplayGrpItemClBalCr
    
    
                    [Line:DisplayGrpItem]
                        Field        : DisplayGrpItem, DisplayGrpItemOpBal, +
                                      DisplayGrpItemLastPymtDate, DisplayGrpItemLastPytVchType, DisplayGrpItemLastPayVchAmt, +
                                      DisplayGrpItemLastBillDate, DisplayGrpItemLastBillVchType, DisplayGrpItemLastBillVchAmt
                        Right Field : DisplayGrpItemClBalDr, DisplayGrpItemClBalCr
                       
                        [Field:DisplayGrpItem]
                            Use        : Name Field   
                            Set as    : $Name
                            Width    : 35
                            Border    : Thin Left
    
    ;;;;;;;PAYMENT SECTION;;;;;;;                       
                        [Field:DisplayGrpItemOpBal]
                            Use        : Amount Field   
                            Set as    : $OpeningBalance
                            Width    : 10
                            Border    : Thin Left
                            Format    : "DrCr"
                           
                        [Field:DisplayGrpItemLastPymtDate]
                            Use        : Uni Date Field   
                            Set as    : $$CollectionField:$Date:1:AllPayVchrs ;$LastVoucherDate
                            Border    : Thin Left Right
                            Width    : 10
    
    
                        [Field:DisplayGrpItemLastPytVchType]
                            Use                : Name Field   
                            Set as            : $$CollectionField:$VoucherTypeName:1:AllPayVchrs
                            Border            : Thin Left Right
                            Width            : 12
                           
                        [Field:DisplayGrpItemLastPayVchAmt]
                            Use                : Amount Field   
                            Set as            : $$CollectionField:$Amount:1:AllPayVchrs
                            Border            : Thin Left Right
                            Width            : 10
                            ;Format    : "DrCr"
       
    
    ;;;;;;;BILL/RECEIPT SECTION;;;;;;;
                        [Field:DisplayGrpItemLastBillDate]
                            Use        : Uni Date Field   
                            Set as    : $$CollectionField:$Date:1:AllJourVchrs ;$LastVoucherDate ;
                            Border    : Thin Left Right
                            Width    : 10
    
    
                        [Field:DisplayGrpItemLastBillVchType]
                            Use                : Name Field   
                            Set as            : $$CollectionField:$VoucherTypeName:1:AllJourVchrs
                            Border            : Thin Left Right
                            Width            : 12
                           
                        [Field:DisplayGrpItemLastBillVchAmt]
                            Use                : Amount Field   
                            Set as            : $$CollectionField:$Amount:1:AllJourVchrs
                            Border            : Thin Left Right
                            Width            : 10
                            ;Format    : "DrCr"
       
    ;;;CLOSING BALANCE;;;;;;;;;;;
                        [Field:DisplayGrpItemClBalDr]
                            Use        : Amount Field   
                            Set as    : If $$IsDr:$ClosingBalance Then $ClosingBalance Else $$AsAmount:0
                            Border    : Thin Left
                            Width    : 12   
                            ;Format    : "DrCr"
    
                        [Field:DisplayGrpItemClBalCr]
                            Use        : Amount Field   
                            Set as    : If NOT $$IsDr:$ClosingBalance Then $ClosingBalance Else $$AsAmount:0
    
                            Border    : Thin Left Right
                            Width    : 12   
    ;Format    : "DrCr"
    ;;;;;;;;;;;;;;;;LEDGER COLL;;;;;;;;;
    [Collection:MyLedgerColl]
        Type    : Ledger
        Filter    : MyLedFilter
        Fetch    : VoucherTypeName, *.*
    
    ;;;;PAYMENT COLL;;;;;;;;;;   
    [Collection:AllPayVchrs]
        Source Collection    : MyLedgerColl
        Type                 : Vouchers : Ledger
        Child Of            : #DisplayGrpItem
        ;Belongs To            : Yes
        Sort                 : Default : -$Date
        Filter                 : MyPayEntries
        Fetch               : Amount, AllLedgerEntries.Amount, Date, AllLedgerEntries.Date, VoucherTypeName, AllLedgerEntries.VoucherTypeName
    
    ;;;;RECEIPT COLL;;;;;;;;;;   
    [Collection:AllRcptVchrs]
        Source Collection    : MyLedgerColl
        Type                 : Vouchers: Ledger
        Child Of            : #DisplayGrpItem
        ;Belongs To            : Yes
        Sort                 : Default : -$Date
        Filter                 : MyRcptEntries
        Fetch               : Amount, AllLedgerEntries.Amount, Date, AllLedgerEntries.Date, VoucherTypeName, AllLedgerEntries.VoucherTypeName
    
    ;;;;JOURNAL COLL;;;;;;;;;;   
    [Collection:AllJourVchrs]
        Source Collection    : MyLedgerColl
        Type                : Vouchers: Ledger
        Child Of            : #DisplayGrpItem
        ;Belongs To            : Yes
        Filter                : MyJourEntries
        Sort                 : Default : -$Date, $Name
        Fetch                : Amount, AllLedgerEntries.Amount, Date, AllLedgerEntries.Date, VoucherTypeName, AllLedgerEntries.VoucherTypeName
       
    
    [Variable:VarGrpItem]
        Type:String
    
    [System: Variables]
        VarGrpItem    : ""
    
               
    [System: Formulae]
        MyLedFilter     : $ClosingBalance > 0 AND $Parent = ##VarGrpItem
       
        MyPayEntries    :  ($$IsPayment:$VoucherTypeName OR $$IsJournal:$VoucherTypeName)
       
        MyRcptEntries    :  ($$IsReceipt:$VoucherTypeName OR $$IsJournal:$VoucherTypeName)
       
        MyJourEntries    :  ($$IsJournal:$VoucherTypeName OR $$IsReceipt:$VoucherTypeName)
       
        GrpFilter        : $Name Contains "Travel"
       
    
     
    Frz, ABDUL AZIZ and psr like this.


  21. ABDUL AZIZ

    ABDUL AZIZ Member


    Thank you so much sir, Its working :)
     


  22. ABDUL AZIZ

    ABDUL AZIZ Member


    Sir, There is one small issue like:

    1. In case of Multi Ledger Entries in a Single Voucher, it is picking the first Amount instead of it should pick the amount against that particular ledger only.
    I have attached two screenshot for your quick look.
    2. In case of Payment Section side, entries should reflect if it is a Debit Entries only. (I have modified the same by adding $$IsDebit:, code is attached please have a look whether it is right way to do)
    3. In case of Bills/Receipt Section side, entries should reflect if it is a Credit Entries only.
    4. On press of enter is should open that Selected Ledger Account and show its all vouchers like drill down report.

    Please...


    Thanks & Regards



    ScreenshotTravelImperest New.JPG

    ScreenshotTravelImperest New 2.JPG
     

    Attached Files:



  23. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    try with LedgerEntries instead of AllLedgerEntries
     


  24. ABDUL AZIZ

    ABDUL AZIZ Member


    Sir,

    Tried but not getting the desired result, in fact no change same output is coming.

    Thanks & Regards
     


  25. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    I can tell you what the issue is :

    when you walk through ledgerentries, you will get two types of entries
    1. Where ledgername is equal to your ledgername
    2. where ledgername is not equal to your ledgername

    you may need to use Walk : LedgerEntries (.. but first check the collection data)
    check your collections data in calculator panel using select command...

    then decide the entry to you want to show, apply the filter accordingly apart from the already applied filters
     


Share This Page