Hi,

I created a script in .NET c# that inserts invoices into Quickbooks from an in-house application. The issue I am having is that certain customers belong to a price profile (for example 10% discount). When I generate the invoice for this customer in Quickbooks, it modifies the price per each invoice line item according to the discount. The invoices that are generated from .NET apply the correct billing codes, but the price does not reflect the price profile discount (For example a billing code may be 165 an hour, but at 10% discount it should show 148.50). I cannot find anywhere on the invoice table that would tell Quickbooks to calculate the invoice based on the price profile, and if I manually add a line item to an invoice generated from code it does apply the discount. I have tried sending adjusted line rates, and have event tried the lineratepercent on the invoiceline table and nothing seems to be working.

I would greatly apprecieate it if someone could point me in the right direction, as I do not know where else to look. Thank you.

Ok, the first step is check if the customer has any special discounts. In QuickBooks we can see it like this:

Using QODBC this can be done using the following query:

SELECT Customer.ListID, {fn IfNull(PriceLevel.PriceLevelFixedPercentage, 0)} as Discount
FROM Customer, PriceLevel
where Customer.PriceLevelRefListID = PriceLevel.ListID
and Customer.FullName='Data Access Worldwide'

Just change 'Data Access Worldwide' to the name of your customer.

The next step is find the base rate for the item you are going to invoice. This can be found by doing:

SELECT ListID, SalesOrPurchasePrice as Price FROM Item where FullName = 'Framing'

Just change 'Framing' to the name of your item.

From the above queries I now know that rate is (Price * ((100 + -Discount) / 100)) or in this case it's (55*((100 + -10)/100))=49.50. So it's just a case of using 49.50 as the InvoiceLineRate like this in your insert statement.

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber",
"InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineQuantity", "InvoiceLineRate",
"InvoiceLineSalesTaxCodeRefListID", "FQSaveToCache")
VALUES ('800000AA-1197768105', 'Discount10',
'10000-933272655', 'Picture Framing North Wall', 5.00, 49.50,
'20000-999022286', 0)

I have a look at the invoice I just create in QuickBooks, everything now works out correctly. The "Commercial" discount has been applied!

See: PriceLevelPerItem Updates & Inserts for how to create PriceLevels using QODBC.