4 Replies Latest reply on Apr 30, 2013 12:05 PM by tomoy

    Stumped on Value List



      Stumped on Value List



           Hi All, I hope I can describe this properly.
           I'm working on a CD database for classical music.  I have a Product table (of CD's) a CompositionProduct (many) table a Composer table and a Composition table.  For some (most) recordings I attach a Composer in CompositionProduct table.  However sometimes I want to attach a Composition as well.
      •           I'm populating data using a CompositionProduct layout.  
      •           The HMID is a drop-down value list of Product:HMID and displaying title too.
      •           The Composer ID is a drop-down value list of Composer:ComposerID and displaying ComposerName as well.
           Here's what I'm trying to figure out.
      •           I want the CompositionID drop-down value list to only show me Compositions by the composer entered in the CompositionProduct:ComposerID field.  In SQL terms, I'd like my value list to look like SELECT * WHERE CompositionProduct:ComposerID = Composition:ComposerID.
           I've tried various relations from Composer:ComposerID -> Composition:ComposerID, to self-relations and never get the results I want.  I can pretty easily get All compositions.  Or just compositions that have been attached to in other CompositionProduct records.  But I can't get just the Compositions composed by the ComposerID selected earlier.
           I'm pretty sure this would be easy if I always had a Composition attached to CompositionProduct.  Then I could reach through to Composer.  But if I don't have any Compositions, and just want a Composer, I seem to be stumped.
           Thanks in advance for any ideas.  Scratching head.


        • 1. Re: Stumped on Value List

               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."

          • 2. Re: Stumped on Value List

                 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.

            • 3. Re: Stumped on Value List

                   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.

              • 4. Re: Stumped on Value List

                     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!