5 Replies Latest reply on Dec 29, 2012 11:13 PM by laguna92651

    Invoice lines aggregation


      A typical set of invoice lines looks like this.


      Article No Product Code Style Size Qty Price Tot Price

      DM13031 DM13031-XS Hoodie XS 0 15 0

      DM13031 DM13031-S Hoodie S 2 15 30

      DM13031 DM13031-M Hoodie M 1 15 15

      DM13031 DM13031-L Hoodie L 2 15 30

      DM13031 DM13031-XL Hoodie XL 3 15 45


      I would like to enter the ordering information in this format, there of course could be a second line in the same format with a different Article No, DM13172 etc. Pricing would be always be the same for all Product Codes for a given Article No.


      Article No Style XS S M L XL Qty Price Tot Price

      DM13031 Hoodie 0 2 1 2 3 8 15 120


      I would still need to be able to break out the individual Product Codes later in Table view for analysis. I will post another question in a second thread, but I need to summarize the data in a similar format, but by Product Code for all invoices.

        • 1. Re: Invoice lines aggregation

          Does anyone have any ideas how I might go about this task. In essence I want to create 5 Invoice lineitems from a single line on the invoice. The user would not see the 5 new lineItems. But I would need those lineItems for inventory and post sales analysis. Basically the system structure is done but the end user now wants a different interface on the invoice. Without a one to five approach they could have 100 invoice lines or more.

          • 2. Re: Invoice lines aggregation



            The long form of the solution could be fairly complex, but I think I can give you the gist of where you want to go...


            The way I would approach it is with a table of products and a table of sizes. For each sproduct size intersection you would have a quantity for each invoice, so each size on each item is a record, not just a field.


            You can then use relationships to show the right row in each instance. If your sizes are not the same for each item, your display gets significantly more complicated. You may want to do some searches on using a virtual list technique that has become popular recently and can solve all sorts of display quandaries caused by complex relational models.


            Let me know if that gets you on the path, or if you need more clarification.




            Court Bowman, CEO

            Cleveland Consulting, Inc.


            Visit us on the web at http://www.clevelandconsulting.com

            • 3. Re: Invoice lines aggregation

              Could you go into a little more detail, I'm just ramping up FM, so could use a little more direction or an example. I looked at your CC Pivot product, it looks great, I give it a run tomorrow. Thanks for your suggestions.

              • 4. Re: Invoice lines aggregation

                I can try.


                You should start by thinking of your products as two-tiered.


                Each product has a product header:


                Article No Product Code

                DM13031   DM13031-XS


                and product details:


                Style      Size     Price

                Hoodie     XS       15


                style may go with the header or detail, I don't really know your data well enough.


                then when you pick a product on an invoice line item, you are first picking the product header once for the line item and then picking the detail 5 times in your example for each line item.


                so you still have those 100 invoice line records you mention, they are just grouped in 5's on each line item.


                Does that make more sense?




                Court Bowman, CEO

                Cleveland Consulting, Inc.


                Visit us on the web at http://www.clevelandconsulting.com

                • 5. Re: Invoice lines aggregation

                  Current setup is:

                  Hi Court


                  Thanks for your help. An ArticleNo is defined by Season, Style and Color, I then generate 5 ProductCodes (for the Product table) based on the 5 sizes (XS, S, M, L, XL), so I basically have the two tiers you are talking about. But the whole objective is to have say only 20 line entries on the invoice with 100 line items records generated from those 20 line entries.


                  The price would be the same for all ProductCodes of a given ArticleNo. If a 0 is entered for a size no ProductCode would be generated. It would be important that this sits on top of the current invoice LINE items, because all other reporting and manipulations of the invoice LINE records is aready done.The invoice lines would look like this.


                  Desired Invoice lines

                  ArticleNo       XS  S  M  L  XL    UnitPrice TotQty  TotPrice

                  DM13031        0    2   1   2  3         $15          8         $120

                  DM13172        1    5   0   4  5         $25         15        $375




                  DM13073       4    1   2   6  3         $20         16        $320


                  The product table entries would be of the form, from the ArticleNo I can pull the other product attributes, Season, Style, Color etc., this is the current basic invoice LINE items:


                  Current Invoie lines

                  ProductCode   ArticleNo   Size  Cost    Qty

                  DM13031-2           1001         2