1 2 Previous Next 19 Replies Latest reply on Aug 18, 2011 5:40 PM by KennySolway

    Create a calculation in field



      Create a calculation in field


      Hi There,


      I would like to create a field where the values are dependent upon the data from another field.  For example, I have a field named 'Sales Stage' with a drop down menu of entries such as 'qualified', 'solution presented', 'won', 'lost', etc.  I would like to create another field called Probability that automatically assigns a percentage to each.  For example when I click on 'qualified' in the Sales Stage field, i would like the data 10% to automatically appear in the Probability field for that same record.  To continue, if I enter 'won' in the Sales Stage field, I would like the percentage to equal 100 in the Probability field for that same record.

      Any assistance would be greatly appreciated.

        • 1. Re: Create a calculation in field

           Would not a simple calculation field do this?

          Result set to Number, formula of:

          Case( Table::SalesStage = "Lost" ; 0 ;
          Table::SalesStage = "qualified" ; 10 ;
          Table::SalesStage = "Solution presented" ; 50 ;
          Table::SalesStage = "won" ; 100 ;
          "" )


          • 2. Re: Create a calculation in field

            Hi Ninja,


            I'm not sure I understand your solution, but thank you for sharing.  Ideally, I'd like 'Sales Stage' as one field and 'Percentage' as another seperate field.  Is that what you're proposing?

            • 3. Re: Create a calculation in field

              Make Probability a calculation (result is number) with Ninja's formula.  In this way, whenever Sales Stage changes, Probability will automatically adjust with it. :^)

              • 4. Re: Create a calculation in field

                Thanks LaRetta, that's exactly what I'm looking for however, I'm extremely novice when it comes to customization.  Are you able to help me understand how I enter this formula into my database/table?  Please and thank you.

                • 5. Re: Create a calculation in field

                  Go to File > Manage > Database.  On Fields tab, find your Probability field.  If you do not have one, create it by typing into the line below where your cursor ends (label says 'field name').  Calculations usually start with a 'c' so you know it's a calc instead of standard field so you might call it cProbability.

                  Now to the right, make the TYPE a calculation then click CREATE.   Enter your calc in the opening calc dialog.  The default data-type is number (see popup below).  Say OK to exit the calc dialog and then OK to exit field definitions and put you back to your layout.

                  • 6. Re: Create a calculation in field

                    I'm there with you LaRetta, I just need to know how to enter the calculation in the 'Specify Calculation' Window.  Are you able to guide me through that please?

                    • 7. Re: Create a calculation in field

                      In calculation dialog ...

                      Type the following ... Case (

                      then find your SalesStage field in the list and double-click it.  This inserts it into the calculation so you then have ...

                      Case ( Table::SalesStage

                      ... where Table::SalesStage is replace with your actual table/field name

                      then continue typing ... = "Lost" ; 0 ;

                      Again double-click your field and then type

                      = "qualified" ; 10 ;

                      ... repeat with the other values.  Look in FM Help and read up on Case() to understand the full syntax.

                      • 8. Re: Create a calculation in field

                        Thanks both of you for trying, but it appears that what I'm asking and what you're proposing are slightly different - hence it's not the right formula.  Not sure how to better communicate it over text.  Kind of frustrating, but I appreciate you trying to help.  Do you know of any other ways to get support over the phone so I can talk to someone?  It's a simple calculation I'm sure, but just really tough to describe the nuances over text.

                        • 9. Re: Create a calculation in field

                          It may help to explain how this fails to work for you. That can reveal ways for us to make a more detailed explanation or to suggest an alternative approach.

                          There are training videos and other tutorials available--even videos on YouTube you can investigate.

                          There are also at least two books on FileMaker that have been published.

                          These may provide you with more of the needed "background" to better understand the help offered here and to help you better describe what you need.

                          • 10. Re: Create a calculation in field

                            I do not know what to say ... if you can't describe what isn't working?  What is your table occurrence and field name?  Do you get errors when entering the calculation?  What does the message say? 

                            If the concept of the calculation is wrong, what should it be?  If you can describe what isn't working over the phone then you should be able to explain it through text.  Don't let it frustrate you ... let's just try again and tell us why the formula isn't working.  Is it producing incorrect results or do you get stuck in the calculation dialog?

                            • 11. Re: Create a calculation in field

                              I'm trying to create a formula that when I change the value of one field, it automatically populates the value of another field.


                              So when I select 'qualified' from the drop down list in the field Sales Stage, the field Probability is populated with the numeric value of 10%.  Should I select the drop down of 'won' from the Sales Stage field, the data in the Probability field is changed to 100%, etc.

                              I don't have a seperate table created for the drop down list in Sales Stage.


                              Is that more clear?  Again, I appreciate you're patience and support and really would like to solve this formula.

                              • 12. Re: Create a calculation in field

                                Please download this example:


                                Tell us if this is producing what you want.  Change one of the Sales Stage values.  If this is not what you want, tell us what needs to change.  Laughing

                                • 13. Re: Create a calculation in field

                                  yes this is what i want...thank you, now are you able to please advise how i incorporate this into my own database

                                  • 14. Re: Create a calculation in field

                                    I have explained it exactly how I added the calculation in my file.  What is your field name?  What are the names of your Sales Stages?

                                    In my test file, change the field name to match your field name.  Change the values in the calculation to match your Sales Stages values.  Then copy and paste this calculation on cProbability and paste it into a calc field in your table.

                                    1 2 Previous Next