### 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?

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.