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.
[Collection: Led Coll From Access2] ODBC : "Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\1.mdb;Uid=;Pwd=;" SQL : Select * From LedgerMaster SQL Object : AccessObj2 [Object: AccessObj2] LedName : $_1 LedParent : $_2 LedOpBal : $$AsAmount:$_3 [Collection: Led Coll2] Source Collection : Led coll From Access2 Compute : LEDDATE : $_1 Compute : LEDNAME : $_2 Compute : LEDAMOUNT : $$AsCrAmt:$_3 Compute : LEDAMOUNT2 :$$AsDrAmt:$_3 Compute : IGST :$_4 Compute : CGST :$_5 Compute : SGST :$_6 compute : qty :$$Number:"2" [#Menu: Gateway of Tally] Add : Button : Led Import2 [Variable : QtyVar] Type : Number Persistent : Yes Set Always : Yes [Button: Led Import2] Key : Alt + F6 Action : Call : Ledger Import2 Title : "Import With Stock" [Function: Ledger Import2] 01 : WALK COLLECTION: Led Coll2 02 : Set:QtyVar :$$String:$qty+" Kg" 03 : If : 1=1 04 : NEW OBJECT: Voucher 05 : Set Value: Date : $$Date($$StringPart:$LEDDATE:8:2) + "-" + + ($$StringPart:$LEDDATE:5:2)+ "-" + + ($$StringPart:$LEDDATE:0:4)) 07 : Set Value:VoucherTypeName :"Purchase" 08 : Set Value: Narration :"Working" 09 : Set Value: PARTYNAME : $LEDNAME 12 : Set Value:REFERENCE:"333" 14 : Set Value: PARTYLEDGERNAME : $LEDNAME 16 : Insert Collection Object: LEDGERENTRIES 19 : Set Value: LedgerName : $LEDNAME 20 : SET VALUE : IsDeemedPositive : No 21 : Set Value:ispartyledger:Yes 24 : Set Value: Amount:$$Number:$LEDAMOUNT+$$Number:$IGST+$$Number:$SGST+$$Number:$CGST 25 : Set Target:.. 28 : Insert Collection Object: LEDGERENTRIES 34 : Set Value: LedgerName : "pur" 35 : Set Value:IsDeemedPositive:Yes 36 : Set Value:ispartyledger:No 37 : Set Value: Amount:"-98788" 40 : Insert Collection Object:INVENTORYALLOCATIONS 45 : Set Value :StockItemName:"Sheet" 47 : Set Value:IsDeemedPositive:Yes 48 : Start Block 49 : Set Object 54 : Set Value :BilledQTY :$$AsQty:##QtyVar 56 : Set Value: AMOUNT:"-98788" 58 : End Block 60 : Set Target:.. 61 : If : $$IsEmpty:$CGST and $$IsEmpty:$SGST 63 : Insert Collection Object:LEDGERENTRIES 64 : Set Value:LedgerName:"IGST" 65 : Set Value:IsDeemedPositive:Yes 67 : Set Value:IsPartyLedger:No 69 : Set Value:AMount:"-"+$$String:$IGST 70 : Save Target 71: Else 72 : Insert Collection Object:LEDGERENTRIES 74 : Set Value:LedgerName:"SGST" 76 : Set Value:IsDeemedPositive:Yes 78 : Set Value:IsPartyLedger:No 79 : Set Value:AMount:"-"+$$String:$SGST 80 : Insert Collection Object:LEDGERENTRIES 81 : Set Value:LedgerName:"CGST" 82 : Set Value:IsDeemedPositive:Yes 83 : Set Value:IsPartyLedger:No 84 : Set Value:AMount:"-"+$$String:$CGST 85 : Save Target 87 : End If 90 : End If 95 : End Walk Can You Correct My Code Sir, I cant Use TgtObject.
IN EXCEL I want do round off decimal figure in sum digit ex. 100.17 ans is 100.18 ex. 100.22 ans is 100.22 ex. 100.23 ans is 100.24 ex. 100.15 ans is 100.16 SO Which formula i use for my decimal point will round off in sum figure Plz reply....
You are talking of RoundOff....whereas you are just adding 0.01 to your examples....... And why is 100.22 is still 100.22 and NOT 100.23?
I am trying to update the opening balance and item rate in stock item. Tried both the methods i.e. by using TGTObject and Start & End Block but in both the cases getting Execution Failed @ 061. Please advice the necessary correction.. [Function : ItmMast Updation] Variable : RCnt : Number Variable : CCnt : Number Variable : APTotRCount : Number : 2 Variable : QtyVar : Number Variable : APFilePath : String Variable : SPShetName : String Variable : GSTItemName : String Variable : GSTItmGrp : String Variable : OpnBal : Number 001 : Set : APFilePath : #GSTItemImportFileName 005 : Set : SPShetName : #GSTItemImportSheetName 010 : Open File : ##APFilePath : Excel : Read 020 : Set Active Sheet : ##SPShetName 022 : While : Not $$IsEmpty$$FileReadCell:##APTotRCount:1) 023 : Increment : APTotRCount 024 : End While 040 : Start Progress : ##APTotRCount : ##SVCurrentCompany : "GST Items Updation" : "Please Wait..." 041 : Set : RCnt : 2 042 : While : Not $$IsEmpty$$FileReadCell:##RCnt:1) 045 : Set : GSTItmGrp : $$FileReadCell:##RCnt:2 046 : Set : QtyVar : $$FileReadCell:##RCnt:5 G01 : If : $$IsEmpty:$Name:StockGroup:##GSTItmGrp G02 : Call: GSTItem Group G03 : End If U01 : If : $$IsEmpty:$Name:Unit$$FileReadCell:##RCnt:3) U02 : Call: GSTItem Units U03 : End If 047 : Set : GSTItemName : $$FileReadCell:##RCnt:1 048 : Set: QtyVar : $Rcnt:5 051 : NEW OBJECT : Stock Item : ##GSTItemName : Yes 053 : Set Value : NAME : $$FileReadCell:##RCnt:1 M45 : Log : ##GSTItemName 055 : Set Value : Parent : $$FileReadCell:##RCnt:2 057 : Set Value : Base Units : $$FileReadCell:##RCnt:3 058 : Set Value : mailingname : $$FileReadCell:##RCnt:4 059 : Start Block 060 : Set Object 061 : SET VALUE : OpeningBalance : $AsQty:##QtyVar 062 : End Block ;;060 : Set Value : OpeningRate : $$FileReadCell:##RCnt:6 151 : Set Target : .. 160 : Alter Target 166 : Increment : RCnt 168 : Show Progress : ##RCnt 170 : End While 162 : Msg Box : "Message" : "GST Stock Item Master Imported Successfully !!!"