[greg.hill]

Hi,

I am trying to figure out a way to create a saved search that will show only orders that have one or many 'license' line items but that do not also have a 'maintenance' line item (therefore, I want a search to find "license only" sales orders).

We have a custom field on all our items that would indicate if the item itself is license vs maintenance, but I can't figure out a way to use this attribute to limit my saved search. If I include this field as a criteria for my search and set the value to show "None Of = Maintenance", my results don't include maintenance lines but the other lines of the order that include maintenance would show up. I want the whole order excluded from my results if any one line includes an item that has the attribute = maintenance.

Is this possible?

Thanks,

Greg
This is a cached copy. Click here to see the original post.

[michoel]

You could do this by using Summary Criteria.

You would need to Group the results by Sales Order in the Results tab, and then in the Criteria tab, click 'Summary' and add two conditions, fixing the exact formula expression per how your custom field is set up

Summary Type = SUM

Field = Formula (Numeric)

Formula = CASE WHEN {custcol_islicense} = 'T' THEN 1 ELSE 0 END

Description = is greater than 0

Summary Type = SUM

Field = Formula (Numeric)

Formula = CASE WHEN {custcol_islicense} = 'F' THEN 1 ELSE 0 END

Description = is equal to 0

[Author]

Posts