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 your own Line-items for an invoicing Action using the Line Itemizer Utility#

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 Line Itemizer Utility...

Let's say you’re using QuickBooks Online and you need to create multiple line-items in an invoice with the following information:

Description Quantity Price Subtotal Id
Blue Shirt 1 10 10 7
Pink Shirt 2 12 24 9
Green Shirt 5 14 70 8

You can use one Formatter step to convert that data into the Line-item format that the Invoice Step needs.

Here's how that step would look:#

Formatter step showing how the line-items are created with Line Itemizer

Here are the line-item values we can now map into the later Steps of our Zap:#

Line-item values created by the Line Itemizer can be used in later Steps

You then use the output of the Line Itemizer in the Invoice Step:#

QuickBooks Online step showing how the newly created line items come into play

Let’s see what that looks like with Setup Preview turned off:#

QuickBooks Online step showing the names of the newly created line items

Here’s the resulting invoice in Quickbooks Online, with the line-items you created:#

Invoice created in QuickBooks Online showing multiple line items based on the previous Formatter steps

Add Shipping Information to your Invoice using the Line Itemizer Utility#

Some e-commerce applications provide shipping as a separate item, with its own set of fields. Many invoice applications want shipping as a standard line in the line-item fields your Zap provides. The Formatter Line Itemizer Utility can facilitate this by appending (or prepending) the shipping value(s) to these standard line-item fields.

Here’s an example set of values you might have from your Trigger:#

Line-item values with standalone shipping price value

You can append the shipping value along with static text values (if needed) in the Formatter Step:#

Formatter step showing how values can be appended to line-items with Line Itemizer

Here’s what that looks like with Setup Preview turned off:#

Formatter step showing value names as they are appended to line-items with Line Itemizer

You then use the output of the Line Itemizer in the Invoice Step:#

QuickBooks Online step showing how the newly appended line items come into play

Here’s the resulting invoice in Quickbooks Online, with the shipping information as a line-item:#

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

If you’d rather prepend the values so that the shipping line shows up first, then put the shipping values into the fields first, add a comma, and then map in the line items that you’re prepending to, like this:

Formatter step showing how values can be prepended to line-items with Line Itemizer

Create Line Items with Subtotals using the Line Itemizer Utility#

Some apps, like Quickbooks Online, require an Amount line item property to be supplied in addition to the Price and Quantity fields that you're getting from your Trigger app. The Amount field should be equal to Price multiplied by Quantity.

If your Trigger app does not supply this value and your quantities are higher than 1, you can perform this calculation for multiple line items using either the Formatter Numbers Action (Spreadsheet Style Formula) or the Formatter Utility Action (Line Itemizer). Let's take a look at Line Itemizer.

First, map in your existing Line item values and change the Create Subtotal Property? field from no to yes.

Here's an example of how the Line Items would be mapped out:#

Line Items with Subtotal property toggled on

Here's the first two line items from testing that step where we can see the Subtotal has been calculated:#

Line Items output including Subtotals

In your Quickbooks Online or other action requiring a Subtotal or Amount value, you can map in the resulting Subtotal property along with the Price and Quantity properties in their appropriate fields.

The Subtotal calculator also works great when you're appending values such as a shipping line item to your line items:#

Line Items output including Subtotals and Shipping

Create a Tag Text Field using the Line-item to Text Utility#

There are some instances when you need your Line-item field as formatted text so it can be used in an Action that doesn't support line-items. This is sometimes referred to as "flattening" a line-item. Tags are an instance of this, as it seems each application uses their own format to delineate multiple tags, and most don't support line-items. Hubspot is an example here, it uses a string of text, with each tag delineated by a ; Here is how you'd get this Tag format for Hubspot using the Line-item to Text Utility:

The Trigger application provides Tags as a line-item field, in this case a product category tag:#

category

Let's run the category field this through the Line-item to Text utility, replacing the , with ;#

tags

Now we are ready for the Hubspot Create/Update Contact Step to add this to the tags field:#

hubspot step

The tags are now part of newly created Hubspot contact#

hubspot

Convert a Line Item Property to Multiple Single Values using the Line Item to Text Utility#

If you need to use a specific value that is part of a series in a line item in your Actions, you can separate those values. One way to do that is to map the line item into the input of the Line Item to Text Utility:

Line item as input

Note that you don't need to enter a separator. This utility is also used for 'flattening' line items into a single piece of text, and you can add the separator between each value. The separator won't be added to the individual values. Let's take a look at the output:

Output of line items as text and separate values

You can see that each part of the Line Item was returned as item_n. The first value in the Line Item will always be item_1 and the last item is repeated as item_last in case you always need to extract the last item from the Line Item property being input into this transform.