Exercise 8: Concatenate two fields

Here's a sample Excel sheet that contains two Columns of data. viz
a) Stock-Item Name (Column A)
b) PartNo (Column B)

Download the Excel sheet with XML tags for Exercise 8.

Did you notice that this Excel sheet is same as that we provided in Exercise 7. Yes, exactly same. If you had tried importing data into Tally using the Exercise 7 XML tags, only one record would be imported in Tally. The reason begin "Duplicate Stock-Item NAMEs in Column A".

The solution to the said problem is to concatenate (join) the two fields (Name and Part-Number).

CONCATENATING FIELDS

As you can see in the current Excel sheet, the Column A contains "Filter" in all the 5 rows, though the Part-no is different. This is common with the Automobile Industry wherein PART-NUMBERs are always unique, whereas the PART-NAME's are may be repeated. However, Tally Software requires both the Stock-Item Name and Part-Number to be unique. As a result, we need to concatenate (join) these two fields to generate new UNIQUE Stock-Item NAME.

How to concatenate (join) two fields or cells in MS-Excel ?

Example:-
Cell A1 contains "Bush"
Cell B1 contains "S101"

Now, try out writing this formula =+A1 & " " &+B1 in Cell C1. The result you will get in Cell C1 will be "Bush S101".

Well, this is a simple Excel formula. For those are not familiar with Excel formula's will have to spend some-time experimenting the same or Google for "Excel Formula"

Now, let's use this formula in our XML tags. The tags are given below for your reference:-

XML Tags for Exercise 8

<XMLTAGS CELLREFERENCE="A1" xmlns:UDF="TallyUDF">
<MASTER TYPE="STOCKITEM">
<NAME.LIST>
<NAME FORMULA="=+A# &amp; &quot; &quot; &amp; +B#"/>
</NAME.LIST>
<PARENT/>
<BASEUNITS/>
<ADDITIONALNAME COLUMNREFERENCE="B"/>
</MASTER>
</XMLTAGS>

Now, let's try to understand the Formula that is used in the above tags.

XML tag with the FORMULA attribute
<NAME FORMULA="=+A# &amp; &quot; &quot; &amp; +B#"/>

Have you started pulling you hair or feel doing so. Well, please be patient and devote few minutes that may be helpful in building a strong-base.

a) There are some special characters in XML and which are to be written in a different way. These characters are called ESCAPE SEQUENCES and are given below:-

Special Character: & (ampersand sign)
Escape Sequence: &amp;

Special Character: " (quote)
Escape Sequence: &quot;

For a complete list of ESCAPE sequences, please refer FAQ: XML Escape Sequences

b) Simply substitute the special characters with their escape sequences in your Excel Formula.
c) Next, substitute the Row.no in your Excel formula with # (This is UDIMagic specific).
d) The Excel Formula and what we have used in XML tags are given below for reference:-

Excel Formula
=+A2 & " " & +B2

Formula used in XML tags
=+A# &amp; &quot; &quot; &amp; +B#

---