I need a reort that shows when I received a Purchase Order and the quantity received. Should also show how much I have sold of the item since it was received. Example
Ordered 10 keyboards and received 8 of them on 1/1/08. Since 1/1/08 I have sold 6 keyboards.
You are correct in that I can get both info in these report. The only drawback is that the sold qty reflects the amount from the filter date. So if I filter last month PO received it gives me the sale of only last month. I want to show the sales in this case would be last month to date. I cannot do this in this report, because sold date and received date are filtered at the same time. Can u help me configure this report or have any other ideas.
"Jas> Try the Item Movement History Report for an item by item report of what you > want. >
You're right - I couldn't see how a report would handle, but I typed up a query that I tested against one PO in my db and it seemed to return the right info....test it in your system (replace the po number with the po you are interested in) - hope this helps....
select i.itemlookupcode, poe.itemdescripti> Hi Jason,
Thank you Convoluted. It works fine but one more favor from you. I would like the sum(te.quantity) to be the quantity I have sold of the item since it was last received from the PO. Let me know if you can help. Thanks in advance.
RP
"c> You're right - I couldn't see how a report would handle, but I typed up a
Try it this way - replace po.ponumber = '0000136' with your po number....
select i.itemlookupcode, i.description, cast(i.lastreceived as char(11)) as LastRcvd, sum(te.quantity) as sold_since, i.lastsold from item i left join transactionentry te on i.id = te.itemid left join [transaction] t on te.transactionnumber = t.transactionnumber where t.time > i.lastreceived and i.id in (select itemid from purchaseorderentry poe inner join purchaseorder po on poe.purchaseorderid po.id where po.ponumber = '0000136') group by i.itemlookupcode, i.description, i.lastreceived, i.lastsold
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.