Pull Data From Reference Table
I am fairly new to FileMaker and appreciate any help you can offer.
I am creating a database that consolidates various mental health tests administered so that a single report can be generated. My problem is trying to pull data from a conversion chart that is used across these various tests. Different tests give different results, but they can all be 'translated' using this conversion chart. The various tests will (usually) give results as "Standard Score," "T-Score," "Z-Score," "Composite Score," or a couple others. I then need to use the given score to find a "Percentile Rank," or posssibly find the Standard Score from the T-Score, or some other variation. On paper this is very easy, as one score always equates to another (Standard Score 75 = Percentile Rank 5 = T-Score 33, etc.). But there are over a hundred lines like this on the conversion chart, and within a test there can be at least a hundred different scores that need to be converted, and upwards of 20 tests given at one time, so direct relationships between each test answer and the corresponding line on the conversion chart is unfeasible.
Any advice you can offer as to how I can look up information in this table would be helpful. While it would be great to set up fields to pull the data back into the database, some sort of calculation that only displays them on a report would be more than acceptable.