Hello! I'm trying to identify the number of parent records that potentially contain duplicate child records, where "duplicate" is based on multiple fields.
The parent is "Invoices", the children are "Line Items". Line Items are considered duplicates, within their parent, if the following fields are identical to each other: Service Date, Timekeeper, Billing Code, and Quantity.
For example, consider Invoice 123 with the following Line Items:
| Line Item | Service Date | Timekeeper | Billing Code | Quantity |
| A | 8/31/23 | Bob | L120 | 0.1 |
| B | 9/1/23 | Bob | L120 | 0.1 |
| C | 9/1/23 | Bob | L120 | 0.1 |
| D | 9/1/23 | Bob | L120 | 0.2 |
Line Items B & C would be considered duplicates, so overall I'd want to count Invoice 123 as an Invoice that contains potential duplicate Line Items. (Note: there are other Line Item fields where B & C will have differences, but we're intentionally not considering those. We only want to evaluate duplicates based on the four fields noted above, and no others.)
If I create a Pivot widget and include the relevant fields in the view, I can just count by the Line Item primary key, and then I can identify duplicates by the rows where the count is greater than 1. For instance:
| NUMBER of Line Items | Service Date | Timekeeper | Billing Code | Quantity |
| 1 [A] | 8/31/23 | Bob | L120 | 0.1 |
| 2 [B & C] | 9/1/23 | Bob | L120 | 0.1 |
| 1 [D] | 9/1/23 | Bob | L120 | 0.2 |
The problem comes in when I remove the fields from the view, and instead try to view across multiple Invoices. For instance, if I listed out all Invoices and the number of Line Items for each, then for Invoice 123, it'll show "4", since there are truly four of them. However, there's really only 3 "unique" Line Items (by the criteria of the four fields I noted above). How do I indicate that?
Ideally I'd have some sort of formula that would calculate this total. Then I could use the difference in those two numbers (# of unique Line Items vs # of actual Line Items) to identify the Invoices with duplicate Line Items. However, when I try to construct formulas to do this, I can't get it to work. The main issue is I don't know how to count "unique Line Items" based on values across multiple fields. How can I do this?
Ultimately, the end goal is that I can identify the Invoices with potential duplicate Line Items in a) a pivot table that lists out each invoice & the number of duplicate Line items (i.e. where each row is one invoice), and b) in an indicator that just shows the overall total number of these invoices.
Any help would be much appreciated!
**PLEASE NOTE: I do not have access to our eCube configuration, so any solutions that involve modifying the eCube or creating any new columns in the eCube are non-starters for me. Thanks!
