How to use Line-Items with Formatter#

Line-items are a special type of Zapier field that contain a list of multiple values (also known as an array). Many invoicing, accounting and form applications support line-item fields as a way to more easily represent their data. You can identify this type of field in Zapier when you look at the results of a Test Step and see a list of numbered rows. Here is a simple example with one value per line:

fruit

and a more complex example with many values per line:

lineprice-transform-1

Zapier will take this output and create line-item fields for use in the next Zapier Step, but what do you do when you need to transform each of the values in these line-item fields? That is where Formatter comes in, it is now "line-item" aware. Any transform that works on one field value will now work on all values in a line-item field. Here are some examples of how to use Formatter with these line-item fields:

Transform each date in a line-item field#

Date formatting is very application specific, especially as you move between different geographies. Here is how to transform a line-item date field from a US date format to an EU format using the Formatter Date/Time Format Transform:

Trigger application provides line-item US dates:#

ldate-1

Formatter Step with date transform for EU date format:#

date-2

Results of Formatter Step with dates in EU format:#

date-3

Calculate line price for each product purchased#

Some online sales applications provide the quantity of each product purchased, the price of each product purchased, but no line price for each product. Here is how to accomplish that with Formatter, two line-item fields and the Spreadsheet-Style Formula Transform.

Trigger application provides line-item price:#

lineprice-transform-1

Trigger application provides line-item quantity:#

lineprice-transform-2

Formatter Spreadsheet-Style Formula Transform calculation that multiplies line-item values from each field:#

lineprice-transform-3

Results of Formatter Step (Quantity * Price):#

lineprice-transform-4

Currency formatting#

Many invoicing applications are currency agnostic, so if you need to take those generic invoice amounts and format them for a specific currency, here is how you would do that with Formatter, a line-item field and the Format Currency Transform:

currency-transform

Transform SKUs from an online sales application to an invoicing application#

Many invoicing applications require the use of their specific product SKUs. Many on-line sales apps generate their own unique product SKUs. How do you then, match the product purchased with the product invoiced? Formatter's Lookup Table Utility and line-item fields, that's how:

 

Create Line-Items using the Split Text Transform#

Not all Trigger apps are capable of providing data in line-item format (in order to add lines to an invoice Action, for example). Luckily, we have a way to do it using Formatter's Split Text function.

Example 1#

In the following example we're going to take the 'New Row' Trigger for Google Sheets and use the data from 3 different columns in that row to create multiple line-items.

We'll then use the Google Sheet 'Create Spreadsheet Row' Action that supports line-items, in order to create 3 separate rows from our original data.

Check out this quick video to see how it's done:

 

Exceptions

There are three transforms that do not support line-items today:

  • Number "Perform Math Operation" - you can use the "Spreadsheet Formula" to do math on line-items.
  • Utilities "Pick from List"
  • Shorten URL

Example 2#

Here's another example of how to use the Formatter's Split Text Transform to create Line-items

Let's say you need to create multiple line items in an invoice with the following information:

  • Description
  • Price
  • Quantity

First, you'll want to create 3 column headers in Google sheet. Each pertaining to the information that you need:

Google sheet showing data to be converted to line items

You'll then want to add data under those columns using the following format:

  • Description 1, Description 2, Description 3,...
  • X1, X2, X3,.. (wherein X would denote the price)
  • N1, N2, N3,... (wherein N would denote the quantity)

For example, let's say you want to create multiple line items for the following:

Description Qty Price
Blue Shirt 2 10
Pink Shirt 3 12
Green Shirt 5 14

It would look like this when entered in Google Sheet:

  • Blue shirt, Pink shirt, Green shirt
  • 2,3,5
  • 10, 12, 14

Formatter step split text function settings showing "," as a separator and "All (as line items) as Segment index

Next, you'll need to create 3 Formatter steps to convert that data into the Line item format that the Invoice Step needs to create the multiple line item.

In order to create the multiple line items, you'll need to ensure that each step is set to use:

  1. A comma (,) as the separator
  2. Segment index set to "all (as line items)"

Here's how each step would look like:

Description:

Formatter step showing how the description line item is created

Price:

Formatter step showing how the price line item is created

Quantity:

Formatter step showing how the quantity line item is created

You then use the output of these formatter steps in the Invoice Step:

Xero step showing how the newly created line items come into play

The output of those formatter steps would create the multiple line items that is required to create the multiple :

Invoice created in Xero showing multiple line items based on the previous formatter steps

↑ Was this documentation useful? Yes No (Suggest Edits)
Get Help