Dear Experts, I am new to TDL developement in Excel to Tally Import. I have gone through many codes available in this Forum but I am not getting how to overwrite vouchers if already imported from Excel if I import same excel sheet again with some changes in that. Kindly request you to provide solution for the same. i tried to check Date & Voucher no from excel data souce with the data in Tally, then deleteing it and creating target again. But it is taking lot of time and hence tally goes on not responding. please provide any sample code if available. Thanks in advance.
1. Fetch Master ID of That Vouchers Using Collection Field by key Function later Concatenate Master Id with a String Like this [System:Formula] MasterID: $$String:"ID:" + $$String:$MasterID And In Function do it like this 10: New Object: Voucher: @@MasterID: Yes This will open voucher in alter mode And Instead of create target use alter target
Thanks a lot. I will try this. But please let me know, whether I have to set Prevent duplicate to Yes or No?
Hello Himanshu Ji, It is altering voucher but there is still one problem. Everytime I import the same sheet, it is aading all inventory and ledgerentries again and again. Only Narration, reference they are getting altered. Please guide for the same.
Do It Like This 10: Walk Collection: InventoryEntries 20: Delete Collection Object: InventoryEntries:1 30: End walk Same For Ledger Entries Also
I am posting Some Part from my code.. where i have added Insert Collection object, it is adding as new lines. Pleas check my code [Function:Mainfunction] Variable:r3invno:String Variable:vmasterid:String Variable:MSTRID:String 45 : SET VALUE : VoucherTypeName :"Sales"; ##MYStkgrp;;;"Sales" 46 : Set value : Date : $NXlsDtComp 47 : Set Value : IsInvoice:Yes 48 : Set Value : VoucherNumber : $NXlsNoComp;;;@VchNumFrml 49 : Set Value : PersistedView : ##SVViewName 50 : SET VALUE : Narration : $NXNarration 51 : Set Value : Reference : $NXlsNoComp 52 : Set Value artyledgername :$CompPartyName 53 : Set Value :Basicbuyername :$CompPartyName ;54 : SET VALUE : BasicOrderTerms : $NXNarration+", "+"Address : "+$CPadd1 Local Formula:ad1:$$StringWordEx$Conaddress1):",":1 Local Formula:ad2:$$StringWordEx$Conaddress1):",":2 Local Formula:ad3:$$StringWordEx$Conaddress1):",":3 Local Formula:ad4:$$StringWordEx$Conaddress1):",":4 Local Formula:ad5:$$StringWordEx$Conaddress1):",":5 Local Formula:ad6:$$StringWordEx$Conaddress1):",":6 54:If:Not $$Isemptyad1 55:Insert Collection Object:Address 56:Set Value:Addressad1 57:Set Target:.. 57Z:End If 55A1:If:Not $$isemptyad2 55A:Insert Collection Object:Address 56B:Set Value:Addressad2 57C:Set Target:.. 57CZ:End If 55D1:If:Not $$IsemptyAd3 55D:Insert Collection Object:Address 56E:Set Value:Addressad3 57F:Set Target:.. 57Fz:End If 55G1:If:not $$Isemptyad4 55G:Insert Collection Object:Address 56H:Set Value:Addressad4 57I:Set Target:.. 57IZ:End If 55J1:If:Not $$isemptyad5 and Not $$isemptyad6 55J:Insert Collection Object:Address 56K:Set Value:Addressad5+","+@ad6 57L:Set Target:.. 57Lz:End If Local Formula:ST:$ConState1 55M1:Set Value:STATENAMEST 55M:Set ValueLACEOFSUPPLYST 58A:Set Value:BASICBASEPARTYNAME:$CompPartyName 58B:Set Value:CONSIGNEESTATENAME:$ConState1 501:If:Not $$Isemptyad1 502:Insert Collection Object:basicBuyerAddress 503:Set Value:basicBuyerAddressad1 504:Set Target:.. 505:End If 506:If:Not $$isemptyad2 507:Insert Collection Object:basicBuyerAddress 508:Set Value:basicBuyerAddressad2 509:Set Target:.. 510:End If 511:If:Not $$IsemptyAd3 512:Insert Collection Object:basicBuyerAddress 513:Set Value:basicBuyerAddressad3 514:Set Target:.. 515:End If 516:If:not $$Isemptyad4 517:Insert Collection Object:basicBuyerAddress 518:Set Value:basicBuyerAddressad4 519:Set Target:.. 520:End If 521:If:Not $$isemptyad5 and Not $$isemptyad6 522:Insert Collection Object:basicBuyerAddress 523:Set Value:basicBuyerAddressad5+","+@ad6 524:Set Target:.. 525:End If ; 58:Insert Collection Object:basicBuyerAddress ; 59:Set Value:basicBuyerAddress:$Conaddress1 ; 61:Set Target:.. ;47D:Set:Lednm:$CompPartyName ;47E: Do If: NOT($$IsobjectExists:Ledger:##Lednm): Call: Create New Ledger 62 : INSERT COLLECTION OBJECT : LedgerEntries 63 : SET TARGET : LedgerEntries 64 : SET VALUE : LedgerName : $CompPartyName 65 : SET VALUE : IsDeemedPositive : Yes 66 : SET VALUE : Amount $$Asamount:$NXDramt) ; 53 : Set Target:.. 67:Insert Collection Object:Billallocations 68:Set Target:Bill Allocations 69:Set Value:Billtype:"New Ref" 70:Set Value:Name:$NXlsNoComp 71:Set Value:amount:-($$Asamount:$NXDramt) 72:Set Target:.. 73:Set Target:.. 74 : Set : TempDateVar : $NXlsDtComp 75 : Set : TempNumVar : $NXlsNoComp 76 : Walk Collection : SaleVchCreationVarsrcCollStock 47D : Set:STKNM:$Stockitemname 47D1: Set:GP:$GSTPER 77 : If : $VchDate = ##TempDateVar AND $VchNum=##TempNumVar 78 : INSERT COLLECTION OBJECT :Inventory entries 77A: Delete Collection Object:Inventory entries:$$Loopindex 79 : SET TARGET : Inventoryentries 80 : SET VALUE : Stockitemname : $stockitemname 81 : Set Value : GODOWNNAME :$Godown 82 : Set Value : Isdeemedpositive :No 83 : SET VALUE : actualqty :$$asqty:$BQ*-1 84 : SET VALUE : Billedqty :$$asqty:$BQ*-1 85 : Set Value : Rate :$$AsRate:$RT 86 : SET VALUE : Amount :$$AsAmount:$Amount 86A:If :$Taxtype equals "VAT" and $GSTPer equals 18 87 : Insert Collection Object : ACCOUNTINGALLOCATIONS 88 : Set Value : LEDGERNAME : "Sales Local GST 18%" 89 : Set Value : ISDEEMEDPOSITIVE : No 90 : Set Value : AMOUNT $$TgtObject:$..Amount) 91 : SET TARGET : ... 91A :End If 086:If :$Taxtype equals "CST" and $GSTPer equals 18 087 : Insert Collection Object : ACCOUNTINGALLOCATIONS 088 : Set Value : LEDGERNAME : "Sales OMS GST 18%" 089 : Set Value : ISDEEMEDPOSITIVE : No 090 : Set Value : AMOUNT $$TgtObject:$..Amount) 091 : SET TARGET : ... 091A :End If 186:If :$Taxtype equals "VAT" and $GSTPer equals 12 187 : Insert Collection Object : ACCOUNTINGALLOCATIONS 188 : Set Value : LEDGERNAME : "Sales Local GST 12%" 189 : Set Value : ISDEEMEDPOSITIVE : No 190 : Set Value : AMOUNT $$TgtObject:$..Amount) 191 : SET TARGET : ... 191A :End If 186A:If :$Taxtype equals "CST" and $GSTPer equals 12 187A : Insert Collection Object : ACCOUNTINGALLOCATIONS 188A : Set Value : LEDGERNAME : "Sales OMS GST 12%" 189A : Set Value : ISDEEMEDPOSITIVE : No 190A : Set Value : AMOUNT $$TgtObject:$..Amount) 191AB : SET TARGET : ... 191AA :End If 386:If :$Taxtype equals "VAT" and $GSTPer equals 5 387 : Insert Collection Object : ACCOUNTINGALLOCATIONS 388 : Set Value : LEDGERNAME : "Sales Local GST 5%" 389 : Set Value : ISDEEMEDPOSITIVE : No 390 : Set Value : AMOUNT $$TgtObject:$..Amount) 391 : SET TARGET : ... 391A :End If 386A:If :$Taxtype equals "CST" and $GSTPer equals 5 387A : Insert Collection Object : ACCOUNTINGALLOCATIONS 388A : Set Value : LEDGERNAME : "Sales OMS GST 5%" 389A : Set Value : ISDEEMEDPOSITIVE : No 390A : Set Value : AMOUNT $$TgtObject:$..Amount) 391AB : SET TARGET : ... 391AA :End If 486:If :$Taxtype equals "VAT" and $GSTPer equals 3 487 : Insert Collection Object : ACCOUNTINGALLOCATIONS 488 : Set Value : LEDGERNAME : "Sales Local GST 3%" 489 : Set Value : ISDEEMEDPOSITIVE : No 490 : Set Value : AMOUNT $$TgtObject:$..Amount) 491 : SET TARGET : ... 491A :End If 486A:If :$Taxtype equals "CST" and $GSTPer equals 3 487A : Insert Collection Object : ACCOUNTINGALLOCATIONS 488A : Set Value : LEDGERNAME : "Sales OMS GST 3%" 489A : Set Value : ISDEEMEDPOSITIVE : No 490A : Set Value : AMOUNT $$TgtObject:$..Amount) 491AB : SET TARGET : ... 491AA :End If 586:If :$Taxtype equals "VAT" and $GSTPer equals 0 ;Or $$IsEmpty:$GSTPER 587 : Insert Collection Object : ACCOUNTINGALLOCATIONS 588 : Set Value : LEDGERNAME : "Sales Local GST 0%" 589 : Set Value : ISDEEMEDPOSITIVE : No 590 : Set Value : AMOUNT :0;($$TgtObject:$..Amount) 591 : SET TARGET : ... 591A :End If 586A:If :$Taxtype equals "CST" and $GSTPer equals 0 ;Or $$IsEmpty:$GSTPER 587A : Insert Collection Object : ACCOUNTINGALLOCATIONS 588A : Set Value : LEDGERNAME : "Sales OMS GST 0%" 589A : Set Value : ISDEEMEDPOSITIVE : No 590A : Set Value : AMOUNT :0;($$TgtObject:$..Amount) 591AB : SET TARGET : ... 591AA :End If 92 : End If 93 : End Walk 255:If :$NXTaxtype equals "CST" and Not $$Isemptycollection:SaleVchCreationVarCollCompare18 ;and ($GSTIGSTRate:StockItem:$$Owner:$StockItemName equals $NXGSTPer) 256 : INSERT COLLECTION OBJECT : LedgerEntries 257 : SET VALUE : LedgerName : "Output IGST 18%" 258 : SET VALUE : Is Deemed Positive: No 258A: Set Value : RATEOFINVOICETAX :$$Number:18 259 : SET VALUE : Amount $$CollAmtTotal:SaleVchCreationVarCollCompare18:$NXTaxamt) 260 : SET TARGET : .. 261 : END IF 255P:If :$NXTaxtype equals "CST" and Not $$Isemptycollection:SaleVchCreationVarCollCompare12 ;and ($GSTIGSTRate:StockItem:$$Owner:$StockItemName equals $NXGSTPer) 256P : INSERT COLLECTION OBJECT : LedgerEntries 257P : SET VALUE : LedgerName : "Output IGST 12%" 258P : SET VALUE : Is Deemed Positive: No 258AP : Set Value : RATEOFINVOICETAX :$$Number:12 259P : SET VALUE : Amount $$CollAmtTotal:SaleVchCreationVarCollCompare12:$NXTaxamt) 260P : SET TARGET : .. 261P : END IF
Hmm... Read my previous post again... I didn't use loop index function... And Please post screenshots because I can't load half TDL in my Tally and Fix it... Plus I am not that Much Experienced that I can solve the code just by seeing it
Suppose I told you to create a ledger and When you are just entering the ledger name, I told you to delete it.... What will happen? If Your answer is nothing Then Good Else Do jt practically The same thing you are doing in code
Sorry that I posted only half code over here and not posted Screenshots. If I dont use Delete Collection object, then it is adding inventory entries and ledgerentries everytime. (5 times imported). If, I use delete collection object, It is showing error as under shown in calculator Panel. 74 : Set : TempDateVar : $NXlsDtComp ;;; Variable to store Voucher date 75 : Set : TempNumVar : $NXlsNoComp ;;; Variable to store Voucher No 76 : Walk Collection : SaleVchCreationVarsrcCollStock ;;; Excel Collection from which inventory details are fetched 47D : Set:STKNM:$Stockitemname 47D1: Set:GP:$GSTPER 77 : If : $VchDate = ##TempDateVar AND $VchNum=##TempNumVar ;;; Condition to match same voucher Inventory details 77AA: Walk Collection:Inventoryentries ;;; As per your suggestion 77A: Delete Collection Object:Inventory entries:1 ;;; As per your suggestion 77B1:End Walk 78 : INSERT COLLECTION OBJECT :Inventory entries ;;; To add inventory Entries 79 : SET TARGET : Inventoryentries 80 : SET VALUE : Stockitemname : $stockitemname 81 : Set Value : GODOWNNAME :$Godown 82 : Set Value : Isdeemedpositive :No 83 : SET VALUE : actualqty :$$asqty:$BQ*-1 84 : SET VALUE : Billedqty :$$asqty:$BQ*-1 85 : Set Value : Rate :$$AsRate:$RT 86 : SET VALUE : Amount :$$AsAmount:$Amount 86A:If :$Taxtype equals "VAT" and $GSTPer equals 18 ;;; to add Sales ledger for 18% GST. Similarly added differenct GST % Ledgers 87 : Insert Collection Object : ACCOUNTINGALLOCATIONS 88 : Set Value : LEDGERNAME : "Sales Local GST 18%" 89 : Set Value : ISDEEMEDPOSITIVE : No 90 : Set Value : AMOUNT $$TgtObject:$..Amount) 91 : SET TARGET : ... 91A :End If
If you have problems or apprehensions in submitting full code, you can always privately EMAIl to the helping member, rather than posting it here. In this manner you are secured also.
Lol... on the funny side..... youngsters nowadays cycle through lots of BFs/GFs You seems to be on a name collection spree......... Betcha...collection of Names .... aka TDL is in your blood.....
