About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Apps Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Best answer

Inserting Line items from 2 tables to Xero

  • 27 March 2021
  • 5 replies
  • 71 views
D

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

Danvers
Rank
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?

D

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).

D

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

D

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

Paolo
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!

Terms & Conditions