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 ;
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?
Make Probability a calculation (result is number) with Ninja's formula. In this way, whenever Sales Stage changes, Probability will automatically adjust with it. :^)
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.
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.
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?
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.
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.
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.
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?
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.
Please download this example:
yes this is what i want...thank you, now are you able to please advise how i incorporate this into my own database
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.