4 Replies Latest reply on Dec 30, 2014 6:28 AM by philmodjunk

    One-to-one relationship or filters?



      One-to-one relationship or filters?


      Hello. In my research project, I have to read a lot of articles, keeping details of them all, but selecting only  some for inclusion. I have a table for the articles, with citation details (author, title etc) and a field for the include/exclude decision. The next step is to have a table that has only the included articles so that I can then analyse them, using additional fields (attributes of the study).

      I suppose the customary way to do it would be to have a view filtered by the include/exclude decision but I would like to keep the size of the table down and I am worried by accidentaI leakage of excluded studies. I am tempted to create the analysis table as a separate table with a one-to-one relationship for the sub-set of included. But how does one create a one-to-one relationship and transfer only selected records? In the past, I   have had two unrelated tables and exported records from one to the other but that is an inelegant solution, one has to do that every time a new article is included. 






        • 1. Re: One-to-one relationship or filters?

          What you describe is a one to many relationship and not a one to one relationship. One very common way to link multiple selected records (your "include" articles) to a specific record in the same or another table is via the standard primary key to foreign key relationship of a one to many relationship linking one record in a table to all the records selected to be "included" and only those records. You have a field that uniquely identifies each record in the first or "parent" record's table and a simple data field defined as a match field to it in the field of articles where you are making your selection. By assigning the value of the parent record's primary key to the selected record's foreign key record, you establish a link between the two. A typical way to set up a primary key field is to define it as an auto-entered serial number field.


          ParentTableOccurrence::PrimaryKey = SelectedArticles::ForeignKey

          A portal to selectedArticles placed on the ParentTableOccurrence layout will list only those selected records from SelectedArticles. There are also several ways to pull up a found set of SelectedArticles on a SelectedArticles layout that are only those selected for a specific parent record.

          And it is very possible that you actually need to link the same "selectedArticles" record to more than one article in your parent Tutorial: What are Table Occurrences?. That would make for a Many to Many relationship. This can be handled with a join table. If that is what you need here, please post back and I'll share more information on that approach.

          • 2. Re: One-to-one relationship or filters?


            A couple of other ways to maybe approach the problem.


            #1  Conditional Formating:

            Use Select All in layout mode, set a formula for conditional formatting that changes the fill color on fields to red if the Include/Exclude option is set to Exclude.  Easy to do and you won't make a mistake thinking you are on "included" record when in fact you are on an "excluded" record because of the red fields staring you in the face.

            #2 Security:

            Assuming you are the only one working in the database, create two new logins (doesn't have to have a password) so that when you open the file you login as "Full" or "Limited".  You may want to leave "Admin" alone, which is why I say create two new ones.  The "Full" privilege set uses the default [Full Access] privilege set.  The "Limited" login uses a custom set where you have gone in and selected "limited" for the table and applied a Record limiting formula of "Case ( Include_Exclude_Field_Name  ≠  "Include" ; 1  ; 0 )".  When you login under Full, you can do anything you want.  When you login under Limited you can't change the data in the table unless it has been flagged as "include".


            Once setup you have the option of then including a previously excluded record if review warrants it but it would be hard to accidentally treat an excluded record as an included record.



            • 3. Re: One-to-one relationship or filters?

              Thanks for those comments, PhilModJunk. I can manage one-to-many and many-to-many relationships "quite" easily. But I still think this is a one to one relationship. If I start with say 400 articles but want only 50 in the analysis, it would be good to have a table with only those 50, especially when co-workers are involved in only the final stage. The two tables have identical records as far as the citation (author/title/year) is concerned but the manipulations are different (each manipulation in the analysis e.g. present/absent has its own field).  Thanks, Mark_M, those are great suggestions and I will explore them. I like the second suggestion a lot. It would stop the situation in which someone accidentally changes the radio button in the include/exclude selection.

              Actually, there are several stages to this project where include/exclude decisions are made. I have so far used scripts so that each phase of the study has its own layout. So when you go to stage 2, you see only the records included at stage 1, at stage 3 only the records included at stage 2 etc. I know that I could extend that to the analysis stage and I could combine that with Mark_M's second suggestion. 

              • 4. Re: One-to-one relationship or filters?

                Sorry, but I disagree. I do not see any one to one relationship here (copying records to another table does not a relationship make) nor do I see much point to copying selected records to another table. There are many ways to select a set of records and restrict access to just those records without the unnecessary duplication of your data involved in copying that data to another table.