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.
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.
Codebook Draft Test.fmp12.zip 228.2 K
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.
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.
Codebook Draft Test.fmp12.zip 230.0 K
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::__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
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.
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.
If you did what I described, it would work.
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.
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.
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?