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();
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;
}
private string processRequestFromQB(string request)
{
try
{
return rp.ProcessRequest(ticket, request);
}
catch (Exception e)
{
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 specific listID of Customer.

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 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 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 = new QBXML();
allinvoices = fromXmlString(response);
var invoiceList = allinvoices.QBXMLMsgsRs.InvoiceQueryRs.InvoiceRet.Find(x => x.CustomerRef.FullName == customerName);
if (invoiceList != null)
{
string responses_itemLines = processRequestFromQB(buildListidRqXml(invoiceList.CustomerRef.ListID));
InvoiceLineItems.QBXML allinvoices_itemLines = new InvoiceLineItems.QBXML();
allinvoices_itemLines = fromXmlString_LineItems(responses_itemLines);
List<InvoiceLineItems.InvoiceRet> invoiceList_itemLines = allinvoices_itemLines.QBXMLMsgsRs.InvoiceQueryRs.InvoiceRet;
List<InvoiceLineItems.InvoiceLineRet> InvoiceLineRet = new List<InvoiceLineItems.InvoiceLineRet>();
foreach (var itemLines in invoiceList_itemLines)
{
foreach (var item in itemLines.InvoiceLineRet)
{
if (item.ItemRef != null)
{
if (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 like:


<!--?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.