3 Replies Latest reply on Jan 19, 2015 9:16 AM by MattLeach

    Getting value from Relationship

    MattLeach

      Im helping a friend out with a database and am stuck on pulling a value from a relationship.

       

      What I am trying to accomplish is having a one to many relationship using a Join Table

       

      Lets say I have Widgets, Join, and Categories. My relationship is setup as follows:

       

      Widgets::_pk_ID = Join::_fk_WidgetID and Join::_fk_CatID = Categories::_pk_ID

       

      I also have a TO called Categories_List which is a TO of Categories table. This is related to Widgets with a constant.

      Widgets::zz_Constant = Categories_List::zz_Constant.

       

      There is a value list that contains a list of all the categories

       

      In the database on the Widgets table I have a field that utilizes the value list of categories.

       

      The way it is setup is you click a dropdown and select the category you want and click a button to add the category to the record. This creates a record in the join table with the Widget ID and the ID of the category.

       

      The issue I am running into is that no matter which category I select, the ID for the category is always 1 for the first record.

       

      I have tried capturing the category id from Categories::_pk_CatID as well as Categories_List::_pk_CatID but not matter which category I select, it's always 1

       

      Im sure I am overlooking something simple but I just cant seem to pinpoint it.

       

      Thanks for any help you can offer

        • 1. Re: Getting value from Relationship
          jbrown

          Hi.

          I think the issue is coming up because of the relationship between your widgets and categories table.

          When there are many records related, it is going to choose only the first record from which to return the value. You have this because you have the two constants equal to each other. In essence, a many-to-many relationship (I believe, without looking at the specifics of the constant calc)

           

          The dropdown list should not be based on a relationship (related values only), but rather the table (all values from that table). Inside that field where the dropdown is located, store the value of the category ID that you get from choosing a category. Then use that value to fill the join table record.

           

          So you can get rid of the relationship between widgets and categories and just use the dropdown to show values from that table.

          • 2. Re: Getting value from Relationship
            erolst

            What about the field “that utilizes the value list of categories”? (Ideally a global field …)

             

            Since you're using a value list to fill it with the desired category/ID, you can simply use the value stored there for the new join table record; it is the selection.

             

            By looking into Categories::_pk_CatID or Categories_List::_pk_CatID, you will either always get a 1 (Categories_list), or a result that depends on which related join table records you have (Categories); either way, it's bound to be incorrect, since the the selection field has no impact on these relationships.

            • 3. Re: Getting value from Relationship
              MattLeach

              For some reason I thought I needed a relationship for the list but low and behold it works well without. Thanks for the clarification