;; Sri Ganeshji : Sri Balaji : Sri Pitreshwarji : Sri Durgaji : Sri Venkateshwara /* Objective(s) :- - To import Price List values of Stock Item from Excel Specific TDL Feature(s) used :- - User Defined Function - On : Form Accept Event - Use of 'ODBC' attribute in Collection Dependencies - Stock Item Names referred in Excel should be available in Tally.ERP9 Last Updation :- - Altered on 13/07/2010 */ ;; System UDF Definition [#Menu: Gateway of Tally] Add : Key Item : After : @@locIndentImport : @@TSPLFFElocPriceListImport : C : Alter : TSPL FFE Price List Import ;; Function to import Price Levels directly from Excel [Function: TSPL FFE Price List Import] ;; Definition Block Variable : TSPL FFE Price List : String Variable : SVCurrentDate : Date Variable : TSPL FFE User Choice : Logical Variable : TSPL FFE PLStkNameVar : String Variable : TSPL FFE PLQtyFromVar : String Variable : TSPL FFE PLQtyToVar : String Variable : TSPL FFE PLRateVar : String Variable : TSPL FFE PLDiscountVar: String Variable : TSPL FFE Counter : Number Variable : TSPL FFE NoStItems : Number ;; Procedural Block 001 : QUERY BOX : "Import Price List" : Yes : No 010 : SET : TSPL FFE User Choice : $$LastResult 020 : IF : ##TSPLFFEUserChoice 030 : SET : TSPL FFE Counter : 0 040 : SET : TSPL FFE NoStItems : $$NumItems:TSPLFFEPLStockItemCollSumm 050 : IF : ##TSPLFFENoStItems > 0 060 : START PROGRESS : ##TSPLFFENoStItems : "Price List Import" : @@CmpMailName : "Importing Price List..." 070 : END IF 080 : WALK COLLECTION : TSPLFFEPLStockItemCollSumm 090 : NEW OBJECT : Stock Item : $TSPLFFESItemName : YES ;; Target Object = Stock Item 100 : WALK COLLECTION : TSPLFFEPLPriceLevelCollSumm 110 : IF : $$IsEmpty:##TSPLFFEPriceList 120 : SET : TSPLFFEPriceList : $TSPLFFEPriceLevel 130 : END IF 140 : IF : $$IsEmpty:##SVCurrentDate 150 : SET : SVCurrentDate : @@TSPLFFEExcelDate 160 : END IF 170 : INSERT COLLECTION OBJECT : FullPriceList ;; Target Object = Stock Item --> Full Price List 180 : SET VALUE : Date : @@TSPLFFEExcelDate 190 : SET VALUE : PriceLevel : $TSPLFFEPriceLevel 200 : WALK COLLECTION : TSPLFFEPLQtyPriceCollSumm 210 : INSERT COLLECTION OBJECT: PriceLevelList ;; Target Object = Stock Item --> Full Price List --> Price Level List 220 : SET : TSPLFFEPLQtyFromVar : $$String:$TSPLFFEFromQty 230 : SET : TSPLFFEPLQtyToVar : $$String:$TSPLFFEToQty 240 : SET : TSPLFFEPLRateVar : $$String:$TSPLFFERate 250 : SET : TSPLFFEPLDiscountVar : $$String:$TSPLFFEDiscount + "%" 260 : SET OBJECT ;; Action SET OBJECT switches the current context to Stock Item --> ;; Full Price List --> Price Level List. This is done as Item context is ;; required to convert respective values to quantity and rate 270 : IF : NOT $$IsEmpty:##TSPLFFEPLQtyFromVar 280 : SET VALUE : Startingfrom : $$AsQty:##TSPLFFEPLQtyFromVar 290 : END IF 300 : IF : NOT $$IsEmpty:##TSPLFFEPLQtyToVar 310 : SET VALUE : EndingAt : $$AsQty:##TSPLFFEPLQtyToVar 320 : END IF 330 : SET VALUE : Rate : $$AsRate:##TSPLFFEPLRateVar 340 : SET VALUE : Discount : $$Number:##TSPLFFEPLDiscountVar 350 : SET TARGET : .. 360 : END WALK 370 : SET TARGET : .. 380 : END WALK 390 : SAVE TARGET 400 : INCREMENT : TSPLFFECounter 410 : SHOW PROGRESS : ##TSPLFFECounter 420 : END WALK 430 : IF : ##TSPLFFENoStItems > 0 440 : END PROGRESS 450 : END IF 460 : IF : ##TSPLFFENoStItems > 0 470 : MSGBOX : "Status" : "Process completed successfully!!" 490 : ELSE : 500 : MSGBOX : "Status" : "No Data available or \n FilePath may be incorrect" 510 : END IF 520 : END IF 530 : RETURN ;; Collection to gather External Data [Collection: TSPL FFE PLStockItemColl] ODBC : "Driver= " + ##TSPLFFEPLDriverInfo + "; DefaultDir= " + ##TSPLFFEPLDirPath + "; Dbq=" + ##TSPLFFEPLFilePath Filter : TSPLFFEEmptyItemFilter Switch : Case 1 : TSPLFFEPL ODBCColl NotExcel : ##TSPLFFEPLDriverInfo NOT CONTAINS "Excel" Switch : Case 1 : TSPLFFEPL ODBCColl Excel : ##TSPLFFEPLDriverInfo CONTAINS "Excel" Client Only : Yes [!Collection: TSPLFFEPLODBCCollNotExcel] SQL : "Select [" + ##TSPLFFEPLTableName + "]." + ##TSPLFFEPLItemName + + ", [" + ##TSPLFFEPLTableName + "]." + ##TSPLFFEPLApplDate + + ", [" + ##TSPLFFEPLTableName + "]." + ##TSPLFFEPLPriceLevel + + ", [" + ##TSPLFFEPLTableName + "]." + ##TSPLFFEPLFromQty + + ", [" + ##TSPLFFEPLTableName + "]." + ##TSPLFFEPLToQty + + ", [" + ##TSPLFFEPLTableName + "]." + ##TSPLFFEPLRate + + ", [" + ##TSPLFFEPLTableName + "]." + ##TSPLFFEPLDiscount + + " from [" + ##TSPLFFEPLTableName + "]" [!Collection: TSPLFFEPLODBCCollExcel] SQL : "Select [" + ##TSPLFFEPLTableName + "$].[" + ##TSPLFFEPLItemName + "]" + + ", [" + ##TSPLFFEPLTableName + "$].[" + ##TSPLFFEPLApplDate + "]" + + ", [" + ##TSPLFFEPLTableName + "$].[" + ##TSPLFFEPLPriceLevel + "]" + + ", [" + ##TSPLFFEPLTableName + "$].[" + ##TSPLFFEPLFromQty + "]" + + ", [" + ##TSPLFFEPLTableName + "$].[" + ##TSPLFFEPLToQty + "]" + + ", [" + ##TSPLFFEPLTableName + "$].[" + ##TSPLFFEPLRate + "]" + + ", [" + ##TSPLFFEPLTableName + "$].[" + ##TSPLFFEPLDiscount + "]" + + " from [" + ##TSPLFFEPLTableName + "$]" [Collection: TSPL FFE PLStockItemCollSumm] Source Collection : TSPLFFEPLStockItemColl By : TSPLFFESItemName : $_1 Keep Source : (). Client Only : Yes ;; Collection 2 - Unique Date and PriceLevel From Excel for the above Item Object context [Collection: TSPL FFE PLPriceLevelCollSumm] Source Collection : TSPLFFEPLStockItemColl By : TSPLFFEPriceLevel : $_3 By : TSPLFFEApplDate : $_2 By : TSPLFFESItemName : $_1 Keep Source : (). Filter : TSPLFFEPLforthisItem Sort : Default : $TSPLFFEApplDate Client Only : Yes ;; Collection 3 - Quantitywise Rate List for the above Item, Date and Price Level [Collection: TSPL FFE PLQtyPriceCollSumm] Source Collection : TSPLFFEPLStockItemColl By : TSPLFFESItemName : $_1 By : TSPLFFEApplDate : $_2 By : TSPLFFEPriceLevel : $_3 By : TSPLFFEFromQty : $$Round$$Number:$_4):1 By : TSPLFFEToQty : $$Round$$Number:$_5):1 By : TSPLFFERate : $$Round$$Number:$_6):0.2 By : TSPLFFEDiscount : $_7 Keep Source : (). Filter : TSPLFFEPLforthisItemandPL Sort : Default : $TSPLFFEApplDate Client Only : Yes [System: Formula] ;; Filters used for Collection 1 and 2 TSPL FFE PLforthisItem : $TSPLFFESItemName = $$ReqObject:$TSPLFFESItemName TSPL FFE PLforthisItemandPL : $TSPLFFEPriceLevel = $$ReqObject:$TSPLFFEPriceLevel AND $TSPLFFEApplDate = $$ReqObject:$TSPLFFEApplDate AND $TSPLFFESItemName = $$ReqObject:$TSPLFFESItemName ;; Formula used for converting Excel Date to Tally Date Format TSPL FFE ExcelDate : $$TSPLFFEConverttoDateFormat:$TSPLFFEApplDate TSPL FFE EmptyItemFilter : NOT $$IsEmpty:$_1 [Include: Price List Config Settings.TDL] ;; End-of-File
Please also upload the code for Price List Config Settings.TDL [Include: Price List Config Settings.TDL]