Best answer

Inserting Line items from 2 tables to Xero

  • 27 March 2021
  • 5 replies
  • 103 views

I have 2 SQL tables that work toghether as a bill. One has name of a person, date, and ItemID that links to other table, where it can be seen which items does he want to have or buy.

Tables look like this:

 

I want to insert the last added row in table1, but also all the items that belong to that row. 

For example the complete row for the Xero bill should look something like this, for last row in table1:

Joe,2021-27-03,[Fuel,Oil],[20,2],[1.4,1.7]. 

All in one row and in arrays, so it can be imported to Xero bill.

Can this be done somehow through Zapier? I know it has a Format option, where you can get line items from array. But not sure how to do it from 2 tables like this.

icon

Best answer by Paolo 4 June 2021, 06:49

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

5 replies

Userlevel 7
Badge +12

Hi @davidgecko!

There is a Formatter function called Line itemizer that looks like it will do what you need it to. It allows you to add additional line items into existing ones:

Would that do what you need it to? Or is it getting the data from the two tables that you’re having trouble with?

Hi @davidgecko!

There is a Formatter function called Line itemizer that looks like it will do what you need it to. It allows you to add additional line items into existing ones:

Would that do what you need it to? Or is it getting the data from the two tables that you’re having trouble with?

 

Actually I am having problem with how to get data from 2 tables. And then get this data together in one line (array).

Nobody knows how to get data from 2 tables in one Xero bill? :confused:

Nobody knows how to get data from 2 tables in one Xero bill? :confused:

And I would need to get 1 or more rows from Table2. Based on a: Table2.ItemID = Table1.ID

Userlevel 3
Badge +5

Hi @davidgecko, The tricky part about the SQL integration requires for a specific table to be selected when setting up a Zap. It’s not possible to have a Zap trigger from two Tables.

Based on the screenshots, it seems that both tables have “ItemID” as a common field. You could work around this by creating a Zap that has a “Find Row” Action. The structure of the Zap would be:

  • Trigger: SQL Server New Row
  • Action: SQL Server Find Row
  • Action: Formatter Line Itemizer
  • Action: Create Xero Bill

The Zap would trigger from the new row added on the first table and the Find Row action would then pull in the data from the second table using the ItemID as a search parameter. The Formatter step would combine both data sets and create the line items that the Create Xero step needs to function.

 

I hope that helps and please don’t hesitate to reply back if you have questions!