In Manage | Database | relationships, make a new table occurrence of Composition by clicking it and then clicking the duplicate button (2 green plus signs). Double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be Composition|Composer.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
CompositionProduct::ComposerID = Composition|Composer::ComposerID
Then format CompositionProduct::CompositionID with a value list thst lists
CompositionID and a name field from Composition|Composer and select "Include only related values, starting from CompositionProduct."
Thanks for taking a look PMJ - I setup everything as indicated - your shorthand is easy to follow.
The result is a value list drop-down that is empty. If I manually go find a value and enter the CompositionID, then, clicking on the drop-down populates with the one value I've just entered. It feels like it's only seeing records its attached to already, not all Compositions it could be attached to. If I select All Values in the value list, it works as expected, pauses for a second then returns all compositions irrespective of composer.
My v5.5 solution is structured exactly as you define it, and it works properly. I'm rebuilding in v12 and so far it's been pretty smooth. Very odd. I think I'll call it a Friday afternoon, tip back a Margarita watch an end-of-the-world movie, then see what else is possible here - maybe check the data to see if it's all clean. Seems buggy in this regard.
Better double check the data source table of the new table occurrence. Doesn't sound like it's the right one.
with a relationship between CompositionProduct and an occurrence of composition, the relationship should match to all composition records by the composer you select in CompositionProduct::ComposerID.
One way to check this is to place a portal to Composition|Composer on your CompositionProduct layout. When you add fields to the portal, you should see fields from the Composition table. WHen you finish with that and return to browse mode, selecting a composer in the composer ID field, you should see all composition records with that composer ID value.
Okay, I double-checked, and it was the right occurrence. The Portal as you defined returned all of the appropriate records for the selected composer. So that confirmed for me that the structure should work, but the value list wasn't. Spent a couple hours investigating.
Discovered that my client/server versions were out of synch. Running 12.0v4 on the client, 12.0v3 on the server. Wasn't sure that would change anything and at first glance it didn't. Then I deleted the value list, rebuilt it and BAM. It works as it should have all along. I'm not 100% certain about the causation, but I can say that I am 100% sure the value list as defined didn't work before the upgrade, and setup exactly the same after, it did.
Whew. Anyway, thanks for your guidance. Once I knew it wasn't sloppy relationship or value list building, it was clearer where to look. Super hyper very much appreciated!