How to identify Similar items on an invoice
I have a solution with several related tables, roughly equivelent to the invoices starter solution that has a table of customers, invoices, products, and invoice line items.
My problem is that the invoices in my solution go through many revisions before they are complete (which I've solved by adding another instance of the invoice table called "current invoices" plus a few identifing fields.
What I would like to do is be able to run a script after a user is done adding items that looks for similar products that have been added twice. For example, after going through several revisions, an invoice might have 150 items, and "widgets" might be listed three times. I would like the script to find and combine the multiple widget lines so that when the final person pulls the widgets off the shelf, they pull the total. I'd like to do this by actually modifying the line items, rather than summing them on a different layout, due to the multiple revisions of the orders that take place.
I can't think of a way to write a script that would find line items that have the same product IDs, without running a sequential find of all products in the inventory (over 1000), which would take quite a long time to perform.
Anyone have any experience with a similar problem? Or have examples of a solution?