7 Replies Latest reply on Jun 18, 2013 1:28 PM by tays01s

    Value list-linked calculation



      Value list-linked calculation


           I want to transfer some functions used in Excel to FM.


           In Excel, I have a drop-down. Each choice selects a different calculation. I'd like a similar function in FM but am not sure what the approach would be to get the equivalent functionality.


           Related to this, some calculations rely and common values, eg. weight. In Excel, it is most convenient to make 'weight' a named range so that when 'weight' appears in a calculation, the value in the named range is looked up by the calculation. In FM do you have to have a field for each such value? Or is there a similar 'named range' method?

        • 1. Re: Value list-linked calculation

               For you value list of calculations, take a look at the evaluate function. It's possible to set up a value list that selects a calculation expression saved as text and then Evaluate can evaluate that expression and return the computed value.

               For you named ranges, take a look at how to set up related tables with records in a related table and also at setting fields with global storage or global varables. Thease are all ways to make specific values available to calculations in multiple records and tables.

          • 2. Re: Value list-linked calculation

                 Evaluate: So if I had:

                 - a 'Disease' field as a pop-up and diseases a, b, c

                 - medicine fields 1, 2 ..... each of which may have a unique mathematical calculation to get the correct dose for disease a, b or c

                 what would an example of the Evaluate function look like?

            • 3. Re: Value list-linked calculation

                   Don't have all the detail that I'd prefer to give you an answer. I thus have to keep this in very general terms:

                   Set up a table of diseases with one record for each disease.

                   Set up a related table (Doses) with one record for each medication that links in turn to a table of Medication where you have one record for each possible medication. Use a text field, DosageFormula, in Doses to record the formula for computing the dose for that patient and disease.


                   Diseases::__pkDiseaseID = Doses::_fkDiseaseID

                   But you'll need at least one more table, possibly two to record information about the patient




                   In either case, Evaluate ( DosageFormula ) will compute different results based on the text entered into DosageFormula and the values entered into the fields referenced in that expression.

              • 4. Re: Value list-linked calculation

                     Sorry it has taken me time to get back to this.


                     It v. complex to get the result in FM cf Excel. In the latter I'd have a drop-down link to 'Disease column'. When the disease was chosen the calculation in the corresponding row would be pulled up. Eacy disease would have a unique calculation and this is only a tiny example of the functionality I need to attain with these calcs.


                     So for example how would I get 'CalcAnswer' in the context of:

                     Disease A, CalcAnswer = temp * 20 + kg * 5

                     Disease B, CalcAnswer = temp * 15 + cm * 2, ie. not even the parameters or number of parameters are the same.

                     How would I go about getting CalcAnswer when the disease is specified from a pop-up?

                • 5. Re: Value list-linked calculation

                       Why not just use a Case Statement?

                       CalcAnswer =

                       Case (

                       Disease =  A,  temp * 20 + kg * 5 ;

                       Disease = B, temp * 15 + cm * 2 ;



                  • 6. Re: Value list-linked calculation

                         Sorbsbuster is making an excellent suggestion, but if you want to look up these calcualtions like I previously suggested:

                         In a table of related records, define a text field, Equation. Define a second field, _pkDiseaseID, a third field:DiseaseName to name the disease for which the calculation applies.

                         Set up your relationships

                         MainTable::_fkDiseaseID = Disease::_pkDiseaseID

                         If my notation is not familiar to you, see the first post of: Common Forum Relationship and Field Notations Explained


                         temp * 20 + kg * 5

                         into the text field of one record of Equation and:

                         temp * 15 + cm * 2

                         into the text field of Equation in a different record

                         This assumes that temp, kg and cm are all fields defined in MainTable. If they are defined in a different table, these text expressions have to be updated to reference them and you'll need a valid relationship to the correct record of that table or tables to get the correct values to be used in the calculation.

                         A calculation field, cEvaluate defined in MainTable can be written as:

                         Evaluate ( Disease::Equation )

                         If you format MainTable::_fkDiseaseID with a vlaue list of values from Disease::__pkDiseaseID, with DiseaseName specified as the value list's secondary field.

                         Then, when you select a disease from the value list, the related equation for that disease is selected and evaluated.

                         Note: If the calculations are very unlikely to need future changes/updates and you are not likely to be adding calculations for new diseases, a case function, which will be less complex to implement, makes sense. The advantage to having a related table of equations is that you can make updating equations or adding an equation for a new disease a data entry operation instead of needing a developer to open up the file and change the expression that uses the case function.

                    • 7. Re: Value list-linked calculation

                           That was fantastic!!

                           Just to push my good fortune.......as you suggest I put the parameters that the calculations use on my main table together with the field that evaluates those calculations. I will then want a portal of the 'Disease', these parameters and the evaluation field on a 'front-end' layout. The catch is, how do I get ONLY those parameters relevant to a particular Disease to appear in the Portal.

                           Eg. Disease_1 may require parameters a, b, c and Disease may require parameters b, d, f, h. Obviously it would be nice to have the fields neatly bunched to the left of the portal (I don't mind so much there being excess space to the right for when there are more parameters).