5 Replies Latest reply on Jun 24, 2013 8:19 AM by philmodjunk

    How to display fields from related table?

    brett_

      Title

      How to display fields from related table?

      Post

           I have 'Purchase Transaction' and Customer tables.  I've created this relation:

           Customer                  Purchase Transaction
           CustomerID  <<>>   CustomerIDFK
           First Name                Product Name    
           Last Name                Price

           I have a layout for Purchase Transaction.  I've added a portol field for Customer::CustomerID.  In the relation, I allow records to be created on the 'Purchase Transaction' side.  The CustomerID field in this layout is a drop-down list.  It is associated to Customer::CustomerID and has a value list with a source to the same Customer::CustomerID field.

           When I create a new record in the 'Purchase Transaction' table/layout and click the CustomerID field, the drop-down list does not appear.  Nothing happens.

           I'd like to select from existing customers (customer IDs) that will then insert into the 'Purchase Transaction' table.

           Any ideas what I did wrong?

        • 1. Re: How to display fields from related table?
          GuyStevens

               I think I spotted your mistake. You don't put the CustomerId field from the Customer table on the Purchase Transaction layout. You need to put the CustomerIdFk field in that layout and set your dropdown value list up on that field. The idea is that you put the Customers id in the CustomerIdFk field of the Purchase Transaction table.

               Then you can just add other fields from the Customer table on the Purchase transaction layout. They will be populated as soon as you set a correct Customer Id in the CustomerIdFk field.

               So you don't need a portal.

               If that doesn't work check these:

               - Is your CustomerId field in the' Customer table a number field that's set to Auto Enter Serial Number?
               - Are there actually serial numbers entered in that field?
               - Do you have any existing Customer records and do they have a Serial Number in the ID field?
               - Is the CustomerIdFk field also a number field?
               - Did you create a relationship between the two tables?
               - You say you added a portal field. But that's strange. You don't really need a portal in this case. But maybe it's just a bad choice of words.
               - You just need the CustomerIdFk field on the Purchase Transaction layout and set that up as  a dropdown.
               - Is the dropdown value list set up to contain the CustomerId in the first field? 
               - Is the dropdown list setting an Id value in the CustomerIdFk field in the Purchase transaction table?

          • 2. Re: How to display fields from related table?
            brett_

                 Thanks.  That's just about there.

                 I removed the ProductID portel field and replaced it with 'Purchase Transaction::ProductIdFK'.  I then changed it to a drop down and chose 'Products' as a value list source.

                 The Product Value List is setup like this:

                 'Use values from field <<Specify field>>
                 >>Use values from first field>>Product::ProductId
                 Also display values from second field>>Product::Name
                 [checked] Show values only from second field

                 I see the product names when I click the product drop-down.  That is finally working.  But once I make a selection, it changes to the ProductID.  I'd like to see the product name here as well.  But it doesn't necessarily need to be stored in 'Purchase Transaction'.  Probably better it isn't.

                 You have some idea how that can done?

            • 3. Re: How to display fields from related table?
              brett_

                   Looks like I can just do a portal to get product name.

              • 4. Re: How to display fields from related table?
                GuyStevens

                     I thought we were talking about relating a customer to a purchase transaction.

                     You only need a portal is you want to show multiple related records. If you want to relate only one record. Be it a Product or a Customer then you can just put the fields of that related table on your layout.

                     For the technique to have the product or customer name show up on the layout instead of the ID try following this video:

                http://www.youtube.com/watch?v=Sg4vyifqZEM

                     This works both in a portal as on a layout.

                • 5. Re: How to display fields from related table?
                  philmodjunk

                       A pop up menu--instead of a drop down list, can still show the name field after you exit the field.

                       A drop down list can be set up the same way with a bit of layout trickery. Place the name field from the related table, formatted with an opaque fill color on top of the drop down list field. Use behavior settings in the Inspector to deny access to this name field when in browse mode. When you click on the name field, the drop down list will pop in front of the name field and the list will deploy. After you seelct a value, it disappears back behind the name field and the name field then displays the associated name for the ID number selected in the drop down list.