12 Replies Latest reply on Jan 24, 2011 12:47 PM by FentonJones

    Horizontal Tally for invoice

    SolGood

      Title

      Horizontal Tally for invoice

      Post

      Hello,

      I'm new to FMP. I am using FMP11. I sell shoes and would like to have my invoices show each shoe size. I assigned item#'s for each shoe size and when I create my invoice it shows each shoe size on a separate line. What I'd like to do is just have each style per line and then have a horizontal tally reflecting the total number of shoes sold - ie 2 size 9's and 3 size 10's = a quantity of 5 shoes on that line.

      I assigned different item numbers per size for inventory purposes, but when it comes to invoicing I end up with a long list of line items which is hard to visually tally as a customer when your talking about say 96 pairs of shoes. I'd like to be able to show the 8 different styles with a "size runs" of 12 displayed on 8 separate lines rather than 96 lines for example.

      I'm not sure if I could sub-tally or if I need to choose a different method for assigning item numbers or something else all together. I've searched forums and looked for templates that suit my needs with no avail. Any input on this would be great!

      Please help

      Thanks,

      Sol

      Shoe_Invoice_Example.jpg

        • 1. Re: Horizontal Tally for invoice
          philmodjunk

          It helps to make sure we are starting from the same point in terms of your basic table structure. A typical set up for invoicing or purchase orders looks like this:

          Invoices---<LineItems>-----Products.

          This is the structure used in the Invoices starter solution and also in this much simpler demo file created by Comment:  

          http://fmforums.com/forum/showpost.php?post/309136/

          With this approach you set up a layout based on Invoices that uses a portal to list the individual items purchased with price and description info looked up from the Products table. If this is new to you, check out Comment's demo file to see how this works.

          This is just to get the basics down. The challenge here is to modify this basic result to get your row of shoe sizes working.

          The simplest way to get this working for data entry purposes, is to make each product record a specific "shoe style" instead of a specific style and size shoe. Then a repeating field or series of dedicated number fields can be used to record the quantities of each size. This is fairly easy to set up, but can really complicate creating certain summary sales reports that total up your shoe sales by both style and size.

          An alternative, that takes a lot more work in scripts and auto-enter settings to create, but avoids the reporting issues would be to not use a portal for the line items, but to use a list view layout with a series of one row portals for recording quantities of each shoe size in a table related to the Line Items table. (There isn't space here to describe this in more detail.)

          I'm by no means an expert in your business, but one possible complication needs to be dealt with now while this design is still "on paper". Can you really rely on the shoe sizes to be the same for every shoe style? Don't mens, women's and children's sizes each have a different series of possible sizes? What about extra large/small sizes? If those are possible variations, your posted example screen doesn't appear to be set up to handle them as you have a single series of sizes for all the shoes in the invoice.

          • 2. Re: Horizontal Tally for invoice
            FentonJones

            If each "style" is a specific ID (unique to that style),* and men's, women's and children's versions of the same "shoe" are different IDs (which I think is generally the case), then I think the Line Items list view would work, with one-row filtered portals showing a Summary field to count. 

            The problem as I see it would be found sets. The 1-row portals are going use a single self-relationship on the "style". But they would also need to be constrained to the found set (otherwise they would return counts of all invoices, all time). Let's say it will be a date range. If you have 2 global fields, Date Begin and Date End, in the Header, and use script triggers to run a Find whenever they are changed, then that would work. The same globals could be included as criteria in the self-relationship itself or in the filters. So the found set and self-relationships see the same date range.

            * I don't know if the Product ID would be an equivalent concept to the "style". You say you have different item#'s for each size, but I don't know where those come from; they don't really help for this report. If each size is a different "product", then perhaps a "styles" table would help, to get an ID for each style.

            • 3. Re: Horizontal Tally for invoice
              SolGood

              Thanks for the advice guys. Phil is there a resource that could explain your second solution in-depth? My invoice is a typical set up as you described above. I did create a series of dedicated fields for each shoe size on the Products record detail and recorded quantity's for each size. I don't know enough about portals to understand how to use them. I've tried everything that seemed logical enough to get those sizes to reflect on the invoice record detail and the inventory summary report, but I appear to be making little headway. Any further detail on how to utilize a portal to record sizes and match against my inventory would be enormously appreciated. 

              "I'm by no means an expert in your business, but one possible complication needs to be dealt with now while this design is still "on paper". Can you really rely on the shoe sizes to be the same for every shoe style? Don't mens, women's and children's sizes each have a different series of possible sizes? What about extra large/small sizes?"

              Oh yeah and as to your last question about the shoes - It's actually only one brand of mens shoes. They all fit identically (all have the same sole). So it's pretty straight forward for recording purposes.

              Thanks

              • 4. Re: Horizontal Tally for invoice
                philmodjunk

                Fenton, by the way is discussing the same List View with horizontal portal approach that I am. you can look up horizontal portal in this forum and other locations to learn more about it.

                Given that all your shoes have the same size series, I'm wondering if that approach will be worth the effort especially for someone that's still fairly new to FileMaker.

                If you had one field for each possible size, you could define summary fields for each such dedicated field and then you could compute totals for each size across multiple line items and multiple invoices with those individual summary fields.

                A calculation such as Size1field + Size2field + size3field.... could add up the total shoes sold on a given line and a summary field totaling that calculation field can report total shoes per invoice or even for multiple invoices.

                Fenton, what do you think?

                • 5. Re: Horizontal Tally for invoice
                  SolGood

                  That's what I started to do - I think. I created fields in the invoice layout view for each size (7,7.5,8.....) I then change the formula in the quantity field of the invoice layout view to total those dedicated size fields as you described above. The series of sizes adds up fine per line, but I'm unable to understand how to let those sizes correspond (subtract) from my products inventory. It addresses my dilemma cosmetically, but not so much for accurate recording/reporting. Also even though I've created dedicated fields for sizes in my product page it does not show in the inventory summary report and I am unaware of how to select those size fields to reflect in that report.

                  • 6. Re: Horizontal Tally for invoice
                    philmodjunk

                    Good points made and that's why I framed my suggestion the way I did I could see the trade offs but didn't want to take you on this particular journey unless you really needed to...

                    For starters, we need to have a table structure that makes sense for your data model. See if this makes sense to you or not:

                    Invoices---<LineItems---<Sizes(InventoryChangeLog)
                                             |
                                             ^
                                         Shoes

                    Invoices::InvoiceID = LineItems::InvoiceID
                    LineItems::ShoeID = Shoes::ShoeID
                    LineItems::LineItemID = Sizes::LineItemID     

                    Sizes is a table occurrence of a data source table named InventoryChangeLog. Summary calculations on this table can compute inventory levels as the same table can be used to log production or shipments received as well as shrinkage and other non-sales driven changes to your inventory.

                    • 7. Re: Horizontal Tally for invoice
                      FentonJones

                      Sorry, I'd forgotten to Subscribe, and I didn't remember I'd been in this thread; I think the title threw me off. (Also, this forum software doesn't show you when you were in a thread; which is useful to know if you get involved in several, over time.)

                      I'm pretty sure I'd go with the multiple instances of 1-row portals, based on one relationship. Whenever you do the "field1, field2" etc., you're creating a bit of a dead end, not to mention unnecessary fields. Unless they are required for speed reasons (and you can deal with the clunkiness and lack of flexibility).

                      These are just needed for more concise visibilty for a report, important, but not worth chopping up the data like that (in my opinion; especially since I'm not the one building it :-) BTW, if you can get something small started, and a way to upload the file to somewhere, we could more easily see & help.

                      If each is filtered by its size, and includes whatever date range you want to look at (required, unless you want to see everything from all time), then related summary fields should work for the "subtotals", in later versions of FileMaker anyway.

                      • 8. Re: Horizontal Tally for invoice
                        FentonJones

                        Here's a file, created by someone else (don't remember who), about Portal Subsummaries with a FileMaker 11 Portal Filter. It is a simple Products table looking at an Orders table.

                        The 1st portal (on the left) is just a regular portal. The 2nd (center) one has a self-relationship on "color", with a calculation based on it, to "total" by color. It is more complex than what you need tho.

                        The 3rd portal (on the right) is one I added. It has a simple Filter by a specified Size, "L" in this case. The "total" field is just a Qty_SUM plain Summary field in the portal's table. You just point at it, no calculations needed. It only makes sense to show that filtered total in a single row portal (with the filter). 

                        That is the basic principle. You'd need a filtered portal for each size (just hard-coded, for now anyway); but all are the relationship plain relationship.

                        Portal_Subsummary_11_fej.fp7.zip

                        • 9. Re: Horizontal Tally for invoice
                          SolGood

                          Very helpful guys... but i didn't mean to select best answer - finicky optical mouse... is there a way to undo that?

                          I'm afraid my inefficacy of FMP is encroaching on to other areas of life:(

                          Just trying the file you linked Fenton - looks good. I'm gonna try and work with it. I've attached my patchwork of a file. Please see

                          http://www.exspecial.com/FMP.html

                          • 10. Re: Horizontal Tally for invoice
                            philmodjunk

                            I'll ask ModMan to clear that selection in a PM. That's the only way to unselect that I know of.

                            • 11. Re: Horizontal Tally for invoice
                              SolGood

                              Thanks

                              • 12. Re: Horizontal Tally for invoice
                                FentonJones

                                It's been a few days. But I thought I'd post this file. It is what I think of as the "relational" approach to the problem. There are no longer multiple hard-code size fields, only 1 size field, in a ProductSizes table. Inventory is specific to a Product AND Size (which makes sense, as you'd likely purchase order specific sizes).

                                Basically it enables situations where you want to have 1 Product for multiple sizes; treating it as one object for pricing, description, etc., but treating each size separately for Inventory.

                                A new product requires a script to go create and populate the product-size combos, which it reads from a simple 1-field Sizes table. You could optionally add/remove sizes from that table, as well as from each product; they are independent.

                                I'm not sure whether it works perfectly; and there seems to be some redundancy in the Line Items "inventory" fields used for counting. But I thought I should upload while the thread is more or less alive.

                                ShoeInvoice_fej.fp7.zip