7 Replies Latest reply on Sep 28, 2011 1:17 AM by Sorbsbuster

    Creating Equations

    NickJudson

      Title

      Creating Equations

      Post

      Hello Everoyone,

       

      So here is my dilema I have a database for document serivces at my work. When we are filling a record out we want the paper price to be filled automatically based on specific specifications. For example if you see the image I added, I want the paper price to fill automatically when someone chooses a paper size, paper quality, and paper type. For the image I added the paper price should be $.15 a sheet. I have no idea where to even being to since I am still a beginner at filemaker. If anyone needs more information please don't hesitate to ask.

       

      Thanks in advance!

       

      Nick

      Screen_shot_2011-09-27_at_4.33.13_PM.png

        • 1. Re: Creating Equations
          philmodjunk

          Open Manage | Database | Fields and select the table you used for the fields shown in your screen shot.

          Enter the name of your Paper Total field in the field name box at the bottom of this window, select calculation and click Create.

          This opens the specify calculation dialog.

          Find the field for Paper Price and Sheets used in the list of fields at top left in this dailog and double click them to add them to the calculation box at the bottom. Insert the multiplication symbol between them. This should produce an expression that looks like this, but with your field names in place of mine:

          Paper Price * Sheets Used

          Make sure that "number" is selected in the return type drop down at bottom left in this dialog box.

          Click OK twice to dismiss the specify calculation and Manage database dialogs.

          Add this new calculation field to your layout and it will compute the price you entered times the number of sheets used.

          • 2. Re: Creating Equations
            mgores

            If you are trying to calculate the $0.15 based on the size, weight and type it is a different matter.  Unless you can come up with a consistent formula that works with a set factor for each choice, it may be easier just to create a new table for all of the possible options listing the price for each possibility and look up the price per sheet from that table. 

            • 3. Re: Creating Equations
              NickJudson

              Mark,

               

              That is exactly what I want to do. I have a converted excel sheet of all of the possibilites, but I how to I get the paper price field to look that up and link everything together? Thank you for your reply I am glad you understood what I was trying to acheive.

              • 4. Re: Creating Equations
                philmodjunk

                Our two suggestions are not contradictory here. The look up method should be used to copy in the price per page and then the calcualtion field mutiplies by the number of sheets to compute a total.

                What does this table of price possibilities look like?

                It should be possible to set up a relationship to look up those values from a table created from your excel spreadsheet.

                • 5. Re: Creating Equations
                  mgores

                  You could import that excel sheet into a new table, then use that table to look up your prices by matching the values to those selected by the user in the Document table.  You would probaly need a script that checks that when size, quality and type are not empty (if any is empty -exit script), then find the record in the Pricelist table that matches those 3 fields, then set field (Documents::paper price ; Pricelist::paper price). 

                  You could then trigger the script on field modify for all three fields on the document layout so that it if you change your mind and pick a different weight, the price will update as you change it.

                  • 6. Re: Creating Equations
                    NickJudson

                    Sorry to make it seem that they weren't contradicting, that wasn't what I was saying.

                    I understand what I need to do, but I am a super beginner at filemaker and have very little scrpting practice. So if possible could you help me with how to script this. I can put up the paper list I have and you can see my other screen shot. Thank you guys for your help it is truly appreciated!

                    • 7. Re: Creating Equations
                      Sorbsbuster

                      Could I suggest coming at the Price List side of things from a different angle?  I think you will find the maintenance of such a complex price list onerous to say the least - even by your starting point here you will have to maintain (assuming all variations are available, which I accept they will not be):

                      4 sizes x 12 weights x 18 types x 2 colours = 1728 lnes, and that's not counting the "Other..." catagory potentials, nor the option for 2-colour, 3-colour, 4-colour, varnishing etc.  It's a long list of combinations.

                      I would be tempted to maintain several, but more manageable, price lists (strictly: 'costs lists').  Have one for the list of papertype and weight, with a cost per sq ft.  Then break the cost (price) calculation into elements.  You can work out what the area is of each sheet, times the cost per sq ft to give you the cost of the raw material.

                      Then you probably have a charge, or an uplift percentage for each colour.

                      Then you probably have a charge or percentage uplift for 2-sided.

                      And so on.

                      The I would have the cost (price) as an easy-to-follow (and de-bug, therefore) caluclation like:

                      ( Paper cost + Colour Cost + Duplex Cost + Profit Uplift ) x Number Of Sheets

                      You can build and test each component of that cost as you develop the file.  As you think of more factors (small run surcharge, for example) you can add that to the calculation.  To get the costs of the paper, etc, use the techniques of look-up that Phil and Mark described.

                      (That's how we did it anyway, when we handled printing.)