The user needs to see the entire address to make a decision? Perhaps you should really be using a related records portal (the example that comes to mind is an online shop like Amazon, where the customer has 3-4 (or more) stored delivery/billing addresses and needs to choose one.
Otherwise, it sounds like you have the right idea. Perhaps your value list needs to be checked that it points at your (new?) concatenation field?
The tables are related. However, I don't want a portal-I don't have the space for the portal. I only want the field showing the possible addressess. And the value list points to the concatenation field - but not displaying all the fields. Only the first field.
Describe how your tried to set up your value list in detail. Using a calcula tion field that combines address, city, state, zip fields as the second value list column should work for you. We need to know more to help you spot what detail is keeping this from working for you.
Here's more info.
I have two tables. One called IEP, the other Parents. They are related.
In the IEP Layout, I have a field called Parent's address. I want this field to show all the addresses(and all fields from each address) from the parents and let the user choose one.
In the parents table, I have a calculation field: Address & City & State & Zip.
I have a value list using the values from the ParentsCalc Field.
When I try it out, the list only shows the address from the first and second record, but is ignoring the other fields.
Also, if I change the info from the parents address, the list does not automatically sync?
What am I doing wrong?
You have several issues here.
First, define a serial number field in parents: ParentID to uniquely identify each parent record. Then define a number field, ParentID in the IEP table.
Define your address calculation field, cParentInfo, as: FullName & " " & Address & " " & City & " " State & " " & Zip
Define a relationship between IEP and Parents as:
Parents::ParentID = IEP::ParentID
Define your value list to list Parents::ParentID as the value in the first column. Specify Parents::cAddress as the second column of your value list.
On your IEP layout, use the field tool to add the individual name and address fields from the Parent table directly to the IEP layout.
Put IEP::ParentID on this layout and format it with your value list. When you select a parent from this value list, the fields from the Parent table will automatically display information from the selected parent record and any editing done to the parent record will be automatically shown on any IEP records that link to that parent record.
Notes: you may find that you only need to show the parent name in the second column of your value list. It depends on your school size, the larger your school system, the more likelyhood that you may see two parent records with the same name. Including the address info in the value list helps the user distinguish between different parents of the same name. You may have other fields that serve that purpose better. The parent ID number is used both because peoples names are not unique and also because people change their names.
Thanks Phil.I have not tried that yet because I have a question before doing so.
Actually, I have another table called Students. SASID is the PK and is connected to Parents and IEP as FK's.
Should I still connect the IEP::ParentID to the Parents::ParentID? (When I tried to do so it says there cannot be more than one relational path between....)
You can have more than one relational path, but you have to create a new table occurrence of an existing data source table in order to do so. This is often necessary in FileMaker and that's really what this dialog was doing, asking if you want to create a new "instance" (Table occurrence) of an existing table.
That said, if a student record can only link to a single parents record, (may not be the case due to divorce and remarriage amongst parents), you do not need to add this relationship. You can select an SASID in IEP::SASID and the fields from the parents table will then show the address info.
If you have multiple parent records, you might want to use a portal to the parents table to display the name and address info for your parents.
To learn more about table occurrences, see this thread: