Best answer

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

  • 21 January 2020
  • 7 replies
  • 436 views

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

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.



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!


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.


Userlevel 7
Badge +12

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


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.


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

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