While importing data from Excel to Tally , Quantity and Rate Fields do not import. Why?

Discussion in 'Knowledge Base' started by admin, Sep 1, 2013.

         
  1. admin

    admin Administrator Staff Member


    Query -
    While importing data from Excel to Tally using User Defined Function, Quantity and Rate Fields do not import resulting in an error, Action Execution Failed.
    Solution -
    In an User Defined Function, there can be 2 Object Contexts at a time viz., Source and Target such that Source Context Method value can be set to Target Context Methods.
    While importing data from Excel to Tally, data collected from Excel acts as a Source and Tally DB Object i.e., Stock Item, Ledger, Voucher, etc. where data is set acts as a Target Object. While setting value to Methods bearing data types, Quantity and Rate, Unit information set within the Stock Item is needed hence the Target context i.e., the Tally Inventory context is needed.
    There are 2 ways of switching the context while setting value from Source Context:
    1) Using Function TGTObject - We can store the Quantity and Rate in a temporary Numeric Variable. Subsequently, using Function TGTObject, switch the context to Target Object Context and parameter can be the specified variable.
    Example:
    [Function: Import from Excel]
    00 : SET : QtyVar : $ExcelQtyNum
    10 : SET : RateVar : $ExcelRateNum
    20 : SET VALUE: BilledQty : $$TGTObject:$$AsQty:##QtyVar
    30 : SET VALUE: Rate : $$TGTObject:$$AsRate:##RateVar

    2) Using Start Block & End Block - We can also use Block Statements with Start Block and End Block which retains the object contexts which was available prior to entering this Block later. Similar to the above, we can retain the Quantity and Rate in a temporary Numeric Variable. Subsequently, within Start Block, change the object context and set value to the required methods.

    Example:
    [Function: Import from Excel]
    00 : SET : QtyVar : $ExcelQtyNum
    10 : SET : RateVar : $ExcelRateNum

    20 : START BLOCK
    30 : SET OBJECT
    40 : SET VALUE : BilledQty : $$AsQty:##QtyVar
    50 : SET VALUE : Rate : $$AsRate:##RateVar
    60 : END BLOCK

    Subsequent to the Block Statements, the Object context would be restored.



     




  2. SIr, Thanks
    now it imports qty and rate also but after entering voucher it shows -1 qty and stock is going negative
    why ? there is no any negative qty and rate , then why shows -1 ?

    what is mistake in below code ( function )?
    can you please guide me ?


    [#Menu: Gateway of Tally]

    Item : Purchase Import: Execute : EXCLFilePathwssale

    [Report: EXCLFilePathwssale]

    [Form: EXCLFilePathwssale]

    Part : EXCLFilePathwssale
    Back ground : @@SV_RELEASEDBLUE
    On : Form Accept : Yes : Form Accept
    On : Form Accept : Yes : Call : ImportSVCH

    [Part: EXCLFilePathwssale]

    Line : EXCLFilePathline1, EXCLFilePathline2 ;, cashgl

    [Line: EXCLFilePathline1]

    Field : Long Prompt,EXCLFilePath
    Local : Field : Long Prompt : Set As : "Enter File Name (eg:D:\File1.xlsx) :"

    [Line: EXCLFilePathline2]
    Field:long prompt,vchtypeselect
    Local:Field:long prompt:Set as:"Select Voucher type Name (Purchase)"

    [Field: EXCLFilePath]
    Use : Name Field
    Modifies : EXCLFilePath
    Full width : Yes
    Max : 100
    [Field:vchtypeselect]
    Use:name field
    Table : ExtractVoucher Type
    Show Table : On Blank
    Modifies:varvchtyp
    Full Width:Yes
    max: 100
    [Variable: EXCLFilePath]
    Type : String
    [Variable:varvchtyp]
    Type:String
    [system : Variable]
    EXCLFilePath :""
    varvchtyp:""

    ;;-----------------------------------------------
    [Function: ImportSVCH]
    Variable : Counter : Number : 2

    1000 : OPEN FILE : ##EXCLFilePath : Excel : Read
    1010 : WHILE : NOT $$IsEmpty:($$FileReadCell:##Counter:1)
    1011: Set file Log On
    1012: Log:$vouchernumber

    40 : NEW OBJECT : Voucher
    30 : SET : SVViewName : $$SysName:InvVchView
    60 : SET VALUE : VoucherTypeName : ##varvchtyp
    50 : SET VALUE : Date : $$Date:(($$StringPart:($$FileReadCell:##Counter:4):0:2 +"/"+ $$StringPart:($$FileReadCell:##Counter:4):2:2 + "/"+$$StringPart:($$FileReadCell:##Counter:4):4:4))
    90 : SET VALUE : VoucherNumber :$$FileReadCell:##Counter:13 + "/"
    100 : SET VALUE : PartyLedgerName : $$String:mad:@partynm
    101 : SET VALUE : PartyName : $$String:mad:@partynm
    110 : SET VALUE : IsInvoice : Yes
    111 : Set Value: amount:$$AsAmount:$$FileReadCell:##Counter:7
    112 : Set Value: REFERENCE :$$FileReadCell:##Counter:5
    113 : Set Value: ReferenceDate:$$Date:$$FileReadCell:##Counter:4
    114 : Set Value: CSTFORMISSUETYPE:"c form"

    130 : INSERT COLLECTION OBJECT : LEDGERENTRIES
    140 : SET VALUE : LedgerName : $$String:mad:@partynm
    150 : SET VALUE : IsDeemedPositive : No
    151 : SET VALUE : IsPartyLedger : Yes
    160 : SET VALUE : Amount : $$AsAmount:$$FileReadCell:##Counter:7
    170 : SET TARGET : ..

    130a : INSERT COLLECTION OBJECT : LEDGERENTRIES
    140a : SET VALUE : LedgerName : $$String:mad:@purgl
    141a : Set Value: IsDeemedPositive :Yes
    170a : SET TARGET : ..

    180 : INSERT COLLECTION OBJECT : INVENTORYENTRIES
    190 : SET VALUE : StockItemName : $$FileReadCell:##Counter:24
    210 : SET VALUE : ActualQty : ($$TgtObject:($$AsQty:"1"))
    220 : SET VALUE : BilledQty : ($$TgtObject:($$AsQty:"1"))
    230 : SET VALUE : rate : ($$TgtObject:($$AsRate:$$FileReadCell:##Counter:7))
    240 : SET VALUE : Amount : $$AsAmount:$$FileReadCell:##Counter:7
    ;241 : Set Value: IsDeemedPositive :Yes

    250 : INSERT COLLECTION OBJECT : BATCHALLOCATIONS
    260 : SET VALUE : GodownName : "stockyard"
    270 : SET VALUE : BatchName : $$FileReadCell:##Counter:11
    280 : SET VALUE : ActualQty : $$String:1
    290 : SET VALUE : BilledQty : $$String:1
    300 : SET VALUE : BatchRate : $$AsAmount:$$FileReadCell:##Counter:7
    310 : SET VALUE : Amount : $$FileReadCell:##Counter:7
    ;311 : Set Value: IsDeemedPositive :Yes
    320 : SET TARGET : ..

    330 : INSERT COLLECTION OBJECT : ACCOUNTING ALLOCATIONS
    340 : SET VALUE : LedgerName : $$String:mad:@purgl
    360 : SET VALUE : Amount : $$AsAmount:$$FileReadCell:##Counter:7
    361 : Set Value: IsDeemedPositive : Yes
    370 : SET TARGET : ..

    1255 : Set Value : Narration:$$FileReadCell:##Counter:12
    ;1255A : Set Value:ENGNOPURCHASE:$$FileReadCell:##Counter:12
    1256a: SET VALUE : PersistedView : ##SVViewName
    1256 : Create Target
    1257 : INCREMENT : Counter
    1257b: Set File Log Off
    1257c: Log:$vouchernumber

    1258 : END WHILE
    1259 : CLOSE FILE

    410 : MSGBOX : "Status" : "Created Purchase Voucher"
    440 : RETURN



    [System:formula]
    myvchno:($$FileReadCell:##Counter:2) + $$FileReadCell:##Counter:1
    partynm :"Hyundai Motors India LTD "+$$FileReadCell:##Counter:8
    purgl:$$FileReadCell:##Counter:8 + " purchases"
    bilqty:$$AsQty:$$FileReadCell:##Counter:25
    VCHNO:$$FileReadCell:##Counter:13 +"/"+ $$String:($$FileReadCell:##Counter:2)
    /*
    [#Line:VCHNARRATION]
    ADD:Field:ENGNOPURCHASE

    [Field:ENGNOPURCHASE]
    Use:NAME FIELD
    Set as:""
    Width: 30
    Storage:ENGNOPURCHASE

    [System:UDF]
    ENGNOPURCHASE:String:9876

    [#Object:VOUCHER]
    ENGNOPURCHASE:$ENGNOPURCHASE
     



  3. Yogender

    Yogender New Member




  4. admin

    admin Administrator Staff Member


    Check carefully on <ISDEEMEDPOSTIVE> Tag.. and some times you need multiply qty with -1
     



  5. Murali.R

    Murali.R Member


    Please tell me Details of Deemed positive Yes or No
     



  6. Yogender

    Yogender New Member


    can you please share excel file and data
     



  7. Yogender

    Yogender New Member


    can you please share excel file and data
     



  8. Yogender

    Yogender New Member


    can you please share excel file and data
     



  9. Ankur Tiwari

    Ankur Tiwari Member


    Deemed positive Yes means Dr and Deemed Positive No Means Cr
     



Share This Page