1 Reply Latest reply on Jul 22, 2013 6:53 AM by philmodjunk

    How to identify Similar items on an invoice



      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?

        • 1. Re: How to identify Similar items on an invoice

               Use a summary script based on the line items table. Use a find or go to related records to pull up a found set of the line items for one invoice. Use a layout similar to that found in your starter solution for printing an invoice but replace the body layout part with a sub summary layout part "when sorted by" your product ID field. Replace quantity fields with a summary field that computes the total of your quantity field and place it in this sub summary part along with the product ID and description fields.

               If you include a summary field that computes the cost of each group of line items, you may want to then use this layout for printing your invoices.