5 Replies Latest reply on Feb 23, 2010 11:21 AM by philmodjunk

    Linking two fields in a portal

    Path

      Title

      Linking two fields in a portal

      Post

      Hi there.

       

      We have created a portal inside a table named competitions. We have a second table called Awards. We need to enter awards inside competitions and for each award to have a category and each category to show a list of subcategories to select from (we need to be able to select more than one subcategory.)

       

      So Competions -> Awards

       

      Category -> Sub Category.

       

      Where do we make the relationship so that it will work that when we enter an award, we can select a category (eg Science or Art) and each category will populate a list of subcategories (eg: Physics, Biology OR Painting, Drawing).

       

      Thank you in advance!!!! 

        • 1. Re: Linking two fields in a portal
          philmodjunk
            

          To set up your conditional value list, define your tables like this:

           

          Awards:

          Category

          SubCategory

          //Any other fields you need to document each Award record

           

          AwardSubCategories

          Category

          SubCategory

           

          Define a relationship in Manage | Database | relationships to link these two tables

          Awards::Category = AwardSubCategories::Category

           

          For your subcategory value list, Specify SubCategory from the AwardSubCategories table as your source of values.

          In the related values part of the dialog, specify Awards as your "starting from" table.

           

          Here's a thread on setting up conditional value lists that may help:

          Custom Value List?

          • 2. Re: Linking two fields in a portal
            Path
              

            Thanks! This is what we want to do. But, it seems that it is only working for values that were defined before we used the value list following your steps. At the moment we have created two portals:

            - one in Competitions layout

            - one in Category layout

            So we are entering the values for subcategories in the portal in category layout

            In the other hand, we are entering awards in the portal in the Competition Layout. When we enter a new award we want to select teh category and based on this selection the subcategories to show in a second field. This is working thanks to you but for some reason is not populating subcategories for new categories added (even do it shows the new categories added in the category drop down list). For subcategory, it shows: <no values defined>

             

            We add a picture below to show you the relationship.

             

            Database relationships

            Thanks is advance, your reply was amazingly helpful :-) 

            • 3. Re: Linking two fields in a portal
              philmodjunk
                

              Your newly added subcategory records likely have a blank category name field and this keeps them from appearing in your conditional value list. Though you will read posts by me and many others telling you to "use an ID number field" in relationships. In this case, I might use the category name field to link the Category and subcategory tables.

               

              An alternative approach, that keeps the relationships as defined, would be to define the category name field in Sub categories to automatically look up the matching category name from categories. This is the "looked up value" option you can select in field options for this field.

              • 4. Re: Linking two fields in a portal
                Path
                  

                Thank you, thank you, thank you! It works perfectly... not sure why we can not use the key fields... but works. Thanks again Phil

                 

                Elora & Ciara 

                • 5. Re: Linking two fields in a portal
                  philmodjunk
                     Well, by your original design, you have TWO key fields, kf_category_id and Category Name. Only one of those fields, kf_category_id, was receiving a valid value when you used your portal to add a new sub-category. To place the needed category name from category into your subcategory record, you have to either change the relationship or use feature such as a looked up value setting to copy the matching value from Category each time you create a new record in your portal.