|
NB: This Universal Function is quite complex and required expert knowledge in using the B1 Validation System and Universal Function systems.
WARNING: PLEASE BE AWARE THAT ALL SUPPORT FOR COMPLEX LINE LOOP SCENARIOS ARE CONSIDERED PREMIUM (PAID) SUPPORT BY BOYUM IT A/S.
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)
Important: When should you use Line Loop and is there a max limit to number of lines is can work with?
While there is no technical upper limit to how many lines you can have a line loop iterate a rule of thumb is that if you have more 50 lines you should seriously evaluate if line loop is the right tool for your job or you should be using custom development. Line loop a very dynamic system and the drawback of such a system is performance. So essentially Line Loop was created with the small number of lines in mind and while it might work on large number of rows we can't guarantee slowness and timeouts.
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
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 
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 
|
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.
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.
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
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.
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
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'
|
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
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.
Sample 3:
Here we test if the payment methods matrix have its row checked.
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.
|
|