How to associate automatically a quickbooks invoice to a deal hubspot?

[ECals]

Hi everyone, 

I'm looking to associate automaticaly quickbooks invoices with hubspot deals. Actually, 3 integrations are existing in the marketplace, but none of them are doing the match between the invoice that we get in hubspot thanks to the quickbooks integration and the hubspot deal. The match is only between the contact in hubspot and the quickbooks invoice.

Does someone already has to face this situation and find an answer to it?

Thanks for your help!

[kkotarba · Accepted solution]

I've just had to deal with this situation and managed to put together a solution which mostly works.

To clarify, I don't believe that Quickbooks invoices are being associated with contacts as such. They are recreated as HubSpot invoices and the HubSpot invoice is what gets associated with the contact record. The HubSpot invoice that is created does however reference the actual QuickBooks invoice which can be seen by previewing the invoice from the contact record. This solution works in the same way by associating the HubSpot invoice with a deal rather than just the contact record.

This approach works assuming the following but can likely be modified for other use cases:

• You are using the Quickbooks integration referenced here
  • You have Quickbooks -> HubSpot invoice sync enabled in the integration
• You have access to Custom Code workflow actions in your HubSpot teir.
• There is already an existing deal you would like to associate the invoice with
  • The deal must also be associated with the same contact that the HubSpot invoice is associated with.
• You have a Private App access token

The first problem that needs to be addressed is that HubSpot workflows do not let you create associations between existing objects using any of the default actions. We can get around this by using the Custom Code action and the HubSpot API.

The bigger problem is that once the QuickBooks invoice is synced into a HubSpot invoice, there is no precise way to identify which deal that invoice should be associated with (hopefully this gets addressed by the integration at some point). There are a number of ways this can be handled but none of them will be perfect so the goal here is "good enough".

Solution: Create an invoice-based workflow and use the contact association to identify the deal the invoice should be associated with. 

The custom code for this roughly works as follows:

1. Find the contact associated with the HubSpot Invoice. For invoices created by the integrations invoice sync, there should be a contact associated with the HubSpot invoice but this is not always the case.

2. Find all of the deals associated with that contact

3. Identify which deal should be associated with the invoice. In this example, I am just selecting the most recently modified deal but the code can be modified to look at other criteria. 

4. Create the association from deal to invoice.

There are probably cleaner ways to code this but I just slapped together some API requests to get this working. You'll need to set up a secret in the Custom Code action to hold your API access token (if you name it "access_token", the code does not need to be modified at all) but aside from that, you should be able to just plug in the code below and it should work as described.

const hubspot = require('@hubspot/api-client');

exports.main = async (event, callback) => {
  const hubspotClient = new hubspot.Client({"accessToken": process.env.access_token});


  const objectId = event.object.objectId;
  const after = undefined;
  const limit = 500;
  let contactId = undefined;
  let dealId = undefined;
  let dealAssociated = false;


  // Check if there is already a deal associated with this invoice
  try {
    const dealCheck = await hubspotClient.crm.associations.v4.basicApi.getPage('invoice', objectId, 'deal', after, limit);
    if (dealCheck.results.length > 0) {
      dealAssociated = true;
    }
  } catch (e) {
    e.message === 'HTTP request failed'
      ? console.error(JSON.stringify(e.response, null, 2))
      : console.error(e)
  }

  // If there is not already a deal associated, find an associated contact
  if (!dealAssociated) {
    try {
      const contactCheck = await hubspotClient.crm.associations.v4.basicApi.getPage('invoice', objectId, 'contact', after, limit);
     
      if (contactCheck.results.length == 1) {
        contactId = contactCheck.results[0].toObjectId;
      } else {
        console.log(contactCheck.results.length > 1 ? "More than one assciated contact found" : "No associated contacts found");
      }
    } catch (e) {
      e.message === 'HTTP request failed'
        ? console.error(JSON.stringify(e.response, null, 2))
        : console.error(e)
    }
  }

  // If a contact is found, check for deals associated with the contact
  if (contactId) {
    try {
      const dealCheck = await hubspotClient.crm.associations.v4.basicApi.getPage('contact', contactId, 'deal', after, limit);
      
      // If there are deals found, check the modified date for those deals and select the most recent
      if (dealCheck.results.length > 0) {
        const deals = dealCheck.results.map(deal => deal.toObjectId);


        const dealList = deals.map((str) => {
          return {id: str}
        })

        const BatchReadInputSimplePublicObjectId = { propertiesWithHistory: [], idProperty: "hs_object_id", inputs: dealList, properties: ["hs_lastmodifieddate"] };
        const archived = false;

        try {
          const apiResponse = await hubspotClient.crm.deals.batchApi.read(BatchReadInputSimplePublicObjectId, archived);
          if (apiResponse.results.length > 0) {
            const dealDates = apiResponse.results.map((result) => {
              return {id: result.id, modified: result.properties.hs_lastmodifieddate}
            });
            const mostRecentDeal = dealDates.reduce((prev, current) => {
              return (new Date(current.modified) > new Date(prev.modified)) ? current : prev;
            });

            dealId = mostRecentDeal.id;

          }

        } catch (e) {
          e.message === 'HTTP request failed'
            ? console.error(JSON.stringify(e.response, null, 2))
            : console.error(e)
        }
      }

    } catch (e) {
      e.message === 'HTTP request failed'
      ? console.error(JSON.stringify(e.response, null, 2))
      : console.error(e)
    }
  }

  // If a deal has been selected, create the association
  if (dealId) {
    const AssociationSpec = [
      {
        "associationCategory": "HUBSPOT_DEFINED",
        "associationTypeId": 176
      }
    ];

    try {
      const apiResponse = await hubspotClient.crm.associations.v4.basicApi.create('deal', dealId, 'invoice', event.object.objectId, AssociationSpec);
      console.log(JSON.stringify(apiResponse, null, 2));
    } catch (e) {
      e.message === 'HTTP request failed'
        ? console.error(JSON.stringify(e.response, null, 2))
        : console.error(e)
    }
  }
}

It's a year late but hope this helps.

[ITBD_KJ]

Hey @kkotarba,

Thanks so much for putting this together! I've tested it out in my HubSpot instance, and it does exactly what it's supposed to.

However, I need something a little bit different. I have a custom deal property (single-line text) called, "QuickBooks Invoice Number", which is manually updated by my finance team whenever they issue the first invoice for a newly closed won deal.

When this deal property is updated, I'd like the custom code to find the invoice with the matching invoice number and associate it with the deal.

For example:

Deal is updated with QuickBooks Invoice Number = S123456.

Deal-based workflow is triggered and uses custom code to search for a matching invoice.

If an invoice is found, then associate it with the deal.

I'm pretty useless when it comes to writing code, so I was hoping to get your help with putting this together. I realize this is a big ask, but I'd be eternally grateful for any assistance you can provide.

Thanks in advance!

EDIT: Out of desperation, I tried using ChatGPT to generate some code, which unsurprisingly threw a bunch of errors. FWIW, here's the code, if it's worth debugging:

const axios = require('axios');

// Define the HubSpot API key
const HUBSPOT_API_KEY = process.env.HUBSPOT_API_KEY;

// Get the QuickBooks Invoice Number from the deal properties
const quickBooksInvoiceNumber = event.inputFields['quickbooks_invoice_number'];

// Function to search for invoices by QuickBooks Invoice Number
async function searchInvoiceByQuickBooksInvoiceNumber(invoiceNumber) {
    try {
        const response = await axios.post(`https://api.hubapi.com/crm/v3/objects/invoices/search`, {
            filterGroups: [{
                filters: [{
                    propertyName: 'quickbooks_invoice_number', // Assuming the property internal name is 'quickbooks_invoice_number'
                    operator: 'EQ',
                    value: invoiceNumber
                }]
            }]
        }, {
            headers: {
                'Authorization': `Bearer ${HUBSPOT_API_KEY}`,
                'Content-Type': 'application/json'
            }
        });

        const invoices = response.data.results;
        return invoices.length > 0 ? invoices[0] : null;
    } catch (error) {
        console.error(`Error fetching invoice by QuickBooks Invoice Number: ${error.message}`);
        return null;
    }
}

// Function to associate the invoice with the deal
async function associateInvoiceToDeal(dealId, invoiceId) {
    try {
        const response = await axios.put(`https://api.hubapi.com/crm/v3/objects/deals/${dealId}/associations/invoices/${invoiceId}/1`, null, {
            headers: {
                'Authorization': `Bearer ${HUBSPOT_API_KEY}`,
                'Content-Type': 'application/json'
            }
        });

        console.log(`Invoice ${invoiceId} associated with Deal ${dealId}`);
    } catch (error) {
        console.error(`Error associating invoice to deal: ${error.message}`);
    }
}

// Main function to perform the association
async function main() {
    const dealId = event.object.objectId; // Get the current deal ID

    // Search for the invoice by QuickBooks Invoice Number
    const invoice = await searchInvoiceByQuickBooksInvoiceNumber(quickBooksInvoiceNumber);

    if (invoice) {
        // Associate the found invoice with the deal
        await associateInvoiceToDeal(dealId, invoice.id);
    } else {
        console.log(`No invoice found with QuickBooks Invoice Number: ${quickBooksInvoiceNumber}`);
    }
}

// Execute the main function
main().catch(error => console.error(error));

[EFranco]

Are there any plans to be able to map a Quickbooks invoice to a Deal instead of an invoice? 

[BérangèreL]

Hi @ECals,

Thank you for reaching out to the Community!

I understand that you are using this "QuickBooks Online" integration, right?

At the moment, the Quickbooks integration does not support association mappings for deals and invoices.

Currently, with the Quickbooks integration, there is no automatic, retroactive, association between an invoice and a deal.

You could try this workaround: if you press on the number under the "In sync" column in your data sync for Quickbooks, and then press on the Record ID of one of the invoices, it will bring you to the page of invoices in HubSpot filtered for that one. If you press on it's number again and scroll down in the pop-up window on the right, you'll see a section for deals with an "+ Add" button. If you click that, you can either create a deal to associate with it, or associate it with an already existing deal.

Or you can use an invoice based workflow (depending on your subscription) to automate this process. Within this workflow you would enroll invoices based on a criteria, then you can use the "create record" action to create a deal record and associate to the invoice. You can only create new deals however and not associate to existing.

I hope this helps!

Have a lovely day!
Bérangère