Dear All, I have created a report to view All the pricelist. I need your support to update the following: 1) Currently those stock items are coming for which price list has been set. I want All the stock item name atleast once even if there are no pricelist entered for that. 2) If there are two rate on a different effective date for a particular stock item in a particular pricelevel, I need only latest rate for that pricelevel of each particular stock item. Any suggestion is highly appreciated. Code: [#Menu: Gateway of Tally] Add : Key Item : Before : @@locquit : Price List : T : Display : PriceList [Report: PriceList] Form : PriceList [Form: PriceList] Part : PriceList Width : 100% Screen Height : 100% Screen Button : RefreshTDL [Part: PriceList] Line: PriceListTitle, PriceListDet Repeat : PriceListDet : AJItems1 Height : 100% Screen Width : 100% Screen Scroll : Vertical [Line: PriceListTitle] Use : PriceListDet Local : Field : Default : Border : Thin Column Titles Local : Field : Field1 : Info : "Date" Local : Field : Field2 : Info : "Item Name" Local : Field : Field3 : Info : "Item Group" Local : Field : Field4 : Info : "Price List Name" Local : Field : Field5 : Info : "Rate" Local : Field : Field6 : Info : "Discount" Local : Field : Field7 : Info : "Qty From" Local : Field : Field8 : Info : "Qty To" [Line: PriceListDet] Field : Field1, Field4, Field3, Field2,Field7, Field8, Field5, Field6 Local : Field : Default : Border : AJ [Field: Field1] Use : Uni Date Field Set as : $AJDate Border : ThinBox [Field : Field2] Use : Name Field Set as : $AJItemName Border : ThinBox [Field : Field3] Use : Name Field Set as : $AjItemGroup Border : ThinBox [Field : Field4] Use : Name Field Set as : $AjPriceLevel Border : ThinBox [Field : Field5] Use : Short Name Field Width : 10 Set as : $$Round:@GrossRate:1 GrossRate : $AJRate * 1.12 Border : ThinBox [Field : Field6] Use : Name Field Set as : $AJDisc Border : ThinBox Width : 10 [Field : Field7] Use : Name Field Set as : $AJQtyFrom Border : ThinBox Width : 10 [Field : Field8] Use : Name Field Set as : $AJQtyTo Border : ThinBox Width : 10 [Collection: AJItems] Type : Stock Item [Collection : AJPriceLevel] Walk : FullPriceList, PriceLevelList [Collection: AJItems1] Source Collection : AJItems Walk : FullPriceList, PriceLevelList Compute : AJItemName : $...Name Compute : AjItemGroup : $...Parent Compute : AJDate : $..Date Compute : AjPriceLevel : $..PriceLevel Compute : AJQtyFrom : $.StartingFrom Compute : AJQtyTo : $.Endingat Compute : AJRate : $.Rate Compute : AJDisc : $.Discount Sort : @@Default : $AJDate, $AJPriceLevel Is ODBC Table : Yes [Border : AJ] Top : Thin Right : Thin
Your Collection is of Pricelist -- so naturally when a StockItem does not have a defined one -- it will not show. You need to use the Collection of StockItem and then call all the PriceList details within that collection.
Thank you @Amit Kamdar Sir for your prompt response. If I call Details from StockItem Collection, I am getting only the first PriceList information. In this case, how to get the information of all the sub collection object... I dont know.. Please guide.
@Amit Kamdar Sir, As per your suggestion, I tried StockItem Collection as my starting point. But There are some unwanted lines as screenshot attached. Please suggest, if I can remove those lines. Further, how to apply filter to show only latest price level for a particular stock item & PriceList Combination. Code: [#Menu: Gateway of Tally] Add : Key Item : Before : @@locquit : Price List : T : Display : PriceList [Report: PriceList] Form : PriceList [Form: PriceList] Part : PriceList Width : 100% Screen Height : 100% Screen Button : RefreshTDL [Part: PriceList] Line: PriceListTitle, PriceListDet Repeat : PriceListDet : AJItems Height : 100% Screen Width : 100% Screen Scroll : Vertical [Line: PriceListTitle] Use : PriceListDet Local : Field : Default : Border : Thin Column Titles Local : Field : Field1 : Info : "Date" Local : Field : Field2 : Info : "Item Name" Local : Field : Field3 : Info : "Item Group" Local : Field : Field4 : Info : "Price List Name" Local : Field : Field5 : Info : "Rate" Local : Field : Field6 : Info : "Discount" Local : Field : Field7 : Info : "Qty From" Local : Field : Field8 : Info : "Qty To" [Line: PriceListDet] Field : Field1, Field4, Field3, Field2,Field7, Field8, Field5, Field6 Explode : PriceListDet1 Local : Field : Default : Border : AJ [Part: PriceListDet1] Object : StockItem Line : PriceListDet1 Repeat : PriceListDet1 : FullPriceList [Line: PriceListDet1] Field : Field1, Field4, Field3, Field2,Field7, Field8, Field5, Field6 Explode : PriceListDet2 [Part : PriceListDet2] Line : PriceListDet2 Repeat : PriceListDet2 : PriceLevelList [Line : PriceListDet2] Field : Field1, Field4, Field3, Field2,Field7, Field8, Field5, Field6 [Field: Field1] Use : Uni Date Field Set as : $Date Border : ThinBox [Field : Field2] Use : Name Field Set as : $Name Border : ThinBox [Field : Field3] Use : Name Field Set as : $Parent Border : ThinBox [Field : Field4] Use : Name Field Set as : $PriceLevel Border : ThinBox [Field : Field5] Use : Short Name Field Width : 10 Set as : $$Round:@GrossRate:1 GrossRate : $Rate * 1 Border : ThinBox [Field : Field6] Use : Name Field Set as : $Discount Border : ThinBox Width : 10 [Field : Field7] Use : Name Field Set as : $StartingFrom Border : ThinBox Width : 10 [Field : Field8] Use : Name Field Set as : $Endingat Border : ThinBox Width : 10 [Collection: AJItems] Type : Stock Item [Collection : AJPriceLevel] Walk : FullPriceList, PriceLevelList [Collection: AJItems1] Source Collection : AJItems Walk : FullPriceList, PriceLevelList Compute : AJItemName : $...Name Compute : AjItemGroup : $...Parent Compute : AJDate : $..Date Compute : AjPriceLevel : $..PriceLevel Compute : AJQtyFrom : $.StartingFrom Compute : AJQtyTo : $.Endingat Compute : AJRate : $.Rate Compute : AJDisc : $.Discount Sort : @@Default : $AJDate, $AJPriceLevel Is ODBC Table : Yes [Border : AJ] Top : Thin Right : Thin
Thank you @parthiban.M for your reply. However, it is not working exactly as i want. I want to remove StockItem Line for which pricelevel exist because for that i have kept pricelist line & Would like to keep stockitems line for which price level doesn't exist. Please help. Also, suggest how to keep only latest pricelevel.
Dear Sir, With your guidance & Suggestion, I have solved my first problem. Now, only the second problem is pending. How to show only Last Date PriceList Details of each pricelevel of every stock items and remove all the historical pricelist details. Code: [#Menu: Gateway of Tally] Add : Key Item : Before : @@locquit : Price List : T : Display : PriceList [Report: PriceList] Form : PriceList [Form: PriceList] Part : PriceList Width : 100% Screen Height : 100% Screen Button : RefreshTDL [Part: PriceList] Line : PriceListTitle, PriceListDet Repeat : PriceListDet : AJItems1 Height : 100% Screen Width : 100% Screen Scroll : Vertical [Line: PriceListTitle] Use : PriceListDet Local : Field : Default : Border : Thin Column Titles Local : Field : Field1 : Info : "Date" Local : Field : Field2 : Info : "Item Name" Local : Field : Field3 : Info : "Item Group" Local : Field : Field4 : Info : "Price List Name" Local : Field : Field5 : Info : "Rate" Local : Field : Field6 : Info : "Discount" Local : Field : Field7 : Info : "Qty From" Local : Field : Field8 : Info : "Qty To" [Line: PriceListDet] Field : Field2,Field3, Field1, Field4, Field7, Field8, Field5, Field6 Local : Field : Default : Border : AJ [Field: Field1] Use : Uni Date Field Set as : $AJDate [Field : Field2] Use : Name Field Set as : $AJItemName [Field : Field3] Use : Name Field Set as : $AjItemGroup [Field : Field4] Use : Name Field Set as : $AjPriceLevel [Field : Field5] Use : Short Name Field Width : 10 Set as : $$Round:@GrossRate:1 GrossRate : $AJRate * 1 [Field : Field6] Use : Name Field Set as : $AJDisc Width : 10 [Field : Field7] Use : Name Field Set as : $AJQtyFrom Width : 10 [Field : Field8] Use : Name Field Set as : $AJQtyTo Width : 10 [Collection: AJStockItems] Type : StockItem [Collection: AJStockItemsWithoutPriceLevel] Type : StockItem Filter : Amit Compute : AJItemName : $.Name Compute : AjItemGroup : $.Parent [System: Formula] Amit : $$IsEmpty:$FullPriceList.PriceLevel [Collection : AJPriceLevel] Source Collection : AJStockItems Walk : FullPriceList, PriceLevelList Compute : AJItemName : $...Name Compute : AjItemGroup : $...Parent Compute : AJDate : $..Date Compute : AjPriceLevel : $..PriceLevel Compute : AJQtyFrom : $.StartingFrom Compute : AJQtyTo : $.Endingat Compute : AJRate : $.Rate Compute : AJDisc : $.Discount [Collection: AJItems1] Collection : AJPriceLevel, AJStockItemsWithoutPriceLevel Fetch : * Sort : @@Default : $AJItemName,$AJDate, $AJPriceLevel Is ODBC Table : Yes [Border : AJ] Top : Thin Right : Thin
Thank you Rohit for your suggestions. I have incorporated the Item Filter Button. Can you suggest how to select all item from the filter report as now i can view only 1 item at once. Your response is highly appreciated. Code: [#Menu: Gateway of Tally] Add : Key Item : Before : @@locquit : Price List : T : Display Collection : AJPriceListItem ;;Report to Get Item Name for Main Report [Collection: AJPriceListItem] Use : AJStockItemsWithPriceLevel Variable : AJItem Report : PriceList Trigger : AJSelectItem Fetch : Name [Report : AJSelectItem] ;From Trigger Attributes Use : Modify Variables Title : $$LocaleString:"Select Stock Item" Local : Form: Modify Variables : Full Height : Yes Local : Part : Modify Variables : Lines : Select Item Local : Field: MV Title : Info : $$LocaleString:"Select Item" [Line: Select Item] Fields : Select Item Title, Select Item Info [Field: Select Item Title] Use : Medium Prompt Info : "Item Name" [Field: Select Item Info] Use : Name Field Table : AJStockItemsWithPriceLevel Show Table : Always Modifies : AJItem [Variable: AJItem] Type : String ;;Main Report [Report: PriceList] Form : PriceList Variable : AJItem [Form: PriceList] Part : PriceList Width : 100% Screen Height : 100% Screen Button : RefreshTDL Button : AJModifyItem [Button : AJModifyItem] Action : Modify Variables : AJSelectItem Key : F4 Title : "Stock Item" [Part: PriceList] Line : PriceListTitle, PriceListDet Repeat : PriceListDet : AJItems1 Height : 100% Screen Width : 100% Screen Scroll : Vertical Common Border : Yes [Line: PriceListTitle] Use : PriceListDet Local : Field : Default : Border : Thin Column Titles Local : Field : Field1 : Info : "Date" Local : Field : Field2 : Info : "Item Name" Local : Field : Field3 : Info : "Item Group" Local : Field : Field4 : Info : "Price List Name" Local : Field : Field5 : Info : "Rate" Local : Field : Field6 : Info : "Discount" Local : Field : Field7 : Info : "Qty From" Local : Field : Field8 : Info : "Qty To" [Line: PriceListDet] Field : Field2,Field3, Field1, Field4, Field7, Field8, Field5, Field6 Local : Field : Default : Border : AJ [Field: Field1] Use : Uni Date Field Set as : $AJDate [Field : Field2] Use : Name Field Set as : $AJItemName [Field : Field3] Use : Name Field Set as : $AjItemGroup [Field : Field4] Use : Name Field Set as : $AjPriceLevel [Field : Field5] Use : Short Name Field Width : 10 Set as : $$Round:@GrossRate:1 GrossRate : $AJRate * 1 [Field : Field6] Use : Name Field Set as : $AJDisc Width : 10 [Field : Field7] Use : Name Field Set as : $AJQtyFrom Width : 10 [Field : Field8] Use : Name Field Set as : $AJQtyTo Width : 10 [Collection: AJStockItems] Type : StockItem Filter : SelectedItem [Collection: AJStockItemsv] Type : StockItem Fetch : $Name [Collection: AJStockItemsWithPriceLevel] Use : AliasCollection Type : StockItem Filter : WithPriceList Fetch : $Name Title : Stock Item [System: Formula] WithPriceList : Not $$IsEmpty:$FullPriceList.PriceLevel SelectedItem : $Name = ##AJItem [Collection : AJPriceLevel] Source Collection : AJStockItems Walk : FullPriceList, PriceLevelList Compute : AJItemName : $...Name Compute : AjItemGroup : $...Parent Compute : AJDate : $..Date Compute : AjPriceLevel : $..PriceLevel Compute : AJQtyFrom : $.StartingFrom Compute : AJQtyTo : $.Endingat Compute : AJRate : $.Rate Compute : AJDisc : $.Discount [Collection: AJItems1] Collection : AJPriceLevel;, AJStockItemsWithoutPriceLevel Fetch : * Sort : @@Default : $AJItemName, $AJPriceLevel,$AJDate Is ODBC Table : Yes [Border : AJ] Bottom : Thin Right : Thick
[Field: Select Item Info] Use : Name Field Table : AJStockItemsWithPriceLevel, All Items Show Table : Always Modifies : AJItem
Thank you Shivam for prompt response. I have modified the field table as you suggested. However, When I selecte All items, Then it opens Blank report without any data. How to modify filter condition to incorporate the same... Please help.
[System: Formula] WithPriceList : Not $$IsEmpty:$FullPriceList.PriceLevel SelectedItem : If $$IsSysName Then Yes Else $Name = ##AJItem Try This in Formula
Code: ;; Amit try.. [Collection: AJStockItemsWithPriceLevel] Use : AliasCollection Type : StockItem ; Filter : WithPriceList Fetch : $Name Title : Stock Item [System: Formula] SelectedItem : If $$IsSysName:$StockItem Then Yes Else $Name = ##AJItem
Thank you everyone for your help. Now, I have completed my pricelist report with only exception that at the time of opening the report if I select "All Item" then it's not opening any report but I am able to select "All Item" by pressing Filter Button "F4" within the report. Although it's not about too much, just would like to know if It can be solved by any means. Code: [#Menu: Gateway of Tally] Add : Key Item : Before : @@locquit : Price List : T : Display Collection : AJPriceListItem ;;Report to Get Item Name for Main Report [Collection: AJPriceListItem] Use : AJStockItemsWithPriceLevel Variable : AJItem Report : PriceList Trigger : AJSelectItem Fetch : Name [Report : AJSelectItem] ;From Trigger Attributes Use : Modify Variables Title : $$LocaleString:"Select Stock Item" Local : Form: Modify Variables : Full Height : Yes Local : Part : Modify Variables : Lines : Select Item Local : Field: MV Title : Info : $$LocaleString:"Select Item" [Line: Select Item] Fields : Select Item Title, Select Item Info [Field: Select Item Title] Use : Medium Prompt Info : "Item Name" [Field: Select Item Info] Use : Name Field Table : All Items , AJStockItemsWithPriceLevel Show Table : Always Modifies : AJItem [Variable: AJItem] Type : String ;;Main Report [Report: PriceList] Form : PriceList Variable : AJItem [Form: PriceList] Part : PriceList Width : 100% Screen Height : 100% Screen Bottom Button: RefreshTDL Button : AJModifyItem [Button : AJModifyItem] Action : Modify Variables : AJSelectItem Key : F4 Title : "Stock Item" [Part: PriceList] Line : PriceListTitle, PriceListDet Repeat : PriceListDet : AJItems1 Height : 100% Screen Width : 100% Screen Scroll : Vertical Common Border : Yes [Line: PriceListTitle] Use : PriceListDet Local : Field : Default : Border : Thin Column Titles Local : Field : Field1 : Info : "Date" Local : Field : Field2 : Info : "Item Name" Local : Field : Field3 : Info : "Item Group" Local : Field : Field4 : Info : "Price List Name" Local : Field : Field5 : Info : "Rate" Local : Field : Field6 : Info : "Discount" Local : Field : Field7 : Info : "Qty From" Local : Field : Field8 : Info : "Qty To" [Line: PriceListDet] Field : Field2,Field3, Field1, Field4, Field7, Field8, Field5, Field6 Local : Field : Default : Border : AJ [Field: Field1] Use : Uni Date Field Set as : $AJDate [Field : Field2] Use : Name Field Set as : $AJItemName [Field : Field3] Use : Name Field Set as : $AjItemGroup [Field : Field4] Use : Name Field Set as : $AjPriceLevel [Field : Field5] Use : Short Name Field Width : 10 Set as : $$Round:@GrossRate:1 GrossRate : $AJRate * 1 [Field : Field6] Use : Name Field Set as : $AJDisc Width : 10 [Field : Field7] Use : Name Field Set as : $AJQtyFrom Width : 10 [Field : Field8] Use : Name Field Set as : $AJQtyTo Width : 10 [Collection: AJStockItems] Type : StockItem Filter : SelectedItem [Collection: AJStockItemsv] Type : StockItem Fetch : $Name [Collection: AJStockItemsWithPriceLevel] Use : AliasCollection Type : StockItem Filter : WithPriceList Fetch : $Name Title : Stock Item [System: Formula] WithPriceList : Not $$IsEmpty:$FullPriceList.PriceLevel SelectedItem : if $$IsSysName:##AJItem or $$IsEmpty:##AJItem THEN YES ELSE $Name = ##AJItem [Collection : AJPriceLevel] Source Collection : AJStockItems Walk : FullPriceList, PriceLevelList Compute : AJItemName : $...Name Compute : AjItemGroup : $...Parent Compute : AJDate : $..Date Compute : AjPriceLevel : $..PriceLevel Compute : AJQtyFrom : $.StartingFrom Compute : AJQtyTo : $.Endingat Compute : AJRate : $.Rate Compute : AJDisc : $.Discount [Collection: AJItems1] Collection : AJPriceLevel;, AJStockItemsWithoutPriceLevel Fetch : * Sort : @@Default : $AJItemName, $AJPriceLevel,$AJDate Is ODBC Table : Yes [Border : AJ] Bottom : Thin Right : Thick
[Report: PriceList] Form : PriceList Variable : AJItem [Report: PriceList] Form : PriceList Variable : AJItem Set : AJItem : $$SysName:"All Items"