Saturday, 18 January 2025

Reduce costs:Paying for each mobile number for Mobile access for ERP/Tally ERP data

 Problem statement: for many companies the number of internal mobile users for ERP/Tally ERP data on mobile are significant. Say the number of mobile users is 10. The other issues to be raised are as follows:

a.) The clients could have multiple companies.

b.) The client could desire to have complete control over the data.

c.) The costs could increase exponentially.

d.) The client could desire to give access to external stakeholders as well.


The solution to this problem can be envisaged in multiple ways. We have looked at the following ways:

a.) Giving access through excel

b.) For tamper proof access giving access through Powerbi[no database involved]

c.) For lowest cost over a large number of end users access through sql server database using row level security.



Access through Excel:

For many of the reports Tally could be used as a webserver and the data imported into Ms Excel. The data could be extracted into Excel through powerquery or odbc.


sample xml import into excel for Tally:


let

    // Define the Tally URL

    Tally_URL = "http://localhost:9000",


    // Define the XML Input

    XML_Input = "

        <ENVELOPE>

            <HEADER>

                <VERSION>1</VERSION>

                <TALLYREQUEST>Export</TALLYREQUEST>

                <TYPE>Data</TYPE>

                <ID>Balance sheet</ID>

            </HEADER>

            <BODY>

                <DESC>

                    <STATICVARIABLES>

                        <EXPLODEFLAG>Yes</EXPLODEFLAG>

                        <SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>

                    </STATICVARIABLES>

                </DESC>

            </BODY>

        </ENVELOPE>",


    // Send HTTP POST request to Tally and fetch the response

    Response = Web.Contents(

        Tally_URL,

        [

            Headers = [#"Content-Type" = "application/xml"],

            Content = Text.ToBinary(XML_Input)

        ]

    ),


    // Parse the XML Response

    XML_Data = Xml.Tables(Response)

in

    XML_Data


The odbc extract is straight forward and can be taken from Tally website.


Once the Excel sheet has been extracted it can be shared via onedrive,googledrive or shared drive.



b.) Usage of Powerbi: Powerbi offers a free on mobile version using the Microsoft platform.Our Powerbi integration would work with the following:

i.) odbc extract :Tally as a webserver

ii.) XML extract: Tally as a webserver

iii.) Json extract: no need of Tally as a webserver, unlimited companies, the business logic has been pre coded in Powerbi.

With the Powerbi version we can also design customized mobile apps for large scale viewing.

Please see our channel for lots of videos and details regarding the same.

SQL Powerbi training module:https://www.youtube.com/watch?v=ptn-rwYGUwY&list=PLZPWImFqOkLtJk2MoUPjb6ap_wFbJN53E

Summarized Powerbi app: https://www.youtube.com/watch?v=eIT4YAITIIg&list=PLZPWImFqOkLvDtnthqV9MzLoXDfoYWNSO



Points to note: If the Myworkspace in the Powerbi module is used then the app can be used by unlimited personnel.

However in case one requires row level access to the data then multiple Powerbi licenses are required. The cost of each Powerbi pro license is USD 10 a month[the data is hosted on the Microsoft cloud servers].


c.) In case the following is the scenario:

i.) Large number of people need to be given access to the data

ii.) Secure row level access for the data is required.

iii.) Data entry features are required.


Then we recommend Web app through SQL Server. The same will work with xml,odbc and json.


Website link:http://103.211.202.97:5002/

Username:Rohit

Password:Rohit@123

View reports: http://103.211.202.97:5002/view-reports?

Data entry format: http://103.211.202.97:5002/data_entry_masterpage?


In this case unlimited people can access Tally data in a secure fashion using row level security.


Please give us a chance for ETL demo.


Name:Apoorv Chaturvedi

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

Phone:+91-8802466356

youtube channel:www.youtube.com\@mndatasolutions2042





Friday, 17 January 2025

Incomplete collections-Tally (a different approach)

 Problem statement: a number of customers still have cash flow issues after taking the following actions:

a.) Usage of Tally

b.) Usage of third party mobile apps.


Here is an analysis of how one can seek to resolve the problem.

a.) Complete receivable data: the complete receivable data is very challenging with Tally. Normal approach is as follows:

i.) Look at ledger closing balances from current assets and to sundry debtors as follows:





Most mobile apps give the debit balance of the sundry debtors as the total to be collected.






Problem with this approach:

a.) Tally calculates sales amount as those entries that affect the ledger primary sales accounts, not optional, not void and not cancelled. The entries can be with multiple voucher types and can affect sales accounts.


For example sales can be done to sundry creditors and the same can be left by this approach.



Issue with using sundry debtors. There could be certain sub groups in sundry debtors and sundry creditors where we might not like to do the payment follow ups. For the same we need to do the group ledger hierarchy flattening. The same can be addressed by looking at the following collection extracts:



Issue with Trial balance extract. The trial balance extract does not give the complete parent hierarchy and does not give the ledger details below the second level hierarchy.

For example with the trial balance export:


<ENVELOPE>

<HEADER>

<VERSION>1</VERSION>

<TALLYREQUEST>Export</TALLYREQUEST>

<TYPE>Data</TYPE>

<ID>Trial Balance</ID>

</HEADER>

<BODY>

<DESC>

<STATICVARIABLES>

<EXPLODEFLAG>Yes</EXPLODEFLAG>

<SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>

</STATICVARIABLES>

</DESC>

</BODY>

</ENVELOPE>


we will get output as follows:



In the details attached above one finds that the parent details are not available completely. Also the ledger name below second level are not exported in trial balance. Hence one has to use ledgerprimary from Tally for ledgers below second level of hierarchy. 


The second step is to analyze the bills receivable statements by group wise. There could be certain groups to be excluded in the collections process.



These ledgers and their groups need to be excluded from any payment collection process.


2.) Adjusting the credit entries : for the same net closing balance needs to be taken that subtracts the debit and the credit closing balances. Simply using the debit balances might not help.

3.) Looking at the bills receivable details. There could be some bills that are part of the bills receivable, group payable , ledger payable and bills payable details.  Using our json extract we have combined all the receivables, payables, group and ledger-group details across unlimited companies.






4.) One will have some collections from bills receivable, bills payable combine along with group ledger hierarchy.


5.) We need to check the ledger credit period for all the remaining ledgers.


6.) Thereafter we will have to find those invoices that have still been left unpaid with onaccount entries. A single table across multiple companies would help.




Once all the above steps have been done then one can make a list of vouchers for which bills receivable intimation is to be done.


The intimation facilities can be done with sms, email or whatsapp.


Contact us for accounting analysts to help solve your problem.


Name: Apoorv Chaturvedi

Phone:+91-8802466356

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

youtube channel:www.youtube.com\@mndatasolutions2042.


Our performance guarantee: full payment follow ups in a period of 2(two) months.

Pre requisites; Tally ERP, powerbi, SQL Server(if preferable)






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>
     <HEADER>
      <TALLYREQUEST>Import Data</TALLYREQUEST>
     </HEADER>
     <BODY>
      <IMPORTDATA>
       <REQUESTDESC>
        <REPORTNAME>Vouchers</REPORTNAME>
        <STATICVARIABLES>
         <SVCURRENTCOMPANY>{company_name}</SVCURRENTCOMPANY>
        </STATICVARIABLES>
       </REQUESTDESC>
       <REQUESTDATA>
        <TALLYMESSAGE xmlns:UDF="TallyUDF">
         <VOUCHER REMOTEID="{Remoteid}"  VCHTYPE="{voucher_type}" ACTION="Create">
          <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>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <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 %}

    <form action="{{ url_for('submit_data') }}" method="POST">
        <label for="company_name">Company Name:</label><br>
        <input type="text" id="company_name" name="company_name" required><br><br>

        <label for="ledger_name">Ledger Name:</label><br>
        <input type="text" id="ledger_name" name="ledger_name" required><br><br>

        

        <label for="ledger_parent">Ledger Parent:</label><br>
        <input type="text" id="ledger_parent" name="ledger_parent" required><br><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>

<HEADER>
<VERSION>1</VERSION>
<TALLYREQUEST>Import</TALLYREQUEST>
<TYPE>Data</TYPE>
<ID>AllMasters</ID>
</HEADER>
<BODY>
<DESC>
<STATICVARIABLES>
<IMPORTDUPS>@@DUPKEYCOMBINE</IMPORTDUPS>
<SVCURRENTCOMPANY>{company_name}</SVCURRENTCOMPANY>

</STATICVARIABLES>

</DESC>
<DATA>
<TALLYMESSAGE>

<LEDGER NAME="{ledger_name}">
      <OLDAUDITENTRYIDS.LIST TYPE="Number">
       <OLDAUDITENTRYIDS>-1</OLDAUDITENTRYIDS>
      </OLDAUDITENTRYIDS.LIST>
      <CREATEDDATE>20231224</CREATEDDATE>
      <PARENT>{ledger_parent}</PARENT>
      <ENTEREDBY>a</ENTEREDBY>
      <CREATEDBY>a</CREATEDBY>
      <TAXCLASSIFICATIONNAME>&#4; Not Applicable</TAXCLASSIFICATIONNAME>
      <TAXTYPE>Others</TAXTYPE>
      <GSTTYPE>&#4; Not Applicable</GSTTYPE>
      <APPROPRIATEFOR>&#4; Not Applicable</APPROPRIATEFOR>
      <SERVICECATEGORY>&#4; Not Applicable</SERVICECATEGORY>
      <EXCISELEDGERCLASSIFICATION>&#4; Not Applicable</EXCISELEDGERCLASSIFICATION>
      <EXCISEDUTYTYPE>&#4; Not Applicable</EXCISEDUTYTYPE>
      <EXCISENATUREOFPURCHASE>&#4; Not Applicable</EXCISENATUREOFPURCHASE>
      <LEDGERFBTCATEGORY>&#4; Not Applicable</LEDGERFBTCATEGORY>
      <ISBILLWISEON>No</ISBILLWISEON>
      <ISCOSTCENTRESON>No</ISCOSTCENTRESON>
      <ISINTERESTON>No</ISINTERESTON>
      <ALLOWINMOBILE>No</ALLOWINMOBILE>
      <ISCOSTTRACKINGON>No</ISCOSTTRACKINGON>
      <ISBENEFICIARYCODEON>No</ISBENEFICIARYCODEON>
      <ISEXPORTONVCHCREATE>No</ISEXPORTONVCHCREATE>
      <PLASINCOMEEXPENSE>No</PLASINCOMEEXPENSE>
      <ISUPDATINGTARGETID>No</ISUPDATINGTARGETID>
      <ISDELETED>No</ISDELETED>
      <ISSECURITYONWHENENTERED>No</ISSECURITYONWHENENTERED>
      <ASORIGINAL>No</ASORIGINAL>
      <ISCONDENSED>No</ISCONDENSED>
      <AFFECTSSTOCK>No</AFFECTSSTOCK>
      <ISRATEINCLUSIVEVAT>No</ISRATEINCLUSIVEVAT>
      <FORPAYROLL>No</FORPAYROLL>
      <ISABCENABLED>No</ISABCENABLED>
      <ISCREDITDAYSCHKON>No</ISCREDITDAYSCHKON>
      <INTERESTONBILLWISE>No</INTERESTONBILLWISE>
      <OVERRIDEINTEREST>No</OVERRIDEINTEREST>
      <OVERRIDEADVINTEREST>No</OVERRIDEADVINTEREST>
      <USEFORVAT>No</USEFORVAT>
      <IGNORETDSEXEMPT>No</IGNORETDSEXEMPT>
      <ISTCSAPPLICABLE>No</ISTCSAPPLICABLE>
      <ISTDSAPPLICABLE>No</ISTDSAPPLICABLE>
      <ISFBTAPPLICABLE>No</ISFBTAPPLICABLE>
      <ISGSTAPPLICABLE>No</ISGSTAPPLICABLE>
      <ISEXCISEAPPLICABLE>No</ISEXCISEAPPLICABLE>
      <ISTDSEXPENSE>No</ISTDSEXPENSE>
      <ISEDLIAPPLICABLE>No</ISEDLIAPPLICABLE>
      <ISRELATEDPARTY>No</ISRELATEDPARTY>
      <USEFORESIELIGIBILITY>No</USEFORESIELIGIBILITY>
      <ISINTERESTINCLLASTDAY>No</ISINTERESTINCLLASTDAY>
      <APPROPRIATETAXVALUE>No</APPROPRIATETAXVALUE>
      <ISBEHAVEASDUTY>No</ISBEHAVEASDUTY>
      <INTERESTINCLDAYOFADDITION>No</INTERESTINCLDAYOFADDITION>
      <INTERESTINCLDAYOFDEDUCTION>No</INTERESTINCLDAYOFDEDUCTION>
      <ISOTHTERRITORYASSESSEE>No</ISOTHTERRITORYASSESSEE>
      <IGNOREMISMATCHWITHWARNING>No</IGNOREMISMATCHWITHWARNING>
      <USEASNOTIONALBANK>No</USEASNOTIONALBANK>
      <BEHAVEASPAYMENTGATEWAY>No</BEHAVEASPAYMENTGATEWAY>
      <OVERRIDECREDITLIMIT>No</OVERRIDECREDITLIMIT>
      <ISAGAINSTFORMC>No</ISAGAINSTFORMC>
      <ISCHEQUEPRINTINGENABLED>Yes</ISCHEQUEPRINTINGENABLED>
      <ISPAYUPLOAD>No</ISPAYUPLOAD>
      <ISPAYBATCHONLYSAL>No</ISPAYBATCHONLYSAL>
      <ISBNFCODESUPPORTED>No</ISBNFCODESUPPORTED>
      <ALLOWEXPORTWITHERRORS>No</ALLOWEXPORTWITHERRORS>
      <CONSIDERPURCHASEFOREXPORT>No</CONSIDERPURCHASEFOREXPORT>
      <ISTRANSPORTER>No</ISTRANSPORTER>
      <ISECASHLEDGER>No</ISECASHLEDGER>
      <USEFORNOTIONALITC>No</USEFORNOTIONALITC>
      <ISECOMMOPERATOR>No</ISECOMMOPERATOR>
      <OVERRIDEBASEDONREALIZATION>No</OVERRIDEBASEDONREALIZATION>
      <ISECDIFFINSDATE>No</ISECDIFFINSDATE>
      <SHOWINPAYSLIP>No</SHOWINPAYSLIP>
      <USEFORGRATUITY>No</USEFORGRATUITY>
      <ISTDSPROJECTED>No</ISTDSPROJECTED>
      <ISSALARYMULFILE>No</ISSALARYMULFILE>
      <FORSERVICETAX>No</FORSERVICETAX>
      <ISINPUTCREDIT>No</ISINPUTCREDIT>
      <ISEXEMPTED>No</ISEXEMPTED>
      <ISABATEMENTAPPLICABLE>No</ISABATEMENTAPPLICABLE>
      <ISSTXPARTY>No</ISSTXPARTY>
      <ISSTXNONREALIZEDTYPE>No</ISSTXNONREALIZEDTYPE>
      <USEFORKKC>No</USEFORKKC>
      <USEFORSBC>No</USEFORSBC>
      <ISUSEDFORCVD>No</ISUSEDFORCVD>
      <LEDBELONGSTONONTAXABLE>No</LEDBELONGSTONONTAXABLE>
      <ISEXCISEMERCHANTEXPORTER>No</ISEXCISEMERCHANTEXPORTER>
      <ISPARTYEXEMPTED>No</ISPARTYEXEMPTED>
      <ISSEZPARTY>No</ISSEZPARTY>
      <TDSDEDUCTEEISSPECIALRATE>No</TDSDEDUCTEEISSPECIALRATE>
      <ISECHEQUESUPPORTED>No</ISECHEQUESUPPORTED>
      <ISEDDSUPPORTED>No</ISEDDSUPPORTED>
      <HASECHEQUEDELIVERYMODE>No</HASECHEQUEDELIVERYMODE>
      <HASECHEQUEDELIVERYTO>No</HASECHEQUEDELIVERYTO>
      <HASECHEQUEPRINTLOCATION>No</HASECHEQUEPRINTLOCATION>
      <HASECHEQUEPAYABLELOCATION>No</HASECHEQUEPAYABLELOCATION>
      <HASECHEQUEBANKLOCATION>No</HASECHEQUEBANKLOCATION>
      <HASEDDDELIVERYMODE>No</HASEDDDELIVERYMODE>
      <HASEDDDELIVERYTO>No</HASEDDDELIVERYTO>
      <HASEDDPRINTLOCATION>No</HASEDDPRINTLOCATION>
      <HASEDDPAYABLELOCATION>No</HASEDDPAYABLELOCATION>
      <HASEDDBANKLOCATION>No</HASEDDBANKLOCATION>
      <ISEBANKINGENABLED>No</ISEBANKINGENABLED>
      <ISEXPORTFILEENCRYPTED>No</ISEXPORTFILEENCRYPTED>
      <ISBATCHENABLED>No</ISBATCHENABLED>
      <ISPRODUCTCODEBASED>No</ISPRODUCTCODEBASED>
      <HASEDDCITY>No</HASEDDCITY>
      <HASECHEQUECITY>No</HASECHEQUECITY>
      <ISFILENAMEFORMATSUPPORTED>No</ISFILENAMEFORMATSUPPORTED>
      <HASCLIENTCODE>No</HASCLIENTCODE>
      <PAYINSISBATCHAPPLICABLE>No</PAYINSISBATCHAPPLICABLE>
      <PAYINSISFILENUMAPP>No</PAYINSISFILENUMAPP>
      <ISSALARYTRANSGROUPEDFORBRS>No</ISSALARYTRANSGROUPEDFORBRS>
      <ISEBANKINGSUPPORTED>No</ISEBANKINGSUPPORTED>
      <ISSCBUAE>No</ISSCBUAE>
      <ISBANKSTATUSAPP>No</ISBANKSTATUSAPP>
      <ISSALARYGROUPED>No</ISSALARYGROUPED>
      <USEFORPURCHASETAX>No</USEFORPURCHASETAX>
      <AUDITED>No</AUDITED>
      <SORTPOSITION> 1000</SORTPOSITION>
      <ALTERID> 10511</ALTERID>
      <OPENINGBALANCE>0.00</OPENINGBALANCE>
      <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>
       <LANGUAGEID> 1033</LANGUAGEID>
      </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>
       <APPLICABLEFROM>20170401</APPLICABLEFROM>
      </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.

Contact us for data insertion into Tally.

Name: Apoorv Chaturvedi
email: support@turbodatatool.com;suppport@mndatasolutions.com
Phone:+91-8802466356
youtube channel:www.youtube.com\@mndatasolutions2042


Wednesday, 15 January 2025

Mobile app developers/analytic app developers-optimizing the incremental load from Tally-reduce the time by 99 percent through datawarehouse

 Problem statement: App developers have synced the initial data from Tally. Now incremental data is to be captured.  Here we discuss the approaches for transactions and masters for the same. We would also look at how the datawarehouse can help reduce the incremental times by more than 99%.


Load through odbc: in case the application works through odbc then one would need to do a full extract each time since the odbc represents a full snapshot. There are more than 160 odbc extracts in Tally. Hence the process of incremental extract would need to be handled in the following manner:

a.) Nightly load

b.) Load over the fortnight.



Load through xml: Many developers like to sync the reports directly through the xml. Complete voucher extraction could put in a lot of load on the system. Hence it is recommended that the extraction of the voucher be done on a day by day basis(https://mndatasolutionsindia.blogspot.com/2025/01/reducing-time-for-mobile-refresh-tally.html).

Difference between ledger vouchers and voucher register: It is to be noted for the developer that the complete sales movement is captured in the ledger vouchers(drill down from the sales accounts in Tally to ledger vouchers). Voucher register gives movement by voucher types[including inventory movements] and hence it could exclude the journal movements.


 



 




Ledgervouchers is a parameterized report by svcurrentcompany, svfromdate and svtodate.

 

Voucher register is movement by vouchertypes only[including inventory]. The sales numbers in the voucherregister might not match the voucherledger movement(all required movement types).

 

 

Alternatives for the developer:

 a.) Extract the profit and loss report by using the following xml-sample has repeatvariables also.

Thereafter based on the year month selected drill down to the required voucher based on lledgerprimary. Alternatively based on the svfromdate and svtodate and company name the request can be sent to Tally to retrive the required sales vouchers:

sample xml code added.


Here ledgername is the input parameter.



Bottleneck: to extract the voucher (from which voucherledger report is extracted) one could need to extract the same on a day by day basis so that Tally does not hang. The same process of synchronization could take a lot of time.

 

Process of reduction of execution time:

we recommend using a datawarehouse and using the event based activities in Tally to capture the changes in transaction and masters(https://mndatasolutionsindia.blogspot.com/2025/01/reducing-time-for-mobile-refresh-tally.html). This will capture the changes as the entries into the voucher or the entries into the masters are happening.


Note: RAM of atleast 16 GB is required.

Internet connection needs to be always on[else synchronization could not happen]

 

Concurrancy impact would need to checked. If there are a lot fo developers then we need to check the load on the datawarehouse.

The onsite cost of hosting the data onto the database also needs to be checked.

Our recommendation would be to load the data onto a desktop tool like Powerbi or desktop database like sql server and then sync with the mobile app.

 

 

 


Can event based activities be used with xml export: as far as we know the same cannot be done with the usage of embedded tdl code.


Contact us for a demo.


Name: apoorv chaturvedi

email:support@mndatasolutions.com;support@turbodataotol.com

Phone:+91-8802466356

 

youtube channel:www.youtube.com\@mndatassolutions2042

 




Reduce costs:Paying for each mobile number for Mobile access for ERP/Tally ERP data

 Problem statement: for many companies the number of internal mobile users for ERP/Tally ERP data on mobile are significant. Say the number ...