4 Replies Latest reply on Jun 12, 2014 10:01 AM by KBHLab

    Comparing records for data entry integrity

    KBHLab

      Title

      Comparing records for data entry integrity

      Post

           I work in a research lab and we are using Filemaker for data entry (i.e., entering paper-and-pencil questionnaires). As part of our data entry double-checking we enter each file twice (as two independent records) so that we can then compare the files and make sure that they are identical (i.e., that no data entry errors were made).  How can I go about comparing two independent records in Filemaker?

           Thanks!

        • 1. Re: Comparing records for data entry integrity
          philmodjunk

               That depends on what you mean by "Two files". If these are two Records, or sets of Records and there is an identifier that identifies both copies of the entered data by using the same value for each, then you can set up a relationship that matches by that identifier and then there are several options for comparing the data, from visually displaying the data on the same layout for easy comparison to calculations fields that return a specific value if the fields/records don't match in some particular.

               But I'd need to know how you organize your data in tables, records and fields before I can discuss such options in detail...

          • 2. Re: Comparing records for data entry integrity
            KBHLab

                 In this case it would two records (not sets of records) and there are two identifiers that would identify both copies (we label it as Baby ID and Subject ID) and I think the easiest way for us would be to set up calculation fields instead of visually displaying the data.

                 I attached a screenshot of our questionnaires to give you an idea of how the data is organized

                 Thanks!

            • 3. Re: Comparing records for data entry integrity
              philmodjunk

                   So for the two records the combined study ID and Baby ID match to only those two records?

                   You can use an added occurrence of this table in Manage | Database | Relationships to set up a self join:

                   YourTable::Study ID = YourTable 2::Study ID AND
                   YourTable:::Baby ID = YourTable 2::Baby ID

                   I would actually set up a layout where every field is doubled, with one field from YourTable and the other from YourTable 2. That way you can see what difference is keeping the fields from matching.

                   You can use conditional format expressions such as YourTable::Field1≠YourTable::Field2 to change the color of the field or it's contents to "flag" any discrepancies.

              • 4. Re: Comparing records for data entry integrity
                KBHLab

                     Your solution worked perfectly!

                     I was wondering however if there were any shortcuts that I could use for applying the conditional formatting (i.e., using some sort of self-function)? As of right now I've been selecting the fields I want to modify and writing the expression as Self ≠ Table_2::Name of Field and then editing the expression of each field with it's appropriate name. Unfortunately, there are over 500 fields to modify so is there any way to use a sort of Self function when referring to a variable in another table?

                     Thanks!