1 2 Previous Next 16 Replies Latest reply on Jan 12, 2011 8:47 AM by philmodjunk

    Compare Results of Records with Duplicate ID Fields



      Compare Results of Records with Duplicate ID Fields


      Hello all,

      I'm new to Filemaker Pro and I've run into a problem.

      I have a database of pre-test and post-test results, with each record having an ID field.  What I would like to do is be able to is compare the results of each specific pre-test result with that of the post-test.  Some students only took the pre-test, some only took the post-test, and some took both.  Those that took both will appear twice in the database (because of duplicate ID#s).  These are the one's I am specifically interested in. 

      Said in another way:

      I want to compare John's pre-test with his post-test. I'd like to be able to do this for all duplicate IDs. 

      I don't want to compare all pre-tests with all post-tests. 

      Thanks very much!


        • 1. Re: Compare Results of Records with Duplicate ID Fields

          What data gets entered that distinguishes pretests from post tests?

          What kind of comparison do you need? (Just see the two values, do a calculation with them  or...?)

          The most basic comparison would be to simply sort the records by your ID# field so that your pre and post test results are grouped together. That can even enable a calculation using GetNthRecord to access data in the preceding or following record if you want. You might also use a relationship to match records in the same table by the ID# field plus one or two other fields to match by student and to discriminate between pre and post tests (So that a record doesn't match to itself).

          • 2. Re: Compare Results of Records with Duplicate ID Fields

            We have another field that distinguishes it as a pre-test or post-test (shown below).  We would like to compare attitude changes between these tests.  Most of the questions are radio buttoms, stating how much he or she agrees with the statements.

            It basically looks like this:


            ID#:  123456

            Quarter Taken: [Will be either Winter (designating a pre-test) or Spring (designating a post-test)]

            Slew of Survey Questions:  How much do you agree with this statement:  Strongly Disagree 1 2 3 4 5 6 Strongly Agree


            Thanks very much for your help, it's been very helpful already!

            • 3. Re: Compare Results of Records with Duplicate ID Fields

              I've been experimenting with the relathionship manager, but I can't get it to output anything.  I also tried browsing known duplicates to see if I can find a link between it's twin that way, but no luck. 

              • 4. Re: Compare Results of Records with Duplicate ID Fields

                Sounds like you want to make a visual comparison? (Just look at the two test results side by side).

                As I said in my last post, simply sorting the records can group them so that the pre and post test records are adjacent to one another. This would enable you to view them one above the other in a list view or possibly in a portal.

                If you want to use a relationship something like this might serve:

                TestTable::ID# = TestTable 2::ID# AND
                TestTable::StudentID = TestTable 2::STudent ID AND
                TestTable::Quarter Taken ≠ TestTable 2::Quarter Taken

                The last pair of fields insures that a record will not match to itself. I'm also assuming two fields where you may have one field. One field to identify the student (StudentID) and one to identify the test (ID#).

                • 5. Re: Compare Results of Records with Duplicate ID Fields

                  Well, it seems like although I've done a lot of reading up on Filemaker, I'm still woefully inadequate and manuevering so far.  I thought that if I could at least get the records to reference or relate to one another, it would be a step in the right direction. 

                  I'd love to be able to compare the changes in the questions from pre to post-test, but I felt baby steps were in order :)

                  On another note:  Although I can get the records ordered so that the duplicates are next to each other (through find and sort), I can't actually get Filemaker to -display- them together, at the same time.

                  • 6. Re: Compare Results of Records with Duplicate ID Fields

                    Have you selected "View as List" from the view menu? That view option should enable you to view the records in a list so that sorting your records puts the test pairs adjacent to each other where you can see two at once if they don't take up too much space in your layout.

                    The relationship can be used to display the second test in a portal. TestTable 2 is created in Manage | Database | Relationships by selecting TestTable (or whatever you've named your table) and clicking the button at bottom left that has two green plus signs. Then you can drag from one "box" to the other to link the fields. Once done, you can use the portal tool to draw a box on your layout and you then select TestTable 2 in the Show Records From drop down.

                    (Portals and their relationships are extremely useful tools in FileMaker. I'd make learning about them and how to use them a priority if I were you.)

                    • 7. Re: Compare Results of Records with Duplicate ID Fields

                      After more reading, I've been able to make a button to find the duplicates, sort them, and place them in list view.  Now I just need to find a way to compare the questions from pre-test to the questions on the post-test. 

                      I'm now starting to learn how to use scripts, but I'm not seeing the functions I need in the dialogue box.  I'm trying to find a function that will compare the duplicates, by calculating the change in each answer.  I'm trying to make a script that basically performs this function:

                      1) If Duplicate ID# found, Perform [Calculation]

                      For example:  It finds 2 User ID 1234's.  Now, it takes the Number Value from Question #1 (in pre-test) and compares it with Question #1 (in post-test). If the answer was 1 before, and became 6 after, the output would be +5. 

                      • 8. Re: Compare Results of Records with Duplicate ID Fields

                        It should be possbile to define a calculation field that uses the relationship we were discussing earier.

                        Something like:

                        If ( quarterTaken = "Spring" and Not IsEmpty (TestTable 2::ID#) ; TestTable 2::Response - Response ; "" )

                        In the post test records where a pretest exists, this will compute the difference in the field "response". (You'll need to use your field and table names in place of mine.)

                        Note that this calculation does not require that the records be sorted nor do the the pre test records have to be part of the current found set. The previous method of sorting the records and selecting list view is just a simple way of visually comparing the records.

                        • 9. Re: Compare Results of Records with Duplicate ID Fields

                          I can tell that the portal is displaying the desired information, but it's pretty messy.  I am yet to find a useful tutorial on portals, since whenever they use it, everything is automatically perfectly displayed. 

                          When I first create a portal, I get a shaded box and many seperate tiny boxes near it.  When I go to Browse or Find, I only see data in the tiny boxes and not the large portal box. 

                          Is the bolded box supposed to be empyy?

                          • 10. Re: Compare Results of Records with Duplicate ID Fields

                            What I've described in my last post does not require a portal, but it can be a useful way to check and see if your relationship is set up correctly. A portal is a method usually used to list multiple related records. Since this relationship is intended to link to exactly one related records, I'd expect to see one row of data and then remaining rows of the portal would be empty and this appears to match what you are describing.

                            After you create a portal, you can resize it and change its settings. You can set it to display only one row at a time if you want and you can change the size of the portal row and the fields within it. You can even resize the portal row and re-arrange it's fields to create a "mini form view" of the data in place of the default "mini table view".

                            • 11. Re: Compare Results of Records with Duplicate ID Fields

                              Just to make sure that I'm starting to understand Filemaker language, you're basically saying:

                              If a record is a Spring/post-test and is filled out, then take the difference of the values inputted.

                              If ( quarterTaken = "Spring" and Not IsEmpty (TestTable 2::ID#) ; TestTable 2::Response - Response ; "" )

                              The final calculation will probably have to be a bit more complicated because while Spring tests are always post, and Winter tests are always pre, they are over two years.  This means that there are technically 4 possibilities of quarter taken, not 2.   Is there any way that I can base the calculation off of a partial match instead of complete? 

                              Also, the field names are quite long, because they are survey questions.  Should I go back and change the field names to something like "Q1" and put the full question in the comment?  In the end, I'd still like the full question to be displayed while browing.

                              Thanks again!  It's a long process, but I'm definitely learning.

                              • 12. Re: Compare Results of Records with Duplicate ID Fields

                                What kind of "partial match" did you want to use? It probably can be done, using a calcuation field to isolate the "partial" text to be matched, but I can't give you an example as I can't picture what you have in mind here.

                                You can certainly rename your fields, but this will also update the field labels on your layout when you change the field names. You may want to edit your field labels on your layouts slightly so that they do not exactly match the internal field names before you change them in Manage | Database | Fields.

                                You also should consider whether you should structure your database into more tables so that you have a separate table for the questions (this is then a master list of questions asked) and a seperate table for the questions so that each question (and response) is in a different record in these tables. Using relationships and finds, you can then work with these responses and their questions by pulling up groups of records for a specific test and/or specific student.

                                If you search this forum using the keyword "survey" you can find a number of threads here that discuss that structure.

                                • 13. Re: Compare Results of Records with Duplicate ID Fields

                                  Ok, I'm remaking the database after looking at some survey threads.  The format I used, while logical to me, just doesn't really follow the way that Filemaker works. 

                                  Tables:  Survey, Answers, and Questions

                                  Relationships:  Survey <--ID--> Answers, Answers <--QuestionID--> Questions

                                  Now I just need to figure out how I am making fields within the Answers and Questions tables.  I know that each question will now be a new record, but I'm still a bit confused on how I actually write the question in the Questions table and the Answers in the Answer table.

                                  After I get the database how I like it, then I'll have to import through Excel, which should be fun :)

                                  • 14. Re: Compare Results of Records with Duplicate ID Fields

                                    Hmm, now that I'm working on creating this database, I started to wonder how it will handle the duplicates.  I'm also quite confused how I will import from Excel now as well...

                                    1 2 Previous Next