I'm still learning how to use FMP myself, so first thing I'll say is that you should wait for further advice before committing yourself to the path I suggest.
I think you may have to create a "Key" rather than just using the Names in each of your tables in order to connect them in the relationships graph. For Example, you'd have the field "England ID" in the English table which would be set up to be an auto-entry serial number, and the same for each of the others. Then in your UK table, you have the UK ID also set up for auto-entry serial number, but you also list the fields England ID, Northern Ireland ID, Scotland ID and Wales ID. These Four can be regular fields, don't have to be validated. Then on your relationship chart, you connect each contry using the ID you created to the corresponding one on your UK Relationship box.
Does that sound clear?
You were on the right track but I think you just need to create those "Keys"
The answer is certainly 'Yes', and it should be easy. Unfortunately I'm finding it hard to think of a 'real-life' example set around the scenario you explained that would seem sensible. I'm sure it's my mis-understanding, as in most of the interpretations of your scenario that I think of I would simply mark up a different field in the same Table, not record in another different Table.
No matter; hopefully you can get what you want from my 'general' explanation:
- Suppose you have 3 Tables, A, B, and C.
- Each have two fields, A1 and A2, B1 and B2, etc
- Set up two relationships, via Field1
Table A --> RelationshipAB (FieldA1::FieldB1) --> Table B
Table A --> RelationshipAC FieldA1::FieldC1 --> TableC
That relates Table A to Tables B and C (via fields A1, B1, C1, all with 'Country' in them, maybe?)
If you want FieldA2 to have the concatonated contents of the fields B2 and C2 then just change FieldA2 to be a calculation:
FieldA2 = RelationshipAB::FieldB2 & " " & RelationshipAC::FieldC2
This will create a field that has the contents of the matching record in Table B, with a space (you can make it a 'dash', or a 'return' if that is better presentation) and then the contents of the matching record in Table C.
Note that you do not use the '+' symbol, but an ampersand.
Because I do not fully understand why you are trying to do this, I have to add a caveat: in this calculation you will only get the contents of the first matching record in Table B joined to the first matching record in Table C. In other words, I would guess it only makes sense if you are certain that there only ever will be one matching record in Tables B and C for each record in Table A.
I hope this helps, anyway,
Edit: Back to my first point: why would you not have put all the records (for the 4 countries) in oneTable, and had a 'Country' field marked up as 'England', 'Scotland'... etc? It would maybe help my understanding, to be able to help you better.
Hi Kat and Sorbs,
Thanks for your suggestions. I can rule out Sorbs' idea because I don't want to concatenate the locations from different countries.
I'm not sure about Kat's advice. If there is only one field in each table, containing unique data, I figure that must be a Primary Key.
The problem for me is the whole vocabulary of Filemaker and the relationships between tables. Every time I look at a solution or Help file, I see a dozen terms that I don't fully understand and I'm having trouble visualising the solution. This problem is not confined to Filemaker alone of course!
Maybe I also didn't explain myself too well.
Say I have two tables:
"Locations England" and "Locations Wales"
Each table has one field:
"Location England" and "Location Wales"
In the England table there are three records: "EngSite1", "EngSite2" and "EngSote3" and in the Wales table there is one record: "WalSite1".
I want a dynamic table that lists all these records in one field. The table is "Locations UK" and the field is "Location UK". There should be four records in the field:
"EngSite1", "EngSite2", "EngSote3", "WalSite1"
If I add another site in my Wales table: "WalSite2", it should automatically also appear in my UK table:
"EngSite1", "EngSite2", "EngSote3", "WalSite1", "WalSite2"
I have set up two Relationships:
"Location England = Location UK"
"Location Wales = Location UK"
In both, the option to create records in this table is clicked only for the Location UK side of the relationship.
Lines come out of the England and Wales tables and go into the UK table.
Then I create a layout using the field "Location UK" from the UK table and switch to Browse mode expecting to see all the records from England and Wales listed there. But instead, all I see are the words "Unrelated table".
I sink into a gloomy state. I am lost. Any advice warmly welcomed.
Sorbsbuster back for another go...
Yip, I think the vocabulary is confusing us.
Table:A collection of records.
Table1 = 'Yellow Pages', Table 2 = 'Grey Pages'.
Record: A collection of fields (all relating to one item)
Table 1, Record 1 = 'AAA Taxis', Record 2 = 'AB Services'... Record (last) = 'ZZ Top Hairdressing'
Table 2, Record 1 = 'Mr A Aardvaark', Record 2 = 'Mrs Ann Austin',... Record (last) = 'Mr Zebediah Zebra'
Field: A single characteristic of the record
Table 1 Fields in all records are 'Business name', 'Business Type', 'Street', 'Town', etc.
Table 2 Fields in all records are 'Surname', 'Title', 'Street', 'Town', etc.
Data: contents of the field in the record in the table.
'Smith', 'Mr', Acacia Avenue', 'Manchester', etc.
Tables consist of a collection of records.
Records consist of a collection of fields
Fields are filled with data.
Hence, your sentence: "Each table has one field: "Location England" and "Location Wales" " doesn't really make any sense. I assume you mean:
Each Record has one field - 'Location'.
One record in that Table has 'England' in that field.
A second record in that Table has 'Wales' in that same field.
I think if you create two Tables you will see the effect you want:
In the 'Country' Table create two fields, 'Country', and 'Location'
In the 'Locations Table' create three fields, 'Country', 'Location', and 'Site'
In 'File -> Manage -> Database', go to the 'Relationships' tab.
Create a new relationship (the button with a square and a '+', bottom left).
On the LHS, choose the Table 'Country', and the field 'Country'.
On the RHS, choose the Table 'Locations' and the field 'Country'
Ok -> Ok, etc..
Change to the layout 'Country'.
Select 'View' - > Layout Mode
Choose the Portal tool from the Status bar
Click and drag a rectangle on to the layout.
In 'Show related records from...', choose 'Locations', and set the 'Portal Rows' to be 5
Choose to set into the portal the fields 'Location', and 'Site'
Go back into Browse mode.
In the 'Country' screen, create one record, and enter 'UK' into the 'Country' field.
Change to the 'Location' screen.
Create one record. Enter 'UK' into the 'Country' field. Enter 'Wales1' into the 'Location' field.
Create another record. Enter 'UK' into the 'Country' field. Enter 'Wales2' into the 'Location' field.
Create another record. Enter 'UK' into the 'Country' field. Enter 'Scotland1' into the 'Location' field.
Now go back to the 'Country' screen. You will see that in the record with 'UK' in the 'Country' field it shows the 3 records listed, in the portal you created.
Change the 'Country' in that record to be 'USA', and they all vanish - there are no 'Locations' whose 'Country' has been tagged as 'USA'.
Go back into the 'Locations' screen.
Add another record. Type 'USA' into the 'Country', and 'Florida' into the 'Location'.
Add another record. Type 'USA' into the 'Country', and 'Texas' into the 'Location'.
Now go back to the 'Country' screen. You will see that in the record with 'USA' in the 'Country' field it shows the 2 records listed, in the portal you created.
Create another record in the 'Country' table. (You will now have two records in the 'Country' Table, and 5 in the 'Locations' Table.)
In one record have the 'Country' as 'UK', and in the other have it as 'USA'.
You will see as you flick between them they list all of the 'Locations' that have been tagged as 'belonging to them'.
If this works for you and makes some sort of sense, you will be able to make all the connections that you are talking about.
Sorry if this isn't clear enough,
Thanks for this detailed answer. I have not got around to testing it yet and will let you know when I do. Meanwhile, your advice is hugely appreciated - thanks so much.