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

April 2, 2018 5 min read

We all know about Quickbooks desktop version, it’s the place where we can manage Sales, Inventory, Products, Suppliers and many more.We can manage whole company data in QuickBooks for a desktop.

There are 3 different Versions of Quickbooks:

QuickBooks Online
QuickBooks Payments
QuickBooks Desktop

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

In Quickbooks desktop SDK we can develop desktop software that integrates Quickbooks easily, Which may be used for small business company’s.

In Quickbooks desktop SDK there are mainly two ways to communicate with Quickbooks Desktop:

QBXML
QBFC

In this blog, I have used QBXML for getting data.

Now Let’s focus on the topic How to find Product Price based on Customer buying History in Quickbooks desktop applications.

For archiving the goal I have 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 Specific Customer’s ListID or FullName with InvoiceLineItems true for getting invoices of that specific Customer.
Get All line items in new List from the Customer Invoices and Search for the specific items the last price from the list.
Set it in the textbox of Price.

Now see all points one by one.

Get All Invoices from Quickbooks:

For getting data of all invoice 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;
}

Send Specific Customer’s ListID or FullName with InvoiceLineItems true for getting invoices of that specific Customer:

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

We also have to request for Invoice Line Items otherwise it will return the invoice to the customer but there is no any 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;
}

Get All line items in the new List from the Customer Invoices and Search for the specific items the last price from the list:

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

Set it in the textbox of a price:

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

So this is how we can find Product Price 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 for Customer Wise invoice, XML should be like this:


<!--?xml version="1.0"?-->
<!--?qbxml version="13.0"?-->
         610000-1071512668         
            true

Hope you enjoyed this article and may it help you out to get better solutions for Quickbooks Desktop.

Still, if you are finding any more difficulties with the implementation then you can ask for help from me or reach out via Quickbooks API Integration Services.