Skip to Content
0
Former Member
Jan 18, 2012 at 01:19 AM

How to not select an invoice if a certain line item type is present?

34 Views

Hi,

I've created a report that lists all invoices that have a particular line item type on it. For example, I wanted to produce a revenue report by item type, "P-Tank". So, the invoice table links to the invoice line items table. Each line item record has a "resource id" on it. The resource id links to the resource type table. So, if invoice #8 has, say, three line items and one of them has a resource type of "P-Tank" then I report on it. Basically, I'm making a report grouped by Resource ID and the Resource ID must by of Resource Type 1, 2 or 3. So, this report I've solved.

Now, I want to report on those invoices that do not have either a resource type 1, 2 or 3 item on it. If I use a formula:

"Not ({resource_type.resource_type_id} in [1, 2, 3]) that doesn't work because it just omits the line item that is of resource type 1, 2 or 3....but still leaves the invoice on the report with the other line item types. * I want to identify any and all invoices that do not have either a line item resource type 1, 2, or 3 on it.*

Any help is much appreciated!

thx,

Mark