I'm wrestling with a search that seems like it should be easier than it it is... but I'm likely overcomplicating it.
I have Invoices that have a custom field on them {cust_thing}.
I want to look at ALL Invoices in the system for the first UNIQUE occurrence of invoices with that field within a date range.
Let me say it this way... IF - over a given time frame (let's say last 1 month) - there is an Invoice created where {cust_thing} is not empty AND it's the FIRST occurrence of an invoice in the system with {cust_thing} populated with that unique value - I want to return that Invoice in the search results. If cust_thing is populated BUT it's not the FIRST record (based on date) that has that has the same value in cust_thing, I don't want it in the results.
Stated another way - I want to return all invoices for a time frame where it's the FIRST appearance of {cust_thing} on ALL invoices in the system.
Showing by example might help...
So - here's some fake data:
Invoice 1, cust_thing=Blue, date=7/1/11
Invoice 2, cust_thing=Red, date=7/2/11
Invoice 3, cust_thing=Green, date=7/3/11
Invoice 4, cust_thing=Blue, date=7/4/11
Invoice 5, cust_thing=Red, date=7/5/11
Invoice 6, cust_thing=Yellow, date=7/6/11
Assuming this is ALL the data I have in NS - and the date criteria is 7/2-7/5 - The DESIRED results would return Invoice 2 (Red) and Invoice 3 (Green)...
Invoice 1 is NOT included because it's outside the date range specified.
Invoice 2 is returned because out of all invoices it's the FIRST time cust_thing = Red AND it's in the date range specified.
Invoice 3 is returned because it's the first occurrence of Green and within the dates and system wide
Invoice 4 would NOT be returned although it's unique within the date range - it's not the FIRST instance of Blue in the system
Invoice 5 would NOT be returned because it's NOT the FIRST occurrence of cust_thing=Red
Invoice 6 would NOT be returned even though it's the first occurrence of Yellow - it's outside the date range specified.
Can this be done with a saved search? I think I'm really close - but just can't get the last little bit. Help will be gratuitously appreciated.