Payment QR Code On Invoice

 Payment QR Code On Invoice

As we know most of the customers are now making payments using UPI Payments apps like Phonepe, Google Pay, Paytm, Amazon Pay.

Using this TDL you can generate the QR code in Tally Automatically and print it on your Invoice so that your customer does not have to be at your place to scan the code, or you do not have to send a mobile number to get the money or share the QR code.

Customers can just scan the QR code Printed on the Invoice and send you the money. 

Just attached the TDL file to your tally and you will get option like below 

Set you UPI id/Bank Account Details or Mobile Number and that it.

Now Create a Sales Invoice and Print it. And you should be able to see the QR code as below.

That is now you can Print/Mail this invoice to customer and they can scan the QR code and make the payment.

Cost only 500/- Only

GST Invoice Customization with eWay Bill No. in A4 size ( Free Tally TDL Code)

As we all know that new Financial year is going to being lot of us might be thinking of changing the impression on customer's by changing the invoice format. And as eWay Bill is going to get implemented from 1st of April 2018. here is the new invoice format with eWay Bill no. printed on it.

If you liked the format please find the below TDL code of the same. NOTE :- Please take the backup before attaching any code to your live data. This code is only for educational purpose we will not be responsible for any data lost due to improper following of instructions.

To know how to attache any tdl to Tally please follow the instruction in below Link.
How to Attach any Tally.ERP9 Customization to Tally.ERP9 Software so that you can easily attach this code to your Tally.ERP9 software.


;;;;;;;;;;;;;;; Font Used in Invoice Customization;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

[Style:O9]
Font:Calibri (Body)
Height:9

[Style:O9B]
Use:O9
Bold: Yes

[Style:O11B]
Font: Adobe Garamond Pro Bold
Height:15
Bold: Yes

;;;;;;;;;;;;;;;;;;;;;;;;;;;Invoice Customization Code Starts;;;;;;;;;;;;;;;;;;;;;;
[#Part: VTYP BehaviourMain]
Option : OTS Vch Type Confirmation: @@IsOTSInv

[!Part : OTS Vch Type Confirmation]
Add : Line : After : VTYP PrintSave :OTS Confirm Vch Type
 
[Line : OTS Confirm Vch Type]
Field : Long Prompt, Logical Field
Local : Field : Long Prompt : Set as : "Print Customized Format?"
Local : Field : Long Prompt : Width : @@LongWidth
Local : Field : Logical Field : Storage : OTSCustomizedFormat

[System : Formula]
IsOTSInv : $$IsSales:$Parent
IsOTSCustomFormat : $OTSCustomizedFormat:VoucherType:$VoucherTypeName

[System : UDF]
OTSCustomizedFormat : Logical : 0001

[#Form : Sales Color]
Option: OTS Customized Invoice: @@IsOTSCustomFormat

[!Form:OTS Customized Invoice]
Delete : Print
Add : Print : OTS Custom Invoice

[Report :OTS Custom Invoice]
Use          : Printed Invoice
Delete: Form        : Printed Invoice
Form : OTS Custom Invoice

[Form:OTS Custom Invoice]
Space Top   : 0.25 inch
    Space Right : 0.25 inch
    Space Left  : 0.50 inch
    Space Bottom: 0.25 inch

Part: OTS Opening Page Break, OTS Invoice Body
Bottom Part:OTS Bottom Part

Page Break : OTS Closing Page Break, OTS Opening Page Break

[Part:OTS Closing Page Break]
Lines :OTS Closing Page Break Line

[Line: OTS Closing Page Break Line]
Fields : Simple Field
Local : Field: Simple Field : Set As : "Continued..."
Local : Field: Simple Field : FullWidth : Yes
Local : Field: Simple Field : Align : Right
Border : Full Thin Top

;;;;;;;;;;;;;;;;;;;;;;;OTS Opening Page Break;;;;;;;;;;;;;;;;;;;;;;;;;;;

[Part:OTS Opening Page Break]
Part: OTS Invoice Title, OTS Invoice Company Details, OTS Invoice Customer Details, OTS Body Coloumns Title
Vertical: Yes

[Part: OTS Invoice Title]
Line: OTS Invoice Title
Border: Thick Cover

[Line:OTS Invoice Title]
Field: Simple Field
Right Field:Name Field
Local: Field: Simple Field: Set as: "TAX INVOICE"
Local: Field: Simple Field: Style: O9B
Local: Field: Simple Field: Space Left: 45
Local: Field: Simple Field: Full Width: Yes
Local: Field: Name Field: Set as:If @@GetCopyNum = 1 Then "ORIGINAL FOR RECIPIENT" Else +
    If @@GetCopyNum = 2 Then $$LocaleString:"DUPLICATE FOR SUPPLIER" Else +
      If @@GetCopyNum = 3 Then $$LocaleString:"TRIPLICATE FOR TRANSPOTER" Else +
      If @@GetCopyNum = 4 Then $$LocaleString:"EXTRA COPY" Else $$LocaleString:"EXTRA COPY"
 
Space Top: 0.25
Space Bottom: 0.25

[Part:OTS Invoice Company Details]
Line: OTS Company Name, OTS Company Address
Border: Thick Cover

[Line: OTS Company Name]
Field: Name Field
Local: Field: Name Field: Set as: @@CmpMailName
Local: Field: Name Field: Style:O11B
Local: Field: Name Field: Full Width: Yes
Space Top: 0.25

[Line:OTS Company Address]
Field: Name Field
Local: Field: Name Field: Set as: $$FullList:CompanyAddress:$Address
Local: Field: Name Field: Width:50% Page
Local: Field: Name Field: Style:O9
Local: Field: Name Field: Line:0

[Part:OTS Invoice Customer Details]
Left Part: OTS Customer Details Left
Right Part: OTS Invoice Details
Border: Thick Cover

[Part:OTS Customer Details Left]
Line: OTS Customer Side Title,OTS Customer Name, OTS Customer Address,OTS Customer State Name, OTS Customer GST No, OTS Customer Contact
Width:50% Page

[Line:OTS Customer Side Title]
Field: Name Field
Local: Field: Name Field: Set as: "Details for Buyer (Billed & Shipped To )"
Local: Field: Name Field: Full Width: Yes
Local: Field: Name Field: Style:O9B
Space Top: 0.50
[Line:OTS Customer Name]
Field: Name Field
Local: Field: Name Field: Set as: $PartyLedgerName;@@BuyerName
Local: Field: Name Field: Full Width: Yes
Local: Field: Name Field: Stylep:O9B
Space Top:0.25

[Line:OTS Customer Address]
Field: Name Field
Local: Field: Name Field: Set as: $$FullList:BasicBuyerAddress:$BasicBuyerAddress
Local: Field: Name Field: Full Width: Yes
Local: Field: Name Field: Line: 0
Local: Field: Name Field: Style:O9

[Line:OTS Customer State Name]
Field: Simple Field
Local: Field: Simple Field: Set as: "State Name"+"  :  "+ $StateName +"  "+ "Code :"+"  "+ $$getgststatecode:@StateName
Local: Field: Simple Field: Local Formula:StateName : If NOT ($$IsEmpty:$StateName OR $$IsSysName:NotApplicable:$StateName) Then $StateName Else $LedStateName:Ledger:@PartyName
Local: Field: Simple Field: Full Width: Yes
Local: Field: Simple Field: Style: O9

[Line:OTS Customer GST No]
Field: Simple Field
Local: Field: Simple Field: Set as: "GSTIN No : " +"  "+ $PartyGSTIN +"  "+" PAN No :"+"  "+$IncomeTaxNumber:Ledger:$BasicBuyerName
Local: Field: Simple Field: Full Width: Yes
Local: Field: Simple Field: Style: O9

[Line:OTS Customer Contact]
Field: Simple Field
Local: Field: Simple Field: Set as: "Contact Details :"+"  "+ @@VchContactNo
Local: Field: Simple Field: Style: O9
Local: Field: Simple Field: Full Width: Yes

[Part:OTS Invoice Details]
Line:OTS Invoice Number Date, OTS Delivery Challanno Date, OTS eWayBillNo, OTS Disptach Doc, OTS Destination, OTS LR No, OTS Order No, OTS Credit Days
Border: Thick Left
Common Border: Yes

[Line:OTS Invoice Number Date]
Field: Simple Prompt, Simple Field, Medium Prompt, Name Field
Local: Field: Simple Prompt: Set as: "Invoice No :"
Local: Field: Simple Prompt: Style: O9
Local: Field: Simple Prompt: Width:10
Local: Field: Simple Field: Set as: $VoucherNumber
Local: Field: Simple Field: Style:O9B
Local: Field: Simple Field: Width:12
Local: Field: Medium Prompt: Set as: "Date :"
Local: Field: Medium Prompt: Width:10
Local: Field: Medium Prompt: Style: O9
Local: Field: Medium Prompt: Border: Thick Left
Local: Field: Name Field: Set as: $Date
Local: Field: Name Field: Style:O9B
Local: Field: Name Field: Width:12
Space Top:0.25
Space Bottom: 0.25
Border: Thick Bottom

[Line:OTS Delivery Challanno Date]
Use:OTS Invoice Number Date
Local: Field: Simple Prompt: Set as: "Challan No"
Local: Field: Simple Prompt: Style:O9
Local: Field: Simple Field: Set as: $BasicShipDeliveryNote
Local: Field: Simple Field: Style: O9B
Local: Field: Medium Prompt: Set as: "Date"
Local: Field: Medium Prompt: Style: O9
Local: Field: Name Field: Set as: $BasicShippingDate

[Line:OTS eWayBillNo]
Use:OTS Invoice Number Date
Local: Field: Simple Prompt: Set as: "e-Way Bill No."

Local: Field: Simple Field: Set as: If @@IsGSTewayApplicable Then @@GSTPrinteWayBillNumber Else +
If $$IsEmpty:$VATTransBillNo Then $UDFVATWayBillNo Else $VATTransBillNo

Local: Field: Medium Prompt: Set as: "Date"

Local: Field: Name Field: Set as: $Date

[Line:OTS Disptach Doc]
Use:OTS Invoice Number Date
Local: Field: Simple Prompt: Set as: "Dispatch Doc No"
Local: Field: Simple Field: Set as: $BasicShipDocumentNo
Local: Field: Medium Prompt: Set as: "Destination"
Local: Field: Name Field: Set as: $BasicFinalDestination;$BasicShippedBy

[Line:OTS Destination]
Use:OTS Invoice Number Date
Local: Field: Simple Prompt: Set as: "Through"
Local: Field: Simple Field: Set as: $BasicShippedBy
Local: Field: Medium Prompt: Set as: "Vehical No"
Local: Field: Name Field: Set as: $BasicShipVesselNo

[Line:OTS LR No]
Use:OTS Invoice Number Date
Local: Field: Simple Prompt: Set as: "LR No"
Local: Field: Simple Field: Set as: $BillofLadingNo
Local: Field: Medium Prompt: Set as: "Date"
Local: Field: Name Field: Set as: $BillofLadingDate

[Line:OTS Order No]
Use:OTS Invoice Number Date
Local: Field: Simple Prompt: Set as: "Order No"
Local: Field: Simple Field: Set as: $BasicPurchaseOrderNo
Local: Field: Medium Prompt: Set as: "Date"
Local: Field: Name Field: Set as: $BasicOrderDate

[Line:OTS Credit Days]
Field: Simple Prompt, Simple Field
Local: Field: Simple Prompt: Set as: "Credit Days"
Local: Field: Simple Prompt: Width: 10
Local: Field: Simple Field: Set as:$BasicDueDateOfPymt
Local: Field: Simple Field: Width: 10
Local: Field: Simple Prompt: Style:O9B
Local: Field: Simple Field: Style:O9B


;;;;;;;;;;;;;;;;;;;;;;;INVOICE BODY STARTS;;;;;;;;;;;;;;;;;;;;;;;;;;
[Part:OTS Body Coloumns Title]
Line: OTS Coloumns Title1, OTS Coloumns Title2
Border: Thick Cover
Common Border: Yes

[Line: OTS Coloumns Title1]
Use: OTS Invoice Body
Local: Field: Default: Type: String
Local: Field: Default: Style: O9B
Local: Field: Default: Align: Center

Local: Field: OTS SrNo: Set as: "Sr"
Local: Field: OTS Description:Set as:" Item "
Local: Field: OTS HSN Code: Set as:"HSN"
Local: Field: OTS GST Per: Set as:"GST"
Local: Field: OTS Qty: Set as:"Qty"
Local: Field: OTS Rate: Set as:"Rate"
Local: Field: OTS SGST Per: Set as:"SGST"
Local: Field: OTS SGST Amt: Set as:"SGST"
Local: Field: OTS CGST per: Set as:"CGST"
Local: Field: OTS CGST Amt: Set as:"CGST"
Local: Field: OTS IGST Per: Set as:"ISGT"
Local: Field: OTS IGST Amt: Set as:"IGST"
Local: Field: OTS GrossAmt: Set as:"Gross"

[Line:OTS Coloumns Title2]
Use: OTS Invoice Body
Local: Field: Default: Type: String
Local: Field: Default: Style: O9B
Local: Field: Default: Align: Center

Local: Field: OTS SrNo: Set as: "No"
Local: Field: OTS Description:Set as:"Description"
Local: Field: OTS HSN Code: Set as:"Code"
Local: Field: OTS GST Per: Set as:"%"
Local: Field: OTS Qty: Set as:""
Local: Field: OTS Rate: Set as:""
Local: Field: OTS SGST Per: Set as:"%"
Local: Field: OTS SGST Amt: Set as:"Amt"
Local: Field: OTS CGST per: Set as:"%"
Local: Field: OTS CGST Amt: Set as:"Amt"
Local: Field: OTS IGST Per: Set as:"%"
Local: Field: OTS IGST Amt: Set as:"Amt"
Local: Field: OTS GrossAmt: Set as:"Amount"
Border: Thick Bottom

[Part:OTS Invoice Body]
Line: OTS Invoice Body
Repeat:OTS Invoice Body: Inventory Entries
Bottom Line: OTS Coloumn Total
Border: Thick Cover
Scroll: Vertical
Float: No
Common Border: Yes

[Line: OTS Invoice Body]
Field: OTS SrNo, OTS Description
Right Field: OTS HSN Code, OTS GST Per, OTS Qty, +
OTS Rate, OTS SGST Per, OTS SGST Amt, +
OTS CGST per, OTS CGST Amt,+
OTS IGST Per, OTS IGST Amt, OTS GrossAmt

Local: Field: OTS SrNo: Width:2
Local: Field: OTS HSN Code: Width:6
Local: Field: OTS GST Per: Width: 3
Local: Field: OTS Qty: Width:7
Local: Field: OTS Rate: Width: 7
Local: Field: OTS SGST Per: Width:3
Local: Field: OTS SGST Amt: Width: 7
Local: Field: OTS CGST per: Width:3
Local: Field: OTS CGST Amt: Width:7
Local: Field: OTS IGST Per: Width:3
Local: Field: OTS IGST Amt: Width:7
Local: Field: OTS GrossAmt: Width: 7

Local: Field: OTS SrNo: Border: Thick Right
Local: Field: OTS HSN Code: Border: Thick Left
Local: Field: OTS GST Per: Border: Thick Left
Local: Field: OTS Qty: Border: Thick Left
Local: Field: OTS Rate: Border: Thick Left
Local: Field: OTS SGST Per: Border: Thick Left
Local: Field: OTS SGST Amt: Border: Thick Left
Local: Field: OTS CGST per: Border: Thick Left
Local: Field: OTS CGST Amt: Border: Thick Left
Local: Field: OTS IGST Per: Border: Thick Left
Local: Field: OTS IGST Amt: Border: Thick Left
Local: Field: OTS GrossAmt: Border: Thick Left
Space Top: 0.15

[Field:OTS SrNo]
Use: Simple Field
Set as: $$Line
Style: O9

[Field:OTS Description]
Use: Simple Field
Style: O9
Set as: if NOT $$IsSysName:$StockItemName then @@InvItemName else ""
Full Width: Yes
Line: 0

[Field:OTS HSN Code]
Use:  Simple Field
Set as:$GSTItemHSNCodeEx
Style: O9

[Field:OTS GST Per]
Use: Simple Field
Set as:If NOT $GSTIsTransLedEx Then "" Else $GSTClsfnIGSTRateEx
Style:O9

[Field:OTS Qty]
Use: Simple Field
Set as: $BilledQty
Style:O9

[Field:OTS Rate]
Use: Simple Field
Set as: $Rate
Style:O9

[Field:OTS SGST Per]
Use:  Number Field
Set as: If NOT $GSTIsTransLedEx Then "" Else $GSTClsfnIGSTRateEx /2
Style: O9
Invisible: If @@IGST > 0 then yes else no

[Field:OTS SGST Amt]
Use:Amount Field
Set as:$Amount * #OTSSGSTPer / 100
Style: O9
Invisible: If @@IGST > 0 then yes else no

[Field:OTS CGST per]
Use: Number Field
Set as: If NOT $GSTIsTransLedEx Then "" Else $GSTClsfnIGSTRateEx /2
Style: O9
Invisible: If @@IGST > 0 then yes else no

[Field:OTS CGST Amt]
Use: Amount Field
Set as:$Amount * #OTSCGSTper / 100
Style: O9
Invisible: If @@IGST > 0 then yes else no

[Field:OTS IGST Per]
Use: Number Field
Set as: If NOT $GSTIsTransLedEx Then "" Else $GSTClsfnIGSTRateEx
Style: O9
Invisible: If @@SGST > 0 then yes else no

[Field:OTS IGST Amt]
Use: Amount Field
Set as:$Amount * #OTSIGSTPer / 100
Style: O9
Invisible: If @@SGST > 0 then yes else no

[Field:OTS GrossAmt]
Use: Amount Field
Set as: $Amount
Style:O9

[Line:OTS Coloumn Total]
Use:OTS Invoice Body
Border: Thick Top Bottom

Local: Field: OTS SrNo: Set as: ""
Local: Field: OTS Description:Set as:"Total"
Local: Field: OTS HSN Code: Set as:""
Local: Field: OTS GST Per: Set as:""
Local: Field: OTS Qty: Set as:""
Local: Field: OTS Rate: Set as:""
Local: Field: OTS SGST Per: Set as:""
Local: Field: OTS SGST Per: Type: String
Local: Field: OTS SGST Amt: Set as:$$FilterAmtTotal:LedgerEntries:SGST1:$Amount
Local: Field: OTS CGST per: Set as:""
Local: Field: OTS CGST Per: Type: String
Local: Field: OTS CGST Amt: Set as:$$FilterAmtTotal:LedgerEntries:CGST1:$Amount
Local: Field: OTS IGST Per: Set as:""
Local: Field: OTS IGST Per: Type: String
Local: Field: OTS IGST Amt: Set as:$$FilterAmtTotal:LedgerEntries:IGST1:$Amount
Local: Field: OTS GrossAmt: Set as:$$CollAmtTotal:InventoryEntries:$Amount

[System: Formula]
SGST :$$FilterAmtTotal:LedgerEntries:SGST1:$Amount
SGST1 :$Name:Ledger:$LedgerName Contains $$LocaleString:"SGST"

CGST :$$FilterAmtTotal:LedgerEntries:CGST1:$Amount
CGST1 :$Name:Ledger:$LedgerName Contains $$LocaleString:"CGST"

IGST :$$FilterAmtTotal:LedgerEntries:IGST1:$Amount
IGST1 :$Name:Ledger:$LedgerName Contains $$LocaleString:"IGST"

Round :$$FilterAmtTotal:LedgerEntries:Round1:$Amount
Round1 :$Name:Ledger:$LedgerName Contains $$LocaleString:"Round"

;;;;;;;;;;;;;;;;OTS Bottom Part;;;;;;;;;;;;;;;;;;;;;;;

[Part:OTS Bottom Part]
Part:OTS Bottom Part 1,VCH GST AnalysisDetails, OTS Bottom Part 2
Vertical: Yes

[Part:OTS Bottom Part 1]
Right Part: OTS Bottom Part 1 Right
Left Part: OTS Bottom Part 1 Left
Border: Thick Cover

[Part:OTS Bottom Part 1 Left]
Line: OTS Amount in Word

[Line: OTS Amount in Word]
Field: Medium Prompt, Name Field
Local: Field: Medium Prompt: Set as: "Amount In Word"
Local: Field: Medium Prompt: Style: O9
Local: Field: Name Field: Set as: $$InWords:$Amount
Local: Field: Name Field: Style: O9B
Local: Field: Name Field: Full Width: Yes
Local: Field: Name Field: Line:0
Space Top: 0.5

[Part: OTS Bottom Part 1 Right]
Line: OTS Invoice Ledger Entries
Repeat: OTS Invoice Ledger Entries:LedgerEntries
Bottom Line: OTS Invoice Amount
Common Border: Yes
Border: Thick Left

[Line: OTS Invoice Ledger Entries]
Field: Name Field, Amount Field
Local: Field: Name Field: Set as:$LedgerName
Local: Field: Name Field: Style: O9
Local: Field: Name Field: Width:22.6
Local: Field: Name Field: Align: Right
Local: Field: Amount Field: Set as: $Amount
Local: Field: Amount Field: Style: O9B
Local: Field: Amount Field: Border: Thick Left
Local: Field: Amount Field: Width:7
Remove if: $LedgerName contains $PartyLedgerName
Space Top:0.25

[Line:OTS Invoice Amount]
Field: Name Field, Amount Field
Local:Field: Name Field: Set as: "INVOICE TOTAL"
Local:Field: Name Field: Style: Large Bold
Local:Field: Name Field: Width:22.6
Local:Field: Name Field: Align: Right
Local:Field: Amount Field: Set as: $Amount
Local:Field: Amount Field: Style: Large Bold
Local:Field: Amount Field: Width: 7
Border: Thick Top
Space Top: 0.5
Space Bottom: 0.5

[Part:OTS Bottom Part 2]
Right Part: OTS Signature
Left Part: OTS Terms and Conditions
Border: Thick Box

[Part:OTS Terms and Conditions]
Line: OTS0,OTS1, OTS2, OTS3

[Line: OTS0]
Field: Simple Field
Local: Field: Simple Field: Set as: "Terms and Conditions"
Local: Field: Simple Field: Style: O9B
Space Top: 0.25

[Line: OTS1]
Field: Simple Field
Local: Field:Simple Field: Set as:"1. Goods Once Sold will not be taken back."
Local: Field: Simple Field: Full Width: Yes
Local: Field: Simple Field: Line:0
Local: Field: Simple Field: Style: Tiny

[Line: OTS2]
Use: OTS1
Local: Field: Simple Field: Set as: "2. If Cheque Bounced, Rs 500/- will be taken as charges"

[Line: OTS3]
Use: OTS1
Local: Field: Simple Field: Set as: "3. Rs 100/- per day will charged for delayed payment after due date."

[Part:OTS Signature]
Line: OTS Signature,

[Line:OTS Signature]
Right Field: Simple Field
Local: Field: Simple Field: Set as: "For" +"  "+ @@CmpMailName
Space Top:3

Note:- This code is written for Print invoice in A4 portrait mode. You will need to do the required settings in your printer for better view. There might be some case were alignment might not come proper this is because of printer and you will need to write code for alignment.

Free Tally TDL to make PAN Card Field Compulsory (PAN Card Validation)

PAN is important especially for financial transactions. According to experts, PAN is a way for Income Tax Department to keep tabs on your financial dealing.

And as we know in Tally.ERP 9 capture PAN card details during ledger creation in Tally most of us skips the same. So to stop this below is a small free Tally TDL code which will make entering PAN card details mandatory in Tally. ERP 9 during Ledger Creation.

[#Field: LED ITNo]
    Validate    :$$StringLength:$$Value = 10

To attach this code in your Tally.ERP9 software please follow step from below url

http://tallyexperttips.blogspot.in/2017/09/how-to-attach-any-tallyerp9.html

Download Free Tally.ERP9 software  (Educational version)

Connecting Tally.ERP 9 to Microsoft Excel 2007 using ODBC

Tally ODBC helps you to extract the Data from Tally.ERP 9 and design the reports in MS Excel 2007.

To extract

Step 1: Enable ODBC

1. Start Tally.ERP 9. It should be open till the process is complete.

2. Ensure that the ODBC Server is running. You can confirm this when the message Running as ODBC Server is displayed in the Configuration block of Information Panel (at the bottom) of Tally.ERP 9 screen, as shown:

In case, the ODBC Server is not running, enable the ODBC Server

· From Gateway of Tally or Company Info menu, press F12 Configure > Advanced Configuration.

· In the Client/Server Configuration screen, set Enable ODBC Server to Yes.

Step 2:

1. Start Microsoft Office 2007.

2. Click Data.

The sub options of Data menu appears as shown:

3. Click From Other sources .

4. Select From Microsoft Query .

Choose Data Source dialog box appears.

5. Select TallyODBC.

Tally.ERP 9 connects to data source and displays Query Wizard – Choose Columns dialog box.

6. Select the columns you would want to include in the query. Select Ledger and Click > button to the right of the following fields: (For example, $Parent)

7. Click Next .

The Query Wizard - Filter Data dialog box appears.

8. Set the filter conditions in the Filter Data dialog box to limit the data to those that match your criteria.

9. Click Next .

The Query Wizard - Sort Order dialog box appears.

10. Sort the data in ascending or descending order as per the requirement.

11. Click Next.

The Query Wizard - Finish dialog box appears.

The option Return Data to Microsoft Office Excel will be selected, by default.

12. Click Finish.

Once the Query Wizard process is complete, the dialogue box entitled Import Data appears.

13. Click OK .

The excel sheet will display the report as shown below:

Simple Tally.ERP9 TDL Code for having contact details In Sales Screen

Most of us you like to have customer's contact details in front of us during Voucher entry and Especially during Sale Invoicing so that we can contact them easily without searching for there contact details here and there.

And using below code you can achieve the same.

NOTE:- This is only for Sales Invoice and will only show the mobile number and contact person.

[#Line: EI PartyLimit]

Add : Field : After : EI CreditLimit : Contact

[Field : Contact]
Invisible : NOT ##CurBalanceFlag
Field : Simple Prompt, Name Field
Local : Field : Simple Prompt : Info : $$LocaleString:"Contact:"
Local : Field : name Field : Set as : $LedgerContact:Ledger:#EIConsignee + "  " + $LedgerMobile:Ledger:#EIConsignee
Local: Field: Name Field: Width:25

To attach the above code you will need to follow the steps given in http://tallyexperttips.blogspot.in/2017/09/how-to-attach-any-tallyerp9.html

After Attaching the above code you will be able to see the contact details of your customer on sales voucher screen provided it is filled in in your Ledger.

Attach any document to voucher entry in Tally.ERP9

In this post i have share code for attaching document to voucher entry and opening that document from within Tally.ERP9. This is very useful at the place were we want our documents to be in soft copy and does not want to maintain hard copy of each and every stuff.

To know how to attache any tdl to tally please visit
http://tallyexperttips.blogspot.in/2017/09/how-to-attach-any-tallyerp9.html


[#Form: Sales Color]
Add    :Button    :Open Attachement

[#Form: Contra Color]
Add    :Button    :Open Attachement

[#Form: Payment Color]
Add    :Button    :Open Attachement

[#Form: Receipt Color]
Add    :Button    :Open Attachement

[#Form: Journal Color]
Add    :Button    :Open Attachement

[#Form: Payroll Color]
Add    :Button    :Open Attachement

[#Form: Debit Note Color]
Add    :Button    :Open Attachement

[#Form: Credit Note Color]
Add    :Button    :Open Attachement

[#Form: Purchase Color]
Add    :Button    :Open Attachement

[#Form: Memorandum Color]
Add    :Button    :Open Attachement

[#Form: Reversing Journal Color]
Add    :Button    :Open Attachement

[#Form: Stock Journal Color]
Add    :Button    :Open Attachement

[#Form: Delivery Note Color]
Add    :Button    :Open Attachement

[#Form: Receipt Note Color]
Add    :Button    :Open Attachement

[#Form: Rejection Inward Color]
Add    :Button    :Open Attachement

[#Form: Rejection Outward Color]
Add    :Button    :Open Attachement

[#Form: Physical Stock Color]
Add    :Button    :Open Attachement

[#Form: Sales Order Color]
Add    :Button    :Open Attachement

[#Form: Purc Order Color]
Add    :Button    :Open Attachement

[#Form: Indent Color]
Add    :Button    :Open Attachement

[#Form: Attendance Color]
Add    :Button    :Open Attachement

[#Form: JobOrderIn Color]
Add    :Button    :Open Attachement

[#Form: JobOrderOut Color]
Add    :Button    :Open Attachement

 [Button : Open Attachement]
Title : $$LocaleString:"Open Attachement"
Key : Ctrl + O
Action :Browse Url Ex : #HyperlinkCompany

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
[#Part: VCH Narration]
Add : Switch    : BankDetRcpt : BankDet VCH Narration

[!Part: BankDet VCH Narration]
Add : Line : HyperlinkCompany

[Line: HyperlinkCompany]
Fields  : Short Prompt, HyperlinkCompany
Local   : Field : Short Prompt : Info: $$LocaleString:"Supporting Doc."

[System: UDF]
hyper1 : String    : 1101

[Key : Execute Hyperlink1]
Title: Exc
Key : Left Click
Action : Browse Url Ex: “www.onetouchsolution.co.in”

[Field: HyperlinkCompany]
Use         : Name Field
Color : Blue
;Border : Thin Bottom
Key : Execute Hyperlink1
Storage : hyper1
Local : Key : Execute Hyperlink1 : Action :Browse Url Ex: "D:\DOCUMENTS\" + $VoucherTypeName +"\"+ #HyperlinkCompa
Skip: $$InAlterMode
Fullwidth:yes

After adding this you will bed able to find new field on each voucher shown as below.

you need to specify the complete path of the document including extension as show below and save the voucher.

After that when you edit the voucher you should be able to see the document as shown below.

and now you can open this document from within Tally by pressing Open Attachment Button on the right hand side.

How to create Custom Menu on Tally Start Instead of Gateway of Tally

In this Post I am sharing sample code about how to change and create custom menu on Tally.ERP9 Start instead of Gateway of Tally. This can be use full for security purpose were we want or employee to have only those options which we want on there screen and which cannot be achieved using default Tally.ERP9 security feature. So lets start.


[System: Formula]
locDefaultGateway                 : $$LocaleString:"Gateway of Tally"
    locInvv   :$$LocaleString:"Inventory Vouchers"
locRepo   :$$LocaleString:"Reports"

[Menu: Vouchers Entry]
Key Item    : @@locInventoryVouchers : T : Create Collection : Company InvVouchers : $$IsInventoryOn:$$CurrentSimpleCompany 

[Menu: Reports]
Key Item    : @@locDayBook                : D : Display   : Day Book              : NOT $$IsEmpty:$$SelectedCmps
Item    : BLANK
Add : Key Item  : GodownWise Stock : G : Display Collection : Godown Summary : $$IsMultiGodownOn:$$CurrentSimpleCompany
Control : @@locGodowns : $$IsMultiGodownOn AND @@IndianAccTerminology AND $$Allow:Display:GodownWiseSummary


[Menu: Default Gateway]

Title       : $$LocaleString:"Gateway of Tally"
    Indent  : @@locIndentMasters
    Item    : BLANK
Key Item : @@locAccountsInfo : A : Menu : Accounts Info. : NOT $$IsEmpty:$$SelectedCmps 
Key Item : @@locPayrollInfo : L : Menu : Payroll Info. : NOT $$IsEmpty:$$SelectedCmps AND ($$AddOnInfo:PayrollEnabled) AND $$IsPayrollOn:$$CurrentSimpleCompany 
Key Item : @@locInventoryInfo : I : Menu : Inventory Info. : NOT $$IsEmpty:$$SelectedCmps AND $$IsInventoryOn:$$CurrentSimpleCompany 
Key Item : @@locQuickSetup : K : Menu : Quick Setup : @@IsIndian AND NOT $$IsEmpty:$$SelectedCmps 
    Item    : BLANK
    Indent  : @@locIndentTransactions
    Item    : BLANK
    Key Item    : @@locAccountingVouchers : V : Create Collection : Company AccVouchers
    Key Item    : @@locInventoryVouchers : T : Create Collection : Company InvVouchers : $$IsInventoryOn:$$CurrentSimpleCompany 
    Key Item    : @@locOrderVouchers : E : Create Collection : Company OrdVouchers : ($$IsPurcOrdersOn:$$CurrentSimpleCompany OR $$IsSalesOrdersOn:$$CurrentSimpleCompany OR $$IsJobWorkOn:$$CurrentSimpleCompany)
    Key Item    : @@locPayrollVouchers : Y : Create Collection : Company Payroll Vouchers : $$IsPayrollOn:$$CurrentSimpleCompany   
Key Item    : @@locAttendanceVouchers : C : Create Collection : Company Attendance Vouchers : $$IsPayrollOn:$$CurrentSimpleCompany AND $$NumAttdTypes >= 1 
    Item    : BLANK
    Indent  : @@locIndentUtilities
    Item    : BLANK
    Indent  : @@locIndentReports
    Item     : BLANK
    Key Item    : @@locBalanceSheet      : B : Display           : Balance Sheet : $$IsAccountingOn:$$CurrentCompany
    Key Item    : @@locProfitLossAcc      : P : Display           : Profit and Loss : $$IsAccountingOn:$$CurrentCompany
    Key Item    : @@locIncomeExpenseAcc  : N : Display           : Profit and Loss : $$IsAccountingOn:$$CurrentCompany
    Key Item    : @@locStockSummary      : S : Display           : Stock Summary : $$IsInventoryOn:$$CurrentCompany
    Key Item    : @@locRatioAnalysis      : R : Display           : Ratio Analysis : $$IsAccountingOn:$$CurrentCompany
    Item    : BLANK
    Key Item    : @@locDisplay            : D : Menu : Display Menu
    Key Item    : @@locMultiAccPrinting  : M : Menu : Printing Menu : (($$LicenseInfo:IsLicensedMode) OR (($$LicenseInfo:RemoteSerialNumber) > 0))
Item    : BLANK
    Key Item    : @@locQuit : Q
Control : @@locAccountsInfo : $$Allow:Create:AccountsMasters OR $$Allow:Alter:AccountsMasters OR $$Allow:Display:AccountsMasters
Control : @@locPayrollInfo : $$IsPayrollOn AND ($$Allow:Create:PayrollMasters OR $$Allow:Alter:PayrollMasters OR $$Allow:Display:PayrollMasters)
Control : @@locInventoryInfo : $$IsInventoryOn AND ($$Allow:Create:InventoryMasters OR $$Allow:Alter:InventoryMasters OR $$Allow:Display:InventoryMasters)
Control : @@locQuickSetup : @@IsIndian
    Control : @@locAccountingVouchers : $$Allow:Create:Vouchers AND $$Allow:Create:AccountingVouchers AND $$CheckAllowedAccVchTypeMenu
Control  : @@locPayrollVouchers : $$IsPayrollOn:$$CurrentSimpleCompany AND $$Allow:Create:PayrollVouchers AND $$CheckAllowedPayrollVchTypeMenu
Control  : @@locAttendanceVouchers : $$IsPayrollOn:$$CurrentSimpleCompany AND ($$Allow:Create:AttendanceVouchers OR $$Allow:Create:Attendance) AND (NOT $$Allow:Create:Payroll OR NOT $$Allow:Create:PayrollVouchers) AND $$CheckAllowedAttendanceVchTypeMenu
    Control : @@locInventoryVouchers : $$IsInventoryOn AND $$Allow:Create:Vouchers AND $$Allow:Create:InventoryVouchers AND $$CheckAllowedInvVchTypeMenu
    Control : @@locOrderVouchers : $$IsInventoryOn AND ($$IsPurcOrdersOn OR $$IsSalesOrdersOn OR $$IsJobWorkOn) AND $$Allow:Create:Vouchers AND $$Allow:Create:OrderVouchers AND $$CheckAllowedOrderVchTypeMenu
    Control : @@locBalanceSheet      : $$Allow:Display:BalanceSheet
    Control : @@locRatioAnalysis      : $$Allow:Display:BalanceSheet
    Control : @@locProfitLossAcc      : $$Allow:Display:ProfitLossAc AND NOT $PLasIncomeExpense:Company:$$CurrentCompany
    Control : @@locIncomeExpenseAcc  : $$Allow:Display:ProfitLossAc AND $PLasIncomeExpense:Company:$$CurrentCompany
    Control : @@locStockSummary : $$IsInventoryOn AND $$Allow:Display:StockSummary
Control : @@locMultiAccPrinting : (($$LicenseInfo:IsLicensedMode) OR (($$LicenseInfo:RemoteSerialNumber) > 0))
    Option : Import Direct : NOT $$IsTallyClient AND NOT $$IsTallyServer
Option : Import InDirect : $$IsTallyClient OR $$IsTallyServer
    Option : FinalAcctsMenu : @@UseFinalAcctsMenu
    Option : MultiAccountButton : (($$LicenseInfo:IsLicensedMode) OR (($$LicenseInfo:RemoteSerialNumber) > 0))
Button : Select Company
Delete :  Key Item    : Stock Report : C : Display Collection    : Stock Query1


In above code i have change the default Tally.ERP9 Gateway of Tally menu to "Main menu" and added the key items which i want my employees to have it on there screen.

I have also added key item which will take me to Default Tally.ERP9  Gateway of Tally but provided i have access to it. So using the above code my employee will only be able to use all the voucher type which can be accessed using Invoice Voucher Option and reports which I want them to see.

Note:- To have it more secure you will need to use default tally security feature on with security level set so that shortcut cannot be used.


Note:- This is just for education purpose you will need to edit the code as per your requirements.

Block Invoicing After Credit Day Overdue Free Tally TDL

As we all know that Tally.ERP9 software as the functionality of setting up Credit days and Credit based on Amount in Ledger Master but does not block billing even if Credit days are over due.

Below Code will help you in blocking the billing if any bill is overdue of a particular customer. 

;;;;; In below code we have found the field were we want our code to check and block the entry and have applied the control;;;;;;;

[#Field: EI Consignee]

Control : CtrlCreditDays : @@ChkCreditDays>0 and $$InCreateMode and $$IsSales:##SVVoucherType

[Collection: Credit PartyPending Bills]

    Type        : Bills

    Child of    : #EIConsignee

    Unique      : $$Name

Format : @@DateDiff

    Format      : $BillDate, 8      : Universal Date

    Format      : $$Name, 10

    Format      : $BaseClosing,-30  : "AllSymbols, DrCr"

Format : @@DueDate

Format : ##SVCurrentDate

Filter : FltrCreditDays, FltrCrAmt

[System : Formula]

CtrlCreditDays : if @@ChkCreditDays = 1 then "The Customer has " + $$NewLine + $$String:@@ChkCreditDays +  $$NewLine + " Bill OverDue." else "The Customer has " + $$NewLine + $$String:@@ChkCreditDays +  $$NewLine + " Bills OverDue."

ChkCreditDays : $$Numitems:CreditPartyPendingBills

FltrCreditDays : $$Number:@@DateDiff>=1 

DueDate : $$Date:$$String:$BillCreditPeriod:UniversalDate

DateDiff : ##SVCurrentDate - @@DueDate

FltrCrAmt : $$IsDr:$BaseClosing 

Common Troubleshooting....

1. After attaching the code to Tally ( http://tallyexperttips.blogspot.in/2017/09/how-to-attach-any-tallyerp9.html ) it is blocking all the invoice even if credit period is not over ?

Answer:- Go to Display-> Statement of Accounts-> Outstanding-> Select Ledger for which you are facing issue-> and check that there is not no of days in overdue column.