Exercise 12

Sales Vouchers Without Inventory With Multiple Ledgers

Published 9 Oct 2026

---

This exercise teaches the GROUPBY attribute. It imports Sales Vouchers (without stock items) where one invoice can have several rows and several Sales Ledgers. Rows with the same Invoice Number become one voucher, and GROUPBY combines them into one credit per Sales Ledger. Unlike SCROLL, which repeats a block for every row, GROUPBY merges rows with the same key into a single block.

The Excel sheet

---

Step 1 — Create the masters

The voucher refers to two kinds of ledgers: the Party Ledger (Column C) and the Sales Ledger (Column D). udiMagic processes MASTER tags before VOUCHER tags, so any ledger that doesn't exist yet in Tally is created in the same import.

<!-- Create Party Ledger Masters from Column C -->
<MASTER TYPE="LEDGER">
  <NAME.LIST>
    <NAME COLUMNREFERENCE="C"/>
  </NAME.LIST>
  <ADDITIONALNAME COLUMNREFERENCE="C"/>
  <PARENT>Sundry Debtors</PARENT>
  <ISBILLWISEON>Yes</ISBILLWISEON>
  <AFFECTSSTOCK>No</AFFECTSSTOCK>
  <ISCOSTCENTRESON>No</ISCOSTCENTRESON>
</MASTER>

<!-- Create Sales Ledger Masters from Column D -->
<MASTER TYPE="LEDGER">
  <NAME.LIST>
    <NAME COLUMNREFERENCE="D"/>
  </NAME.LIST>
  <PARENT>Sales Accounts</PARENT>
  <ISBILLWISEON>No</ISBILLWISEON>
  <ISCOSTCENTRESON>No</ISCOSTCENTRESON>
  <AFFECTSSTOCK>No</AFFECTSSTOCK>
  <USEFORVAT>No</USEFORVAT>
</MASTER>

Step 2 — Declare the key column and group the rows

CELLREFERENCE="A1" on XMLTAGS declares Column A (Invoice No.) as the key field, so all consecutive rows with the same Invoice No. go into one voucher. Inside the voucher, GROUPBY on ALLLEDGERENTRIES.LIST takes a column or an expression (see udiMagic XML tags). Rows with the same value are merged into one ledger entry, and their Amounts are added together.

Here's how the GROUPBY attribute is used in this exercise:

<XMLTAGS CELLREFERENCE="A1" xmlns:UDF="TallyUDF">
  ...OtherTags...
  <!-- GroupBy Sales Ledger -->
  <ALLLEDGERENTRIES.LIST GROUPBY="=D#">
    ...OtherTags...
  </ALLLEDGERENTRIES.LIST>
</XMLTAGS>

This gives the following vouchers:

Invoice No.	Excel rows	Party debit	Sales Ledger credits
1001	2–4	450	SALES-SILK 250 (100 + 150), SALES-MIX 200
1002	5	250	SALES-MIX 250
1003	6–7	350	SALES-SILK 150, SALES-MIX 200
1004	8	500	SALES-SILK 500

Step 3 — Write the Voucher tags

The first ALLLEDGERENTRIES.LIST debits the Party with the total of the invoice. The second one credits each Sales Ledger with the total of its rows (GROUPBY="=D#"). Invoice 1001 therefore has one Party entry but two Sales Ledger entries, even though SALES-SILK appears twice.

<!-- One Sales Voucher per invoice number in Column A -->
<VOUCHER>
  <GUID FORMULA="=+&quot;udi-SVML-FIXED-&quot; &amp; A#"/>
  <DATE COLUMNREFERENCE="B"/>
  <EFFECTIVEDATE COLUMNREFERENCE="B"/>
  <VOUCHERTYPENAME>Sales</VOUCHERTYPENAME>
  <VOUCHERNUMBER COLUMNREFERENCE="A"/>
  <REFERENCE COLUMNREFERENCE="A"/>
  <NARRATION COLUMNREFERENCE="E"/>
  <ISINVOICE>No</ISINVOICE>

  <!--
    Party debit: 
    Debit amount is negative and ISDEEMEDPOSITIVE is Yes.
  -->
  <ALLLEDGERENTRIES.LIST>
    <LEDGERNAME COLUMNREFERENCE="C"/>
    <ISDEEMEDPOSITIVE>Yes</ISDEEMEDPOSITIVE>
    <AMOUNT FORMULA="={Round(F#,2)*-1}"/>
    <BILLALLOCATIONS.LIST SKIP="=Len(trim(A#))=0">
      <NAME COLUMNREFERENCE="A"/>
      <BILLTYPE>New Ref</BILLTYPE>
      <AMOUNT FORMULA="={Round(F#,2)*-1}"/>
    </BILLALLOCATIONS.LIST>
  </ALLLEDGERENTRIES.LIST>

  <!--
    Sales credits: GROUPBY=D# groups rows by Sales Ledger within the voucher.
    udiMagic sums the AMOUNT formula for each grouped Sales Ledger.
    Credit amount is positive and ISDEEMEDPOSITIVE is No.
  -->
  <ALLLEDGERENTRIES.LIST GROUPBY="=D#">
    <LEDGERNAME COLUMNREFERENCE="D"/>
    <ISDEEMEDPOSITIVE>No</ISDEEMEDPOSITIVE>
    <AMOUNT FORMULA="={Round(F#,2)}"/>
  </ALLLEDGERENTRIES.LIST>
</VOUCHER>

---