How to Find Product Price Based on Customer Buying History from QuickBooks Desktop Applications

Introduction

We all know about QuickBooks’s desktop version. It’s where we can manage Sales, Inventory, Products, Suppliers, and more.

Using QuickBooks, we can manage the whole company’s data on a desktop.

There are 3 different versions of Quickbooks

1. QuickBooks Online
2. QuickBooks Payments
3. QuickBooks Desktop

QuickBooks Desktop SDK Overview

1. QBXML
2. QBFC

I have divided whole things into small parts for understanding purposes.

1. Get All Invoices from Quickbooks desktop.
2. Send the specific customer’s ListID or full name with InvoiceLineItems true to get invoices for that specific customer.
3. Get All line items in the new List from the Customer Invoices and Search for the specific items with the last price from the list.
4. Set it in the textbox of Price.

Now, see all the points one by one.

QuickBooks Desktop stores valuable customer purchasing information that businesses can use to improve sales efficiency and pricing accuracy.

One common requirement in desktop applications is automatically displaying a product’s previous selling price based on a customer’s buying history.

Instead of manually reviewing invoices, applications can retrieve historical transaction data directly from QuickBooks and suggest the most recent price used for a specific customer and product combination.

Step 1: Get All Invoices from Quickbooks

string request = "InvoiceQueryRq";
connectToQB();
int count = getCount(request);
string response = processRequestFromQB(buildInvoiceQueryRqXML(customerName));

private string buildInvoiceQueryRqXML(string fullName)
{
    string xml = "";
    XmlDocument xmlDoc = new XmlDocument();

    // Build the request envelope
    XmlElement qbXMLMsgsRq = buildRqEnvelope(xmlDoc, maxVersion);
    qbXMLMsgsRq.SetAttribute("onError", "stopOnError");

    // Create InvoiceQueryRq element
    XmlElement InvoiceQueryRq = xmlDoc.CreateElement("InvoiceQueryRq");
    qbXMLMsgsRq.AppendChild(InvoiceQueryRq);

    // Set the request ID attribute
    InvoiceQueryRq.SetAttribute("requestID", "0");

    // Get the XML string
    xml = xmlDoc.OuterXml;

    return xml;
}

private string processRequestFromQB(string request)
{
    try
    {
        // Process the request and return the response
        return rp.ProcessRequest(ticket, request);
    }
    catch (Exception e)
    {
        // Handle exceptions
        MessageBox.Show(e.Message);
        return null;
    }
}

QBXML allinvoices = new QBXML();
allinvoices = fromXmlString(response);
public static QBXML fromXmlString(string xmlString)
{
    var reader = new StringReader(xmlString);
    var serializer = new XmlSerializer(typeof(QBXML));
    var instance = (QBXML)serializer.Deserialize(reader);
    return instance;
}

Step 2: Retrieve Invoice Line Items for a Specific Customer

In this, we will send the Customer’s ListID.

var invoiceList = allinvoices.QBXMLMsgsRs.InvoiceQueryRs.InvoiceRet
    .Find(x => x.CustomerRef.FullName == customerName);

if (invoiceList != null)
{
    string responses_itemLines = processRequestFromQB(buildListidRqXml(invoiceList.CustomerRef.ListID));
}

private string buildListidRqXml(string listID)
{
    string xml = "";
    XmlDocument xmlDoc = new XmlDocument();

    XmlElement qbXMLMsgsRq = buildRqEnvelope(xmlDoc, maxVersion);
    qbXMLMsgsRq.SetAttribute("onError", "stopOnError");

    XmlElement InvoiceQueryRq = xmlDoc.CreateElement("InvoiceQueryRq");
    qbXMLMsgsRq.AppendChild(InvoiceQueryRq);

    if (listID != null)
    {
        XmlElement RefElement = xmlDoc.CreateElement("EntityFilter");
        InvoiceQueryRq.AppendChild(RefElement).InnerXml = "" + listID + "";

        XmlElement RefElement1 = xmlDoc.CreateElement("IncludeLineItems");
        InvoiceQueryRq.AppendChild(RefElement1).InnerXml = "true";
    }

    xml = xmlDoc.OuterXml;
    return xml;
}

// Process the XML response
InvoiceLineItems.QBXML allinvoices_itemLines = new InvoiceLineItems.QBXML();
allinvoices_itemLines = fromXmlString_LineItems(responses_itemLines);

public static InvoiceLineItems.QBXML fromXmlString_LineItems(string xmlString)
{
    var reader = new StringReader(xmlString);
    var serializer = new XmlSerializer(typeof(InvoiceLineItems.QBXML));
    var instance = (InvoiceLineItems.QBXML)serializer.Deserialize(reader);
    return instance;
}

Step 3: Get Line Items and Retrieve the Last Purchase Price for a Specific Product

// Retrieve list of invoice line items
			List invoiceList_itemLines = allinvoices_itemLines.QBXMLMsgsRs.InvoiceQueryRs.InvoiceRet;
			
			List InvoiceLineRet = new List();
			
			// Iterate through each invoice
			foreach (var itemLines in invoiceList_itemLines)
			{
				// Iterate through each line item in the invoice
				foreach (var item in itemLines.InvoiceLineRet)
				{
					if (item.ItemRef != null)
					{
						if (item.ItemRef.FullName == itemText)
						{
							// Add the line item to the result list
							InvoiceLineRet.Add(item);
						}
					}
				}
			}
			
			// Iterate through each line item in the filtered result list
			foreach (var item in InvoiceLineRet)
			{
				rate = item.Rate;
			}

Step 4: Set the Retrieved Price in the TextBox

Rate = bindLastPriceOfItem(customerName, selectedProductValue, Rate);
textBox_Price1.Text = Rate;

private void comboBox_Item1_SelectedIndexChanged(object sender, EventArgs e)
{
    var customerId = comboBox_Customer.SelectedIndex;
    var customerName = comboBox_Customer.Text;
    ComboBox cmb = (ComboBox)sender;
    int selectedIndex = cmb.SelectedIndex;
    var selectedProductValue = cmb.Text;
    string rate = "0.00";
    rate = bindLastPriceOfItem(customerName, selectedProductValue, rate);
    textBox_Price1.Text = rate;
}

private string bindLastPriceOfItem(string customerName, string itemText, string rate)
{
    string request = "InvoiceQueryRq";
    connectToQB();
    int count = getCount(request);
    string response = processRequestFromQB(buildInvoiceQueryRqXML(customerName));
    
    QBXML allInvoices = fromXmlString(response);
    var invoiceList = allInvoices.QBXMLMsgsRs.InvoiceQueryRs.InvoiceRet.Find(x => x.CustomerRef.FullName == customerName);

    if (invoiceList != null)
    {
        string responsesItemLines = processRequestFromQB(buildListidRqXml(invoiceList.CustomerRef.ListID));
        InvoiceLineItems.QBXML allInvoicesItemLines = fromXmlString_LineItems(responsesItemLines);
        List<InvoiceLineItems.InvoiceRet> invoiceListItemLines = allInvoicesItemLines.QBXMLMsgsRs.InvoiceQueryRs.InvoiceRet;
        List<InvoiceLineItems.InvoiceLineRet> invoiceLineRet = new List<InvoiceLineItems.InvoiceLineRet>();
        
        foreach (var itemLines in invoiceListItemLines)
        {
            foreach (var item in itemLines.InvoiceLineRet)
            {
                if (item.ItemRef != null && item.ItemRef.FullName == itemText)
                {
                    invoiceLineRet.Add(item);
                }
            }
        }

        foreach (var item in invoiceLineRet)
        {
            rate = item.Rate;
        }
    }
    
    disconnectFromQB();
    return rate;
}

private string buildListidRqXml(string listID)
{
    string xml = "";
    XmlDocument xmlDoc = new XmlDocument();
    XmlElement qbXMLMsgsRq = buildRqEnvelope(xmlDoc, maxVersion);
    qbXMLMsgsRq.SetAttribute("onError", "stopOnError");
    
    XmlElement invoiceQueryRq = xmlDoc.CreateElement("InvoiceQueryRq");
    qbXMLMsgsRq.AppendChild(invoiceQueryRq);

    if (listID != null)
    {
        XmlElement refElement = xmlDoc.CreateElement("EntityFilter");
        invoiceQueryRq.AppendChild(refElement).InnerXml = "<ListID>" + listID + "</ListID>";
        
        XmlElement refElement1 = xmlDoc.CreateElement("IncludeLineItems");
        invoiceQueryRq.AppendChild(refElement1).InnerXml = "true";
    }

    xml = xmlDoc.OuterXml;
    return xml;
}

private string buildInvoiceQueryRqXML(string fullName)
{
    string xml = "";
    XmlDocument xmlDoc = new XmlDocument();
    XmlElement qbXMLMsgsRq = buildRqEnvelope(xmlDoc, maxVersion);
    qbXMLMsgsRq.SetAttribute("onError", "stopOnError");
    
    XmlElement invoiceQueryRq = xmlDoc.CreateElement("InvoiceQueryRq");
    qbXMLMsgsRq.AppendChild(invoiceQueryRq);
    invoiceQueryRq.SetAttribute("requestID", "0");
    
    xml = xmlDoc.OuterXml;
    return xml;
}

public static QBXML fromXmlString(string xmlString)
{
    var reader = new StringReader(xmlString);
    var serializer = new XmlSerializer(typeof(QBXML));
    var instance = (QBXML)serializer.Deserialize(reader);
    return instance;
}

public static InvoiceLineItems.QBXML fromXmlString_LineItems(string xmlString)
{
    var reader = new StringReader(xmlString);
    var serializer = new XmlSerializer(typeof(InvoiceLineItems.QBXML));
    var instance = (InvoiceLineItems.QBXML)serializer.Deserialize(reader);
    return instance;
}

NOTE: When you are requesting a Customer Wise invoice, XML should be like this:

<?xml version="1.0" encoding="UTF-8"?>
<?qbxml version="13.0"?>
<QBXML>
    <QBXMLMsgsRq onError="stopOnError">
        <InvoiceQueryRq>
            <CustomerRef>
                <ListID>610000-1071512668</ListID>
            </CustomerRef>
            <IncludeLineItems>true</IncludeLineItems>
        </InvoiceQueryRq>
    </QBXMLMsgsRq>
</QBXML>

Conclusion

Retrieving product prices based on customer buying history can help businesses maintain pricing consistency, improve customer service, and reduce the time spent searching through historical invoices.

By leveraging the QuickBooks Desktop SDK and QBXML requests, developers can access customer invoices, retrieve line-item details, and identify the most recent price used for a particular product.

Frequently Asked Questions (FAQs)

How do I get customer purchase history from QuickBooks Desktop?

Customer purchase history can be retrieved using the QuickBooks Desktop SDK by querying invoices associated with a specific customer. The returned invoice data can then be analyzed to identify purchased products, quantities, and pricing information.

Can QuickBooks Desktop provide the last selling price of a product?

Yes. By retrieving invoice line items for a specific customer and product, developers can identify the most recent transaction and use its rate as the last selling price.

What is QBXML in QuickBooks Desktop?

QBXML (QuickBooks XML) is an XML-based communication format used by the QuickBooks Desktop SDK. It allows external applications to send requests to QuickBooks and retrieve accounting, customer, invoice, inventory, and transaction data.

Why use customer buying history for pricing?

Using customer buying history helps maintain pricing consistency, improves quoting accuracy, reduces manual data entry, and enables sales teams to reference previous transactions when preparing new invoices or estimates.

How do I retrieve invoice line items in QuickBooks Desktop?

Invoice line items can be retrieved by sending an InvoiceQuery request with the IncludeLineItems element set to true. This returns detailed product-level information associated with customer invoices.

Can I search invoices by Customer ListID?

Yes. QuickBooks Desktop allows developers to filter invoice queries using the customer’s ListID, enabling retrieval of invoices associated with a specific customer record.

What information can be retrieved from invoice line items?

Invoice line items can contain product details, item names, quantities, rates, descriptions, amounts, and other transaction-related information depending on the data stored in QuickBooks.

Is QuickBooks Desktop SDK suitable for custom desktop applications?

Yes. The QuickBooks Desktop SDK enables developers to build custom integrations and desktop applications that can interact with customer records, invoices, inventory items, payments, sales orders, and various accounting data stored within QuickBooks Desktop.

Can historical product pricing be used to auto-populate invoice forms?

Yes. Many custom business applications use historical invoice data to automatically populate product prices, helping users create invoices faster while maintaining pricing consistency for returning customers.

What are the benefits of integrating QuickBooks Desktop with custom applications?

Benefits include automated data synchronization, reduced manual entry, improved operational efficiency, real-time access to accounting data, customized workflows, and better reporting capabilities across business systems.