Line activity formulas - conditional script line/ parameter/

moderator · Post by **moderator** » Thu Apr 19, 2018 4:24 pm

Line activity formulas is the name of an additional functionality contained in EasyInput, that is configured in columns P-Z and sometimes used in column J (field value) of the EI_Script_XX worksheet.

Line activity formulas are used to:

Make a certain script line conditional (T)
Calculate a value of the field value column (J)

Line activity formulas can be based on:

Data contained in the data worksheet (by default EI_Data)
Technical data contained in column Z - Loop Index
Data read in the script (ReadData option, Y)

Referencing data contained in the data worksheet
Each script line is assigned to a certain level/order. When processing a Data line assigned to the a level/order all script lines with the same level/oder are processed from top to bottom in the active script. For each script line if columns P/Q/R/S are filled with references to Data sheet, relevant data sheet values are copied to columns U/V/W/X. Thus one can create an Excel formula in the line based on data in columns U/V/W/X, as these columns are filled with data in runtime. Remember that, the script is processed from the top down, so if one reads a value in the first line of a certain level/order one can reuse it in formulas in the lines below with the same level/order or with higher level (without filling the references in columns P/Q/R/S in these lines).
Important: From version 2.17 B1009 on it is possible to read data row number. Just put string ROW in columns P/Q/R/S.

Referencing the technical data contained in column Z - Loop Index
Sometimes in the formula one has to make a distinction between the first document line and the others. For this purpose Loop Index value is filled in column Z of the script worksheet in the runtime. Loop Index column contains for lines on level 0 the number of consecutive transaction processed, and for the lines on higher levels, the number of a consecutive data line (e.g. Line item number) processed within a transaction.

Referencing data read in the script (ReadData option, Y)
If a script contains several transactions/ function modules then the result read in one can be used in the next. Apart from copying the data read with the ReadData statement to the Data worksheet, the data is also copied to column Y of the script worksheet to the relevant line. Important! Since reading data is performed after the transaction/ function module is called, its result from column Y cannot be used when a script contain only one transaction/function module!

Make a certain script line conditional (T)
Apart form static inactivation in column E of the script, a dynamic inactivation of the line can be performed in the column T. If the script is not inactivated in column E, then:

If there is no formula in column T, the script line is active
If there is formula returning true in column T, the script line is active
If there is formula returning false in column T, the script line is inactive

If the script is inactivated in column E, than the line is inactive and no other checks are performed.

Calculate a value of the field value column (J)
Value passed to script line can be passed as:

A constant put into J column
A variable read from the Data worksheet with the statement WriteData and the reference to the data column (L/M)
A formula put into J column

In the third case one has to take off the script worksheet protection and change the formatting of the cell to general, so that Excel treats the formula as the formula and not as a string. In the formula one can reference the fields filled in the runtime in columns U/V/W/X and in the special cases in column Y.

See also video tutorial here https://addons.snp-poland.com/viewtopic.php?f=18&t=108 (Lesson 6) How to use Line Activity Formulas