Export item data from invoice

Hi,

i would like to made the report "Part Sales by Customer Report" with access.

I made the invoice from order. I extract all data from the table oelineitem, but i can't have the information of the qty and the item for each invoice.

For example, :

I create 3 invoice from 1 order, i would like to display the quantity of a certain item for each invoice, but in oelineitem i only have one rows for tthe item with the total qty that is inthe order.

how can i do to have the single qty for invoice for this item?

Parents
  • 0

    The OElineItem table has the line items for kits, invoices, orders, quotes and returns.  To get the invoice line items you would need to filter on the oelineitem.Type.   An oelineitem.type = "I" are invoices, oelineitem.type = "O" are orders, etc.  

    In the OE, transfer, custom exports create a template and use the OE invoices line items data source.  Select the fields you need including the oelineitem Shipqty, oelineitem unitprice and oelineitem extendedprice.  This will show the shipped quantity on each invoice.  Filter on the item you are looking for.

  • 0 in reply to CShular

    In oelineitem I have only fktype 0 because I convert the order into invoice. I haven't any other type of value.

    In OEInvoice I have only the Header information from the invoice, but where are the items information? In OELineitem i suppose.

    When I insert an order for one item (500 pcs) and I create 3 invoices for 200 200 100 pcs and after I create a report and join oeinvoice whit oeline item i have 3 rows with 500 pcs and not three rows for 200 200 and 100.

    Have you understood my problem?

  • 0 in reply to fabio

    When you convert the order to an invoice(s) it creates new oelineitem records for each invoice. These have an oelineitem fktype = "I".

    To get the invoice line items you would need to link the ARinvoice SKARinvoice to the OEInvoice FKARInvoice (if you want to filter on invoice number). Then link the OEinvoice SKoeinvoice to the OElineitem FKentity and filter on fktype = "I".   This will give you the line items for each invoice.  You can pull the ship quantity from the oelineitem shipqty

Reply
  • 0 in reply to fabio

    When you convert the order to an invoice(s) it creates new oelineitem records for each invoice. These have an oelineitem fktype = "I".

    To get the invoice line items you would need to link the ARinvoice SKARinvoice to the OEInvoice FKARInvoice (if you want to filter on invoice number). Then link the OEinvoice SKoeinvoice to the OElineitem FKentity and filter on fktype = "I".   This will give you the line items for each invoice.  You can pull the ship quantity from the oelineitem shipqty

Children