Line Item Updates
There are times when you need to work with line items to get your data processed properly. Invoices and Purchase Orders are 2 examples where line items are often used. In those applications, it is common to need to verify their line item data is accurate or complete. In this Line Item Updates article we will show you how to loop through line items and do a lookup on another Record Type to pull back data into that line item.
Example: Update Invoice Line Items by GL Code
In the example in this article, we are going to process invoices. The line items in the invoice have a GL Code but do not have a Description. We will show you how to loop through the Invoice’s line items using a “Line-Item Loop” action. In that loop, we will use a “Search and Update” action to find an Inventory Record that matches the line item’s GL Code. When we find a match, we will pull the Inventory Record’s Description value back into the line item.
Line Item Loop Action
Use the “Line Item Loop” action to loop through each line item. The loop has a place to add actions so that, for each line, these actions will be run. During each loop, the system will update a variable called “LineCtr” to the number of the current line in the loop. You can use that variable by using [LINECTR] in your actions. For the first line, the [LINECTR] will be 1, the second line it will be 2, etc. You can use this to read and write the proper lines in your actions. In order to get values from the line you are in, use the [LI([LINECTR]|FieldName)] syntax. The [LI()] variable is used extensively when processing line items so that is one you will get used to using.
Search and Update Action
The “Search and Update” action will find records in the system just like when you do a manual search. Once the search runs, it will then perform the update we want. This is how we can pull data from one Record into another.
Search
When configuring the search, think about how you would do the search manually. In fact, you may want to do it yourself using the normal search to help you configure the action. In our example, we will choose the “Inventory” record type using the “Search In” setting. This will limit our search to only Inventory items.
Then we can add a search value to make sure we find the correct GLCode. The Field Name setting is the name of the field in the Inventory record type where we will find our matching GL Code. In this case, the field is named GLCode so we enter that into the Field Name field. You can (should) also use the field pickers to be sure the spelling is correct.
For the Value field, we need to be sure we specify the GL Code form the LINE ITEM ONLY. If we have a GLCode field in the line items you may be tempted to use [GLCODE] here but that would grab a value from the HEADER – not the line item. For line item data, use the [LI()] variable. In this case we would use [LI([LINECTR]|GLCode)] to grab the GLCode value from the current Line Item. Remember the [LINECTR] variable will return the line number we are on so that ensures you get the correct values from the current line item.
Update
Once the search is configured, you need to set the Update settings. In our case, we only need to update one field but you could add several here. For now, add a single Value Update. Set the Field Name field to Description. This is the name of the field in the LINE ITEM that we want to update. Then set the Value field to what value you want to place into that field. In our example, the field int he Inventory record is named “Description”, so we would use “[Description]” to pull its value. Next, be sure to tell the system which line you wish update. The “Line #” field should be set to [LINECTR] to make sure you put the Description int the proper line.
Final Thoughts
Line Item updates are quite common when processing Invoices and Purchase Orders but are also used on other applications. For instance, maybe you have an HR record and want to record the employee’s children. Line items may be a perfect way to handle that. No matter what the application, looping through line items is something that comes up. Knowing how to use the Line Item Loop and Search and Update actions will get you a long way toward handing your line item needs.
Related Articles
Workflow Part 1 – Introduction to the Basics
Duplicate Checking with Workflow