[go: up one dir, main page]
Scribd
Upload
2K views18 pages

Tally ERP 9 To SQL Server

The document discusses transferring data from Tally ERP 9 to a SQL Server database using the DX-Fusion utility. It provides steps to download and install DX-Fusion, create a database in SQL Server, specify the connection string in DX-Fusion, send XML requests from a programming language like VB to DX-Fusion to export Tally masters and vouchers, and retrieve the data in the SQL Server database in real-time.

Uploaded by

apoorvgupta29
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2K views18 pages

Tally ERP 9 To SQL Server

The document discusses transferring data from Tally ERP 9 to a SQL Server database using the DX-Fusion utility. It provides steps to download and install DX-Fusion, create a database in SQL Server, specify the connection string in DX-Fusion, send XML requests from a programming language like VB to DX-Fusion to export Tally masters and vouchers, and retrieve the data in the SQL Server database in real-time.

Uploaded by

apoorvgupta29
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 18

DX-Fusion - Tally to database/MQSQL/EXCEL/XML

DX-Fusion (Free Edition) is an utility that transfers data from Tally ERP 9 into your database. It transfers all masters and all vouchers without any restriction on date or voucher-type or number of records. You can get more details about DX-fusion at http://www.rtslink.com/dxfusion/index.html The DX-fusion (Commercial Editions) supports transfer of all masters and vouchers from Tally into your database in Real-time (online mode).
Tally ERP 9 to SQL Server
Preface
This document is for Software developers who wish to get data from Tally ERP 9 into their database using VB6, VB.NET, C# or any other programming language. The sample program provided underneath is written in VB6. It uses the free utility DX-Fusion to get the Tally ERP 9 data into SQL Server. However, with some minor modifications (i.e. connection string) you can get the data in any other database.

Getting data from Tally ERP 9 into SQL Server using VB / VB.NET / C# / PHP etc
It is presumed that you have already downloaded and installed the DX-Fusion Free Edition utility. Steps :1) Create a new database named 'Tally' in SQL Server. 2) Specify proper connection string in 'c:\wamp\www\dx-fusion\dx-fusion.ini file' to connect to your SQL Server database. 3) Start Tally ERP 9 and open your Company which contains some data. 4) Create a program in VB6 (or any other programming language of your choice). 5) Place a button on the Form / Window. 6) In the button click event, use HTTP Post to send a post-xml-request to the DX-Fusion Server at http://127.0.0.1:90/dx-fusion/upload.php 6a) XML request to be sent to DX-Fusion Server (for Masters):

<!-- This is the request format which must be sent to the DX-FUSION server -> <!-- You must specify proper values for tags given below --> <ENVELOPE> <HEADER> <TALLYREQUEST>Export Data</TALLYREQUEST> </HEADER> <BODY> <EXPORTDATA> <REQUESTDESC> <STATICVARIABLES> <!-- Specify the MASTER Type here -->

<RTS_SVMASTERTYPE>GROUP</RTS_SVMASTERTYPE> <!-- Specify the MASTER Name here --> <RTS_SVMASTERNAME>Fixed Assets</RTS_SVMASTERNAME> <!-- Specify the ACTION here --> <RTS_SVACTIONTYPE>CREATE</RTS_SVACTIONTYPE> <!-- Must be set to Yes always --> <REDIRECTREQUESTTOTALLY>YES</REDIRECTREQUESTTOTALLY> <!-- If the Tally Server and DX-Fusion Server are on the same machine --> <!-- then keep 127.0.0.1 else specify the IP address for Tally Server --> <TALLYSERVERIP>127.0.0.1</TALLYSERVERIP> <TALLYSERVERPORT>9000</TALLYSERVERPORT> </STATICVARIABLES> <REPORTNAME>RTSExportData</REPORTNAME> </REQUESTDESC> </EXPORTDATA> </BODY> </ENVELOPE>
6b) XML request to be sent to DX-Fusion Server (for Vouchers):

<!-- This is the request format which must be sent to the DX-FUSION server -> <!-- You must specify proper values for tags given below --> <ENVELOPE> <HEADER> <TALLYREQUEST>Export Data</TALLYREQUEST> </HEADER> <BODY> <EXPORTDATA> <REQUESTDESC> <STATICVARIABLES> <!-- Record-type --> <RTS_SVMASTERTYPE>VOUCHER</RTS_SVMASTERTYPE> <!-- Specify the MASTERID here --> <RTS_SVMASTERID>1</RTS_SVMASTERID> <!-- Specify the ACTION here --> <RTS_SVACTIONTYPE>CREATE</RTS_SVACTIONTYPE> <!-- Must be set to Yes always --> <REDIRECTREQUESTTOTALLY>YES</REDIRECTREQUESTTOTALLY> <RTS_SVMULTIPLERECORDS>No</RTS_SVMULTIPLERECORDS> <!-- If the Tally Server and DX-Fusion Server are on the same machine --> <!-- then keep 127.0.0.1 else specify the IP address for Tally Server --> <TALLYSERVERIP>127.0.0.1</TALLYSERVERIP>

<TALLYSERVERPORT>9000</TALLYSERVERPORT> </STATICVARIABLES> <REPORTNAME>RTSExportData</REPORTNAME> </REQUESTDESC> </EXPORTDATA> </BODY> </ENVELOPE>

DX- Fusion
Product : DX-Fusion Platform : Windows 2000 and above Tally : Tally ERP 9 Release 1.61 and above Category : Utility to transfer data from Tally Software into RDBMS (database) in real-time.

An Introduction to DX-Fusion
DX-Fusion is an utility that transfers data from Tally Accounting Software into your database in real-time. It supports MS-SQL Server, MS-Access, MySQL, Oracle and many other popular databases. DX-Fusion works with Tally ERP 9 Release 1.61 and higher versions. It can retrieve data from Tally in both online (real-time) and off-line modes. DX-Fusion utility does NOT require any coding (except for a connection-string to connect to the database) and can be used by developers and end-users both. Developers can use this data for designing of MIS reports and Integration with other external softwares.

DX-Fusion Editions
Free Edition A fully functional edition of DX-Fusion utility which supports transfer of all masters and vouchers data from Tally Sofware into your database without any restriction on number of entries or any date restriction. It supports off-line mode only. Standard Edition A software utility for transferring masters and vouchers data from Tally Accounting Software into your database in Real-time (online mode).

Download DX-Fusion (Free Edition) Getting Started DX-Fusion - Getting Started FAQs Frequently Asked Questions Tally ERP 9 to SQL Server Getting data from Tally ERP 9 into SQL Server Editions FREE Edition Vs Standard Edition Blog http://dx-fusion.blogspot.com

Tally Customization with Text File

No need of Tally Developer 2.7 to compile the files - You can use plain TXT files rather than the TCP files for Tally Customization

In simple words, you can create plain TXT files (using any editor like Notepad) and customize Tally Accounting Software. It works with Tally 7.2 and Tally 9. It even works with the educational version of Tally. Other users may try it own their own. And now here's what you need to do. STEPS:1) Create a TXT file using Notepad with the following TEXT. [#Menu: Item: [Menu: Item: Item: Item: MyOption2 My Gateway Option of Menu Tally] MySubMenu MySubMenu] MyOption1 MyOption2

2)

Save

the

file

as

TEST1.TXT

in

your

Tally

folder

(C:\TALLY)

3) Open the file Tally.ini using Notepad which is located in the Tally folder and make the following changes:User TDL = C:\TALLY\TEST1.TXT TDL = Yes

4) Save the Tally.ini file and then run the Tally Software. 5) A new menu-option will appear in the "Gateway of Tally" screen at the end. To learn more about TDL programming, visit tdlplayground

TDL Functions
An Alphabetical-list of TDL functions (Partial list) is provided herewith for your reference.
Function $$Abs Syntax $$Abs : <Expression> Return Value Same as parameter type Remarks Returns the absolute (positive) value for the given expression $$Alias returns alternate value for the current Object. Stock Item and Ledger have Alias name. Example:Fetch Ledger-Alias using SQLRequest() Checks whether the current user has permission to access the specified object or report. $$CollectionList function is used to fetch the nth Method/field of a Collection. Sample program using $$CollectionList function Returns the Period starting date Returns the Period ending date $$FullList function is used to fetch any Method/Field of a Collection. The Method/Field values are separated by commas. Returns true if the selected mode is Print, otherwise false. Refer program TDL42 Checks whether the specified VoucherType is Contra Checks whether the specified VoucherType is CreditNote Checks whether the specified VoucherType is DebitNote Checks whether the specified VoucherType is Delivery Note Checks whether the parameter passed is empty or not. Parameter type can be String; Numeric; Date; Logical $$IsEmpty $$IsEmpty: <parameter> Logical Example:$$IsEmpty: $$Value Refer program TDL34 $$IsFirstObject $$IsFirstObject $$IsJournal : <VoucherTypeName> $$IsMemo : <VoucherTypeName> $$IsPayment : <VoucherTypeName> $$IsPhyStock : <VoucherTypeName> Logical Returns true if the current Object is the 1st object of the Collection. Refer program TDL42 Checks whether the specified VoucherType is Journal Checks whether the specified VoucherType is Memo Checks whether the specified VoucherType is Payment Voucher Checks whether the specified VoucherType is PhysicalStock Voucher

$$Alias

$$Alias

String

$$Allow

$$Allow : <mode> : <familyreport-name>

Logical

$$CollectionList

$$CollectionList: <Method/Field> : Method/ Field <PositionNumber> : Datatype <Collection> $$DateFrom $$DateTo $$FullList : <Collection> : <FieldList> Date Date Method/ Field Datatype

$$DateFrom $$DateTo

$$FullList

$$InPrintMode

$$InPrintMode $$IsContra : <VoucherTypeName> $$IsCreditNote : <VoucherTypeName> $$IsDebitNote : <VoucherTypeName> $$IsDelNote : <VoucherTypeName>

Logical

$$IsContra $$IsCreditNote $$IsDebitNote $$IsDelNote

Logical Logical Logical Logical

$$IsJournal $$IsMemo $$IsPayment $$IsPhyStock

Logical Logical Logical Logical

$$IsPurchase $$IsPurcOrder $$IsRcptNote $$IsReceipt $$IsRejIn $$IsRejOut $$IsRevJrnl $$IsSales $$IsSalesOrder $$IsStockJournal

$$IsPurchase : <VoucherTypeName> $$IsPurcOrder : <VoucherTypeName> $$IsRcptNote : <VoucherTypeName> $$IsReceipt : <VoucherTypeName> $$IsRejIn : <VoucherTypeName> $$IsRejOut : <VoucherTypeName> $$IsRevJrnl : <VoucherTypeName> $$IsSales : <VoucherTypeName> $$IsSalesOrder : <VoucherTypeName> $$IsStockJournal : <VoucherTypeName>

Logical Logical Logical Logical Logical Logical Logical Logical Logical Logical

Checks whether the specified VoucherType is Purchase Voucher Checks whether the specified VoucherType is Purchase Order Voucher Checks whether the specified VoucherType is ReceiptNote Voucher Checks whether the specified VoucherType is Receipt Voucher Checks whether the specified VoucherType is RejectionIn Voucher Checks whether the specified VoucherType is RejectionOut Voucher Checks whether the specified VoucherType is Reverse Journal Voucher Checks whether the specified VoucherType is Sales Voucher Checks whether the specified VoucherType is Sales Order Voucher Checks whether the specified VoucherType is StockJournal Voucher

Function $$LocaleString

Syntax $$LocaleString: <string>

Return Value Unicode string

Remarks Accepts a string parameter and returns unicode string. This is to provide multilingual support to Tally Checks whether the Field value of the <Field> is equal to the <ExpC> Example: $$IsVchTypeOfFamily: $VoucherTypeName : "Payment" Remarks: The function $$IsVchTypeOfFamily returns true if the Field-value of $VoucherTypeName is "Payment" Returns the Date as per your computer system settings

$$IsVchTypeOf Family

$$IsVchTypeOfFamily :<VoucherTypeName>

Logical

$$MachineDate

$$MachineDate

Date Example:Select $$MachineDate from Company Returns the Time as per your computer system settings

$$MachineTime

$$MachineTime

String Example:Select $$MachineTime from Company Returns the Miniimum value. The parameter type can be Numeric, Date or Same as String parameter type Example:$$Max:100:15 returns 100 Returns the Miniimum value. The parameter type can be Numeric, Date or Same as String parameter type Example:$$Min:100:15 returns 15

$$Max

$$Max : <Expression>

$$Min

$$Min : <Expression>

Returns the ending date of the specified month. $$MonthEnd $$MonthEnd: <ExpD> Date Example:Select $$MonthEnd:$$MachineDate from Company Returns the Month (example 12) of the specified date $$MonthOfDate $$MonthOfDate:<ExpD> Numeric Example:Select $$MonthOfDate:$$MachineDate from Company Returns the starting date of the specified month. $$MonthStart $$MonthStart: <ExpD> Date Example:Select $$MonthStart:$$MachineDate from Company $$Round function is used to round-off a value Example:$$Round : 125.72 : 0.10 returns 125.70 $$Round function is used to round-off a value Example:$$Round : 125.72 : 0.10 returns 125.70 $$Round function is used to round-off a value Example:$$Round : 125.72 : 0.10 returns 125.80 Returns the number of companies currently open in Tally Software. Refer Example at www.rtslink.blogspot.com Returns the Tally Serial Number. Refer example at www.rtslink.blogspot.com Converts numeric expression into string. Example:[Field: My Field] Set as : $$String:500 Returns the length of the string parameter Example:[Field: My Field] Set as: $$StringLength:"Shweta Softwares" Used to extract a sub-string from another string. Example:[Field: My Field] Set as: $$StringPart:"Shweta Softwares":7:9 Remarks:This will return "Computers".i.e. beginning at the 8th character, it returns the next 9 characters. Please note that the 1st character is numbered as 0. $$StringRemWord $$StringRemWord: <Index>: <ExpC> String Returns the remaining word from the <ExpC> string after the Index.

$$Round

$$Round : <ExpN> : <RoundLimit>

Numeric

$$RoundDown

$$RoundDown : <ExpN> : <RoundLimit>

Numeric

$$RoundUp

$$RoundUp : <ExpN> : <RoundLimit>

Numeric

$$SelectedCmps

$$SelectedCmps

Numeric

$$SerialNumber

$$SerialNumber

String

$$String

$$String: <ExpN>

String

$$StringLength

$$stringLength: <ExpC>

Numeric

$$StringPart

$$StringPart: <expC>: <StartPos>: <Length>

String

Example:$$StringRemWord:2:"Shweta Softwares Gandhidham" This returns the word "Gandhidham". i.e. the word after 2 words as specified by the Index Returns the word specified by the Index. $$StringWord: <Index>: <ExpC> Example:$$StringWord:2:"Shweta Softwares Gandhidham" This returns the word "Computers". i.e. the 2nd word as specified by the Index Specifies the System-name for the reserved string Returns the value for the System variable SVFROMDATE $$SystemPeriod From $$SystemPeriodFrom Date Example:Select $$SystemPeriodFrom from Company or Select ##SVFROMDATE from Company Returns the value for the System variable SVTODATE $$SystemPeriodTo $$SystemPeriodTo Date Example:Select $$SystemPeriodTo from Company or Select ##SVTODATE from Company Converts the string to upper-case Returns the value of a field/method. Example:[Field: My Field] Use: Name Field Validate: NOT $$IsEmpty:$$Value In the above code, function $$Value is used which returns the value of the field 'My Field'. Refer program TDL34 Returns the Year for the specified Date. Example:Select $$YearOfDate:$$MachineDate from Company The above returns the year (.i.e 2007) Returns the Year end-date for the Current year (based on the Date passed as parameter) $$YearEnd $$YearEnd: <ExprD> Date Example:Select $$YearEnd:##SVTODATE from Company The above returns the year-end date (ex. in my system it returns 31-3-2008). $$ZeroFIll $$ZeroFill : <ExpC> : <ExpN> String $$Zero pads the given string with zeros. Example:$$Zero : "525" : 5 returns "00525"

$$StringWord

String

$$SysName

$$SysName: <reserved-string>

String

$$Upper

$$Upper: <ExpC>

String

$$Value

$$Value

Method/Field data type

$$YearOfDate

$$YearOfDate: <ExpD>

Numeric

You can test check the TDL functions by writing TDL code or by using WRAPPER.EXE provided with RTSlink DLL. STEPS for using WRAPPER.EXE to try out the TDL functions:1) Create a plain text file (say SQL1.TXT) using notepad which contains the following text:SELECT $$Max:100:15 from Company

2) Save the file in C:\RTSlink folder. 3) Go to DOS prompt and change to C:\RTSlink folder. (It is assumed that you have already downloaded and installed RTSlink DLL) 4) Type the following command:Wrapper <FileName> /SQL

5) This will generate response in a file named RESPONSE.LOG Tags:

Tally & Excel:


Cash Receipt Vouchers Here's a Excel sheet that contains "Cash Receipt Vouchers"

Sales Vouchers without Items Here's a sample Excel sheet that contains Sales Vouchers (without Items). As this is the first exercise (for Vouchers), we have purposely taken up a simple Excel sheet.

Here's what the sample Excel sheet looks like. Column A - Invoice No Column B - Invoice Date Column C - Party Name Column D - Sales Ledger Column E - Narration Column F - Invoice Amount XML tags for XML tags Explanation 1) First, you must write XML tags to create Masters (i.e. Party Ledgers and Sales Ledgers) . As you can see in the Excel sheet snapshot, the Column C contains Party Names whereas the Column D contains the Sales Ledgers. The XML tags to create Ledger Masters look like this :<MASTER TYPE="LEDGER"> ...... tags...... </MASTER> Remarks:a. Please refer the XML tags file for complete tags. You can also view it online by clicking on the link given above. b. UDIMagic first processes the MASTER tags and then the VOUCHER tags. 2. You can get the Tally supported tags for Vouchers from Tally Software itself. All you need to do is to manually enter the sample data (only 1 voucher) as given in the Excel sheet into the Tally Software. Next, export it in XML format using option "Gateway of Tally >> Display >> Daybook >> Alt+E". This will generate the tags in Daybook.xml file. You can open/view these tags in IE. In the Daybook.xml file, refer to the tags that appear within the <TALLYMESSAGE> tag and try relating the same with the tags as given in the XML tags for this exercise.

Welcome to the world of VBA / Macros VBA or Visual Basic for Applications is a light weight programming language (quite similar to Visual Basic) that allows automation of tasks in MS-Office Applications. UDI Magic v3.0 or higher supports the use of VBA. Using VBA, you can sort data; perform calculations; insert or delete columns or rows in MS-Excel at run-time. In simple words, VBA allows you to do almost any task that you can manually do in MS-Office Applications. In this exercise, we shall take up "How to use VBA (Visual Basic for Applications) with UDI Magic ?" STEP 1: Creating a macro file To begin with, we shall write a simple macro that displays a message "Hello World". You can use Notepad (or any text-editor) to create macro files. Here's a simple macro file (Exercise-15-macro.txt) which displays a message.
Sub HelloWorld MsgBox "Hello World !!!" End Sub

Remarks:Sub...End Sub is used to declare a procedure / routine. Before we write XML tags for importing data from our Excel sheet into Tally, let's have a look at the UDI Magic supported XML tags for loading and executing macros. XML tags for Loading / executing the Macro file Syntax:<VBA LOADFILE="macro-filename-with-path" RUNMACROATSTARTUP="macro-name"/>

Example 1:<VBA LOADFILE="exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>

Example 2:<VBA LOADFILE="c:\exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>

Explanation:1) In the first example, we have not specified the full-path for the macro-file. Hence, UDIMagic searches for the macro-file in the UDI Magic startup folder. In the second exmple, we have specified the full-path for the macro-file.

2) The tag <VBA> is UDI Magic specific. 3) The attribute LOADFILE="macro-filename" is used to specify the macro-filename. 4) The attribute RUNMACROATSTARTUP is used to specify the macro name which is to be executed at startup. 5) The Macro-file is executed before the Excel sheet data is processed. As a result,the macro used in this exercise displays a message "Hello World". STEP 2: Create an Excel Sheet and write XML tags for it To make the task simple, we shall take up an Excel sheet with Stock Item Masters records (having only one field i.e. the NAME field). For your reference, the XML tags given below:<XMLTAGS CELLREFERENCE="A1" xmlns:UDF="TallyUDF"> <!-- This loads and processes the macro-file --> <VBA LOADFILE="exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/> <!-- Create StockItem Masters --> <MASTER TYPE="STOCKITEM"> <NAME.LIST> <!-- Fetch the value for NAME tag from Column A of Excel Sheet --> <NAME COLUMNREFERENCE="A"/> </NAME.LIST> </MASTER> </XMLTAGS>

Import data into Multiple Companies simultaneously Here's an Excel sheet that contains Stock Item master records. The XML tags for this Excel sheet have been written such that the data is imported into two companies simultaneously. Follow these steps to import data into Tally 1) Create a company named "Company A" in Tally. 2) Create another company named "Company B" in Tally. 3) Next, Minimize Tally software. Make sure that both the companies are open in Tally. 4) Run UDI Magic software and select option "Excel to Tally". 5) Next, select option "Masters".

6) Next, select the Excel sheet and the XML tags file. You must select the XML tags file in the same screen in which you are prompted to select the Excel file. 7) Follow the wizard instructions to import data into Tally. Understanding the XML tags The XML tags used in this exercise are quite simple. We have already written such tags (for Stock Item Master) in our previous exercises. Now, let's see how do we import data into multiple companies... STEP 1 Write XML tags to create STOCKGROUP masters <!--This will create StockGroup Masters in Company A --> <MASTER TYPE="STOCKGROUP" COMPANYNAME="Company A"/> <NAME.LIST> <!--Fetch StockGroup Name from Column B of the Excel Sheet--> <NAME COLUMNREFERENCE="B"/> <NAME.LIST> </MASTER> <!--This will create StockGroup Masters in Company B --> <MASTER TYPE="STOCKGROUP" COMPANYNAME="Company B"/> <NAME.LIST> <!--Fetch StockGroup Name from Column B of the Excel Sheet--> <NAME COLUMNREFERENCE="B"/> <NAME.LIST> </MASTER> Remarks:1) Note that COMPANYNAME attribute is used to specify in which Company the data is to be imported. 2) We used the same tags twice, only the Company Name has been changed. STEP 2 Write XML tags to create STOCKITEM masters <!-- Create STOCKITEM Masters in Company A--> < MASTER TYPE="STOCKITEM" COMPANYNAME="Company A"> <NAME.LIST> <!-- Fetch the value for NAME tag from Column A of Excel Sheet --> <NAME COLUMNREFERENCE="A"/> </NAME.LIST>

<!-- StockGroup Name is to be taken from Column B--> <PARENT COLUMNREFERENCE="B"/> </MASTER> <!-- Create STOCKITEM Masters in Company B--> < MASTER TYPE="STOCKITEM" COMPANYNAME="Company B"> <NAME.LIST> <!-- Fetch the value for NAME tag from Column A of Excel Sheet --> <NAME COLUMNREFERENCE="A"/> </NAME.LIST> <!-- StockGroup Name is to be taken from Column B--> <PARENT COLUMNREFERENCE="B"/> </MASTER> Using the COMPANYNAME attribute To import data into a specific Company in Tally, you need to use the COMPANYNAME attribute. Generally, this is required when two or more companies are open in Tally and you wish to import data into a specific company. Herein, we shall take a simple Excel sheet that contains Stock Item Masters. For simplicity, we have taken only the Stock Item NAME in the Excel Sheet. Follow these steps to import data into Tally 1) Create two Companies in Tally. viz "Company A" and "Company B" 2) Select the "Company A". 3) Minimize Tally software. 4) Run UDIMagic and follow instructions in the wizard to Import data from the sample Excel-sheet. You must select the XML tags file in the same screen in which you are prompted to select the Excel file. If everything goes fine, then 5 Stock Item master records will be imported into "Company B" in Tally.You can view the same using "Gateway of Tally >> Inventory Info >> Stock Item >> Display" option. Attribute : COMPANYNAME Applies to: MASTER and VOUCHER tags Example 1:<MASTER TYPE="LEDGER" COMPANYNAME="MyCompany"> .....XML tags..... </MASTER>

Example 2:<VOUCHER COMPANYNAME="MyCompany"> .....XML tags..... </VOUCHER> Remarks:1) The COMPANYNAME attribute can be used with the MASTER and VOUCHER tags only. 2) If there two or more MASTER tags (or VOUCHER tags) in a single XML file, then you need to use the COMPANYNAME attribute with each MASTER tag.

Using the SCROLL attribute In this exercise, we shall take up an Excel sheet that contains Stock Item Master records with Standard Cost and Standard Price details. As the data for a single MASTER record may span to multiple rows, we have used the SCROLL attribute which is described later on. Here's a snapshot of the Excel sheet that we shall be using in this exercise. You can create a similar sheet

STEP 1 Write XML tags to create UNIT masters <MASTER TYPE="UNIT"/> <NAME COLUMNREFERENCE="D"/> <ISSIMPLEUNIT>Yes</ISSIMPLEUNIT> </MASTER>

STEP 2 Write XML tags to create STOCKGROUP masters <MASTER TYPE="STOCKGROUP"/> <NAME.LIST> <NAME COLUMNREFERENCE="C"/> <NAME.LIST> </MASTER> STEP 3 Write XML tags to create Stock Item Masters <!-- Create STOCKITEM Masters --> < MASTER TYPE="STOCKITEM"> <NAME.LIST> <!-- Fetch the value for NAME tag from Column A of Excel Sheet --> <NAME COLUMNREFERENCE="A"/> <!-- ALIAS to be taken from Column B of Excel Sheet --> <NAME COLUMNREFERENCE="B"/> </NAME.LIST> <!-- StockGroup Name is to be taken from Column C--> <PARENT COLUMNREFERENCE="C"/> <!-- BaseUnits is to be taken from Column D--> <BASEUNITS COLUMNREFERENCE="D"/> <!-- Standard Cost details --> <STANDARDCOSTLIST.LIST SCROLL="Yes"> <DATE COLUMNREFERENCE="E"/> <RATE FORMULA="=+F# &amp; &quot;/&quot; &amp; +G#"/> </STANDARDCOSTLIST.LIST> <!-- Standard Price details --> <STANDARDPRICELIST.LIST SCROLL="Yes"> <DATE COLUMNREFERENCE="H"/> <RATE FORMULA="=+I# &amp; &quot;/&quot; &amp; +J#"/> </STANDARDPRICELIST.LIST> </MASTER>

a) As you can see in the Excel sheet, the Standard Cost and Standard Price data for the first Master Record "BunnyBT1" spans to 3 rows. Also note that the Column A (key-field) contains the same Name i.e. "BunnyBT1" as all these 3 rows are part of one MASTER record. b) Example:<STANDARDCOSTLIST.LIST SCROLL="Yes"> <DATE COLUMNREFERENCE="E"/> <RATE FORMULA="=+F# &amp; &quot;/&quot; &amp; +G#"/> </STANDARDCOSTLIST.LIST> The SCROLL attribute used in the above XML tags, instructs UDI Magic that the data spans to multiple rows. c) Use option "Alter Standard Rates" to specify the Standard Cost and Price details in the Stock Item Master as shown below:-

Exercise 11 Alternative 1 Change the Heading in Cell A1 to NAME

Alternative 2 Add the following tags in your XML tags file :<COLUMNNAME.LIST> <COLUMNNAME>NAME</COLUMNNAME> </COLUMNNAME.LIST> Remarks:1) Tally use the Master's NAME as the key-field to uniquely identify the Master records. Hence, we must have a Column titled as NAME or specify the same using <COLUMNNAME> tag.

You might also like