Search code examples
.netquickbooksquickbooks-online

QuickBooks v3 .NET API: How to retrieve Invoices that have Balance < TotalAmt

The following code that is written to retrieve paid invoices and partially paid invoices:

List<Invoice> paidInvoices = invoiceQueryService.ExecuteIdsQuery("SELECT * FROM Invoice WHERE Balance < TotalAmt AND DocNumber IN ('1123', '1124') MAXRESULTS 1000").ToList();

This line throws the following exception:

'BadRequest. Details: QueryParserError: Encountered " "<" "< "" at line 1, column 69.
Was expecting one of:
    <EOF> 
    "." ...
    <AND> ...
    "between" ...
    "contains" ...
    "=" ...
    "in" ...
    "iterator" ...
    "like" ...
    "maxresults" ...
    "!=" ...
    <NOT> ...
    <OR> ...
    "order" ...
    "orderby" ...
    "startposition" ...
    "=" ...
    "!=" ...
    "=" ...
    "!=" ...
    "=" ...
    "!=" ...
    "." ...
     , '

Is there any way to retrieve this Invoices without bringing all to memory and then filter them?

I want to do the filtering on QuickBooks API.

Solution

  • The API does not allow you to use another field as a value parameter. The value parameter must be a literal, i.e. 'true', 'false', '0.0'.

    So if you wanted to obtain all paid invoices you should use:

    List<Invoice> paidInvoices = invoiceQueryService.ExecuteIdsQuery("SELECT * FROM Invoice WHERE Balance = '0.0' AND DocNumber IN ('1123', '1124') MAXRESULTS 1000").ToList();

    Try the API explorer at: https://developer.intuit.com/app/developer/qbo/docs/api/accounting/all-entities/invoice

    Here you can run your query to see if the syntax is correct.

    enter image description here

    Here's more information on how to structure your queries: https://developer.intuit.com/app/developer/qbo/docs/learn/explore-the-quickbooks-online-api/data-queries