Line Loop

Previous  Next |  Direct link to this topic

NB: This Universal Function is quite complex and required expert knowledge in using the B1 Validation System and Universal Function systems.

With the Line Loop Universal Function, you are able to work more advanced on line level, by adding line by line validation and executing other Universal Functions with data from each line (Allowing you to manipulate and transfer multiple lines)

UF Config Line Loop

You should see Line Loop as a secondary step to a header level event (from a Function button or B1 Validation configuration) that for each line in a matrix is allowed to execute a Universal Function as if the event happened on that line. Furthermore, for each line pass you can apply a condition (optional) if you actually wish to react to this line and also for each line collect data that should be reacted to after all lines have been looped.

The following high-level Flowchart describes the functionality for better understanding

image457

 

NB: Please note that line loop does not support working with BOM items on Sales and Purchase documents.

Data

Description

Line Item UID

First thing you need to define is the Item UID of the matrix that holds the lines that you wish to loop.

 

Samples:

-         The matrix on a Sales order have an item-Uid = 38 so we write that as the item UID

o   image458

O   TIP: ALL ITEM-BASED SALES AND PURCHASE DOCUMENTS HAVE 38 IS ITEM-UID AND 39 FOR THE SERVICE-BASED DOCUMENTS

-         The Payment methods matrix on Business Partner Master data have UID = 217 as we can see here in this screenshot

o   image459

Ignore Last Line

The ‘ignore last row’ checkbox is created because SAP Business One always leaves an extra line for new data on sales and purchase documents, and the last row needs to be ignored here (because it does not contain any user-data). Remove only the check if you work with a matrix that does not leave such extra lines (An example is Payment Method Matrix on the BP Master Data shown above).

Ignore text-lines and sub-totals

This checkbox determines if you on sales and purchase documents should ignore text-lines and sub-totals (these are by default not supported). On other window types this option is ignored.

Freeze during line loop

This checkbox determines if the main window (and others that might be opened by macros) should be frozen to avoid flickering when executing the line loop.

Direction

This option allows you to choose the direction of the line loop. Example if you have 5 lines and choose First to last it will go 1, 2, 3, 4, 5 while if you choose Last to First it will go 5, 4, 3, 2, 1. The most common scenario is First to Last, but Last to first especially become handy when you wish to remove lines (If you run First to Last and on the fly removed in the list you loop some items would be skipped).

No condition

The Line Loop functionality gives you the option to work with all lines in the defined matrix or only the ones that meet certain criteria.

 

clip0155

 

If you wish to work with all lines you should choose the ”No condition” option and fill your functionality into the fields below.

Data

Description

First pass UF

(Optional)

The First Pass Universal Function can define with a Universal Function that should be executed only when the very first line is found (after that it is ignored). The reason for this option is created in order to have the following scenario and others:

 

You wish to make a transfer of line data from one to another screen. For that you will need two things. First you need to open the new window and send some header data perhaps and secondary you need to send the line-data (for all lines).

 

As you can see we need to open a window, but it only makes sense to open the window once, and that is what the First pass UF is used for.

Line UF

(Optional)

If you read the explanation about the First Pass UF, the line UF gives itself that it now need place all the line-transferring in the Line UF… However you can also use Line UF by itself without a First Pass UF (When you just want to manipulate line data on current window)

Final result value

(Optional)

Working on each line is not all about only executing Universal Functions on the line. Line Loop have a built in Final Result system in which you can collect data about the line… The collected data can then be used for a final result after all lines have been looped. An example of this could be that you wish to find the total sum of total-price field and if it exceeds a certain amount something special should happen.

In this field you can use static text or the normal dynamic syntax $[$item.col.type] and the SQL:<SQL to select>…  Furthermore, this field can work with a special keyword $[LINNUM] that allows you to refer to the current line-num.

TIP: IF YOU WISH TO INSERT LINE BREAKS PLEASE USE THE <NEWLINE> KEYWORD TO DO IT.

(see the Final result section for more).

Final result type

Here you can control how the final result value should be applied to the perhaps already existing result. The options are as follows:

 

Type

Description

Append

Append simply append text to the previous result

Samples:

-         If you meet the values “A”, “B” and “C” on 3 lines append will result in the result “[A] [B] [C]”

-         If you have 2 lines and use the value “Error on line $[LINNUM]” you will get the result “[Error on line 1] [Error on line 2]”.

Ignore if previous value exists

 

With this option the value from this line is only set if the previous result is empty, else it is ignored

Samples:

-         If value “A” is met and previous value is empty, the new value is set to “A”

-         If value “A” is met but the previous value is already “B”, the value remains “B”

Overwrite

With this option you always overwrite the previous result.

Samples:

-         If value “A” is met and the previous value is “B”, it is overwritten and end with the result “A”

Count

Count simply ignores the final result value (although you need to have a dummy value) and increment the result by one each time it is hit

Samples:

-         If a result is hit 3 times, you get a result of 3

Min

With this option you can among all lines find the minimum value.

Samples:

-         Setting type as Min and the value to $[$38.11.NUMBER] that is the Qty. on a document, you will end with a result that is the value of the line with the lowest quantity

Max

Max works the same as Min but will instead give you the highest value.

Sum

With this option you can summarize all columns.

Samples:

-         Setting type as Sum and the value to $[$38.11.NUMBER] that is the Qty. on a document, you will end with a result that is the total value the lines.

Simple Condition

The Line Loop functionality gives you the option to work with all lines in the defined matrix or only the ones that meet certain criteria.

 

clip0155

 

If you wish to work with all lines you should choose the ”No condition” option and fill your functionality into the fields below.

Data

Description

Condition

Here you determine if the line is one you wish to use or not

 

clip0157

 

What you need to do is to define what item (and column)'s value meet your criteria.

 

Tip: If you can leave Item field blank the system automatically assume you mean the same item uid as you defined in the Line Item Uid

 

Sample 1:

If you wish to react to all lines that have a higher Quantity that 10 you choose Item = 38 (The Matrix) and Column = 11 (Quantity Column) as type Number. You then choose comparison type "Greater Than" and value 10.

clip0158

 

Sample 2:

If you wish to react to all lines where the Item No. is 'A00001' you choose Item = 38 (The Matrix) and Column = 1 (Item No. Column) as type String. You then Choose Comparison type "Equals" and value A00001

clip0159

 

Sample 3:

If you wish to react to all lines but only if the Documents Business Partner is 'C20000' you choose Item = 4 (The CardCode) and leave Column blank and type as string. You then choose Comparison type 'Equals'

clip0160

First pass UF

(Optional)

The First Pass Universal Function can define with a Universal Function that should be executed only when the very first line is found (after that it is ignored). The reason for this option is created in order to have the following scenario and others:

You wish to make a transfer of line data from one to another screen. For that you will need two things. First you need to open the new window and send some header data perhaps and secondary you need to send the line-data (for all lines).

As you can see we need to open a window, but it only makes sense to open the window once, and that is what the First pass UF is used for.

Line UF

(Optional)

If you read the explanation about the First Pass UF, the line UF gives itself that it now need place all the line-transferring in the Line UF… However you can also use Line UF by itself without a First Pass UF (When you just want to manipulate line data on current window)

Final result value

(Optional)

Working on each line is not all about only executing Universal Functions on the line. Line Loop have a built in Final Result system in which you can collect data about the line… The collected data can then be used for a final result after all lines have been looped. An example of this could be that you wish to find the total sum of total-price field and if it exceeds a certain amount something special should happen.

In this field you can use static text or the normal dynamic syntax $[$item.col.type] and the SQL:<SQL to select>…  Furthermore, this field can work with a special keyword $[LINNUM] that allows you to refer to the current line-num.

TIP: IF YOU WISH TO INSERT LINE BREAKS PLEASE USE THE <NEWLINE> KEYWORD TO DO IT.

(see the Final result section for more).

Final result type

Here you can control how the final result value should be applied to the perhaps already existing result. The options are as follows:

 

Type

Description

Append

Append simply append text to the previous result

Samples:

-         If you meet the values “A”, “B” and “C” on 3 lines append will result in the result “[A] [B] [C]”

-         If you have 2 lines and use the value “Error on line $[LINNUM]” you will get the result “[Error on line 1] [Error on line 2]”.

Ignore if previous value exists

 

With this option the value from this line is only set if the previous result is empty, else it is ignored

Samples:

-         If value “A” is met and previous value is empty, the new value is set to “A”

-         If value “A” is met but the previous value is already “B”, the value remains “B”

Overwrite

With this option you always overwrite the previous result.

Samples:

-         If value “A” is met and the previous value is “B”, it is overwritten and end with the result “A”

Count

Count simply ignores the final result value (although you need to have a dummy value) and increment the result by one each time it is hit

Samples:

-         If a result is hit 3 times, you get a result of 3

Min

With this option you can among all lines find the minimum value.

Samples:

-         Setting type as Min and the value to $[$38.11.NUMBER] that is the Qty. on a document, you will end with a result that is the value of the line with the lowest quantity

Max

Max works the same as Min but will instead give you the highest value.

Sum

With this option you can summarize all columns.

Samples:

-         Setting type as Sum and the value to $[$38.11.NUMBER] that is the Qty. on a document, you will end with a result that is the total value the lines.

SQL condition

The Line Loop functionality gives you the option to work with all lines in the defined matrix or only the ones that meet certain criteria. If you wish to work with only certain lines you should use the text-area here to define an SQL that should be executed for the line. Most cases will have an IF/ELSE or a CASE/WHEN Structure that in the end provide no or one result back. This result will then be validated against what line in the below matrix react on this result.

Tip: If your condition is very simple you most like benefit of using Simple Condition instead as it can give you better performance as it does not need to execute a SQL sentence for each line it meet (aka all condition validation is done client-side).

TIP: IN ESSENCE, THIS WORK EXACTLY THE SAME AS SQL-CONDITION ON B1 VALIDATION CONFIGURATION, SO IF YOU A FAMILIAR WITH THAT YOU SHOULD EASILY UNDERSTAND THIS

Sample 1:

Here we loop all sales order lines but we only wish to react if the lines has an item-code = Freight

LineLoop sampel 1

Sample 2:

Here we test if a line is an item line ($[$38.1.0] is not empty) and the if the price is zero.

LineLoop sampel 2

Sample 3:

Here we test if the payment methods matrix have its row checked.

LineLoop sampel 3

The samples here all only have one result but you are allowed to have multiple result-lines if needed.

Once the result is found the functionality of that line is executed as described below.

 

Column

Description

Result

Here you enter the result value you wish to react to. As an example in sample 1 the result value is the word PRESENT, and this line will only get executed if the SQL result in this value.

 

NB: SHOULD YOU HAVE TWO LINES THAT HAVE THE SAME RESULT VALUE IT IS ONLY THE FIRST THAT WILL BE EXECUTED

First pass UF

(Optional)

The First Pass Universal Function can be defined with a Universal Function that should be executed only when the very first line is found (after that it is ignored). The reason for this option is created in order to have the following scenario and others:

 

You wish to make a transfer of line data from one to another screen. For that you will need two things. First you need to open the new window and send some header data perhaps and secondary you need to send the line-data (for all lines).

 

As you can see we need to open a window, but it only makes sense to open the window once, and that is what the First pass UF is used for.

Line UF

(Optional)

If you read the explanation about the First Pass UF, the line UF gives itself that it now need place all the line-transferring in the Line UF… However you can also use Line UF by itself without a First Pass UF (When you just want to manipulate line data on current window)

Final result value

(Optional)

Working on each line is not all about only executing Universal Functions on the line. Line Loop have a built in Final Result system in which you can collect data about the line… The collected data can then be used for a final result after all lines have been looped. An example of this could be that you wish to find the total sum of total-price field and if it exceeds a certain amount something special should happen.

In this field you can use static text or the normal dynamic syntax $[$item.col.type] and the SQL:<SQL to select>…  Furthermore, this field can work with a special keyword $[LINNUM] that allows you to refer to the current line-num.

TIP: IF YOU WISH TO INSERT LINE BREAKS PLEASE USE THE <NEWLINE> KEYWORD TO DO IT.

(see the Final result section for more).

Final result type

Here you can control how the final result value should be applied to the perhaps already existing result. The options are as follows:

 

Type

Description

Append

Append simply append text to the previous result

Samples:

-         If you meet the values “A”, “B” and “C” on 3 lines append will result in the result “[A] [B] [C]”

-         If you have 2 lines and use the value “Error on line $[LINNUM]” you will get the result “[Error on line 1] [Error on line 2]”.

Ignore if previous value exists

 

With this option the value from this line is only set if the previous result is empty, else it is ignored

Samples:

-         If value “A” is met and previous value is empty, the new value is set to “A”

-         If value “A” is met but the previous value is already “B”, the value remains “B”

Overwrite

With this option you always overwrite the previous result.

Samples:

-         If value “A” is met and the previous value is “B”, it is overwritten and end with the result “A”

Count

Count simply ignores the final result value (although you need to have a dummy value) and increment the result by one each time it is hit

Samples:

-         If a result is hit 3 times, you get a result of 3

Min

With this option you can among all lines find the minimum value.

Samples:

-         Setting type as Min and the value to $[$38.11.NUMBER] that is the Qty on a document, you will end with a result that is the value of the line with the lowest quantity

Max

Max works the same as Min but will instead give you the highest value.

Sum

With this option you can summarize all columns.

Samples:

-         Setting type as Sum and the value to $[$38.11.NUMBER] that is the Qty on a document, you will end with a result that is the total value the lines.

Break Loop

The break loop option allows you to end the check of lines before all lines have been checked. This is handy as an example if you wish to use the Line Loop to test if a specific item or value is present, and as soon as you have validated that it is pointless to test the additional lines.

Sample:

If you have 20 lines in a sales order and you need to test if an item with item-code = “Freight” is present to validate if it is OK to add the document. Imagine that the freight item is on line 3 if would be pointless to check the remaining 17 lines (because our requirement is already met). Here it would make sense to set the Break Loop and gain performance in not needing to check the rest of the lines.

NB: EVEN IF YOU BREAK THE LOOP THE FINAL RESULT OPTION WILL STILL BE EXECUTED

NB: IF A SQL RESULT IS ONE NOT IN THE RESULT-OPTION THE LINE IS SIMPLY IGNORED AND MOVES TO THE NEXT LINE.

Final Result

(Optional)

Once all lines have been looped or a break loop is met, the Line Loop goes into is final phase of the Final result validation. One or more final result conditions can be executed using the data below.

 

Column

Description

Condition

First thing in a final result is if we actually want a final result at all. It can also happen that we only want a final result if our final result value has a specific value, and here you set that condition. You have the following options:

 

Option

Description

Do not execute

No Final Result should be executed

Always execute

No matter what the value of the result is, execute the function

Execute if condition is met

Only execute the final result if the result fits the condition set by the following 3 fields (see below)

Execute if not empty

Only execute the final result if the final result value have a value.

Value Type

NB: ONLY USE IF CONDITION IS SET TO “EXECUTE IF CONDITION IS MET”

Here you choose if the result should be treated as a string or a number.

Compare type

NB: ONLY USE IF CONDITION IS SET TO “EXECUTE IF CONDITION IS MET”

Here you chose how the result should be compared (Equal is default)

Compare value

NB: ONLY USE IF CONDITION IS SET TO “EXECUTE IF CONDITION IS MET”

Here you set the value to compare against.

In this field you can use static text or the normal dynamic syntax $[$item.col.type] and the SQL:<SQL to select>…

Universal Function

If the lines Condition has evaluated that the final result should be executed, it now execute the entered Universal Function. This universal Function can be any of the other types (though some of them do not make that much sense).

IMPORTANT:

To make the tie complete the other Universal Functions can if they are executed as part of the Final Result use a special keyword containing the result of the line loop – You can use:

 

-         $[LLRESULT.STRING]

o   To get the line loop result as string

-         $[LLRESULT.NUMBER]

o   To get the line loop result as a number

Sample:

Type the following in a Universal Function of type message to show a message containing the line loop result. If the line loop result was “[Line 3] [Line 4] [Line5]” you can write

The following lines had errors: $[LLRESULT.STRING]. Please correct this and try again.

Will result in the message:

The following lines had errors: “[Line 3] [Line 4] [Line5]. Please correct this and try again.

SQL condition keyword

Universal functions can use the result of the Line Loops SQL condition there activated them, by using the following syntax.

$[LLRESULT.COLNAME.STRING]

$[LLRESULT.COLNAME.NUMBER]

$[LLRESULT.COLNAME.DATE]

Where the COLNAME is the name of the column in the SQL statement.

If your SQL condition statement result in more than one record, values from the first record is used.

NB: Column names must not contain the characters (| ( ) "space" ; $) and these will be replaced with a underscore.