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

April 2, 2018 5 min read

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

QuickBooks Online
QuickBooks Payments
QuickBooks Desktop

We can manage all the accounting and invoicing data using QuickBooks Desktop.

QuickBooks Desktop SDK Overview

In QuickBooks desktop SDK, we can develop desktop software that integrates QuickBooks easily, which small business companies may use.

There are two main ways to communicate with QuickBooks Desktop through the SDK:

QBXML
QBFC

In this blog, I have used QBXML to get data.

Now, Let’s focus on finding Product Prices based on Customer buying History in Quickbooks desktop applications.

To achieve this goal, I used the Sample Code of Quickbooks desktop SDK in QBXML.

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

This is a few steps which I want to discuss with you

Get All Invoices from Quickbooks desktop.
Send the specific customer’s ListID or full name with InvoiceLineItems true to get invoices for that specific customer.
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.
Set it in the textbox of Price.

Now, see all the points one by one.

Step 1: Get All Invoices from Quickbooks

For getting data of all invoices from Quickbooks using qbxml, we have to request the Quickbooks using xml.


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;
    }
}

In response, we will get all the invoices in XML form with all the fields of Invoice other than Invoice Line Items. We cannot get all the invoice line items.

For invoice line items we have to pass a specific listID of Customers.

For using further we have to Convert all XML into C# list.


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.

We also have to request Invoice Line Items; otherwise, it will return the invoice to the customer, but there are no Invoice Line Items in that.


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;

This is how we can find product prices based on customer buying history from QuickBooks.

Here is the full combined code; I have provided all parts in one code below.


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>

I hope you enjoyed this article, and it may help you find better solutions for Quickbooks Desktop.

If you encounter any further difficulties with the implementation, you can ask me for help or see our Service Quickbooks API Integration Services.