Dear experts, please help me correct the code i am designing the report in which i wanted to show each item batch no. (Multiple) with expiry date and mfg date, i am not able to get mdf date and i am getting only single bath if item having multiple batches then its not showing .. i know my code is wrong so i need help to achieve the same . below is the code .. ======================= [#Menu: Gateway of Tally] Add: Item: Expiry Report : Display: APExpiry Report [Report: ApExpiry Report] Title : "Stock Item Expiry report " Print Set : Report Title : "Stock Item Expiry report" Variable : SVFromDate,SVToDate Set : SVFromDate : $$MonthStart:##SVCurrentDate Set : SVToDate : $$MonthEnd:##SVCurrentDate Form :APExpiry Report [Form: APExpiry Report] Part : APEP MRTL,APEXP ReportTL,APExpiry Report Bottom Part : APExpiry_ReportBTM Background : Released Pale Yellow Buttons : ExplodeFlag,PrintButton, ExportButton, UploadButton, MailButton Button : ChangePeriod BottomButton : InvReports, AcctReports, Report Operations, DYBKConfigure, FilterButton, ValueButton Bottom Toolbar Buttons : BottomToolBarBtn1, BottomToolBarBtn8, BottomToolBarBtn9, BottomToolBarBtn10, BottomToolBarBtn11, BottomToolBarBtn12 Space Top : if $$InPrintMode then ##SVSpaceTop else 0.05 inches Space Bottom : if $$InPrintMode then 0.5 else 0 inches Space Left : if $$InPrintMode AND $$InPixelMode then 0.5 else 0 inches Space Right : if $$InPrintMode AND $$InPixelMode then 0.25 else 0 inches Option : Small Size Form : NOT $$InPrintMode [Part: APEP MRTL] Line : APEP MRTL Invisible : $$InPrintMode [Line: APEP MRTL] Field : Name Field Right Field: Simple Field Local : Field: Name Field :Set as: "Stock Expiry Report" Local : Field: Simple Field :Set as: $$String:##SVFromDate + " to " + $$String:##SVToDate Local : Field: Simple Field :Width : 30 % Page Local : Field: Simple Field :Align: Right [Part: APEXP ReportTL] Line : APEXP ReportTL Border : Column Titles [Line: APEXP ReportTL] Field : EPSL1,EPITEMNAME,EPBATCHNAME,EPEXPIRYDT,EPMFDDT, Right Field : EPGRPNAME,EGDW,EPD,ECS Local : Field : Default : Type : String Local : Field : Default : Align : Center Local : Field : EPSL1 : Set As : "SL" Local : Field : EPITEMNAME : Set as : "Item Name" Local : Field : EPBATCHNAME : Set as : "Batch Name" Local : Field : EPEXPIRYDT : Set as : "Expiry Date" Local : Field : EPMFDDT : Set as : "Mfd On" Local : Field : EPGRPNAME : Set as : "Group Name " Local : Field : EGDW : Set as : "Godown" Local : Field : EPD : Set as : "No. of Days till Expiry" Local : Field : ECS : Set as : "Closing Stock " Local : Field : Default : Line : 0 [Part: APExpiry Report] Object : Batch Allocations Line : APExpiry Report Repeat : APExpiry Report :APExpiry Report Scroll : Vertical Common Border : Yes Float : No Total : [Line: APExpiry Report] Field : EPSL1,EPITEMNAME,EPBATCHNAME,EPEXPIRYDT,EPMFDDT Right Field :EPGRPNAME,EGDW ,EPD,ECS Local : Field : Default : Style : Small Space Top : .20 [ Field : EPSL1] ;;==============Sr No Use : Name Field Set as : $$Line Width : 4 Border : Thin Left Align: Center [ Field : EPITEMNAME] ;;===========Item Name Use: Name Field Set as: $StockItemName Border : Thin Left ;Invisible : $$IsEmpty:$EXPIRYPERIOD [ Field : EPBATCHNAME];;;;============Batch Name Use: Name Field Set As : $$CollectionField:$BatchName:1:BatchAllocations Border : Thin Left Align : Center [ Field : EPEXPIRYDT] ;;===============Expiry Date Use : Due Date Field Set As : $$CollectionField:$EXPIRYPERIOD:1:BatchAllocations Align : Center Border : Thin Left Inactive : Not #DSPShowMfd [ Field :EPMFDDT];;==============MfG Date Use : Due Date Field;; type : string Set As: @@BatchMfgDate ;$$CollectionField:$MfdOn:1:BatchAllocations Border : Thin Left Width: 15 [ Field : EPGRPNAME];;;;;;;;;;;;=======Group Name Border: thin left Use : Name Field Set as :$Parent Border : Thin Left [ Field : EGDW] ;;;;;;;;;;;;=======Godown Name Use : Name Field Set As :$$CollectionField:$GodownName:1:BatchAllocations ;Width : 4 Border : Thin Left Align : Center [ Field :EPD] ;;========= Ramian Days Use : Number Field Setas : #EPEXPIRYDT - ($$SysInfo:SystemDate) Border : Thin left Align: Center ;Width: 12 [ Field :ECS] ;;========= Closing Stock Use : Number Field Setas : $ClosingBalance Border : Thin left Align: Center [Part: APExpiry_ReportBTM] Line : APExpiry_ReportBTM Border : Totals [Line:APExpiry_ReportBTM] Field : EPSL1,EPITEMNAME,EPBATCHNAME,EPEXPIRYDT,EPMFDDT Right Field : EPGRPNAME,EPD Local : Field : Default : Type : String Local : Field : Default : Align : Center Local : Field : EPSL1 : Set As : "" Local : Field : EPITEMNAME : Set as : "" Local : Field : EPBATCHNAME : Set as : "" Local : Field : EPEXPIRYDT : Set as : "" Local : Field : EPMFDDT : Set as : "" Local : Field : EPGRPNAME : Set as : "" Local : Field : EGDW : Set as : "" Local : Field : EPD : Set as : "" Local : Field : ECS : Set as : " " Local : Field : Default : Line : 0 [Collection: APExpiry Report] Type : Stock Item Fetch : BatchName, StockItemName Filter : NonEmptyStkClBalance Compute : BatchwiseOn : $$IsBatchwiseOn AND $IsBatchwiseOn =======================================
try using Function $$FullList, it will give comma separated value, use like below $$FullList:ObjectName:$MethodName
Thanks for your reply but i am not able to see batch wise expiry please find the below sample format. i intention for this report to see all expiry item with batch wise & mdf date.
IF.......Item 1 contains 3 batches.... item 2 contains 5....and item 3 contains 7.......... so am not able to get the report to show 3+5+7 lines as per the batches....for that you need to change your collection settings...which is not my expertise.... maybe some experts can help you with that. Your collection should be on BATCHES but sorted on StockItemName.........
HI...managed to this level.... in above sample data.... Crocin has 3 op.batches, paracitamol-3 and Vicks-5.....so 11 items are appearing.... However my collection takes the VALUES of the first item i.e. "CROCIN" and its 1st batches.... hence all fields are same. Also it does not pick up the purchases during the year. Can anyone tell what is wrong with the collection??
Sir I wnt Modifi in your file but not succes so plz hlp [#Menu: Gateway of Tally] Add: Item : Expiry Report : Display : APExpiry Report [Report: ApExpiry Report] Title : "Stock Item Expiry report " Print Set : Report Title : "Stock Item Expiry report" Variable : SVFromDate,SVToDate Set : SVFromDate : $$MonthStart:##SVCurrentDate Set : SVToDate : $$MonthEnd:##SVCurrentDate Form :APExpiry Report [Form: APExpiry Report] Part : APEP MRTL,APEXP ReportTL,APExpiry Report Bottom Part : APExpiry_ReportBTM Background : Released Pale Yellow Buttons : ExplodeFlag,PrintButton, ExportButton, UploadButton, MailButton Button : ChangePeriod BottomButton : InvReports, AcctReports, Report Operations, DYBKConfigure, FilterButton, ValueButton Bottom Toolbar Buttons : BottomToolBarBtn1, BottomToolBarBtn8, BottomToolBarBtn9, BottomToolBarBtn10, BottomToolBarBtn11, BottomToolBarBtn12 Space Top : if $$InPrintMode then ##SVSpaceTop else 0.05 inches Space Bottom : if $$InPrintMode then 0.5 else 0 inches Space Left : if $$InPrintMode AND $$InPixelMode then 0.5 else 0 inches Space Right : if $$InPrintMode AND $$InPixelMode then 0.25 else 0 inches Option : Small Size Form : NOT $$InPrintMode [Part: APEP MRTL] Line : APEP MRTL Invisible : $$InPrintMode [Line: APEP MRTL] Field : Name Field Right Field: Simple Field Local : Field: Name Field :Set as: "Stock Expiry Report" Local : Field: Simple Field :Set as: $$String:##SVFromDate + " to " + $$String:##SVToDate Local : Field: Simple Field :Width : 30 % Page Local : Field: Simple Field :Align: Right [Part: APEXP ReportTL] Line : APEXP ReportTL Border : Column Titles [Line: APEXP ReportTL] Field : EPSL1,EPITEMNAME,EPGRPNAME,ECT,EUT,EGDW,EPBATCHNAME,EPMFDDT,EPEXPIRYDT,EMRP,EPD,ECS Local : Field : Default : Type : String Local : Field : Default : Align : Center Local : Field : EPSL1 : Set As : "SL" Local : Field : EPITEMNAME : Set as : "Item Name" Local : Field : EPGRPNAME : Set as : "Group Name" Local : Field : ECT : Set as : "Category" Local : Field : EUT : Set as : "Units" Local : Field : EGDW : Set as : "Godown Name" Local : Field : EPBATCHNAME : Set as : "Batch Name" Local : Field : EPMFDDT : Set as : "Mfd. Date" Local : Field : EPMFDDT : Color : Blue Local : Field : EPMFDDT : Style : Normal Local : Field : EPEXPIRYDT : Set as : "Expiry Date" + $$Newline + "(count from " + $$String:##SVToDate + ")" Local : Field : EPEXPIRYDT : Color : Blue Local : Field : EPEXPIRYDT : Style : Normal Local : Field : EMRP : Set as : "M.R.P." Local : Field : EMRP : Color : Red Local : Field : EPD : Set as : "Days Till Expiry" Local : Field : ECS : Set as : "Stock" Local : Field : ECS : Color : Blue Local : Field : Default : Line : 0 Border : Flush Totals [Part: APExpiry Report] ; Object : Batch ;Allocations Line : APExpiry Report Repeat : APExpiry Report : APExpiry Report Scroll : Vertical Common Border : Yes Float : Yes Total : yes [Line: APExpiry Report] Field : EPSL1,EPITEMNAME,EPGRPNAME,ECT,EUT,EGDW,EPBATCHNAME,EPMFDDT,EPEXPIRYDT,EMRP,EPD,ECS ;Local : Field : Default : Style : Small Space Top : .20 [ Field : EPSL1] ;;==============Sr No Use : Name Field Set as :$$Line Width : 5 Border : Thin Left Align: Center [ Field : EPITEMNAME] ;;===========Item Name Use: Name Field Set as:$StockItemName Width : 20 Border : Thin Left Align : Left ;Invisible : $$IsEmpty:$EXPIRYPERIOD [ Field : EPGRPNAME];;;;;;;;;;;;=======Group Name Border: thin left Use : Name Field Set as :$Parent Width : 13 Align : Left Border : Thin Left [ Field : ECT];;;;;;;;;;;;=======Category Name Use : Name Field Set as :$SimpleUnits Width : 12 Align : Left Border : Thin Left [ Field : EUT];;;;;;;;;;;;=======Units Name Use : Name Field Set as :$BaseUnits Width : 10 Align : Center Border : Thin Left [ Field : EPBATCHNAME];;;;============Batch Name Use: Name Field Set As : $$CollectionField:$BatchName:1:BatchAllocations Width : 15 Border : Thin Left Align : Left [ Field : EGDW] ;;;;;;;;;;;;=======Godown Name Use : Name Field Set As :$$CollectionField:$GodownName:1:BatchAllocations Width : 18 Border : Thin Left Align : Center [ Field :EPMFDDT];;==============MfG Date Use : Uni Date Field;; type : string Set As: $$CollectionField:$MfdOn:1:BatchAllocations Align : Center Border : Thin Left Width: 12 [ Field : EPEXPIRYDT] ;;===============Expiry Date Use :Uni Date Field Set As : $$CollectionField:$EXPIRYPERIOD:1:BatchAllocations Align : Center Width : 12 Border : Thin Left ;Inactive : Not #DSPShowMfd [ Field : EMRP] ;;;;;;;;;;;;=======M.R.P. Name Use : Amount Field Set As :$ClosingValue Width : 8 Border : Thin Left Align : Center [ Field :EPD] ;;========= Ramian Days Use : ShortDateTitleField Set as : $$SPrintf@DaysFormat#EPEXPIRYDT - #SVToDate) Border : Thin left Align: Center Width: 14 [ Field :ECS] ;;========= Closing Stock Use : Qty Primary field Setas : $ClosingBalance Border : Thin Left Align: Center Width : 9 ;Space Right:0 [Part: APExpiry_ReportBTM] Line : APExpiry_ReportBTM Border : Totals [Line:APExpiry_ReportBTM] Field : EPSL1,EPITEMNAME,EPGRPNAME,ECT,EUT,EGDW,EPBATCHNAME,EPMFDDT,EPEXPIRYDT,EMRP,EPD,ECS Local : Field : Default : Type : String Local : Field : Default : Align : Center Local : Field : EPSL1 : Set As : "" Local : Field : EPITEMNAME : Set as : "" Local : Field : EPGRPNAME : Set as : "" Local : Field : ECT : Set as : " " Local : Field : EUT : Set as : " " Local : Field : EGDW : Set as : "" Local : Field : EPBATCHNAME : Set as : "Total" Local : Field : EPBATCHNAME : Align : Centre Local : Field : EPBATCHNAME : Color : Blue Local : Field : EPMFDDT : Set as : "" Local : Field : EPEXPIRYDT : Set as : "" Local : Field : EMRP : Set as : $$Total:EMRP Local : Field : EPD : Set as : "" Local : Field : ECS : Set as : $$Total:ECS Local : Field : Default : Line : 0 [Collection: APExpiry Report] Type : Stock Item Fetch : VCHBatchName, StockItemName Filter : NonEmptyStkClBalance Compute : BatchwiseOn : $$IsBatchwiseOn AND $IsBatchwiseOn =======================================
What u trying to put in Category column?? Simple Units??? What exactly you need in this column? Do not understand your need for total of MRP column?? as there is no need for any RATES to be totaled? You are using $ClosingBalance in ECS field...that would show total closing balance and not stockvalue of that BATCHName..... Try making a purchase entry against a stock item with a different Batch and see if you are getting the desired results or not?? You are using $ClosingValue in EMRP field. That would give you closing value and not MRP. However your screenshot shows MRP.......a contradiction? For Same reason as above....why you need Total of Qty field at bottom.... when your total Qty is already there in the column??
Dear All, Please try through explode, it will come definitely... But i need one clarification .. if i need only finished goods what code should i add
How To Sort ExpiryPeriod in Batch Collection... This Code Is Not working by Sort Date... Plz Anybody Give me Solution.. [#Collection: Active Batches VchExtract] Title : $$LocaleString:"List of Active Batches 1111" ; SubTitle : $$LocaleString:"Name", @@ExpirySubTitle, $$LocaleString:"Balance" ; ; Parm Var : pvStockItemName : String : $StockItemName ; Source Collection : Batches VchSrc ; Fetch : Name, Parent, ExpiryPeriod, ActualQty, ClosingBalance, GodownName, ClosingAsondate, MfdOn ; Format : $Name, 10 ; Format : $ExpiryPeriod, 8 : Month Ending ; Format : $ClosingAsondate,8 : "Base" ; Filter : BelongsToSVGodown ; Client Only : Yes ; Keep Source : .... Add : Sort : @@DateIncr : $ExpiryPeriod
This ExpiryPeriod is sstored as STRING..... Also in a collection the sort will be on the first column only. If you want to really sort, then re-arrange the column so expiryperiod is first and then convert the string into yyyymmdd format, then it will sort.
Changed but Not Correct.... [*Collection: Active Batches VchExtract] Title : $$LocaleString:"List of Active Batches 1111" SubTitle : @@ExpirySubTitle,"mY EXP",$$LocaleString:"Name", $$LocaleString:"Balance" Parm Var : pvStockItemName : String : $StockItemName Source Collection : Batches VchSrc Fetch : Name, Parent, ExpiryPeriod, ActualQty, ClosingBalance, GodownName, ClosingAsondate, MfdOn Format : @@NewVCHEDIInvDateNew, 10 Format : $ExpiryPeriod, 8 : Month Ending Format : $Name, 10 Format : $ClosingAsondate,8 : "Base" Filter : BelongsToSVGodown Client Only : Yes Keep Source : .... Add : Compute : D555Remain $$Date:$ExpiryPeriod) Add : Sort : @@DateIncr : -$$Date@NewVCHEDIInvDateNew [System : Formula] NewVCHEDIInvDateNew : If $$IsEmpty:$D555Remain Then "" Else $$String@NewVCHEDIInvDateFrml NewVCHEDIInvDateFrml : $$SPrintf@NewEDIDateStringFrml@NewVCHEDIYearOfDate@NewVCHEDIMonthOfDateAct@NewVCHEDIDayOfDateAct NewEDIDateStringFrml : $$LocaleString:"%s%s%s" NewVCHEDIDayOfDate : $$DayOfDate:$D555Remain NewVCHEDIDayOfDateAct : If $$Number@NewVCHEDIDayOfDate<$$Number:"10" Then "0"+$$String@NewVCHEDIDayOfDate Else @@NewVCHEDIDayOfDate NewVCHEDIMonthOfDate : $$MonthOfDate:$D555Remain NewVCHEDIMonthOfDateAct: If $$Number@NewVCHEDIMonthOfDate<$$Number:"10" Then "0"+$$String@NewVCHEDIMonthOfDate Else @@NewVCHEDIMonthOfDate NewVCHEDIYearOfDate: $$YearOfDate:$D555Remain ;;
Can anyone help me adding a filter button for Mfg.dt and Expiry dt. To and From Date for these on this report. Thanks in advance