Why do you need the lookup field? To copy the address? What happens when two families have the same last name?
How deep does this go? What if the family members have different addresses or last names? For instance a married daughter. Do you move that record out to its own "family" or delete that contact all together?
For simplicity I would assign a FamilyID and link the family members together with that. You could also have a table that just has Family ID, Contact ID, and possibly Priotity ID (role as you called it for the list of Father>Mother>First child>Second child. You could pull the list with ExecuteSQL and parse the result how you want in a text field or a merge field.
Main purpose is to look up the address and be able to link families within the same household. When someone leaves the household the record will be updated with a new address (and a new family ID) It's not meant to be go much further than that. The families-contacts join table has exactly what you're describing, ContactID, FamilyID and Role. What I need to figure out is how to crate a calculation that combines the names of the mother and father into one lookup value, making it easier to find and which table this should go in.
I'm quite new to this. Am I way off in my thinking?
Thanks for the suggestions.
The address seems to be relating the families together. But since you contacts table is maybe not built around the address it is not too straight forward.
I am not sure if you can consider an address table with family member names and roles as related records. If you think of it I terms of a "household" table instead of "address" this may make more sense.
I am really trying to figure how you are assigning the proper family ID to start with but I will assume you have a process for that.
If everything is in place and you just need the calculation for the names I will help with that when I get back to a computer unless someone gets to you by then. On the phone right now.
Here are some general comments which I hope you find useful in starting by avoiding any of the common pitfalls that can overtake a new developer.
Take a look at these pieces for some helpful starters on how to get the best out of FileMaker
Simplicity delivers performance:
Design principles for performance
Designing for language:
Designing a good Portal Filter:
What is happening when FileMaker Server becomes overloaded (and how to avoid it):
Other benefits of using a language table for localisation
There are several means of getting information from another record to appear in the current record:
(1) Unstored calc
(2) Stored calc
(3) Auto enter with don't replace
(4) Auto enter without don't replace (i.e replace) (better described as "Auto enter & update")
Many folk use (3) to set data which will not change on record creation.
If you just uncheck the "don't replace box" option (4) - you have a rather magical ability to have the data change automatically as necessary - when the source changes - but be stored and thus indexable and automatic. In my experience this is preferable to using a lookup which seems to take longer. But there are limitations to be aware of - see the following comments.
The question then is what is your source?
There are two ways of doing this. The source can just be a record in another table but there you run into whether and when your field will in fact update. It depends and you need to create a test file to work out what works and what doesn't - there are several techniques. As a general guide my preferred approach is the simplest and most reliable which - on balance - is to use an unstored calc in the current record as the source for the type 4 auto enter - this is foolproof and always works as expected. So if for example the father's data is amended the children's records will all update just like that but the children's records can still be indexed on their auto entered + replaced father's data.
Nick has plenty of experience to draw from. Please take the time to read the resources he has mentioned. To sum up what nick has said, the design matters! A bad design decision will haunt you for a long time.
#2 is easy with a simple relationship and a filtered and sorted portal on the layout.
#1 is a little more difficult.
For the calc I would suggest GetNthRecord(Contacts::FirstName, 1) & "-" & GetNthRecord(Contacts::FirstName, 2) if the relationship is correct and you have a sort order on the relationship by role numerically ascending. That should get you going in the right direction.
If you are feeling brave you may consider ExecuteSQL if you are familiar with it. It will allow you to grab data without regard to the relationship. Done properly is can be fast. Some people do this with only one table and 3 fields, an ID or UUID, data type, and data value. You might need a couple more fields for the family relation data. I know people use this for very easily doing value lists and that sounds like what you are looking for. Take a look around about it.
Thanks for the resources. I'll have a good read through them and see if I can get a better handle on where I'm going.
My pleasure Mike, please click the like button if you find this useful!
I also have an application where I need to keep track of families, and early on I realised that using surnames or addresses (or even Family IDs) to keep track of families was increasingly flawed, certainly in this area of the UK anyway, so I gave up the notion of "Family" as an entity in the database. I just use a "Relationships" table to link people, and a person's "Family" is everybody to whom he or she is linked in this way.
I wrote a blog post a while ago about this - Family Relationships – Design Walkthrough | A Filemaker Miscellany, which may (or may not!) be interesting. One of the advantages of this approach is that you can define non-family relationships - e.g. "Childminder<>Minded child", "Neighbour<>neighbour" etc., which makes the whole thing more flexible. And it's much less prone to errors through reliance on non-unique data, etc.
First thing I thought when reading this is how to narrow this down to less foreign-keys.
- fk_children (repeating field?!)
All it needs could be built on relationships. That's where you end in something like a babushka principe about how to "fold down" relationships. You'll need to get relationship of relationships to get grandchildren, for example.
Actually, all relations are there but they wouldn't resolve themselves like a recursion.
At this point I get the same spooky feeling like when setting 2 mirrors in opposition and set my had in-between
Thanks for all the suggestions. I'll try to dig through the reading and do some thinking on the design over the weekend.