TDL Procedural Capabilities
TDL functions are action statements defined by an application developer. The developer has a complete control over the sequence in which these actions get executed. A TDL function extends the following benefits to the TDL programmer:
- To perform a set of actions sequentially with or without any conditional statements.
- Allows looping and conditional execution of a set of actions.
- Defining once and executing it repetitively passing different set of parameters.
- To define variables, manipulate and set values in it.
- To work on data elements like getting an object context from the calling UI, changing the context, looping the objects of a collection, reading data from source and setting value in the target object, and so on.
- Creation and manipulations of the existing internal objects.
With the advent of TDL the application developer can write complex business functions with ease and without much platform dependency.
Traditionally, TDL was always a non-procedural, action-driven language. The sequence of execution was not in the hands of the programmer. But, with this development in a ‘Function’ Definition, Conditional evaluation of statements and looping has been made possible. User defined Functions basically can be used for performing complex calculations or executing a set of actions serially. Functions can accept parameter(s) as input, and return a ‘Value’ to the caller.
With this development, the programmers can write business functions with complex computations by themselves, without platform dependency.
Function – Building Blocks
In TDL, Function is also a definition. It has two blocks:
- Definition Block
- Procedural Block
A glimpse into the function:
[Function : Function Name]
;; Definition Block
;; Parameter Specification
Parameter : Parameter 1 : Datatype
Parameter : Parameter 2 : Datatype
;; Variable Declarations
Variable : Var 1 : Number
Variable : Var 2 : String
;; Explicit Object Association
Object : ObjName : ObjectType
;;Return Value
Returns : Datatype
;;Definition Block Ends here
;;Procedural Block
Label 1 : Statement 1
Label 2 : Statement 2
|
|
Label n : Statement n
;; Procedural Block Ends here
Definition Block
The definition Block is utilized for the following purposes:
Parameter Specification
This implies specifying the list of parameters which are passed by the calling code. The values thus obtained, are referred to in the function with these variable names. The syntax for specifying the same is given below:
Syntax
PARAMETER : <Variable Name> : <Data Type of the Variable>
Where,
< Variable Name > is the name of the Variable which holds the parameter sent by the caller of the Function.
< Data Type of the Variable > is the Data type of the Variable sent by the caller of the Function.
Example
[Function: FactorialOf]
Parameter : InputNumber : Number
The Function $$FactorialOf receives number as the parameter from the Caller.
Please note that we can also pass the optional parameters in user defined functions. The function will execute smoothly even if the caller of the function does not pass the values to these optional parameters. However, the caller of the function must pass all the mandatory parameters. Only the rightmost parameters can be optional, i.e., any parameter from the left or middle cannot be optional.
If the Parameter value is supplied by the calling Function, then the same is used, else the default Parameter value provided within the ‘Parameter’ Attribute is used as the Parameter value. For this enhancement, the Function attribute ‘Parameter’ has been modified to accept parameter value.
Syntax
[Function : <Function Name>]
Parameter : <Parameter Name1> : <Data Type>
Parameter : <Parameter Name2> : <Data Type>
Parameter : <Parameter Name3> : <Data Type> [: Parameter Value]
Parameter : <Parameter Name4> : <Data Type> [: Parameter Value]
Where,
<Parameter Name1> and <Parameter Name2> are mandatory parameters for which, values must be passed while calling the function.
<Parameter Name3> and <Parameter Name4> are optional parameters for which, values may or may not be passed while calling the function. If values for these parameters are passed, the parameter value specified within the ‘Parameter’ Attribute is ignored. In absence of these values, the specified parameter value is taken into consideration.
Parameter Value indicates Optional Parameters, and all the Optional Parameters should be the rightmost elements of the function.
Example
[Function : Split VchNo]
;; this Function returns the number part of voucher number from a string. For example, Voucher Number is Pymt/110/2010-11. This Function will return only ‘110’.
Parameter : pVchNo : String
Parameter : pSplitChar : String : “/”
;; usual separator
00 : FOR TOKEN : TokenVar : ##pVchNo : ##pSplitChar
10 : IF : $$LoopIndex = 2
20 : RETURN : ##TokenVar
30 : ENDIF
40 : END FOR
While invoking the function $$SplitVchNo, only the Voucher No is passed. The 2nd Parameter is optional and the default value is “/”. It is passed only if the separator character is other than “/”.
Optional parameters can be very useful where the Parameter values remain constant in most of the cases; and rarely require some change.
A small change has been done in the way function parameters are tokenized. The last parameter passed to the function is not broken up into sub-parts now. This is particularly useful in cases where we require the result of one function to be treated as a parameter to another function. In other words, if a function requires 4 parameters, it tokenizes only till 3 parameters and all the subsequent values are considered as the 4th parameter (last parameter).
Variable Declaration
If a function requires some Variable(s) for intermediate calculation, then those Variable(s) need to be defined. The scope of these Variable(s) will be within the Function, and the Variable(s) lose their value after exit from the function.
Syntax
VARIABLE : <Variable Name> [:<Data Type of the Variable>]
Where,
< Variable Name > is the name of the Variable.
< Data Type of the Variable > is the Data type of the Variable.
Datatype is optional. If datatype is specified, a separate Variable definition is not required (these are considered as in-line variables). If data type is not specified, the interpreter will look for a variable definition, with the name specified.
Example
[Function : FactorialOf]
Parameter : InputNumber : Number
Variable : Counter : Number
Variable : Factorial : Number
The Function $$FactorialOf requires intermediate Variables ‘Counter’ and ‘Factorial’ for calculation within the ‘Function’ Definition.
Static Variable declaration
Static Variable is a Variable, whose value persists between successive calls to a Function.
The scope of the static variable is limited to the Function in which it is declared, and exists for the entire session.
Syntax
STATIC VARIABLE : <Variable Name> [:<Data Type of the Variable>]
Where,
< Variable Name > is the name of the Static Variable
< Data Type of the Variable > is the Data type of the Static Variable.
Data type is optional. If data type is specified, a separate Variable definition is not required (these are considered as in-line variables). If data type is not specified, the interpreter will look for a variable definition with the name specified.
Example
[Function : Sample Function]
Static Variable : Sample Static Var : Number
The static variable Sample Static Var retains the value between successive calls to the function Sample Function .
Return Value Specification
If a function returns a value to the caller, then its data type is specified by using RETURNS statement.
Syntax
RETURNS : <Data Type of the Return Value>
Where,
<Data Type of the Return Value > is the Data type of the return value of the Function.
Example
[Function : FactorialOf]
Parameter : InputNumber : Numb e r
Returns : Number
Variable : Factorial : Number
The function FactorialOf returns the value of type Number to the caller of the Function
Object Specification
Function will inherit the Object context of the caller. This can be overridden by using the attribute Object for Function definition. This now becomes the current object for the function.
Syntax
Object : <ObjType> : <ObjIdValue>
Where,
< ObjType > is the type of the object, and
< ObjIdValue > is the unique identifier of the object.
Example
[Function : Sample Function]
Object : Ledger : “Party”
The function Sample Function will be in the context of the ledger Party .
Procedural Block
This block contains a set of statements. These statements can either be a programming construct or an Action specification. Every statement inside the procedural block has to be uniquely identified by a label specification.
Syntax
LABEL SPEC I FICATION : Programming Construct
OR
LABEL SPECIFICATION : Action: Action Parameter
Example
[Function : DispStockSummary]
01 : Display : Stock Summary
02 : Display : Stock Category Summary
The Function DispStockSummary is having two Actions with Label.
Valid Statements Inside a Function
All the valid statements used within the procedural block can be categorized as below:
Programming Constructs
- Conditional Constructs
- Looping Constructs
- Control Constructs
Actions
- System Actions
- Object and Context Manipulation Actions
- User Interface Actions
- Debugging Actions
- File I/O Actions
The statements used inside the procedural block of a function can be:
- A programming construct
- A TDL action
Programming Constructs in Function
Conditional Constructs
In TDL following conditional constructs are provided to control the flow of execution:
- IF – ENDIF
- IF – ELSE – ENDIF
- DO – IF
IF–ENDIF
The IF–ENDIF statement is a powerful decision making statement and is used to control the flow of execution of statements. It is basically a two-way decision statement and is used in conjunction with an expression. Initially, the expression will be evaluated and based on the whether the expression is True or False, it transfers the execution flow to a particular statement.
Syntax
IF : <Conditional Expression>
STATEMENT 1
…
STATEMENT N
ENDIF
Where,
<Conditional Expression> is an expression which returns a logical value. If it is TRUE then the specified statements are executed.
Example
If the function parameter sent to the Function FactorialOf is less than zero, then it is multiplied by -1 to find the absolute value.
[Function : FactorialOf]
Parameter : InputNumber : Number
Returns : Number
Variable : Counter : Number
Variable : Factorial : Number
1 : SET : Counter : 1
2 : SET : Factorial : 1
3 : IF ##InputNumber <
4 : SET : InputNumber : ##InputNumber * -1
5 : END IF
6 : WHILE : ##Counter <= ##InputNumber
7 : SET : Factorial : ##Factorial * ##Counter
8 : SET : Counter : ##Counter + 1
9 : END WHILE
10 : RETURN ##Factorial
IF–ELSE–ENDIF
The IF–ELSE–ENDIF statement is an extension of the simple IF-ENDIF statement. If the condition expression is True, then the ‘True’ block’s statement(s) are executed; otherwise, the ‘False’ block’s statement(s) are executed. In either case, either the True block or the False block will be executed, not both.
Syntax
IF : <Conditional Expression>
STATEMENT 1
…
STATEMENT N
ELSE
STATEMENT 1
…
STATEMENT N
ENDIF
Where, <Conditional Expression> is an expression which returns a logical value. If it is TRUE then the specified statements 1- N are executed other wise the statements 1 to M are executed.
Example
Finding the greatest of three numbers
[Function : FindGreatestNumbers]
Parameter : A : Number
Parameter : B : Number
Parameter : C : Number
RETURNS : Number
01 : IF : ##A > ##B
02 : IF : ##A > ##
03 : RETURN : ##A
04 : ELSE
05 : RETURN : ##C
06 : END IF
07 : ELSE
08 : IF : ##B > ##C
09 : RETURN : ##B
10 : ELSE
11 : RETURN : ##C
12 : END IF
13 : END IF
DO IF
When an IF-ENDIF statement block contains only one statement, then the same can be written in a single line by using DO-IF statement.
Syntax
DO IF : <Condition Expression> : STATEMENT
Example
Here, IF – END IF statement is rewritten using the DO – IF statement.
[Function : FactorialOf]
Parameter : InputNumber : Number
Returns : Number
Variable : Counter : Number
Variable : Factorial : Number
1 : SET : Counter : 1
2 : SET : Factorial : 1
3 : DO IF : ##InputNumber < : ##InputNumber * -1
4 : WHILE : ##Counter <= ##InputNumber
5 : SET : Factorial : ##Factorial * ##Counter
6 : SET : Counter : ##Counter + 1
7 : END WHILE
8 : RETURN ##Factorial
Looping Constructs
WIHLE – ENDWHILE
In looping, a sequence of statements is executed until some condition(s) for termination of the loop is satisfied. A typical loop consists of two segments, one known as the body of the loop and the other known as the control statement. The control statement checks the condition and then directs the repeated execution of the statements contained in the body of the loop.
The WHILE – ENDWHILE is an entry-controlled loop statement. The condition expression is evaluated and if the condition is True, then the body of the loop is executed. After the execution of the statements within the body, the condition expression is once again evaluated and if it is True, the body is executed once again. This process of repeated execution of the body continues until the condition expression finally becomes False, and the control is transferred out of the loop.
Syntax
WHILE : <Condition Expression>
STATEMENT 1
…
STATEMENT N
ENDWHILE
Example
[Function : FactorialOf]
Parameter : InputNumber : Number
Returns : Number
Variable : Counter : Number
Variable : Factorial : Numb e r
1 : SET : Counter : 1
2 : SET : Factorial : 1
3 : WHILE : ##Counter <= ##InputNumber
4 : SET : Factorial : ##Factorial * ##Counter
5 : SET : Counter : ##Counter + 1
6 : END WHILE
7 : RETURN ##Factorial
The Function FactorialOf repeats statements 4 and 5, till the given condition is satisfied.
WALK COLLECTION – END WALK
If a Collection has ‘n’ Objects, then WALK COLLECTION – ENDWALK will be repeated for ‘n’ times. Body of the loop is executed for each object in the collection, making it the current context.
Syntax
WALK COLLE C TION : <Collection Name>
STATEMENT 1
…
STATEMENT N
ENDWALK
Example
Walking over all the Vouchers and counting the same.
[Collection : Vouchers Coll]
Type : Voucher
[Function : CountVouchers]
Returns : Number
Variable : Count : Number
001 : SET : Count :
002 : WALK COLLECTION : Vouchers Coll
003 : SET : Count : ##Count + 1
004 : END WALK
005 : RETU R N : ##Count
FOR COLLECTION
When WALK COLLECTION is used inside a function, the object of collection is set as the current object in the context of iteration, i.e., the loop is executed for each object in the collection, making it as the current context.
FOR COLLECTION provides a context-free walk, as the current object is not set as the current object context while looping. It loops on the collection for the specific value and returns the value in the iterator variable. The value of the iterator variable can be referred to by the actions inside the For Collection loop.
Syntax
FOR COLLECTION : <IteratorVar> : <CollExprn> [:<Value Exprn : <Rev Flag>]
Where,
<Iterator Var> is the name of the variable which is used for the iteration. This variable is created implicitly.
<Coll Exprn> can be any expression which evaluates to a collection name.
<Value Exprn> can be any expression, whose value is returned in the iterator variable. If the value expression is not specified, the name of the object is returned.
<Rev Flag> can be any expression which evaluates to a logical value. If it is True, then the collection objects are traversed in reverse order. This parameter is optional. The Default value is False.
Example
[Function : Test Function]
|
|
30 : FOR COLLECTION : i : Group : $ClosingBalance > 1000
40 : LOG : ##i
50 : END FOR
The value Yes is logged in the file TDLFunc.log if the closing balance is greater than 1000, else No.
FOR TOKEN
The looping construct FOR TOKEN is used to walk a String expression separated by a delimiter character. It loops on a String expression and returns one value at a time. The value is returned in the iterator variable.
Syntax
FOR TOKEN : <IteratorVar> : <SrcStringExprn> [:<DelimiterChar>]
Where,
<IteratorVarName> is the name of the variable used for iteration. The variable is created implicitly.
<Src String Exprn> can be any string expression separated by a <delimiter Char>.
<Delimiter Char> can be any expression which evaluates to a character used for separating the string expression. It is optional. The default separator char is ‘:’.
Example
[Function : Test Function]
|
|
01 : FOR TOKEN : TokenVar : “Tally : Shopper : Tally Developer” : “:”
02 : LOG : ##TokenVar
03 : END FOR
This code snippet will give the output as shown below:
Tally
Shopper
Tally Developer
FOR RANGE
As explained earlier, TDL allows different looping constructs for varied usage. The existing loop constructs allow us to loop on the objects in the collection or on the tokenized string or condition-based looping of a set of the statement.
There are scenarios where the looping is to be performed for a range of values. For this, the loop FOR RANGE is used. This loop construct allows to loop on a range of numbers or date. This loop can be used to repeat a loop for the given range of specified values. The range can either be incremental or decremental. The FOR RANGE loop can be used to get the Period Collection-like functionality.
Syntax
FOR RANGE : <Iterator Var>: <Data type>: <StartRangeExpr>: <EndRangeExpr>[:<Increment Expr>[:<DateRangeKeyword>]]
Where,
<Iterator Var Name> is the name of the variable used for the iteration. This variable is created implicitly.
<Data Type> can be Number or Date only.
<StartRangeExpr> is an expression which evaluates to number or date values. It refers to the starting value of the range.
<EndRangeExpr> is an expression which evaluates to number or date values. It refers to the end value of the range.
<Increment Expr> is an expression which evaluates to a number by which the <Starring- Expr>value is incremented. It is optional, and the default value is 1.
<DateRangeKeyword> is optional, and only applicable if the data type is Date. The values can be any one of ‘Day’, ‘Week’, ‘Month’ and ‘Year’.
Example
|
01 : FOR RANGE : IteratorVar : Number : 2 : 10 : 2
02 : LIST ADD : EvenNo : ##IteratorVar
03 : END FOR
|
The values 2,4,6,8,10 are added in the List variable EvenNo, as the range of value is 2 to 10, and the value is incremented by 2 with each iteration.
Example
The following code snippet is used to calculate the number of weeks elapsed between System date and Current Date set in Tally.
|
09 : FOR RANGE : IteratorVar : Date : ##SVCurrentDate : $$MachineDate : 1 : “Week”
10 : LOG : ##IteratorVar
20 : INCREMENT : Cnt
30 : END FOR
50 : LOG : “No of weeks Back Dated : ” + $$String:##Cnt
60 : RETURN : ##Cnt
|
Assume that the range for this is from 15 – Mar – 2009 to 30 – Mar – 2009. The values 15-Mar-2009, 22-Mar-2009 and 29-Mar-2009 are logged, as the increment is done by a ‘Week’. So, there are three iterations of the loop. The number of weeks is logged using the counter.
Example
|
09 : FOR RANGE : IteratorVar : Date : ##SVFromDate:##SVToDate : 1 : “Month”
10 : LOG : $$MonthEnd:##IteratorVar
20 : END FOR
|
Assume that the range for this is from 1-Jan-2009 to 5-Mar-2009. The values 31-Jan-2009, 28- Feb-2009 and 31 -Mar -2009 are logged.
Control Constructs
Loops perform a set of operations repeatedly until the condition expression satisfies the given condition or the Collection is exhausted. Sometimes, when executing the loop, it becomes desirable to skip the part of the loop or to exit the loop as a certain condition occurs, or to save the current state and return back to the current state later.
BREAK
When a BREAK statement is encountered inside the loop, the loop is immediately exited and the control is transferred to the statement immediately following the loop. BREAK statement can be used inside WHILE – END WHILE and WALK COLLECION – END WALK. When loops are nested, the Break would only exit from the loop containing it.
Syntax
BREAK
Example:
[Function : PrintNumbers]
Variable : Counter : Number
1 : SET : Counter : 1
2 : WHILE : ##Counter < = 10
3 : LOG : ## Counter
4 : IF : ## Counter > 5
5 : BREAK
6 : END IF
7 : SET : Counter : ##Counter + 1
8 : ENDWHILE
9 : RETURN
In the Function Print Numbers, the loop is running from 1 to 10. But because of BREAK statement, the loop will be terminated as the counter reaches 6.
CONTINUE
In some scenarios, instead of terminating the loop, the loop needs to be continued with next iteration, after skipping any statements in between. For this purpose, CONTINUE statement can be used. CONTINUE statement can be used inside WHILE – END WHILE and WALK COLLECION – END WALK.
Syntax
CONTINUE
Example:
Function to Count the total number of Journal Vouchers
[Collection : Vouchers Coll]
Type : Voucher
[Function : CountJournal]
Returns : Number
Variable : Count : Number
01 : SET : Count :
02 : WALK COLLECTION : Vouchers Coll
03 : IF : NOT $$IsJournal:$VoucherTypeName
04 : CONTINUE
05 : ENDIF
06 : Count : ##Count + 1
07 : END WALK
08 : RETURN : ##Count
START BLOCK – END BLOCK
START BLOCK –- END BLOCK is used to save the current state and execute some actions within the block and return back to the original state. This is handy in cases where the Object context needs to be temporarily switched for the purpose of executing some actions. Current source and target object contexts are saved and the further statements within the START BLOCK and END BLOCK section get executed. Once the END BLOCK is encountered, the Object context is restored back to the original state.
Syntax
START BLOCK
Block Statements
END BLOCK
Example
10 : WALK COLLECTION : EDCollDetailsExtract
11 : INSERT COLLECTION OBJECT : InventoryEntries
12 : SET : QtyVar : $$String : $Qty + ” Nos”
13 : SET : Amt Var : $$String:$Amt
14 : START BLOCK
15 : SET OBJECT
16 : SET VALUE : ActualQty : $$AsQty :##QtyVar
17 : SET VALUE : BilledQty : $$AsQty :##QtyVar
18 : SET VALUE : Amount : $$AsAmount:# #AmtVar
18A : END BLOCK
19 : SET TARGET : …
20 : SET VALUE : StockItemName : $Item
21 : END WALK
;; For Explanation on Object context, i.e., Source Object and Target Object,
;; Set Target, Set Object, etc., please refer to the Topic ‘Function Execution – Object Context’
In this code snippet, the collection EDCollDetailsExtract is being walked over and the values for the Objects within Voucher Entry are being set.
RETURN
This statement is used to return the flow of control to the calling program, with or without returning a value. When RETURN is used, the execution of the function is terminated and the calling program continues from where it had called the function.
Syntax
RETURN : <value expression>
Where,
<value expression > is optional, i.e., it can either return a value or return void.
Example
The Function ‘FactorialOf’ returns factorial of number
[Function : FactorialOf]
Parameter : InputNumber : Nu m ber Returns : Numb e r
Variable : Counter : Number
Variable : Factorial : Numb e r
1 : SET : Counter : 1
2 : SET : Factorial : 1
3 : WHILE : ##Counter <= ##InputNumber
4 : SET : Factorial : ##Factorial * ##Counter
5 : SET : Counter : ##Counter + 1
6 : ENDWHILE
7 : RETURN : ##Factorial
Actions Used in a TDL Function
Actions for Variable Manipulation
TDL provides various new actions that can be used inside User Defined Functions.
SET
This action is used to assign a value for a variable.
Syntax
SET : <VariableName> : <Value Expression>
Where,
< Variable Name> is the variable for which the value needs to be assigned.
< Value Expression> is the formula evaluating to a value.
Example
[Function : FactorialOf]
Parameter : InputNumber : Number Returns : Number
Variable : Counter : Number
Variable : Factorial : Number
1 : SET : Counter : 1
2 : SET : Factorial : 1
3 : WHILE : ##Counter <= ##InputNumber
4 : SET : Factorial : ##Factorial * ## Counter
5 : SET : Counter : ##Counter + 1
6 : ENDWHILE
7 : RETURN : ##Factorial
EXCHANGE
This action is used to exchange (swap) the values of two variables. But, values of only the variables of the same Data type can be exchanged.
Syntax
EXCHANGE : <First Variable Name> : <Second Variable Name>
Where,
< First Variable Name > and < Second Variable Name > are the Variables whose values need to be swapped.
Example:
01 : EXCHANGE : Var1 : Var2
In this statement, both variables are of type Number and their values are swapped.
INCREMENT
This action is used to increment the value of variables by any numerical value. INCREMENT is used inside the loop to increment value of control variables.
You can specify multiple variables by separating them with a comma.
Syntax
INCREMENT : <Variable Name> [,<Variable Name>, …] [:<Number Expression>]
Where,
<Variable Name> is the name of the variable whose value needs to be incremented.
<Number Expression> is the numerical value by which the variable needs to be incremented. If no number is given, the variable will be incremented by 1.
Example
[Function: VATVchrColWalk_Inv_GUJ_AnnxError_LoadVAR]
If : ##GUJeVATAnex201BError
Increment : vVATAnxTotalCount, vVATAnxErrorCount, vVATAnx201BTotalCount, vVATAnx201BErrorCount
Else
Increment : vVATAnxTotalCount, vVATAnx201BTotalCount
End If
The keyword Last will only work from release 5.4.8.
DECREMENT
This action is used to decrement the value of variables by any numerical value. DECREMENT is used inside the loop to decrement the value of the control variables.
Syntax
DECREMENT : <Variable Name> [,<Variable Name>, …] [:<Number Expression>]
Where,
< Variable Name > is the name of the variable whose value needs to be decremented by the number specified.
< Number Expression > is the numerical value by which the variable needs to be decremented. If no number is given, the variable will be decremented by 1.
Example
[Function : PrintNumbers]
Variable : Counter : Number
1 : SET : Counter : 10
2 : WHILE : ##Counter >
3 : LOG : ##Counter
4 : DECREMENT : Counter
5 : ENDWHILE
6 : RETURN
In the function PrintNumbers, the loop is running from 10 to 1.
Use Case – Import from Excel
Scenario
ABC Company Limited, which is into the trading business, is using TallyPrime. It deals with the purchase and sale of computers, printers, etc. The company management wants to import the stock items from the Excel sheet, or a text file into TallyPrime.
Functional Demo
A configuration report is added to accept the file location, worksheet name, column details, etc. An option to display the error report can also be specified.
By default, Excel format is selected. But, the user can also select the Import source format as Text and specify the file details. The text separator character should be specified as well, in addition to the column details.
Once the details are entered, a confirmation message is displayed to start the import process.
If the user has selected the option to display the error report after successful import, the report is shown with imported stock items and status as “Imported successfully”, as seen in the figure:
If the user has selected the option to display the Log file, then after the import, the log file is displayed as follows:
The imported items are now available in the Stock Item list as follows:
In case the import is unsuccessful, the error report, with the reason for failure, is displayed as follows:
Solution Development
The import from the excel file is simplified, as the user can specify the import details. The file I/O capabilities are used to develop the solution. The steps followed to achieve the requirement are:
- A report is designed to display the configuration report. The value entered by the user is stored in a system variable.
Local : Field : Name Field : Modifies : SIC Source : Yes
Local : Field : Name Field : Variable : SIC Source
|
|
Local : Field : Name Field : Modifies : SIC DirPath : Yes
Local : Field : Name Field : Variable : SIC DirPath
[System : Variable]
SIC Source : “Excel”
SIC DirPath : “C:\Tally.ERP9”
- On form accept, the function to import the stock item is called.
On : Form Accept : Yes : Form Accept
On : Form Accept : Yes : Call : Smp Import Stock Items
- A function “Smp Import Stock Items” is defined.
- In this function, first of all, the format of the source file is checked, and then, the action ‘Open File’ is used to open the file in ‘Read’ mode accordingly.
20 : IF : ##SICSource = “Excel”
30 : OPEN FILE : @@TSPLSMPTotFilePath : Excel : READ
40 : ELSE
50 : OPEN FILE : @@TSPLSMPTotFilePath : Text : READ
60 : ENDIF
- The data from the Excel cells are read and added as items in the list variable.
120 : WHILE : NOT $$IsEmpty:($$FileReadCell:##Row:##ItemColumns.ItemName)
130 : LISTADDEX : ItemDetails
140 : SET : ItemDetails[$$LoopIndex].ItemName : $$FileReadCell:##Row:##ItemColumns.ItemName
150 : SET : ItemDetails[$$LoopIndex].ItemGrp: $$FileReadCell:##Row:##ItemColumns.ItemGrp
160 : SET : ItemDetails[$$LoopIndex].ItemUOM: $$FileReadCell:##Row:##ItemColumns.ItemUOM
170 : INCREMENT: Row
180 : END WHILE
- If source format is ‘Text’, the text file is read line by line and added as items to the list variable.
210 : WHILE : NOT $$FileIsEOF
220 : SET : TempVar : $$FileRead
230 : IF : NOT $$IsEmpty:##TempVar AND (NOT ##SICIncHeader OR (##SICIncHeader AND $$LoopIndex>1))
240 : LIST ADD EX : ItemDetails
250 : SET : ItemDetails[##Counter].ItemName : $$SICExtractDet:##TempVar:##ItemColumns.ItemName
260 : SET : ItemDetails[##Counter].ItemGrp : $$SICExtractDet:##TempVar:##ItemColumns.ItemGrp
270 : SET : ItemDetails[##Counter].ItemUOM : $$SICExtractDet:##TempVar:##ItemColumns.ItemUOM
280 : INCREMENT : Counter
290 : ENDIF
300 : END WHILE
- A collection is populated using the List variable as data source.
[Collection : TSPLSMPImpStockItem]
Data Source : Variable : ItemDetails
[Collection : TSPLSMPImpStockItem Summ]
Source Collection : TSPL SMP Imp Stock Item
By : SICStockItem : $ItemName
By : SICStockGroup : $ItemGrp
By : SICStockUOM : $ItemUOM
Filter : TSPLSMPNonEmptyItem
- Now, the Stock Item objects are created. If the item can’t be imported, then the item details are written in the error file or compound variable, based on the format selected for displaying, i.e., ‘Report’ or ‘Log’.
380 : WALK COLLECTION : TSPL SMP Imp StockItem Summ
390 : SET : Last Status : “”
400 : IF : $$IsEmpty:$Name:StockItem:$SICStockItem
410 : NEW OBJECT: Stock Item
420 : SET VALUE : Name : $SICStockItem
430 : IF : NOT $$IsEmpty:$Name:StockGroup:$SICStockGroup
440 : SET VALUE : Parent : $SICStockGroup
450 : ELSE
460 : SET : LastStatus : “Group” + $SICStockGroup + “does not exist”
470 : ENDIF
480 : IF : NOT $$IsEmpty:$Symbol:Unit:$SICStockUOM
490 : SET VALUE : Base Units : $SICStockUOM
500 : ELSE
510 : SET : LastStatus : “Unit” + $SICStockUOM + “does not exist”
520 : ENDIF
530 : IF : $$IsEmpty:##LastStatus
540 : SAVE TARGET
550 : SET : Last Status : “Imported Successfully”
560 : ENDIF
570 : ENDIF
;; Writing Import Status to the LOG File, if LOG File is to be displayed at the end
580 : IF : ##SICOpenLogFile
590 : WRITE FILE LINE : $SICStockItem + ##SICTextSep + ##LastStatus
600 : ENDIF
;; Updating List of Compound Variables is the Status is to be displayed in a Report
610 : IF : ##SICDisplayReport
620 : LIST ADD EX : ItemImportStatus
630 : SET : ItemImportStatus[##Counter].ItemName : $SICStockItem
640 : SET : ItemImportStatus[##Counter].Status : ##LastStatus
650 : INCREMENT : Counter
660 : ENDIF
670 : END WALK
- If the format selected is ‘Report’, then the stock item name and the status is updated in a compound variable; whereas, if the format selected is Log file, then the action ‘Write File’ is used to write in the file.
WRITE FILE LINE : $SICStockItem + ##SICTextSep + ##LastStatus
- After import, if the user wants to display error report, a function is called to display the same.
690 : IF : ##SICDisplayReport
700 : DISPLAY : TSPL Smp SIC Error Report
710 : ENDIF
- After the import, if the user has selected to display the log file, then the log file is displayed.
720 : IF : ##SICOpenLogFile
730 : EXEC COMMAND : @@TSPLSmpErrorFilePath
740 : ENDIF
- The Error Report displays the reason of failure, if the Stock Item cannot be imported. In error report, the line is repeated over the collection populated, using list variable as the data source.
