Selecting a Table Record Out of a Portal

[sidcam]

Ok, stupid newbie question (and I'm just not "getting it" reading the manuals or FM's knowledge base). Thanks in advance to those who help.

I have two tables (Table A; "Customer Data" and Table B; "Product Desired"). Table A is the table users will use to enter in their data. Every record in Table A needs to have a one to one relationship with a record in Table B (and only one). Up until now I've give every record in Table B a unique serial number (Serial Number Field) that was related to a drop down menu field in Table A where the user could select one option and marry the table records. Works fine.

HOWEVER, I want to significantly increase the options in Table B and have the ability to add and change records in Table B without having to constantly update Table A's relationship field options in the drop down list.

I would also like to make the selection process more user friendly than a single drop down field- i.e. let the Table A user(s) see/sort through the whole list of options in Table B with product photos (container fields in Table and a description field (again, field in Table . Basically a catalog page.

A Portal seems to be the obvious (if not only) answer. However I can't seem to get the table relationships to the point where I can see/sort every record in Table B (in the portal) and then select the record that I want the one to one relationship with. (I should note that the portal will always need to show the full catalog so users can come back and make changes later).

If I understand it right I should need two relationships between the two Tables- one that is a one to all relationship (for the portal) and a second that is a one to one relationship (for the actual records).

I tried to create a second ID field in the two Tables that were both "1" so the Portal could see everything but I can't figure out how to reestablish the one to one relationship. I've also been thinking the solution is to have Table A see everything in Table B via the portal and then have some kind of select and copy option that duplicates one record in Table B to a Table C for Table A's use (that only allows one record which is then deleted and rerecorded when a new Table B option is selected).

Am I completely making this harder than it should be? It seems to me this should be an easy no brainer task.

[Fitch]

First of all, if you did stick with a drop-down list, you don't have to hard-code the values, you can use related values. So there's no maintenance required.

As for the one-to-one relationship, I'm not sure what the problem is; however, you don't have to do it that way, you can just use the cartesian (X) relationship when you want to show all records.

The typical way to select an item would be to put a button in the product portal that, when clicked, puts the product ID into a corresponding product ID field in the customer record.

E.g.:

Set field[ customer::product ID ; product::product ID ]

You would use a relationship between the customer product ID and the product product ID to show the selected product in the customer record.

(By the way... can two customers choose the same product? If so, that would be called a one-to-many relationship, not one-to-one.)