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

  • Home
  • Get Help
  • How Do I
  • Line Item Automation Workaround - is there a solution that doesn't require Google Sheets?
Best answer

Line Item Automation Workaround - is there a solution that doesn't require Google Sheets?

  • 21 January 2020
  • 7 replies
  • 434 views
J
Userlevel 1

I am starting to develop an app using Knack. Knack interfaces with Qbooks Online. I have an issue with one zap that has been identified. I built a zap to create several new records in Knack from line item data from Qbooks but I don't like the fact that I had to use Google Sheets to accomplish it and the zap is currently quite slow (given the multiple interfaces (Qbooks -> Google Sheets -> Knack).

Zapier's documentation(see below) states that Knack is not one of the 20 or so apps providing line item automation. So I used the recommend "Google Sheets solution" that was suggested (temporary repository for the line item data).

Although my zap is working I would like to know if there is solution that eliminates the need for Google sheets?

Thanks!

image.png


icon

Best answer by JuliaG 4 March 2020, 12:41

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.

7 replies

AndrewJDavison_Luhhu
Userlevel 7
Badge +10

Hi @JuliaG

Sadly, no. Unless Knack's integration gets updated to support line items, you're going to have to stick with Google Sheets in the middle.



We're Zapier Certified Experts - we help businesses do cool stuff with automation | www.luhhu.com
TimS
Userlevel 7
Badge +3

Hi @JuliaG !

Tim here from Zapier Support. Andrew is correct that Google Sheets is the most user friendly way to set this up. There is one other way, if you're familiar with Javascript or Python by using a Code by Zapier Step in your Zap.

Most Steps in Zapier give an output of one JSON object wrapped in an array. When using any value from that object in a subsequent Step, that Step will run once. If you use a Code Step to output multiple JSON objects of the same structure wrapped in an array instead, the later Steps will run once for each object in the array instead, creating the loop you're currently getting with Google Sheets. 

A common example of this is that you're already familiar with is a Zap Trigger. Triggers that query an app for new data to Trigger get 0 or more objects in an array returned, and then the Zap runs once for each object. 

​When you're testing this in the Zap editor, similar to how you can only use one Sample from a Trigger, you'll only see the first object, and the Zap Steps will only run once when testing. When running live, they will run once per object.

If you need to break the loop, that can be a bit tricky, but there is a way to do it. Give each object a count value from 0 counting up. At the end of the loop, add a Filter that only continues the Zap if the count value is equal to 0. The Filter will be run once for each object in the loop, but will only pass once, meaning that you'll be back to not looping for the rest of the Zap. If you don't need to go back to non-looping, you don't need to worry about breaking the loop. 

I hope this is helpful in giving you another possibility to explore!


J
Userlevel 1

Thanks for the reply Tim S. I think your solution will work. I ended up solving it in a similar but slightly different approach. I'll document it here in case it can help someone else...

First off I should state that I discovered that the accountant was moving from Qbooks Desktop to Qbooks Enterprise which is not supported by Zapier, Qbooks Online is. I solved this interface issue by exporting the sales orders from Qbooks into a excel file. I wrote vba code to flatten the Qbooks report so that all the necessary cells are populated, push this data into Google Sheets. These new records in Google Sheets cause a zap to trigger.

Each row in GS contains Sales Order data as well as Container data however each Sales Order (SO) has many containers. Similar to what you suggested, the VBA code populates a 'NEW SO' column with a 'Y' if it's a new SO. This column is crucial for the zap to run properly.

Zap: New record in Google Sheet triggers zap to create new records in 2 tables in Knack.

Logic: if "NEW SO' field contains a 'Y' then follow path A

Path A: create new record in Knack SO table

Delay - it didn't work without this delay; needed time to create above record

create a new record in Container table

Else Path B:

create a new record in Container table

Thanks to all for the comments. Fairly new to Zapier and it's been a great solution for our small business.


Danvers
Userlevel 7
Badge +12

Thanks for sharing the solution that you found, @JuliaG!


O
Userlevel 2

Hello everyone, I am glad to realize that this seems to be hot topic for Zapier users.

@TimS is there any JS examples available that we can use please? or a source where a non technical user can get illustrated? Thanks in advance.


Danvers
Userlevel 7
Badge +12

is there any JS examples available that we can use please? or a source where a non technical user can get illustrated?

We don’t have any official resource on that, but I think that this would be a great question for the Developer Discussion category @OscarM! 🙂

D
Userlevel 1

Thank you very much @TimS for pointing out in the right direction.

 

We had exactly the same problem as posed here on the first post: insert multiple records (Line-items) on a Knack DB at once. The “for loop” functionality was indeed replicated through a Zap Code with Python.

 

Best Regards

 

PS - also thank you for the tip regarding “only one record” while testing, it spares another few hours of unnecessary troubleshooting

Terms & Conditions