8 Replies Latest reply on Nov 28, 2012 10:54 PM by productionQC

    Value List to Populate Portal!

    productionQC

      Title

      Value List to Populate Portal!

      Post

           Hello All-

           I am stuck.  I have a small database that we are trying to setup for monitoring and testing the production of our products.  I have the main table, which is SAMPLES.  The second table is PRODUCTS.  The Fields are as follows:

      SAMPLES                                            PRODUCTS
           SampleNo                                           ProductName
           Product                                                ProductDescription
           TestResult                                          SearchEnter                          
           SampleDate                                       Details
           TestDate

           The relationships are as follows:

           Product     -----=-----<    ProductName
            

           So the user would use a layout based on SAMPLES to input a sample number, select a product from a dropdown list (using the records in PRODUCTS) and enter the dates, followed by their test result.  I have another layout for Products, to allow the user to add new products and edit details.  To make it faster to find a product, I have a dropdown list based on the global field SearchEnter.  The dropdown is scripted to find the record.  Everything works great.  My probelm is that I would like to add a portal to the PROCUCTS layout that would show all related test results for a product.  Thus, when a user selects the product from the dropdown, not only does it display the product info, but in the portal below it grabs the all the sample records from SAMPLES table that are based on the selected product.  So if I select widget D, only sample taken from widget D's appear in the portal.  I can not for the life of me figure out how to make this work.  In fact I can't even seem to get anything to display in the portal.  Do I need a script for this, or a relationship...

           Any suggestions would be greatly appreciated!

           Thank you!

            

        • 1. Re: Value List to Populate Portal!
          philmodjunk
               

                    The relationships are as follows:

               

                    Product     -----=-----<    ProductName

               I think you really have:

               Product     >-----=-----   ProductName

               Surely you have many samples to just one Product record, correct?

               WHile it's not a relationship that I recommend (see why at the end of this post), it should work for your portal and with no extra scripting. Selecting a procuct name in the Samples::Product field should automatically link it to any records in Products that have that exact name. It may be that one of these fields is of type number instead of text.

               The problem with this relationship is that it is based on a name. If the name of a product should be changed at a later date--perhaps to correct an error in enting the product name in Products, it breaks the link to any existing sample records for that product. It's better to define an auto-enetered serial number field in Product to link to a number field in Samples. Your valule list for selecting a product can use ProductID as the first field and the Product name as the second so that a user can select a product by name, but the value list enters the ID number.

          • 2. Re: Value List to Populate Portal!
            productionQC

                 Hi-

                 I see what you are saying about the relationship.  But my relationship is as stated.  I setup the Products table to populate the dropdown list on the Samples table.  So therefore I have the field Product on the Samples table to show all of the products.  I think the theory is that the user can select from many PRODCUTS for one SAMPLE. It will not let me define this relationship any other way.  We are not looking at it from a Product to Sample point of view.  We are only interested in generating a record of all the samples we take, the results, etc and let the user simply choose the product from a table that we can constantly add products to.  We would have made a list for the dropdown, but the product range could be infinite, thus I did not want a giant list, rather make another table to store the data.  Does this make sense or am I am missing something.  I did as you stated and created the field PrimaryID in the Product table and the field ChildID in the Sample table.  That relationship is exactly as you stated using the new fields  SAMPLES:ChildID   >-----=-----   PRODUCT:PrimaryID.  The funny thing is that now I have added this realtionship it changed my original relationship for the drop down list.  It is now Product --------- ProductName.  One to one I guess. Everything still works as far as the dropdown list, so I think we are on the same page now.  However, my portal will not populate with any data.  Any Ideas?

            SAMPLES                                            PRODUCTS

                 SampleNo                                          ProductName
                 Product                                              ProductDescription
                 TestResult                                          SearchEnter                          
                 SampleDate                                        Details
                 TestDate                                            ParentID
                 ChildID                                               

                 ChildID >-------- ParentID
                 Product --------- ProductName

                  

            Thanks

                  

            • 3. Re: Value List to Populate Portal!
              philmodjunk

                   You appear to have two pairs of match fields and that won't work for this relationship.

                   What I recomended is that the records match only by a serial number field defined in Products:

                   Products------<Samples

                   Products::__pkProductID = Samples::_fkProductID

                   See this thread for an explanaition of the notation that I am using:

                   I'm confused by your latest post. What IS the relationship between products and samples supposed to be???

                   How could one sample refer to more than one product?

                   I've been assuming that for a given record in products, you might have many samples but that you would never have one sample that refers to many products records.

              • 4. Re: Value List to Populate Portal!
                productionQC

                     Hi - You are correct.  One Product can have many samples.  I think what was confusing me is the relationship to make the dropdown list perform.  I was think in reverse.  One sample could have many choices of products.  I have since deleted the relationship between product and product name.  The only relationship in effect is ParentID -----------< ChildID.  My dropdown list still works perfectly.  So you are right on all accounts, sorry for the confusion.  I have my head around that part now, but how do I get the portal in Products table to show all of the related samples.  When the user selects the product from the dropdown it goes to that record and shows the details perfectly.  But I can't get anything to show in the portal.  I want to make it so that when a user selects it the portal brings in all of the product test results with a matching name.  User selects widget A and the record for widget A is displayed.  At the same time the portal grabs all records from the SAMPLES table with the same name...showing all of it test results.  Again, sorry for my ealier blunders and thanks again for your help.

                      

                • 5. Re: Value List to Populate Portal!
                  philmodjunk

                       And what layout are you one when you do this?

                       A layout based on samples or a layout based on products?

                       I think you want to find a product record in products and then a portal to samples will list all sample records for that product.

                  • 6. Re: Value List to Populate Portal!
                    productionQC

                         I would like it to be in the Products layout if this is possible.  I would prefer to use the Sample layout  for the purpose of entering test results.  Thus the user could flip to Products to review the results on that particular product.  The collective sample data is of no concern to the person entering the results from the test, thats all they do is test, not analize.  I could then give them only access to the Sample side without showing them all of the collective results in the Product layout.  Can this be done, or does the portal have to be in the Samples layout?

                          

                    • 7. Re: Value List to Populate Portal!
                      philmodjunk

                           No, it should be placed on the Products layout if you want to list records from samples in the portal.

                           That's step one, putting a portal to samples on your products layout. This should be as simple as using the portal tool to draw a rectangle on the products layout and then selecting Samples from the Show Related Records From drop down.

                           If this produces an empty portal, then you are not matching records correctly in your relationship. Perhaps your existing samples records do not have the needed value in ChildID to match to the correct record in Products. This field does not get a value automatically, you have to enter or select a valud in it to link it to a products record. The ChildID field should be formatted with a drop down list or pop up menu of ProductID values, you can specify the ProductName field as the second field in this value list so that a user can select a product by name, but the value list enters the ID number.

                      • 8. Re: Value List to Populate Portal!
                        productionQC

                             Changed the relationship around and portal now populates.  Thank you for your help!yes