In this blog post, we’re going to show you how to build an advance query using our tool. Once you understand how it works, you’ll be able to easily apply it in all of our products from G-Accon for Xero/QuickBooks/HubSpot.
Let’s start.
In our example, we will be logging in to our Xero organization through G-Accon for Xero. Our goal today is to build a query that will join three tables – Invoices, Payments, and their corresponding Accounts.
The first step is to select the desired Invoice’s attributes: Invoice Number, Description, Line Items, Payment ID, and so on. This Payment ID will be used when we join the second (Payments) table.
The next step is to select Add-ons -> G-Accon for Xero -> Get Accounting Data -> Payments. After selecting the Payments option from the drop down menu, you can click “all attributes” or “some attributes” based on your preferences. You can also expand attributes to see the whole structure with sub-attributes, as well as collapse it. The Account ID attribute needs to be selected, as well.
Next, select the “Filter By IDs” radio button, highlight the required Payment ID in the sheet, and use the “reference” icon to insert the data range.
To display our data in the same table, we need to adjust the start cell. In our case, it is D1:
In this example, we joined two tables: Invoices and Payments and their corresponding details.
Finally, we will join the 3rd table – Accounts. To do this, click Add-ons -> G-Accon for Xero -> Get Accounting Data -> Accounts. After selecting the Accounts option from the drop down menu, click the Filter By IDs radio button, highlight the required Account ID in the Google Sheet, and use the “reference” icon to insert the data range.
Then we can go ahead and execute the query.
As you can see in our final results, we’ve combined the three tables with our desired attributes. This query can be manually or automatically refreshed by using our workflows.
