4 Replies Latest reply on Aug 25, 2011 2:14 PM by BobCastles

    Estimating variable quantities of a product



      Estimating variable quantities of a product


      I'm setting up an estimating db for a printing operation. On a single estimate, I need to be able to estimate multiple quantities (of the same product and inside the details for each quantity there are calculations based on both static and variable information. For example, the size of the product is obviously the same across all of the quantities, but items like production footage, speed, time, etc. are based off of calulation fields. We could have a 4" x 6" product and the customer would want to see pricing for quantities of 500, 1000, 1500, 2000, 3000, 10,000, etc.

      I have the calculations entered and the db more or less working for the first quantity at this point. There are probably 40-50 calculation fields associated with each quantity and I'd like to not have to enter a new field and change all of the reference fields in each equation for each of those items under each of the quantities. Is there a simpler way to do this with some sort of relative calculation setup? I don't know if I'm explaining this very well, but what I'm looking for is some sort of functionality that would be similar to how you can "drag" a function cell in Excel across multiple columns and the relative cell references in the function will move across the columns as you drag the function over. I tried, briefly, to do this with repeating fields, but that didn't seem to work very well and it seems like the general consensus is that they are not an ideal structure to use in general.

      Hopfully that makes some sort of sense. If not, please ask quesitons and I'll see if I can explain it more lucidly.

      Thanks in advance,



        • 1. Re: Estimating variable quantities of a product

          FileMaker isn't a spreadsheet and won't easily simulate a spreadsheet interface. I suggest focusing on the results you want more than replicating a familiar "look and feel" from a spreadsheet.

          What you describe is much like an invoice where a customer purchases a number of items and the cost, tax, etc. for each listed item has to be computed and you won't know how many such items a given customer will buy. You'll just substitute estimate detail lines for line items purchased on an invoice.

          What you can do is define a related table of Estimate line items, link this table to your Estimates table by an EstimatesID serial number and then you can place a portal to this table on your Estimates layout. The calculations you describe would be added to the Estimate Line Items table to perform the needed calculations for just one such item. In your Estimate, you can then simply add more records as needed in the portal to generate your different estimates for each quantity.

          Check out this simple invoicing demo created by Comment to see how you might put this together:  http://fmforums.com/forum/showpost.php?post/309136/

          • 2. Re: Estimating variable quantities of a product

            Thank you for the quick response, Phil. I know it's not a spreadsheet and I wasn't trying to replicate Excel exactly, I was just trying to use that to help explain the issue that I'm having.

            I can see where keeping each quantity in the Estimate Line Items table would work, but I'm not sure that a portal is going to work to display all of the information that I need. I've attached a sample report format for the information that I need to display for each quantity. Listing all of those fields out horizontally in a portal isn't going to be very functional. Is there a way to change the orientation of a portal so that it lists related records in columns instead of rows?


            EDIT: Sorry, couldn't get the screed cap to attach to this reply, so I put it in with the original post.

            • 3. Re: Estimating variable quantities of a product

              I think you have your terms mixed up. Portals list data vertically in rows. What you are requesting it so list the data horizontally in columns. Wink Vertically in rows is the easiest way to set this up and this is an example of what I meant by trying not to replicate the way you would do this in a spreadsheet.

              What you show, can be done with what is known as the horizontal portal technique, but it's much more complex a design and much less flexible than listing your quote items in rows instead of in columns. Please also note that the number of different quantities can vary, but the rows in your example are fixed. That's where you lose flexibility as you'll be limited to a specific number of columns for your quantities with your layout design.

              That said, I can see why you have set it up this way due to the many different items you need to compute for each quantity.

              To produce a horizontal portal, you place a portal on your layout defined to show initial row: 1, number of rows: 1. After you have this layout set up exactly like you want it, (You can resize this single row portal to match the first column of data on your sample layout.), duplicate it and then use portal setup to change it to Initial row: 2 ; Number of rows 1. Repeat this 6 more times, and you'll have your horizontal portal.

              • 4. Re: Estimating variable quantities of a product

                Ok, I've got you. I think that will work. Thank you very much!

                I understand where you're coming from with respect to the portal orientation, and maybe it's just because this is how I've always seen this data reported (Excel or not), but I think I would have a hard time reviewing and mentally organizing all of those details across a row instead of down a column. Maybe that's just a preference thing.

                Thanks again!