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 Products
Learn more about Zapier's new products.

Zapier Tables Zapier Interfaces

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.

Best answer

Invoicing through Quickbooks Online but ACH'ing clients through GoCardless

  • 10 July 2023
  • 9 replies
  • 249 views
Justin-at-Singenuity
Userlevel 1
Badge

Hi Zapier community,

 

I’ve been tasked with finding an automation solution for our team to create invoices in our Quickbooks Online environment and have Zapier automatically trigger our GoCardless environment to ACH our clients. We would then need a Zap to update the Quickbooks Online invoice once it is successfully paid via GoCardless.

A few tidbits that might help paint a clearer picture:

  • ~150 clients
  • Billed bi-weekly with different amounts each billing period

If anyone could help describe the Zaps we would need to solve for this, I would greatly appreciate it. Is it possible to create a Zap that can input the client’s Quickbooks Online invoice balance in a CSV and have GoCardless read from it? Like a master list instead of having to create a separate Zap for each client?

If it helps, I have created a CSV containing the following for each of our clients:

  • Name, Email, Address, Bank Account/Routing #, Mandate ID, Quickbooks ID, and GoCardless ID.

 

Thank you very much,

Justin

icon

Best answer by SamB 11 July 2023, 11:35

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.

9 replies

SamB
Rank
Userlevel 7
Badge +11

Hi there @Justin-at-Singenuity, welcome to the Community! 🎉

I’m not sure you’d need a CSV file containing every client’s balance. The Find Customer action for QuickBooks is able to find the customer balance details so you would not need a separate Zap for each client. The only issue is that you would need a “Find Customer” action for GoCardless as well, in order to obtain the necessary Customer ID needed for the Create Instant Bank Payment action. And there isn’t one available currently.

There is an existing feature request for a Find Customer action to be added to the GoCardless app, so I’ve submitted your vote for that. I can’t make any promises as to when it would become available but we’ll email you as soon as it is.

In the meantime, if you can export a list of your Customers and their IDs from GoCardless into a Google Sheets spreadsheet you could search that spreadsheet for the customer ID instead of using a Find Customer action. Here’s an outline of the Zaps I’m thinking you’d need for this:

Zap 1 - Requests the payment when and invoice is created

  • Trigger: New Invoice (QuickBooks Online) - triggers when a new invoice is created.
  • Action: Find Customer (QuickBooks Online) - returns information for the customer such as their balance.
  • Action: Numbers > Spreadsheet-Style Formula (Formatter by Zapier) - If you need to do a calculation of how much to charge based on the customer’s current balance the you can use a formula to do so here. Otherwise you can just omit this action step and just charge the amount supplied by the New Invoice trigger step. 
  • Action: Lookup Spreadsheet Row (Google Sheets) - searches for the customer to find their GoCardless ID number.
  • Action: Create Instant Bank Payment (GoCardless) - creates an instant bank payment request.



Zap 2 - Updates the Invoice when a payment is made

  • Trigger: Payment Paid Out (GoCardless) - triggers when a new payment is made.
  • Action: Find Invoice (QuickBooks Online) - searches for the invoice ID based on the invoice number provided by the trigger step.
  • Action: Update Invoice (QuickBooks Online) updates invoice to mark it as having been paid. You’ll need to reference the invoice using the Custom value option on the Invoice field, then select the ID field that’s output by the Find Invoice action. This will ensure that the Zap is able to update the correct invoice.


How does that approach sound? If I’ve missed anything or you run into any issues in setting this up just let us know. Always happy to help further! 🙂

Justin-at-Singenuity
Userlevel 1
Badge

Thank you so much for the incredibly thorough answer @SamB! I am going to follow your steps today and will circle back with the results. I genuinely appreciate the time you took to write that out for me!

 

If I follow and create the steps in your guide without any adjustments, which pricing plan would we need to sign up for in order to continue after our trial ends?

 

Thanks again Sam! You’re amazing!

SamB
Rank
Userlevel 7
Badge +11

Aww, thank you @Justin-at-Singenuity. You are most welcome! 🤗  

 

As it uses Multi-step Zaps you’d need to be on a Starter plan at least. That said, the kind of plan you’d need would also depend on your task usage. If you find you need more tasks then you’d need to upgrade to a higher plan. And if you need less tasks later on you can always downgrade your plan. You’re free to upgrade, downgrade or cancel your Zapier plan at any time. 

Hope that helps. 🙂

Justin-at-Singenuity
Userlevel 1
Badge

Hi @SamB I’m sorry to burden you one more time. Your guide has been extremely helpful and I’ve successfully created Zap #1 to where an invoice is being created in QB and properly charged in GoCardless.

 

However, I’m currently stuck on this step on Zap #2

  • Trigger: Payment Paid Out (GoCardless) - triggers when a new payment is made.
  • Action: Find Invoice (QuickBooks Online) - searches for the invoice ID based on the invoice number provided by the trigger step.

Unfortunately when I attempt to set up the “Find Invoice” action, it is wanting to search the GoCardless payment details for the QB invoice number, but I’m not seeing QB invoice # listed in the GoCardless transaction details.

Circling back to Zap #1, on the “Create Invoice in Quickbooks Online” action, I have the “Invoice Number” field set to “AUTO_GENERATE”, if that helps you.

Now that I’m typing this out, am I supposed to use the GoCardless invoice # in the “Find Invoice” on Zap #2?

Sorry if this is written poorly or confusing. I’m happy to provide screenshots or make a Loom video if you’d prefer.

Thanks again for all of your help Sam! We are so close to having it automated!

Justin

Justin-at-Singenuity
Userlevel 1
Badge

Hi @SamB, last question I promise!

 

In addition to the clarification above, how do I have multiple line items on the Quickbooks Online invoice that I created in the 1st Zap?

 

Thanks!

Justin

Justin-at-Singenuity
Userlevel 1
Badge

To add a little bit of detail to the line item question:

Our two line items on invoices are Booking Fees and Photo Fees. Where it gets tricky, is some clients have lower Booking Fee rates or lower Photo Fee rates due to their revenue volume.

So for example, Client A has a booking fee rate and photo fee rate of 4% and 3% of their net sales of each line item, while Client B’s BF & PF rates are set at 3.5% and 2.5%.

These rates are static and do not change from pay period to pay period. So Client A will always be at 4% / 3% for their booking and photo fees.

 

Thanks @SamB !

SamB
Rank
Userlevel 7
Badge +11

I’m so sorry for missing your replies here previously, @Justin-at-Singenuity. Happy to help with this! 🙂

There’s quite a few questions to get through so I’ll try to answer each one in turn…

Unfortunately when I attempt to set up the “Find Invoice” action, it is wanting to search the GoCardless payment details for the QB invoice number, but I’m not seeing QB invoice # listed in the GoCardless transaction details.

There should be a Description field available for the Payment Paid Out (GoCardless) trigger which, assuming it uses the same value that’s set in the Create Payment Request action you could use to get the Invoice ID. In Zap 1, in Description field on the Create Payment Request action you’d need to add the ID field from the New Invoice (QuickBooks Online) trigger. Then in the Find Invoice action on Zap 2 you can, in theory, use the invoice number from Payment Paid Out trigger as the search value.
 

Now that I’m typing this out, am I supposed to use the GoCardless invoice # in the “Find Invoice” on Zap #2?

Yes, in Zap 2 is where you’d want to search for the invoice as you only want to update it once the payment is made and the Zap isn’t able to see whether the payment has been made yet or not in Zap 1. So Zap 2 triggers once a payment has been made, then searches for the relevant invoice and updates it to mark it as paid.
 

In addition to the clarification above, how do I have multiple line items on the Quickbooks Online invoice that I created in the 1st Zap?

To have Zap 1 create the invoice you’d need to use Create Invoice (QuickBooks Online) action. It supports line items so in the line item fields (that appear just under the Invoice Number field) you’d need to supply line items containing the relevant information.

With the Product/Service field (on that Create Invoice action) you’d need to use the Custom value option and select the IDs for the product/service. If you only have the product/service names not their IDs, then you’d need to add a Find Product(s) search step to find the product/service IDs:
77fad8f45ef247e8c538cf3f21876bcb.png

Then in the Find Product(s) action you’d select the line items from the relevant trigger or action that contains the relevant product/service names. I’m not sure what trigger you’re using for Zap 1 but I’m guessing that’s what would supply the relevant product/service names here. Is that the case or does the trigger not supply the information as line items?

 

Our two line items on invoices are Booking Fees and Photo Fees. Where it gets tricky, is some clients have lower Booking Fee rates or lower Photo Fee rates due to their revenue volume.

So for example, Client A has a booking fee rate and photo fee rate of 4% and 3% of their net sales of each line item, while Client B’s BF & PF rates are set at 3.5% and 2.5%.

These rates are static and do not change from pay period to pay period. So Client A will always be at 4% / 3% for their booking and photo fees.

Ah, ok so are these booking fees the only line items that would ever need to be added? If so, are they set up in QuickBooks as two different products/services with multiple rates - e.g. Booking fee (4% or 3%), Photo fee (4% or 3%)? Or are there a different product/services set up for each rate - e.g. Booking fee 4%, Booking fee 3%, Photo fee 4%, Photo fee 3%?

If the booking fee and the photo fees are two separate product/services in QuickBooks with different rates then I think you’d need to set the rate in the Rate field. But we’ll need to figure out which rate to apply. Is there any information in QuickBooks that would indicate which rate should be applied?

If so, perhaps a Find Customer action would return that information, then you could use a Formatter (Utilities > Lookup Table) action to check that information and output the relevant rate that they would need to be charged for the Booking and Photo Fees. Would that work or are the rates to charge for each client stored somewhere else and manually referenced when creating an invoice?
 

Looking forward to hearing from you on this!

Justin-at-Singenuity
Userlevel 1
Badge

Hi @SamB - thank you SO much for helping me tackle this. I hope to address your questions and please don’t hesitate to let me know if I missed anything or if additional detail will help.

 

In Quickbooks, we have two products/services with multiple rates. In this case, the Booking Fee & Photo Fee both have a default rate of 0 and the current process is to refer to a Google Sheet that has the client’s Fee %’s and then manually update them in the QB invoice. As you can imagine, it’s a very manual and time-consuming process.

Here are the settings for both the Booking Fee and Photo Fee along with an example invoice when they’re initially added.

Photo Fee
Service Fee

 

Rate is manually changed 

 

As mentioned before, we have a master Google Sheet that lists out all of the clients and their rates, so I initially set up the Zapier Google Sheet like below, hoping to associate rates this way. So for example, the Client in Row #2 would use the % Fee in K2 (Booking rate) and N2 (Photo Rate) and the Client in Row #3 would use K3 and N3, and so forth. While I was able to get it to work for the Booking fee, unfortunately I wasn’t able to figure out how to set up the Zap to look for the 2nd service/product (Photos) and then create a total balance based off both sub-totals.

 

Unfortunately I’m not seeing anywhere on the QB Customer Details page where we currently assign rates to each client, but I do want to check-in with you to see if adding two Custom Fields (bottom-right where it says Sales Rep) would be a potential solution to indicate the Booking and Photo rates for each client:

 

Thank you so much Sam! I can’t thank you enough!

SamB
Rank
Userlevel 7
Badge +11

Thanks for getting back to me with those helpful details and screenshots, @Justin-at-Singenuity!

Since the row in Google Sheets would contain both fee rates (Booking and Photo) then if you’re using a Lookup Spreadsheet Row (Google Sheets) action the rates for both should be available to select in the Zap.

What you’d need to do though is turn those rate values into line items that you can pass into the Rate field on the Create Invoice action. You’d also want to include the product IDs in the line items that are created. That way you’d be able to send QuickBooks the ID and rate information for both product/services as line items.

To create the line items that you’ll need to add a Formatter (Utilities > Line Itemizer) action to the Zap. We’ve got some guides that help explain how to use Formatter’s Line Itemizer action here:


With the custom fields, I’m not 100% sure whether those details would be available to select from a Find Customer (QuickBooks) action, but even if they were since they would be separate fields you’d still need to add them into the Line Itemizer action in order to create the line items that Quickbooks would need.

Hopefully the above guides will help in getting that set up, but if you get stuck at all just let us know! 🙂

Terms & Conditions