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.
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?
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::__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.
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.
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.
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?
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.