1 2 Previous Next 19 Replies Latest reply on Nov 22, 2010 12:58 PM by Antonio

    Multiple product portals on one invoice

    Antonio

      Title

      Multiple product portals on one invoice

      Post

      I have a table of ingredients that I bring in to an invoice as a portal to create a recipe with prices per ingredient. I want to create multiple recipes on one invoice.

      How can I create multiple portals (multiple recipes) and subsequent charges on one invoice?

      When I create additional portals they automatically add the data in the initial portal.

      Thank you,

        • 1. Re: Multiple product portals on one invoice
          philmodjunk

          If you are using FileMaker 11, you might try using a portal filter so that each portal filters out the records not part of that recipe. (If you are using an older version of FileMaker, you can still do this with different relationships that include a filtering pair of fields in the relationships.)

          Another option might keep all the ingredients in the same portal with labeling fields and a specified sort order that keeps the recipe ingredients grouped for a given recipe.

          A third option made possible with the Release of FileMaker 10, to set up a list view layout for your invoice with a subsummary part for identifying each recipe and with the ingredients listed in the body.

          You could also set up a list view of recipe records with a portal to the ingredients in each record on the lst.

          You can also have one portal for listing recipes by name and a second for listing the ingredients. You set up a script so that selecting a portal row in the recipe name portal updates the ingredients portal to list the ingredients for the selected recipe.

          • 2. Re: Multiple product portals on one invoice
            Antonio

            Thank you for your reply. I have just upgraded from Filemaker 5 to 11. I have been a very casual user until now. I am redesigning my work database.

            I need to do some homework on your recomendations and get back to you. Do you know of any tutorials on creating what you've described here?

            Thanks again.

            • 3. Re: Multiple product portals on one invoice
              philmodjunk

              Sorry, that I posted such a long list, but the more I typed, the more I kept thinking of different approaches to your problem. Wink

              Here's a basic Invoicing demo created and uploaded to a different forum by Comment: 

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

              It's a good basic approach to invoicing you may want to examine to make sure you are started correctly. (I think you are.)

              Here's a demo that can introduce you to how summary reports can be created and used:

              Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: Multiple product portals on one invoice
                Antonio

                So I have spent some time reviewing those suggestions and I purchased the Filemaker missing manual book. So far I am finding no viable options for what I want to do although I am sure there is a way to do it. So here is some more detail.

                I am an herbalist. The recipes are combinations of herbs. For one patient I may prepare several different formulas. I would like the formulas to appear on one invoice. I have created the following tables: "Invoices", "herbs", and I have linked them with the join table "herb line items". I would like to make at least three different portals on an invoice that would allow me to select individual herbs and herb amounts from the "herbs" table. The selection of herbs will change for each portal/formula.

                I will keep studying but any additional help would be great.

                • 5. Re: Multiple product portals on one invoice
                  philmodjunk

                  You might need a "Formula" table that documents the formula for a particular product. Groups of records in this table (with the same "product" identifier) could list all the ingredients and quantities for each herbal product. Yet another table could have one record for each herbal product that you produce.

                  You could set up your system so that you select a product from the Product table, and a script uses the matching records in the Formula table to populate the line items table. This isn't a trivial undertaking for someone new to databases, but it can be made to work so that in one portal you'd see:

                  ID Description Qty Unit Price Total
                  123 Product #1 10 grams $5.00  $50.00
                    Ginsing 1 gram    
                    St Johns Wort 9 gram    
                  234 Product #2   . 40 grams $0.25     . $10.00
                    Herb #3 10 grams    
                    Herb #4 20 gram    
                    Herb #5 10 gram    
                  • 6. Re: Multiple product portals on one invoice
                    Antonio

                    Thanks again for giving it some thought. The problem is that the herbs and the amounts can change so the calculation for the cost on the invoice is based upon which herb and how much of it is used. In my previous data base I created 40 individual fields in the invoice that each had a relationship with the herbs table. I could get a pop-up list of the herb and the price per gram and do a calculation based upon how much of that herb was used but anytime I changed the herb prices all the previous invoices changed as well. In your example the assumption seems to be that the price is based on the product not on a calculation of the individual amounts of the herbs.

                    Sorry this is complicated.

                    • 7. Re: Multiple product portals on one invoice
                      philmodjunk

                      The formula table could include relative proportions in percentages so that by specifying 20 grams of product 1, specific quantities for each herb can be entered in the invoice using total product X percentage.

                      For managing price changes, the fields in the invoice line items should use a looked up value setting to copy over the current unit price for that herb. Changes to prices in the herbs table would then only affect future invoices and existing invoices would not be affected.

                      • 8. Re: Multiple product portals on one invoice
                        Antonio

                        In the portal I want to create the formula. It would be a custom formula tied to the herbs table. I would like to be able to create multiple custom formulas with the portals changing the herbs and the amounts as I wish. I can set up one of them but if I set up a second portal it shows me the same data as the first one.

                        In one formula for the same patient I may have

                        Foxglove 18

                        Angelica 10

                        Peony 8

                        In another it may be

                        Foxglove 12

                        Astragalus 20

                        Actractylodes 10

                        Phellodendron 10

                        etc.

                        These are totally custom made.

                        Sorry I have not communicated this well.

                        • 9. Re: Multiple product portals on one invoice
                          philmodjunk

                          I would not use multiple portals for this--just one. What you describe now is actually much simpler to implement.

                          Stick with your three tables.

                          Enter your herbs by selecting from a drop down that looks up herb description and pricing data from the herbs table.

                          Start each new custom product with a description line you enter, and leave the pricing fields blank for this header line.

                          This gives you a line to identify the start of each new product with the ingredients listed on following lines.

                          All your ingredients are then listed in a single portal--but grouped under a header row for each product.

                          • 10. Re: Multiple product portals on one invoice
                            Antonio

                            I thought it could be simpler. So the description line is something added to any of the fields?

                            I read somewhere that I could break a portal up into columns by using multiple versions of the same portal distinguished by the initial row. This might accomodate me making each formula in a different "portal" although it is only a different section of the same portal. That would allow me to put each of the portal sections into a different tab of the invoices layout keeping everything clean for input.

                            I am feeling more optomisitic. Thank you very much for helping me get some clarity on this.

                            • 11. Re: Multiple product portals on one invoice
                              philmodjunk

                              That's not what I meant. In your LineItems portal, there's usually a field that looks up an item description from the products table. In your case, it would be a text field that looks up the name of the herb from the herbs table. Since this is just a text field, you can type text directly into this field to identify the start of each new herbal product.

                              In your portal, you'd see this:

                              Product #1

                              Foxglove 18g $0.40/g $7.20

                              Angelica 10 $0.40/g $4.00

                              Peony 8 $0.40/g $3.20

                              Product #2

                              Foxglove 12 $0.40/g $4.80

                              Astragalus 20 $0.40/g $8.00

                              Actractylodes 10 $0.40/g $4.00

                              Though presumably you'd use a product name more creative than "Product 1" and "Product 2". Wink

                              • 12. Re: Multiple product portals on one invoice
                                Antonio

                                Great. Yes that is what I understood you to be suggesting. I will give it try.

                                Thank you again.

                                • 13. Re: Multiple product portals on one invoice
                                  Antonio

                                  Actually, Im not out of the woods yet. I thought I could to a sum of the extended prices after entering the herbs and the amounts but then I remembered I need to multiply that by whatever number of bags of that formula I provide.

                                  Product #1

                                  Foxglove 18g $0.40/g $7.20

                                  Angelica 10 $0.40/g $4.00

                                  Peony 8 $0.40/g $3.20

                                  3 BAGS

                                  Product #2

                                  Foxglove 12 $0.40/g $4.80

                                  Astragalus 20 $0.40/g $8.00

                                  Actractylodes 10 $0.40/g $4.00

                                  1 BAG

                                  This is why multiple portals appealed to me in the first place as it allowed me to get a total based on the number of bags of the formula.

                                  • 14. Re: Multiple product portals on one invoice
                                    philmodjunk

                                    I think you've sold me on multiple portals. I worked up a demo file:  http://www.4shared.com/file/vkfQrHco/HerbalRemedies.html

                                    It uses a list view layout for your invoice. Each record on this layout is a line item record with invoice and customer fields in the header and a portal to a "formulations table" for listing the individual herbs.

                                    A list view summary report based on the formulations table can then be used to print an invoice.

                                    It's all a very crude demo, but should give you an idea as to how you might get this to work. The scripts attached to the buttons on the Line Items layout are key to creating the necessary records with the correct values in place to link them correctly.

                                    1 2 Previous Next