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

    Value list-linked calculation

    tays01s

      Title

      Value list-linked calculation

      Post

           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
          philmodjunk

               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
            tays01s

                 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
              philmodjunk

                   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-----<Doses

                   Diseases::__pkDiseaseID = Doses::_fkDiseaseID

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

                   Patient>-----Diseases

                   or

                   Patient----<Diagnosis>-----Diseases

                   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
                tays01s

                     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
                  Sorbsbuster

                       Why not just use a Case Statement?

                       CalcAnswer =

                       Case (

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

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

                       etc

                       )

                  • 6. Re: Value list-linked calculation
                    philmodjunk

                         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

                         Enter

                         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
                      tays01s

                           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).

                            

                           Thanks,

                           Stephen