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.
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.
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?
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.
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.
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 )
Double_Data_Entry.fmp12.zip 70.6 K
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?
Creating a concatenated value from exclusion and expression would certainly work.
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.