Remove Records based upon 3 columns and return based upon choice of one or the other

[jurbin]

Hello,



I am working with a data set where someone reviews a work item and approves it where it then moves to the next step in approval. There are several status' someone can choose, but only certain ones move it to the next queue.For instance, possible choices could be - 'approved' 'IGO' 'NIGO' 'On Hold' but only 'Approved' would move it to the next queue. Sometimes people are confused because 'IGO' and 'Approved' are essentially the same thing, so they will press 'IGO' then realize it didnt move and press'Approve' where it then moves to a new queue. Since I know that ANYTIME this happens, in the same queue it is a mistake, could I create a formula in a query where I say something like "If 'IGO' and 'NIGO' appear for the same [Event Operator ID] in the same [Event Queue] for the same [Work Item Number] the record with 'IGO' should be removed.

Please see attached picture of my column names so you can understand what I mean with the above. Any help would be sincerely appreciated.

[June7]

Show some example data of this situation.

[jurbin]

Show some example data of this situation.

Thanks, attached is a sample of what the duplicate might look like but the only item that's different is in column 'event status' one says "IGO" the other says "Approved" I want to automatically remove the IGO line IF something looks duplicated like this except for those two status' in 'Event Status'

[June7]

This means selection of a record is dependent on value in another record. Not simple. This calls for nested subquery or domain aggregate (DLookup).

For nested query, review http://allenbrowne.com/subquery-01.html#AnotherRecord

However, this is complicated by the IF condition because you also want the IGO records if there is no Approved. I am sure similar requirement has been discussed before but cannot remember the solution. Makes my head spin. Will have to do some testing but that is not much of a data sample to work with. Want to attach a spreadsheet of records or even the db? Follow instructions at bottom of my post.

Is it possible to have another record with same WorkItemNumber but maybe another field with different value, say ItemSubtype?

Just how do these 'duplicate' entries get created? Would be better to prevent this up front.