11 Replies Latest reply on Apr 26, 2017 7:30 AM by philmodjunk

    Showing related versions of a record within each record

    gdidomenico@brandeis.edu

      I am currently trying to create a database that will store the measures we use in our  lab. For instance we use many different self-report measures, and cognitive tasks and we need a database that will hold information about them (how they are scored, sub scales, amount of items ect.). I also have another table that is Studies, and this table will tell us what measures were used in each study.

       

      I am new to Filmmaker and am unsure if my relationship design is really how I want it. Right now I am struggling with how to show different versions of a measure within a measure. For instance some self-report measures have long and short forms. I want each survey to be a unique recored, but I would like to be able to see, and jump to a self-report measure's related survey. For instance,  if you are looking at the Self Compassion Scale, you can see that there is also a Self Compassion Scale short form and be able to go to it.

       

      Do not know if there are any sample files, or suggestions on how to do this. I have attached the relationship design I have right now.

        • 1. Re: Showing related versions of a record within each record
          Jason Wood

          It's hard to follow what you're trying to do when you your tables don't have any fields in them (other than key fields) and the table names use industry terminology that don't really give a lot of clues as to what you're doing. And especially the fact that you used the term "measures" several times in your post but this term does not appear in the graph. What is a measure??

           

          Try doing a quick overview of what this solution will do, while briefly explaining the meaning of the terms you use and how they relate to each other. And if you update your example file, try adding some fields to each table that give clues as to what the table is for.

          • 2. Re: Showing related versions of a record within each record
            gdidomenico@brandeis.edu

            Hopefully my new file will make it more clear. The measures we are using in the lab are scales (aka self-report measures) and cognitive tasks, so I have created a "Scales" table and a "CogTasks" table. Because one study can have many measure (Scales & Cog Tasks) and one measure can be in many studies I created a joint table between CogTasks and Studies called "StudiesCogTasks" and a joint table between Scales and Studies called "StudiesScales".

             

            What I am struggling with right now is how I can show related scale records within a scale. Like I said before, an example would be a scale record for a Self-Compassion Scale. Within that record, I want links to different versions of the scale. So there be a link to the Self-Compassion Scale short-form. I understand you can show related records from a different table, but I want to show related records within the same table.

            • 3. Re: Showing related versions of a record within each record
              philmodjunk

              You can show related records from the same table just like you would Related records from a different table.

               

              Open manage database and use the button with two plus signs to create a new occurrence of your table. Link them in a relationship.

              • 4. Re: Showing related versions of a record within each record
                gdidomenico@brandeis.edu

                I created a new occurrence of the scales table, and joined them and I believe it is working correctly. I created the ScaleName field as a drop down so they can choose from the different scales. Is there any way to have the relationship automatically show up in the other version? For example in the file, I add the related survey "Scale 1 short form" to the record "Scale 1". How can I have it so when I go into "Scale 1 short form" it already has "Scale 1" listed as a related survey.

                 

                Also, I would like the portal record to auto fill based on what ScaleName is selected. So if I choose "Scale 1" then ItemNumber will automatically be filled in.

                • 5. Re: Showing related versions of a record within each record
                  philmodjunk

                  If you have your portal set up with fields from the related table, the other fields will show data from that related record, no "auto-fill" involved. Keep in mind that records in the portal are records from the same table as that of your current layout.

                   

                  If all the records that are essentially variations of the same "scale" need to link, Just give a match field in that record the same value. This could be a "type" or "Category" field.

                   

                  In other cases, linking records in a "two way link" may require a join table:

                   

                  TableA----<join>---TableA 2

                   

                  TableA::__pkTableAID = Join::_fkTableAID 1

                  TableA 2::__pkTableAID = Join::_fkTableAID 2

                   

                  With this set up, you put a portal to Join on your Table A layout, and then add fields from Table A 2 to the rows of the portal in addition to the _fkTableAID 2 field that you use for selecting a record to link to the current record on the layout.

                   

                  Linking two records of Table A requires creating a record in Join with the two __pk field values. To make it a "two way" link, you then create a second record with the same values, but swapped between the two fields in the Join table.

                   

                  Say that you want to link a record with __pkTableAID of 223 to a record with __pkTableAID of 244:

                   

                  creating a two join records:

                  _fkTableAID 1         _fkTableAID 2

                  223                             244

                  244                             233

                   

                  A script performed by a script trigger can create the second record in the join table using the values of the fields in the first to automate the "back link" needed.

                  • 6. Re: Showing related versions of a record within each record
                    gdidomenico@brandeis.edu

                    I am unsure if you looked at the file I attached, but I believe I did that and it did not work. Also, I do not believe the second example applies to what I am trying to accomplish.

                    • 7. Re: Showing related versions of a record within each record
                      philmodjunk

                      If you did what I described, it would work.

                      • 8. Re: Showing related versions of a record within each record
                        BruceRobertson
                        I am unsure if you looked at the file I attached, but I believe I did that and it did not work.

                        Nope.

                        There is no join table in your file.

                        Attached is a screenshot of a cleaned up version of the graph from your second file.

                        (Objects have been moved but nothing about your relationship setup was changed)

                        scale thing.png

                        • 9. Re: Showing related versions of a record within each record
                          philmodjunk

                          My original suggestion was to give the same value to a field for every record in a group that you want to link to each other. You don't select individual records, you select the group to which you want to assign them.

                           

                          So, using made up values, if you want to link All "linear" scales to each other and all "logarithmic" scales to each other, you would set up this relationship:

                           

                          Scales::Type = Scales 2::type

                           

                          And the value of Type in all linear scales would be "linear" while the value of Type for all logarithmic scales would be "logarithmic". As long as no record has to be a member of more than one group, that's all you need. A table of such "types" might prove useful both as a source of values for a value list and to make certain reporting tasks easier.

                          • 10. Re: Showing related versions of a record within each record
                            gdidomenico@brandeis.edu

                            But I do not want to assign each scale to a group. I want to be able to see each of the individual scales each scale is related to on that scales webpage. Then I want to be able to go to the related scales records. Similar to an e-commerce page that would show for example shirts that are similar to the one you have selected. Doing it by type is something I want to use for categorizing the scales as "anxiety scales" or "depression scales".  What I want is different than that.

                            • 11. Re: Showing related versions of a record within each record
                              philmodjunk

                              I want to be able to see each of the individual scales each scale is related to on that scales webpage.

                              And that describes a group of scales. By assigning them to a group, the individual scales are now related to each other and allow you to do what you want. You may not call that a group, but it most definitely IS a group. If they didn't have some common characteristic, you wouldn't be linking them to each other, correct?