9 Replies Latest reply on Oct 3, 2016 5:42 AM by philmodjunk

    Need advice on double data entry validation

    FaithP.

      We have a project that aims to identify what medical conditions are being excluded from a list of research studies. We have 2 medical professionals review each study protocol, and then independently identify the medical exclusion (ICD-10) codes per study, as well as whether they were implicit or explicit in the protocol.

       

      A third person will then review the data entries, and adjudicate discordant data entries.

       

      I set up Filemaker so that there are 2 tables (with the same variables), one for each data entry personnel, and a 3rd table that will contain all the concordant and adjudicated codes

       

      Attached is a sample file with the following tables:

      exclusion_a - contains data entered by Staff A

      exclusion_b - contains data entered by Staff B

      exclusion_final - final dataset with all concordant entries, and adjudicated entries

       

      The fields are:

      NCT_ID = study ID

      exclusion = exclusion code (ICD10)

      exexpress = Implicit or Explicit

       

      Here's where I've hit a brick wall: Since one ID can have multiple codes, I'm not sure what the best method is for flagging discordant entries based on the combination of exclusion and exexpress.

       

      One approach I was thinking of was to create a report/layout for the third reviewer. I would like the layout to list all the ICD codes entered by both data entry personnel and flag the discordant entries. It would also be nice if the concordant entries can be automatically added to the 3rd table: exclusion_adjudicated. And then for each discordant codes, I'd like a button that gives the reviewer the option of adding that term to the 3rd table.

       

      I would really welcome any additional insights/advice the FM community can provide.

        • 1. Re: Need advice on double data entry validation
          coherentkris

          i have not looked at your file but if you have a business process for multiple approvals and edits you should ask yourself if you need to track all edits and only "publish/save" the final authorized version of the record.

          You light also think about whether you need to be able to roll a record back to a previous state.

          The answers might cause you to rethink your solution and build a strictly controlled scripted process for managing the life cycle of the record from creation, thru multiple edit and the subsequent tracking, to final approval.

          If you are in a business that is regulated by law or internal policy for "official" record handling then the answers might seriously affect the direction you take.

           

          Understand the business process first.. then build the solution.

          • 2. Re: Need advice on double data entry validation
            philmodjunk
            I set up Filemaker so that there are 2 tables (with the same variables), one for each data entry personnel,

            That's rarely the best design approach. Use one table for both users to enter data but "flag" the records with the user's AccountName that they used to log into your solution. There are then many different ways to limit the users' ability to view/edit records so that they can only work with their records and not be able to see or modify those created by another user.

            • 3. Re: Need advice on double data entry validation
              FaithP.

              Thanks for the advice.

               

              We're still in the early stages of the project, so I can definitely change the design. How would you suggest I go about the validation/adjudication part?

              • 4. Re: Need advice on double data entry validation
                FaithP.

                The main database has an audit log system. So I think we're covered on that part. In terms of strict regulations, there are none, as this is mainly a QA study.

                • 5. Re: Need advice on double data entry validation
                  philmodjunk

                  Studies----<Study_Exclusion>----Exclusions

                   

                  Studies::__pkStudyID = Study_Exclusion::_fkStudyID

                  Exclusions::__pkExclusionID = Study_Exclusion::_fkExclusionID

                   

                  Might be the basic data model

                   

                  A portal to Study_Exclusion, placed on the Studies layout can be used to select and show Exclusions for that study. Fields from Exclusions can be placed in the portal row.

                   

                  To keep records from another reviewer, I'd use record level access control to be sure, but I'd also put a portal filter expression on the filter that limits the records shown to just those by the current user.

                  • 6. Re: Need advice on double data entry validation
                    Malcolm

                    Real data can be confusing. I've replaced your exclusions with symbols. I've also modified the setup slightly. As philmodjunk suggested, the exclusions for both examiners are in the same table.

                     

                    I've used value lists to generate a list of unique entries for both examiners. These lists can then be filtered to obtain matching items and distinct items.

                     

                    To see the results, you simply enter the ID number ( 1 or 2 ) and the examiner ID ( A and B )

                    1 of 1 people found this helpful
                    • 7. Re: Need advice on double data entry validation
                      FaithP.

                      Malcolm, thank you for the solution! Works amazingly well, and exactly what we are looking for.

                       

                      Follow-up question: How do I then add the expression field, so that the "implicit" vs "explicit" values are also included in the "matching" results? This also means that if both staff entered the same exclusion but did not enter the same expression, that entry will be considered discordant.

                       

                      My initial thought is to concatenate the exclusion field with the expression field before doing the matching. What are your suggestions?

                      • 8. Re: Need advice on double data entry validation
                        Malcolm

                        Creating a concatenated value from exclusion and expression would certainly work.

                        • 9. Re: Need advice on double data entry validation
                          philmodjunk

                          Concatenation would work, but may not be necessary. If matching fields in a relationship, for example, you can simply match two pairs of fields instead of one, so it depends on the specifics as to whether you actually need to do this or not.