when I change one they all change to the same selection.
That suggests that you only have one field, but three copies of the same field on your layout. If you enter layout mode and select each field in turn, do you see the same text in "display data from" in the Inspector's Data tab? If so, you have three copies of the same field. The first thing you would need to do is define three separate fields in Manage | Database | fields.
Then you might need three separate relationships to different occurrences of the same customer table, but that depends on what you intend to do on this layout. And it may be that you need a portal to three related records instead of three different customer fields.
Yes I am using the same "data", so I guess that is where my confusion comes from, I don't know how to select 3 distinct customers from that table, I haven't used a portal before. Do you think that is the best way to do it?
I lack sufficient detail about what you want to do to suggest an answer.
To clarify, are you telling me that if I have a "customer" table with a primary key of "custID", that I won't be able to pull more than one of those at a time into another table without using a portal?
No I am not. If you read my first post, you'll find that I said: "The first thing you would need to do is define three separate fields in Manage | Database | fields. Then you might need three separate relationships to different occurrences of the same customer table,"
It is possible that your design will be simpler and more efficient using a portal.
I saw that I guess my brain didn't compute what you were asking. I guess I don't totally understand what those fields would be. Right now I have CustomerName, CustomerEmail, phone, street, etc. etc. with CustomerId being the primary key. Can you give me some insight as to what the 3 separate fields would entail? Sorry for being such a rookie...
I wouldn't want to help you complete a design that might not be the correct way to go with your database.
I'd prefer a description of your layout and how you are trying to use it. In particular, I'd like to know why you need fields for 3 customer ID's for the same record as that is a bit unusual. That way I can recommend an approach best suited to your needs.
haha, I realize the 3 customer ID's is unusual, ultimately this data is on real estate transactions and so each record has a listing agent, a selling agent and the escrow officer, and we need to have all 3 tagged to this particular property that is being sold. Is that more helpful?
Very much so. Is the number of parties alway limited to no more than 3? If there is even the slightest chance that you need more than three in some circumstances, you need a portal to a related table so that the number of "customers" listed for a given record can be fully flexible.
Here's what I understand so far:
You need field for both agents and the escrow officer--all from the same table named "customer".
Use Manage | Database | Fields to define three number fields: SellingAgentID, ListingAgentID, EscrowOffID. I'll call the table you have for these fields RealEstate, but you can stick with the name you have.
Now we need 3 different "occurrences" of your Customers table...
In Manage | Database | relationships, make a new table occurrence of Customers by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as ListingAgent.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Add it to your relationships like this:
RealEstate::ListingAgentID = ListingAgent::CustomerID
Repeat the process to get:
RealEstate::EscrowOffID = EscrowOfficer::CustomerID
RealEstate::SellingAgentID = SellingAgent::CustomerID
You now have three different relationships between RealEstate and different occurrences of your Customers table.
You now have two options for showing the CustomerName, CustomerEmail, phone, street, etc. etc for each of these. You can, for example, add the CustomerName field from ListingAgent to your layout and it will display the ListingAgent's name when you select an ID number in ListingAgentID. Or you can define a text field in RealEstate and set it to auto-enter the CustomerName from ListingAgent via a "looked up value" setting in field options. The first approach means that any changes to data in the customers table automatically update when you examine your records in RealEstate. The second option captures the data that was current at the time you selected a CustomerID number in the field. This then will not automatically update if you edit the customer name in the original table. Both options are useful, you need to determine which is best for your specific database.
Fantastic, I will give this a shot!
And I assume when you say "Add it to your relationships" that is done on the relationships graph and I am just dragging the fields to connect them?
I assume when you say "Add it to your relationships" you mean in the relationship graph, and I am just dragging to connect the fields?
sorry if you already got this when I posted this question a second ago I am not sure it went through
When I am in Browse mode and try to use those fields I get this message.
Yes you need to drag from a field in one table occurrence "box" to a field in another to link them in a relationship. You can then double click the line to open up a dialog where you can specify more details about a given relationship if needed. (Not something you should need to do here.)