does anyone know how to get a search/report of sales orders that were invoiced, but not fulfilled, at a particular date?
the reason is: at the end of each month we want to get a figure for the amount of sales to move to deferred income - ie the sales we've invoiced, to get payment in advance, but haven't fulfilled and thus can't recognise in the P&L
i can create a search (shown below) that finds all the sales order line items that are either invoiced before X OR fulfilled after X, which is a superset of what i want, but i can't do an AND - it returns nothing, because the search is returning two rows for each sales order line item, one row for the invoice and one row for the fulfillment.
this is my search:
Type is Sales Order And Date Created is on or after 1.1.2007 And Status is none of Sales Order:Pending Billing, Sales Order:Cancelled And (( Applying Link Type is Order Bill/Invoice And Applying Transaction : Date is on or before 31.3.2007 ) Or ( Applying Link Type is Receipt/Fulfillment And Applying Transaction : Date is after 31.3.2007 ))
(the date created & status criteria are to reduce the set it searches and stop it timing out)any ideas? thanksben