6 Replies Latest reply on Oct 5, 2016 6:14 PM by mike_wallace_rcc

    When table fields equal, pull fields from 2nd table to 1st?

    ezeitgeist

      I have 2 tables: Test_Results table & Test_Answers table.

       

      The Test_Answers table has a record that includes each correct answer for each of the different tests available.

       

      The Test_Results table has one set of user answers for each test taken. A field in Test_Results table is "test_name"; that field exists in Test_Answers table as well.

       

      I want to pull the individual test correct answers from Test_Answers to compare them and do calculation as relates to the user answers from Test_Results table. There are 8 DIFFERENT types of tests, and each type of test will have a number of different tests within them.

       

      My initial thought is to create a set of fields in Test_Results that when the TR::test_name = TA::test_name that all the correct answer fields are pulled into the previously blank ones created with each new Test_Results record.

       

      Is this possible to do? If so, how would I do that? Would it just be in each individual calculation field in Test_Results that says if TR::test_name = TA::test_name, TR::correct_1 = TA::correct_1 (and so on for each question)?

       

      OR, is there an easier way of doing it? I would then, in the Test_Results table have calculation fields to do all the comparisons. Can I do the comparisons WITHOUT pulling the fields and just directly comparing them?

       

      My hiccup comes with the test_names having to match and how to make sure the Test_Results table compares user answers to the correct test answers.

        • 1. Re: When table fields equal, pull fields from 2nd table to 1st?
          philmodjunk

          Each test result and correct answer should be a separate record. That allows you to set up a relationship that matches both by test and result to see if the result matches the corresponding correct answer.

           

          And I wouldn't link them by a test name. Say you accidentally mistype that test name and don't spot the error right away. If you later try to correct the misspelled name, you break the link to any records linked by that misspelled name in your relationship and you then have to track them down and correct the error there as well. It's better to link by an ID--either an auto-entered serial number or a text field that auto-enters Get ( UUID ). you can still use the test name to find or select a test, while continuing to link records by an ID.

          • 2. Re: When table fields equal, pull fields from 2nd table to 1st?
            ezeitgeist

            But if each test has 200 questions, I have 8 test types, and let's just say a minimum of 3 different tests for each test type, I have 24 tests and we are talking entering 4800 different records. Wouldn't it be easier to have response_001 through response_200 in a single record in Test_Results table, and the same in Test_Answers table, and then setup a calculation field for each response in Test_Results table that says that if test_ID = test_ID (between the tables) to compare the response fields? That way, once I set up the 200 calculations in the Test_Results table, no matter the Test_Answer record it is comparing it to, the calculations will be done?

            • 3. Re: When table fields equal, pull fields from 2nd table to 1st?
              BruceRobertson

              Absolutely not.

              Use separate records.

              • 4. Re: When table fields equal, pull fields from 2nd table to 1st?
                philmodjunk

                Whether you enter the data into separate fields or separate records, it's the same size data entry task.

                But separate records gives you a flexibility you just can't get if you use multiple fields within the same record.

                • 5. Re: When table fields equal, pull fields from 2nd table to 1st?
                  jfletch

                  In case you are doubting the wisdom of Phil and Bruce, let me add my $.02: they're right. Fields will limit your options and actually make everything harder. Sets of Answer records with links to a table of Test records where each Test record is the parent of its own set of Answer records will serve you best.

                   

                  One thing you might consider is to create your question records from the Answer records. When the test is chosen you have a script that creates a set of records for the test taker that matches certain fields in the set of records that is the Answer set related to the test. Then it will be easier to match them up when grading. You can even include the answer in the question record (and just not show them to the test-taker) to make grading easy and instantaneous.

                   

                  Good luck!

                  • 6. Re: When table fields equal, pull fields from 2nd table to 1st?
                    mike_wallace_rcc

                    I also will say that over a decade of experience has taught me that all of these gentlemen are correct.  Use records.  Big tables should be avoided anyway (wide = lots of fields)  FileMaker can handle your record count without breaking a sweat.