Best in class reporting solutions from ERPs: Data insertion-Tally(approaches)

Problem statement: Many softwares look to insert data into Tally from their application.This blog looks at issues and approaches for the same.

There arer two ways of inserting the data into Tally(as per Tally site):

a.) Tally development code

b.) Tally xml.

Tally xml: For insertion into Tally from an external application there are two aspects that need to be taken care of.

a.) Insertion and update of Masters

b.) Insertion, update and delete of Vouchers.

Attached is the process step for doing the same using XML:

Insertion and updates of masters:

a.) Export the copy of the masters by using the daybook or the ledger master copy in xml format.

b.) Use Tally as a webserver and assign a port for the same.

c.) Decide on the scripting language to be used to insert the data into Tally. We chose python. The data insertion could be done directly from the python web form or from the database.

Insertion/updation/deletion of vouchers:

For example to insert the data from an excel sheet to Tally using python the following code could be used:

from flask import Flask, request, redirect, url_for, flash, render_template_string

import requests

import pandas as pd

# Load Excel file and read the data into a DataFrame

file_path = 'C:\Tally_difference\sample_import\sample_purchase_import.xlsx' # Replace with your Excel file path

df = pd.read_excel(file_path)

# Tally server URL

tally_url = 'http://localhost:9000'

# Filter rows where the 'index' column is greater than 5

df_filtered = df[df['index'] >= 1]

for index, row in df_filtered.iterrows():

# Collect data from form

Remoteid=row['Remoteid']

company_name = row['Companyname']

voucher_type = row['Vouchertype']

voucher_date = row['Voucherdate']

party_ledger = row['partyledgername']

ledger_name = row['Ledgername']

amount = row['Amount']

# XML payload

xml_data = f"""<ENVELOPE>

<TALLYREQUEST>Import Data</TALLYREQUEST>

</HEADER>

<BODY>

<REPORTNAME>Vouchers</REPORTNAME>

<SVCURRENTCOMPANY>{company_name}</SVCURRENTCOMPANY>

</STATICVARIABLES>

</REQUESTDESC>

<DATE>{voucher_date}</DATE>

<VOUCHERTYPENAME>{voucher_type}</VOUCHERTYPENAME>

<PARTYLEDGERNAME>{party_ledger}</PARTYLEDGERNAME>

<ALLLEDGERENTRIES.LIST>

<LEDGERNAME>{ledger_name}</LEDGERNAME>

<AMOUNT>-{amount}</AMOUNT>

</ALLLEDGERENTRIES.LIST>

<ALLLEDGERENTRIES.LIST>

<LEDGERNAME>{party_ledger}</LEDGERNAME>

<AMOUNT>{amount}</AMOUNT>

</ALLLEDGERENTRIES.LIST>

</VOUCHER>

</TALLYMESSAGE>

</REQUESTDATA>

</IMPORTDATA>

</BODY>

</ENVELOPE>"""

# Send data to Tally

headers = {'Content-Type': 'application/xml'}

response = requests.post(tally_url, data=xml_data.encode('utf-8'), headers=headers)

print(f"Status Code: {response.status_code}")

print(f"Response Content: {response.text}")

Explanation:

Tally is declared as webserver

# Tally server URL

tally_url = 'http://localhost:9000'

Excel sheet is run row by row
The xml request is run. The unique row in the excel row is identified by rowid( REMOTEID="{Remoteid}). The given remoteid helps capture insert, update and deletes for vouchers.
Response code sends the requests to post the data into Tally.

Insertion/updation of ledgers: for the case of ledgers Tally has a clause called @@dupkeycombine as specified in the sample xml request.

from flask import Flask, request, redirect, url_for, flash, render_template_string

import requests

app = Flask(__name__)

app.secret_key = 'your_secret_key' # Replace with a strong key for production

# Tally server URL

tally_url = 'http://localhost:9000'

# HTML template as a string

html_template = """

<!DOCTYPE html>

<head>

<title>Tally Ledger Entry Form</title>

</head>

<body>

<h1>Tally Ledger Entry Form</h1>

{% with messages = get_flashed_messages(with_categories=true) %}

{% if messages %}

<ul>

{% for category, message in messages %}

<li class="{{ category }}">{{ message }}</li>

{% endfor %}

</ul>

{% endif %}

{% endwith %}

<label for="company_name">Company Name:</label><br>

<label for="ledger_name">Ledger Name:</label><br>

<label for="ledger_parent">Ledger Parent:</label><br>

<button type="submit">Submit to Tally</button>

</form>

</body>

</html>

"""

@app.route('/')

def index():

return render_template_string(html_template)

@app.route('/submit', methods=['POST'])

def submit_data():

# Collect data from form

company_name = request.form['company_name']

ledger_name = request.form['ledger_name']

#reserved_name = request.form['reserved_name']

ledger_parent = request.form['ledger_parent']

#ledger_name = request.form['ledger_name']

#amount = request.form['amount']

# XML payload

xml_data = f"""<ENVELOPE>

<TALLYREQUEST>Import</TALLYREQUEST>

<ID>AllMasters</ID>

</HEADER>

<BODY>

<DESC>

<IMPORTDUPS>@@DUPKEYCOMBINE</IMPORTDUPS>

<SVCURRENTCOMPANY>{company_name}</SVCURRENTCOMPANY>

</STATICVARIABLES>

</DESC>

<DATA>

<OLDAUDITENTRYIDS.LIST TYPE="Number">

</OLDAUDITENTRYIDS.LIST>

<PARENT>{ledger_parent}</PARENT>

<TAXCLASSIFICATIONNAME> Not Applicable</TAXCLASSIFICATIONNAME>

<TAXTYPE>Others</TAXTYPE>

<GSTTYPE> Not Applicable</GSTTYPE>

<APPROPRIATEFOR> Not Applicable</APPROPRIATEFOR>

<SERVICECATEGORY> Not Applicable</SERVICECATEGORY>

<EXCISELEDGERCLASSIFICATION> Not Applicable</EXCISELEDGERCLASSIFICATION>

<EXCISEDUTYTYPE> Not Applicable</EXCISEDUTYTYPE>

<EXCISENATUREOFPURCHASE> Not Applicable</EXCISENATUREOFPURCHASE>

<LEDGERFBTCATEGORY> Not Applicable</LEDGERFBTCATEGORY>

<SERVICETAXDETAILS.LIST> </SERVICETAXDETAILS.LIST>

<LBTREGNDETAILS.LIST> </LBTREGNDETAILS.LIST>

<VATDETAILS.LIST> </VATDETAILS.LIST>

<SALESTAXCESSDETAILS.LIST> </SALESTAXCESSDETAILS.LIST>

<GSTDETAILS.LIST> </GSTDETAILS.LIST>

<HSNDETAILS.LIST> </HSNDETAILS.LIST>

<LANGUAGENAME.LIST>

<NAME.LIST TYPE="String">

<NAME>{ledger_name}</NAME>

</NAME.LIST>

</LANGUAGENAME.LIST>

<XBRLDETAIL.LIST> </XBRLDETAIL.LIST>

<AUDITDETAILS.LIST> </AUDITDETAILS.LIST>

<SCHVIDETAILS.LIST> </SCHVIDETAILS.LIST>

<EXCISETARIFFDETAILS.LIST> </EXCISETARIFFDETAILS.LIST>

<TCSCATEGORYDETAILS.LIST> </TCSCATEGORYDETAILS.LIST>

<TDSCATEGORYDETAILS.LIST> </TDSCATEGORYDETAILS.LIST>

<SLABPERIOD.LIST> </SLABPERIOD.LIST>

<GRATUITYPERIOD.LIST> </GRATUITYPERIOD.LIST>

<ADDITIONALCOMPUTATIONS.LIST> </ADDITIONALCOMPUTATIONS.LIST>

<EXCISEJURISDICTIONDETAILS.LIST> </EXCISEJURISDICTIONDETAILS.LIST>

<EXCLUDEDTAXATIONS.LIST> </EXCLUDEDTAXATIONS.LIST>

<BANKALLOCATIONS.LIST> </BANKALLOCATIONS.LIST>

<PAYMENTDETAILS.LIST> </PAYMENTDETAILS.LIST>

<BANKEXPORTFORMATS.LIST> </BANKEXPORTFORMATS.LIST>

<BILLALLOCATIONS.LIST> </BILLALLOCATIONS.LIST>

<INTERESTCOLLECTION.LIST> </INTERESTCOLLECTION.LIST>

<LEDGERCLOSINGVALUES.LIST> </LEDGERCLOSINGVALUES.LIST>

<LEDGERAUDITCLASS.LIST> </LEDGERAUDITCLASS.LIST>

<OLDAUDITENTRIES.LIST> </OLDAUDITENTRIES.LIST>

<TDSEXEMPTIONRULES.LIST> </TDSEXEMPTIONRULES.LIST>

<DEDUCTINSAMEVCHRULES.LIST> </DEDUCTINSAMEVCHRULES.LIST>

<LOWERDEDUCTION.LIST> </LOWERDEDUCTION.LIST>

<STXABATEMENTDETAILS.LIST> </STXABATEMENTDETAILS.LIST>

<LEDMULTIADDRESSLIST.LIST> </LEDMULTIADDRESSLIST.LIST>

<STXTAXDETAILS.LIST> </STXTAXDETAILS.LIST>

<CHEQUERANGE.LIST> </CHEQUERANGE.LIST>

<DEFAULTVCHCHEQUEDETAILS.LIST> </DEFAULTVCHCHEQUEDETAILS.LIST>

<ACCOUNTAUDITENTRIES.LIST> </ACCOUNTAUDITENTRIES.LIST>

<AUDITENTRIES.LIST> </AUDITENTRIES.LIST>

<BRSIMPORTEDINFO.LIST> </BRSIMPORTEDINFO.LIST>

<AUTOBRSCONFIGS.LIST> </AUTOBRSCONFIGS.LIST>

<BANKURENTRIES.LIST> </BANKURENTRIES.LIST>

<DEFAULTCHEQUEDETAILS.LIST> </DEFAULTCHEQUEDETAILS.LIST>

<DEFAULTOPENINGCHEQUEDETAILS.LIST> </DEFAULTOPENINGCHEQUEDETAILS.LIST>

<CANCELLEDPAYALLOCATIONS.LIST> </CANCELLEDPAYALLOCATIONS.LIST>

<ECHEQUEPRINTLOCATION.LIST> </ECHEQUEPRINTLOCATION.LIST>

<ECHEQUEPAYABLELOCATION.LIST> </ECHEQUEPAYABLELOCATION.LIST>

<EDDPRINTLOCATION.LIST> </EDDPRINTLOCATION.LIST>

<EDDPAYABLELOCATION.LIST> </EDDPAYABLELOCATION.LIST>

<AVAILABLETRANSACTIONTYPES.LIST> </AVAILABLETRANSACTIONTYPES.LIST>

<LEDPAYINSCONFIGS.LIST> </LEDPAYINSCONFIGS.LIST>

<TYPECODEDETAILS.LIST> </TYPECODEDETAILS.LIST>

<FIELDVALIDATIONDETAILS.LIST> </FIELDVALIDATIONDETAILS.LIST>

<INPUTCRALLOCS.LIST> </INPUTCRALLOCS.LIST>

<TCSMETHODOFCALCULATION.LIST> </TCSMETHODOFCALCULATION.LIST>

<LEDGSTREGDETAILS.LIST> </LEDGSTREGDETAILS.LIST>

<LEDMAILINGDETAILS.LIST>

<ADDRESS.LIST TYPE="String">

<ADDRESS>NANANA</ADDRESS>

</ADDRESS.LIST>

</LEDMAILINGDETAILS.LIST>

<GSTRECONPREFIXSUFFIXDETAILS.LIST> </GSTRECONPREFIXSUFFIXDETAILS.LIST>

<CONTACTDETAILS.LIST> </CONTACTDETAILS.LIST>

<GSTCLASSFNIGSTRATES.LIST> </GSTCLASSFNIGSTRATES.LIST>

<EXTARIFFDUTYHEADDETAILS.LIST> </EXTARIFFDUTYHEADDETAILS.LIST>

<TEMPGSTITEMSLABRATES.LIST> </TEMPGSTITEMSLABRATES.LIST>

<VOUCHERTYPEPRODUCTCODES.LIST> </VOUCHERTYPEPRODUCTCODES.LIST>

<LEDADDRESS.LIST> </LEDADDRESS.LIST>

<DEFMULTIPLETOPHONENO.LIST> </DEFMULTIPLETOPHONENO.LIST>

</LEDGER>

</TALLYMESSAGE>

</DATA>

</BODY>

</ENVELOPE>

"""

# Send data to Tally

headers = {'Content-Type': 'application/xml'}

response = requests.post(tally_url, data=xml_data.encode('utf-8'), headers=headers)

# Handle response

if response.status_code == 200:

flash("Data submitted to Tally successfully.", "success")

else:

flash("Failed to submit data to Tally.", "error")

return redirect(url_for('index'))

if __name__ == '__main__':

app.run(debug=True, use_reloader=False)

These are the key points to be attached with the xml import for vouchers.

Tally import with tdl:

The data for masters and transactions can also be imported with tdl.

In case of TDL it becomes a little bit more complex since the matching of the ledger names code needs to be added. Sample function is given below:

[function: Add_Stockitem]

00: Walk Collection: testcoll

0X: IF : $$IsEmpty:@@itemexists

Variable:setdate:Date

Variable:setrate:Rate

00A:Set:setdate:$$Date:"01-04-2023"

01E:Set:setrate:$$AsRate:"100"

01A: New Object: StockItem

01BA: Set VALUE:NAME:$$LocaleString:$item_name;$item_name

01BC: Set VALUE:PARENT:$item_type;$item_name

01BD: Set VALUE:CATEGORY:$sub_category_name

01BD1: Set VALUE:DESCRIPTION:$sku_code

01BE: Set VALUE:GSTAPPLICABLE:$$LocaleString:"Applicable"

01bF: Set Value: BASEUNITS:$$LocaleString:$Uom

01bG: Set Value: NARRATION:$$LocaleString:$sku_code

;; Set cost & selling price

0010 : INSERT COLLECTION OBJECT : STANDARDCOSTLIST

0011 : Set Value : Date : "1-11-2017"

0012 : Set Value : Rate : $$TgtObject:$$AsRate:@@RateVal1

0013 : Set Target : ..

0014 : INSERT COLLECTION OBJECT : STANDARDPRICELIST

0015 : Set Value : Date : "1-11-2017"

0016 : Set Value : Rate : $$TgtObject:$$AsRate:@@RateVal2

0017 : Set Target : ..

01D1:SAVE Target

01D:End If

;02: END WALK

02: END WALK

021CC:Create Target

Addition of voucher using TDL: for addition of voucher using tdl we have yet to figure outt how to use remoteid. Hence the same has been excluded in this blog.

Name: Apoorv Chaturvedi

email: support@turbodatatool.com;suppport@mndatasolutions.com

Phone:+91-8802466356

youtube channel:www.youtube.com\@mndatasolutions2042

Best in class reporting solutions from ERPs

Friday, 17 January 2025

Data insertion-Tally(approaches)

No comments:

Post a Comment

Data insertion-Tally(approaches)

Blog Archive

Labels