13 Replies Latest reply on Sep 8, 2011 1:24 PM by philmodjunk

    Pull Data From Reference Table

    MikeWillis

      Title

      Pull Data From Reference Table

      Post

      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.

        • 1. Re: Pull Data From Reference Table
          Jade

          If you want to use a cumulative Normal Distribution function in FMP to calculate a "Percentile Rank" from the other scores, this thread may be of interest to you:

          Normal Distribution Function in FMP

          • 2. Re: Pull Data From Reference Table
            philmodjunk

            It's also quite possible to establish a relationship that looks up values from a table of your conversion values when a number in the first table falls in a specific range. This is a fairly simple thing to set up--provided you have a good structure to your database tables.

            In particular: "within a test there can be at least a hundred different scores that need to be converted"

            Those different scores should all be stored in a table with one score per record and then linked via a test ID to your Tests table. That way, you have one relationship linking a "score" record to your conversion table.

            • 3. Re: Pull Data From Reference Table
              MikeWillis

              Phil,

              I'm a little unclear exactly what you mean. If I have the following mock tables set up how would another table be used?

              (Each test is set up as its own table for the results. Each test has a relationship only back to a table with client info, related by Client ID. Tests A & B have results showing as Standard Scores, but Test C has results showing as T-Scores. For this example, let's say I need to retrieve the Percentile for each test. As I said, pulling the info back into the table would be ideal, but if I could only have a way to have the Percentile calculated and displayed on a report that would be fine) 

                            Test A      Test B     Test C          

              Client ID    001          001        001

              Result 1     110           91          34

              Result 2     77            93          81

              Result 3     141          72          52

               

              Conversion Chart (Only using some of the 7+ columns provided, and some of the records of the actual 110+)

              Standard Score     T-Score     Percentile

                   72                  31              3

                   77                  34              6

                   91                  44              27

                   93                  45              32

                   103                52              58

                   110                57              75

                   141                77              99.3

                   146                81              99.9

              • 4. Re: Pull Data From Reference Table
                MikeWillis

                Jade,

                I appreciate the suggestion, and may end up needing to go with it, but i have fears of screwing up the math and want to see if there is a relational solution first before going down that path.

                • 5. Re: Pull Data From Reference Table
                  philmodjunk

                  Your post illustrates what I meant about setting up the appropriate table structure so that the relationships are simple and easy to work with. Your data should be structured as:

                  TestsTaken:

                  Client ID     TestID       

                  1                 234       
                  1                 432
                  1                 342

                  TestResults:

                  TestID    Result

                  234         110
                  342         34
                  432         91         

                  Then you can set up a relationship from TestResults to the lookup table and you have only one such relationship instead of having to set up a separate look up relationship for each test.

                  It's still possible to display the data in the grid like your original example, BTW, if you use one form or another of a horizontal portal technique to organize the related data into columns if that is needed. (Horizontal portals are a series of one row portals placed side by side. They either list data from row 1, for the first portal, row 2 for the second, etc. or they use portal filter expressions to display the correct data in each column.)

                  • 6. Re: Pull Data From Reference Table
                    MikeWillis

                    Just so I have some clarity on your explanation (and that is hopefully inspite of my limited knowledge in this area), each answer to each test should be its own record(?). So if we have ten tests and they each have fifty questions, each new client will create 500 additional records in this table? That is not a problem, I just want to make sure I have a good understanding of what you are explaining.

                    • 7. Re: Pull Data From Reference Table
                      philmodjunk

                      Yes, that's the concept. Note that this approach then allows a flexible method for dealing with your answers. A test can have 3, 30 or 3,000 questions and the same setup continues to work, at least as far as the relationships, look ups and any calculations are concerned.

                      • 8. Re: Pull Data From Reference Table
                        MikeWillis

                        Great, thanks for the help. I will set up a few mock ups and see what I can do. I appreciate the insights.

                        • 9. Re: Pull Data From Reference Table
                          Jade
                           Mike, You should also include the test type (Standard, T, etc.) with each test result record since the means and deviations  of each test type are different.
                          • 10. Re: Pull Data From Reference Table
                            MikeWillis

                            I am trying to test this method out, but am running into some stumbling blocks. I have a form/layout setup to enter the various results. What field can the data be entered into? If I use 'Result,' it duplicates the entry across all records. I am sure there is a way to differentiate based on the Test ID, but I'm not sure how to set this up. I am also unclear as to which table(s) should be allowed to create new records based on the entry of new data.

                            • 11. Re: Pull Data From Reference Table
                              philmodjunk

                              We'd need to know more about what exactly you've set up here and the relationship options.

                              "allow creation of records..." in the relationship options only comes into play when you either using a portal to add new related records or when you directly modify a field in the table from a related table's layout to create the first (and only the first) such related record.

                              Entering a value in "result" is not duplicated the entry across all records, but it may look like that depending on how you've set this up.

                              • 12. Re: Pull Data From Reference Table
                                MikeWillis

                                While I feel that I get your suggestion on a basic level, I clearly have a problem understanding how to execute the concept. In trying to simplify so that I can get the concept down, I believe I would need four tables: ClientInfo, TestsTaken, TestResults, and the ConversionChart. Again for simplicity, let's say the tables consist of the following fields (lined up with the same named related fields next to each other):

                                ClientInfo               TestsTaken           TestResults               ConversionChart

                                ClientID                 ClientID               

                                First Name              TestID                 TestID

                                Last Name                                        Result                     Result

                                                                                                                   Percentile

                                 

                                My problem comes in how to create the records in the various tables each time someone goes in to enter results for a new client. I need an easy to read form for someone to go into, enter the client's info then enter the results across various tests. In dealing with the layouts my instinct is to drop in a field where a test result can be entered, and thereby creating a new TestResult record, but without the direct relation between TestResults and ClientInfo I can't figure out how to do this. Once that step is understood I do know how to pull the corresponding information from the ConversionChart.

                                • 13. Re: Pull Data From Reference Table
                                  philmodjunk

                                  Do you understand how to set up portals?

                                  A portal to testsTaken can be placed on a ClientInfo layout to list the tests that they have taken and be used to log new tests taken. A portal to test results on a TestsTaken layout can show the results for that test and be used to record new results. That's the simplest to set up, though perhaps not the easiest to use. You'd find a client record on the client info layout, add new tests taken in that portal and click a button in the portal row to switch to the TestTaken layout in order to log the test results.

                                  A more sophisticated approach puts portals to testResutls and TestsTaken on the ClientInfo layout. Selecting a row in the testsTaken portal triggers a script to display the test results for that test in the testResults portal. That approach takes additional relationships and scripting but makes it all happen on one layout.