In this tutorial Isaac from Upstart.co demonstrates how to automate the extraction of monthly transaction counts for multiple clients by linking QuickBooks Online, Airtable, and Zapier. He walks through the prerequisite setup of Airtable and QuickBooks accounts, emphasizing the need to store each client’s QuickBooks company ID in an Airtable CRM table. He then builds a Zapier workflow that runs on a monthly schedule, filters active clients with a company ID using an Airtable formula, calls the QuickBooks API via a GET request to retrieve a transaction list for the specified month, and uses an AI‑assisted code step to parse the API response and count the transactions. The resulting count is written back to a “Monthly Transaction Summary” table in Airtable. Isaac also outlines several extensions of this framework, such as pulling revenue, net income, cash balances, and setting up threshold‑based alerts, illustrating how the integration can support broader financial monitoring and client outreach. The video concludes with a reference to a deeper dive on QuickBooks reporting via Zapier.
Chapters
Hey, this is Isaac from Upstart.co, and in this video I'll share how you can connect QuickBooks to Airtable using Zapier. And I'll also share a few use cases and what becomes possible once you do. The use case we'll focus on today is how to pull how many transactions each of your clients has, although the same framework would apply to do much more, which I'll discuss briefly as well. So with that, let's get into it. All right, so I'm assuming you already have an Airtable account and a QuickBooks account, which you should if you are watching this video or if you want to implement something like this. We have like, it's just a very basic CRM or like client information database with people, with companies. And the more— the important thing to keep in mind here is that I have in that list of companies, I have added this column to suggest or to have available the QuickBooks company ID. This is gonna come in handy later on. As for pretty much any automation you're working on, it's something that you're gonna want to have available for the automations to know which client relates to which specific QuickBooks account. Okay, now let's go into Zapier directly here and go ahead and connect both Airtable and QuickBooks. Should be an easy process if you go ahead and click on that Create button at the top and create on steps, which are automated workflows we want to create. And I'll just click here on the second step to look for Airtable, and you'll see that it prompts me to select an account. In this case, I already have an account connected. If you don't have an account connected, you're going to want to come here and click on Connect a New Account. It's going to ask you for what do you want to provide to that account. You might suggest it's all resources, or you might select the specific base that you're working on. You'll click on Grant Access. That's pretty much it, the process to connect it. Of course, you have to be logged in for that process to happen that quickly, so make sure you do so before, before going through this. Eventually, we're also going to need QuickBooks Online connected. I'm going to go ahead and effectively do the same process and making sure that account is available. We use a demo account that has some data for June 2024 for the purposes of videos like this. So if I click on that, this is the one I'll be working with. But again, similarly, if don't have an account, you'll click on that Connect New Account button. Make sure you have, uh, using the production QuickBooks environment unless you're testing and you want to kind of work on the sandbox for a little bit. But fundamentally, as you saw, I went through that process and connected the account I am currently logged in into. If you're using an accountant account, which you probably do if you are an accounting firm listening to this, it will actually show you a second screen where you'll have to pick your firm, see if you want to install that for your firm or for one of your individual clients, and you have to connect. those individually. For now, I'm just gonna go ahead and go back to the dummy data that we're gonna be working with. And let's just continue the process. Effectively, I have my 2 accounts connected at this point. In the example we shared earlier that I want to know how many transactions our clients have, let's assume that I simply wanna know that on a monthly basis. You might have noticed that in our Airtable base, I simply have a table that's called Monthly Transaction Summary. And here I'm simply gonna want the month, The client, I want to connect it to the client because I can do some interesting things later on, and the transaction count. Let's go back to the automation. And because I'm doing it monthly, I'm going to want to have a schedule using some of the Zapier built-in tools. And in this particular instance, the frequency of that schedule is going to be every month. I can select the day of the month. You might want to select, for example, the 1st, or maybe if you know that the 1st of the month, all transactions won't be there for one reason or another, if there's transactions that come in later on or journal entries that you're going to make, In your reconciliation process, you might want to select maybe something like, for example, the 10th of the month or the 15th of the month. Let's just suggest that this is going to be at midnight. Click on continue, click on test trigger. Let's go to the second step now where we're going to be finding the records from our table. Why are we doing this first? I could go directly here to get the list of transactions, but actually I don't know about which clients. So that's where the table we built comes in handy because I can come here and say, let's go into the accounting CRM base. Let's go into the companies table. And in this particular instance, let's assume I'm just looking for specifically active clients, which is the base we were looking at before here. But I only want to pull active clients that have the QuickBooks company ID because those are the ones I'll be able to use this for anyway. So to do that, let's actually use a formula, right? I can search through a formula and the way it works is that what I always like to do is just actually create a formula here to make sure and validate that it is— that's doing what you want to do. So for example, in this case, give me a 1 if the QuickBooks company ID is available and give me a 0 if it's not. I can leverage some of the kind of AI features that are built into Airtable. For there to create the formulas for me. And if I copy that, I'll create the field first. This is what I like to do just to confirm that the formula would work. And in this case, it's telling me I would pull this record because it's the one that has company ID available. It's another way of saying Zapier, hey Zapier, give me the active clients that have a QuickBooks company ID. Okay, I'm going to click on test. And in this particular instance, I should be able to get that Ops or Test with all of the details, including the QuickBooks company ID. It's worth noting that for the purposes of this demonstration, I'm using find record, singular. You would probably want to use find records, which we've done for other clients and more in-depth implementations where we want to do this not for just one client, but for, for example, for all the clients in your roster. Let's keep going. Let's go to the 3rd step here where we're going to be using QuickBooks Online to get the list of transactions. That's kind of what I'm aiming for here. Now, you might notice that if you scroll through this, you'll see the ability to create certain things, create payments, create sales receipts, a bunch of things, right? Send, update. Void, and then you can find accounts and find invoices, find products. There's no really such a thing as kind of getting a list of transactions. However, you do have the access to the API, which really increases or expands the possibilities you have available to you. Although a little bit more technical, fundamentally an API, it's linked to a robot. So in this particular context, I just need to find a link and transform that link into the specific format the API requires. There's a handy little box here that SAP has to give you an easy reference to the API documentation from QuickBooks, which is very helpful. And because I know where to go, I'll just go straight into it. But fundamentally, in this particular instance, what we're looking for is to get a transaction list. Okay, if you scroll and kind of see some of these little gray boxes that the QuickBooks API has available for you, you'll notice that you have a link to the production base URL. Remember, we picked production because we want to test this in real life. And also you get like what happens after that, right? This particular instance, I have company reports transaction lists and I have GET as the method. That also says like here in Zapier, I want to leave that as GET and I want to just simply populate the rest of it. Now you might notice that this is a large number and it represents the QuickBooks company ID. As we mentioned earlier, we're going to want to change that to come from the previous step, which in this particular case, it's going to pull the QuickBooks company ID from the demo account that we have available. Now beyond that, technically speaking, you would want to make sure that you have a start date and an end date, right? This is just parameters that the API itself is going to tell you they have as additional— let me show you, right? Additional things that are going to allow you to effectively pull the transactions from that month. So let's actually test this. If I click on continue and I click on test step, there's a bunch of things I'm going to get. But primarily I'm looking for 2 things. Number one, this 200, which means the API call was successful in computer language. And I'm looking for like a body. But this essentially, if I start to read through it, you'll see, okay, the time, the report name, the start of the period, which is the 1st of June, 2024, the end of the period, and then a bunch of other data. Okay, I'm going to copy that for now because I want to provide that context to the next step that we'll be working on. Okay, the next step we'll have, and please don't be intimidated by this, but it's code. And fortunately for us, the Pure interface itself has an ability for us to generate that code with AI, although we could always rely on another AI tool for this. So I'm just going to give it the context. I will provide you with a transaction list report from QDO. I want to count number. I want to get in return the number of transactions from it. Here's the data structure from the response. Okay, now this is super long, unfortunately, so I'll just like delete a couple of things, but it should have sufficient data there to do its thing and come back with an answer. Because this is a lot of text, it might take, you know, a minute or two, probably less. All right, this is now done, and you'll see that actually it's smart enough to suggest, hey, this is the data we're going to need to input, and this is effectively what we're going to do. And not to bore you with the details, but it's essentially saying, hey, this report has a certain number of rows, let's see if those rows are available, and let's count how many of them are there. So I'm going to use this code and do— actually, just looking at this, I know that this isn't the right value. It should actually be the body itself. So let's do that and let's test and see if the number comes up, which it should. And I think just cross-referencing with what I had here before, it's 89 transactions. That's exactly correct. So this particular client demo account for the month of June 2024 has 89 clients. And again, if I kind of put the dates dynamically, I can make sure that this works and runs every month. And if I do the work as well of connecting different clients, making sure I have the IDs of all the different clients, I can run this every month for every client automatically. The last step here is going to be to go back to our table and populate that because remember we have that little base. So I'm going to create a record, go to the accounting CRM base, go to the monthly transaction summaries table. And in this particular instance, I'm going to add June 2024. Again, I'll do this manually this time around. But in this case, actually, you should be able to pull the name of the client and the transaction count from the step above. And if I test that, should be able to see that. Come in right here and there you go. There we have it. So later on you can come from here and say, hey, we should have a series of transactions here and we could even suggest or later on add a rollup field to say, you know, count the total number of transactions. For example, you could say sum the values and then you can see that 89 count from this kind of client list table. And as more and more transactions come up, That transaction count will be adding the different months you have available. Part of the reason why firms sometimes want to do this is to compare transactions with revenue. So you can also, you know, look into how to pull revenue from your account for this particular client and then have that comparison across clients. There's a lot of other use case ideas that we've done in the Airtable environment. Things like getting data from individual accounts, like net income, meals, charitable contributions in order to make quarterly tax estimates. Like, you could do that. You could pull net income or sales or cash balance to see how your clients are doing. You could definitely do that. You could also have triggers for certain— to be alerted of certain financial thresholds, like when the client hits, I don't know, $100 grand in net income, or their cash balance drops below $20,000, or maybe they've had a negative a month or 3 months in a row. Like, once that happens, you want to be notified so you can reach out and support them. All of this becomes possible when you have QuickBooks connected it to something like Airtable and Zapier. And when you have some— the right structure, data structure in place to make that scalable across clients. So this is something I get excited about. And actually I recorded a video about how to pull report and reporting data from QuickBooks using Zapier that will give you a little bit more context to and get a little bit deeper into how to pull those specific numbers if you wanted to do something similar. So click in the card on the screen to watch that one next. I'll see you in the next one. And until then, stay efficient.
