How can I get the invoices that has been paid on an specific date from QuickBooks to .NET app?

Question

I am trying to get a list with all the invoices from QuickBooks that has been paid on an specific date.

I found in a blog the way to get all the invoices from QuickBooks.

            bool sessionBegun = false;
            bool connectionOpen = false;
            QBSessionManager sessionManager = null;

            try
            {
                //Create the session Manager object
                sessionManager = new QBSessionManager();

                //Create the message set request object to hold our request
                IMsgSetRequest requestMsgSet = sessionManager.CreateMsgSetRequest("US", 8, 0);
                requestMsgSet.Attributes.OnError = ENRqOnError.roeContinue;

                //Connect to QuickBooks and begin a session
                sessionManager.OpenConnection("", "IDN InvoiceAdd C# sample");
                connectionOpen = true;
                sessionManager.BeginSession(@"C:\Users\Public\Documents\Intuit\QuickBooks\Company Files\MyCia.qbw", ENOpenMode.omDontCare);
                sessionBegun = true;

                IInvoiceQuery invoiceQueryRq = requestMsgSet.AppendInvoiceQueryRq();

                invoiceQueryRq.IncludeLineItems.SetValue(true);

                //Send the request and get the response from QuickBooks
                IMsgSetResponse responseMsgSet = sessionManager.DoRequests(requestMsgSet);
                IResponse response = responseMsgSet.ResponseList.GetAt(0);
                IInvoiceRetList invoiceRetList = (IInvoiceRetList)response.Detail;

                var invoices = new List<Invoice>();

                if (invoiceRetList != null)
                {
                    for (int i = 0; i < invoiceRetList.Count; i++)
                    {
                        IInvoiceRet invoiceRet = invoiceRetList.GetAt(i);

                        var invoice = new Invoice
                        {                                                        
                            QuickBooksID = invoiceRet.TxnID.GetValue(),
                            EditSequence = invoiceRet.EditSequence.GetValue()
                        };
                    }
                }
            }

And after that I can check the "IsPaid" parameter.

But I'm thinking that when I'll have a really big amount of invoices this will not longer be a efficient Solution.

So how can I filter my query to just get the invoices that has been paid on that specific date?

Note: I'm using C# with the QBFC13Lib Library.

Answer 1

I would probably do a query for all received payments on the day(s) that you are looking for, include the linked transaction, then query for those specific invoice transaction IDs. This would ensure that you get the smallest amount of data and you can ensure that you have only those payments on the day that you need. Here's a snippet of how I would do this:

// Holds a list of our invoices to query for
List InvoiceTxnIDList = new List();

// Create payment query IReceivePaymentQuery pmtQuery = MsgRequest.AppendReceivePaymentQueryRq(); pmtQuery.ORTxnQuery.TxnFilter.ORDateRangeFilter.TxnDateRangeFilter.ORTxnDateRangeFilter.TxnDateFilter.FromTxnDate.SetValue(FromDate); pmtQuery.ORTxnQuery.TxnFilter.ORDateRangeFilter.TxnDateRangeFilter.ORTxnDateRangeFilter.TxnDateFilter.ToTxnDate.SetValue(ToDate);

// Process query and get results IMsgSetResponse MsgResponse = SessionManager.DoRequests(MsgRequest); IResponse response = MsgResponse.ResponseList.GetAt(0); if (response.StatusCode == 0) { IReceivePaymentRetList pmtRetList = (IReceivePaymentRetList)response.Detail;

    // Loop through our payment list
    for (int index = 0; index < pmtRetList.Count; index++)
    {
        IReceivePaymentRet pmt = pmtRetList.GetAt(index);

        // Check to see if we have any linked transactions
        if(pmt.AppliedToTxnRetList != null)
        {
            // Loop through all the linked transactions and see if it is
            // already on our query list
            for (int indey = 0; indey < pmt.AppliedToTxnRetList.Count; indey++)
            {
                IAppliedToTxnRet appTxn = pmt.AppliedToTxnRetList.GetAt(indey);
                if(!InvoiceTxnIDList.Contains(appTxn.TxnID.GetValue()))
                    InvoiceTxnIDList.Add(appTxn.TxnID.GetValue());
            }
        }
    }

// Create a query for all the txnIDs that we found
MsgRequest.ClearRequests();
MsgRequest.Attributes.OnError = ENRqOnError.roeStop;

for (int index = 0; index < InvoiceTxnIDList.Count; index++)
{
    IInvoiceQuery invQuery = MsgRequest.AppendInvoiceQueryRq();
    invQuery.ORInvoiceQuery.TxnIDList.Add(InvoiceTxnIDList[index]);
}

// Process the request and get the invoice ret list
// *****
// *****

Answer 2

Well, I found out the answer by myself looking through the fields of the objects there you have it. If anyone else is interested in.

Any filter you want you just have to use the "ORInvoiceQuery.InvoiceFilter" properties of the "invoiceQueryRq" object. And VERY IMPORTANT at the end to use "SetValue" I had a hard time trying to use equal to operator to set the value.

invoiceQueryRq.ORInvoiceQuery.InvoiceFilter.ORDateRangeFilter.ModifiedDateRangeFilter.FromModifiedDate.SetValue(DateTime.Now.AddDays(-1),true);

invoiceQueryRq.ORInvoiceQuery.InvoiceFilter.ORDateRangeFilter.ModifiedDateRangeFilter.ToModifiedDate.SetValue(DateTime.Now.AddDays(1),true);

invoiceQueryRq.ORInvoiceQuery.InvoiceFilter.PaidStatus.SetValue(ENPaidStatus.psPaidOnly);

Finally place this code just after the following sentence:

IInvoiceQuery invoiceQueryRq = requestMsgSet.AppendInvoiceQueryRq();