Wednesday, September 25, 2013

Understanding new features in v5.0

SCROLLHORIZONTALLY

The SCROLLHORIZONTALLY attribute is like a Looping contruct / mechanism supported in programming languages. In other words, it allows you to loop through the Excel sheet Columns.

To understand what this attribute is, let's have a look at some tags required to create Ledger Masters (in v4.0 or lower) based on values given in Header Row in Excel Sheet.

XML tags required in UDIMagic v4.0 or lower versions
<!-- Create a Ledger Master based on value given in cell F1 -->
<MASTER TYPE="LEDGER" SINGLERECORD="YES">
<NAME.LIST>
<NAME COLUMNREFERENCE="$F$1">
</NAME>
<PARENT>Indirect Expenses</PARENT>
</MASTER>

<!-- Create a Ledger Master based on value given in cell G1 -->
<MASTER TYPE="LEDGER" SINGLERECORD="YES">
<NAME.LIST>
<NAME COLUMNREFERENCE="$G$1">
</NAME>
<PARENT>Indirect Expenses</PARENT>
</MASTER>

<!-- Create a Ledger Master based on value given in cell H1 -->
<MASTER TYPE="LEDGER" SINGLERECORD="YES">
<NAME.LIST>
<NAME COLUMNREFERENCE="$H$1">
</NAME>
<PARENT>Indirect Expenses</PARENT>
</MASTER>

Remarks:-
1) In earlier versions (v4.0 or lower), the aforesaid tags were required to create 3 ledgers based on values given in Cell F1, G1, H1
2) We had to copy/paste the same XML tags and had to change the Cell-reference, in case if we wanted to create additional Ledgers for values given in I1, J1, K1,L1 and so on....

XML tags required in UDIMagic v5.0 onwards
<!--Create Ledger Masters based on value given in cell F1 and
scrollhorizontally till blank cell is found -->
<MASTER TYPE="LEDGER" SCROLLHORIZONTALLY="=Len(F$1)>0">
<NAME.LIST>
<NAME COLUMNREFERENCE="F$1">
</NAME>
<PARENT>Indirect Expenses</PARENT>
</MASTER>

Remarks:-
1) The SCROLLHORIZONTALLY attribute is used herein. It is similar to LOOP mechanism found in programming languages.
2) It contains a condition (any valid Excel Formula) which is evaluated at run-time. If the condition is true, then the COLUMN references used within the FORMULA are incremented.
3) In the aforesaid cited example,
>> the Ledger Name is initially taken from cell F1. It is then enters a loop and increments the Column-references in the formula
>> next, if the cell G1 contains any data, then the condition Len(G$1)>0 evaluates to true and ledger is created for value given in G1. It then again increments the Column-references in the Formula.
>> next, if the cell H1 contains any data, then the condition Len(H$1)>0 evaluates to true and ledger is created for value given in H1. It then again increments the Column-references in the Formula.
>> This looping process continues till the condition becomes false.
>> This way with lesser xml code, we can process any number of Excel sheet Columns.

You can download the sample Excel templates (supported by v5.0 only) from :-
http://www.rtslink.com/excelfiles/v5.0


No comments: