Well, the ideal method to do this would be to upload a table of unique reference numbers into a parent table. If you're doing data manipulation and you're familiar with pivot tables maybe that's not too terrible for you. Then obviously the line item data has the reference number so you could make a relationship. In the relationship you would have a summary for whether or not that reference number had any failures, then you could make a formula on the reference number table similar to what you have on the line item table to determine if a whole reference shipped clean on time or not.
Another technique for quickly creating unique reference numbers on a parent table is to make a summary report of orphaned line items where the reference number parent does not exist. Typically I do that by making a formula checkbox field, which is true on the parent reference number table and looking it up down to the children. If that checkbox is blank, then the record is an orphan. So you make a summary report by reference number of the orphans and manually click the button to copy these to another table. As manual processes go once you have this set up it literally takes five seconds to create the missing parent reference numbers.
