Line Item Variables

Variable

Action or Value

Example

[LINEITEMSCOUNT()]

[LICOUNT()]

[LICOUNT(where|name)]

Returns the number of line items for the current Record

This is very useful in looping workflow actions.

where = Field name and value combination to limit the line items returned. Can be repeated as many times as necessary using ^ as a separator per field. You can use 3 elements for range searching. I.e. Amount=1 for only line items where Amount is set to 1. Amount=1=100 for only line items where Amount is between 1 and 100.

You can also use <> to find lines where a variable is not set to a value.

Wildcards are supported in both the name and the value of the 'where' logic.

Matching is NOT case sensitive.

name = The Named Record to use. This is ONLY valid in workflow operations.

[LICOUNT()] = 4

[LICOUNT(Matched=Yes)] = 2

[LICOUNT(Matched=Y*)] = 2

[LICOUNT(Le*ers=AB*D)] = 1

Finds count of lines where any field that starts with 'LE' and ends with 'ERS' has a value that starts with 'AB' and ends with 'D'.

[LI()]

[LINEITEMS()]

Returns a JSON collection of all line items for the current Record.

This is typically used along with the OBJPROP command to gather data from line items.

{Quantity: 1, Description: "Part 123", Amount: "123.45"}

[LI(#)]

[LINEITEMS(#)]

Returns the entire line item # as a JSON object.

# = Line number of the values to return

[LI(1)] = {Quantity: 1, Description: "Part 123", Amount: "$123.45"}

[LI(#|Field)]

[LINEITEMS(#|Field)]

-or-

[LI(#|Field||likematch|where|name)]

[LINEITEMS(#|Field||likematch|where)]

Returns a field value from the specified line item

# = Line number of the value to return. Use 0 (zero) to return all lines' values.

Field = Name of field to return

likematch = Set to YES, Y, TRUE, or 1 to use LIKE syntax (with * as a wildcard) to find the proper variable name.

You can also use <> to find lines where a variable is not set to a value.

name = The Named Record to use. This is ONLY valid in workflow operations.

[LI(1|Amount)] = 123.45

[LI(1|Amo*||Yes)] = 123.45

[LI(0|Amount)] = [{Amount: 123.45},{Amount: 10},{Amount: 5.02}]

[LI(0|Amount|;)] = 123.45;10;5.02

[LI(0|Amo*|;|Yes)] = 1;10;5

[LI(0|Amount|||Amount=1=100)] = 12.45;10

[FIELD(#)]

Simplified form of [LI(#|Field)] but returns only a single line item field value for the specified row

# = Line number of the value to return. It must be > 0 to work. If <= 0 then the variable is ignored.

Note: This variable takes a lesser precedence than other variables. If you used a reserved field name for your line item name then that will be returned instead of the line item data. Likewise, if you have a Custom Variable the same name as the line item field then the Custom Variable is returned instead.

[Amount(1)] = $123.45

[LINENUM(field|val|valto|name)]

Returns the first line number in the record where the field specified matches a value.

field = Name of the line item field to look for

val = the val that is in the field specified

valto = IF specified then this is the TO of the range and val is the FROM of the range

name = The Named Record to use. This is ONLY valid in workflow operations.

[LINENUM(Amount|100)] = Returns first line number where the amount field = 100

[LINENUM(Amount|100|200)] = Returns first line number where the amount field is from 100 to 200.

[LITOTALDIFF(RecordID|LineItemField)]

Returns the amount by which a record's line items are out of balance with its header fields. It adds up a Number line item field across all of the line items, then subtracts the header field or fields named in that line item field's Total Fields setting on the Record Type. This is the same difference the Record screen shows when the line item total does not match. Use it in a workflow rule to stop or reroute an invoice whose lines do not add up, or in a merge template or email to report the difference.

A result of 0.00 means the record is balanced. A negative result means the line items add up to less than the header fields, and a positive result means they add up to more. For example, -25.00 means the line items are 25.00 short.

The Total Fields setting can name one header field, or several header fields that are each added or subtracted, such as Amount + Tax - Shipping. The variable reads the same setting, so it always agrees with the Record screen.

RecordID = ID of the record to check. Leave it blank to use the current record, including any changes that have not been saved yet. Another record can be checked only when the current user has access to it. On public pages and forms, only the current record can be checked.

LineItemField = Name of the line item field whose total is checked. Type the field name as it is, without square brackets. Leave it blank when only one line item field on the Record Type has a Total Fields setting. If more than one does, name the one you want.

The result is a plain number with two to four decimal places, with no currency symbol and no thousands separator, so it can be used inside other variables such as [MATH] and [IF].

The variable returns an empty value when there is nothing to compare: the line item field has no Total Fields setting, the line item field was left blank and either no line item field or more than one has a Total Fields setting, a header field named in Total Fields no longer exists, all of the header fields are empty, or a header field holds something that is not an amount. When some of the header fields have values and others are empty, the empty ones count as zero. It also returns an empty value when the record ID given is not a number or is a record the user does not have access to.

Amounts can include a currency symbol ($, €, £ or ¥), thousands separators, and negative amounts written as (50.00) or 50.00-. Line item values that are not amounts are skipped, and deleted line items are not counted.

[LITOTALDIFF] is not the same as [DEVIATION]. [DEVIATION] returns the difference between two values as a fraction of the second value (for example .05 for a 5% difference), while [LITOTALDIFF] returns an amount.

This variable works anywhere bracketed variables are replaced, including workflow actions, E-form properties, merge templates and emails. It does not work in Importer or Exporter settings, because there is no record there.

[LITOTALDIFF()] = returns -25.00 when the line items on the current record add up to 25.00 less than the header fields in Total Fields, or 0.00 when they match

[LITOTALDIFF(|Amount)] = checks the Amount line item field on the current record

[LITOTALDIFF(4521|Amount)] = checks the Amount line item field on record 4521

[IF([LITOTALDIFF()]|=|0|Balanced|Out of balance)]` returns Balanced when the line items match the header fields, otherwise Out of balance. An empty result (nothing to compare) also returns Out of balance.

[ABS([LITOTALDIFF()])] = returns how far off the record is in either direction, without the minus sign

Parses out a text blob and places the data elements into the line items of the current Record.

StringToParse = The string blob to parse

LineDelimiter = The character to use to split each line

ColumnDelimiter = The character to use to split each element of each line

SkipLines = The number of lines at the top of the blob to skip while processes to handle header rows (default = 0)

LineNumberOffset = The line numbers to skip so you can add data to the end of existing line iems (default = 0)

field... = list of field names to use for the columns. Blank field names are skipped)

Command = The command to use for each line. Useful to evaluate the line as to whether or not to process it.

[TOLINEITEMS(Make,Model,Qty~Ford,Mustang,2~Ford,Taurus,10|~|,|1|0|Make,,Quantity|<<IF(<<line>>|C|SKIP||<<line>>)>>)]

This example parses out the text using ~ for lines and , for columns. It skips the first line and does not skip any line item numbers.

It saves the Make and Qty columns into the Make and Quantity line item fields and skips the Model column completely.

Each line is compared to see if they contain the work SKIP. If they do they are blanked out so a line item will not be added for it. If it does not contain SKIP then the line is processed.