"No Data available or \n FilePath may be incorrect" error shows in below code plz help

Discussion in 'Tally Developer' started by Kishan Raval, Jan 24, 2015.

    
  1. Kishan Raval

    Kishan Raval New Member


    ;; 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
     


  2. mpal2311

    mpal2311 Active Member


    Please also upload the code for Price List Config Settings.TDL

    [Include: Price List Config Settings.TDL]
     


  3. Kishan Raval

    Kishan Raval New Member


    uploaded the file, kindly help me to resolve issues
     

    Attached Files:



Share This Page