Contacts Database

[Benjer]

Thanks in advance for your advice! I really appreciate it.

Here's the scenario:

I am building a database of the families at our church. I've started with two tables. The first table is a list of youth and their contact information (address, email, phone, school, etc.). The second is a list of individual families (mom name, dad name, address, phone, and kids, etc.)

What I would like to do is be able to do is have the Youth table autofill information from the youth's information in the Family table. I would settle for it being the other way around.

I can do it just fine when a family has only one youth. However, some families have more than one youth (which I've labeled youth 1, youth 2, etc.). I can put a portal on the Families table that pulls the information nicely from the Youth table, but then I cannot print a report of each family that only includes contact info and children.

Lets say a family (in Families) has youth 1 John Smith and youth 2 Jane Smith. How can I create a new record in my Youth table with name John Smith and have it automatically enter John's info (from the youth 1 fields in the Smith Family) and create a second record with name Jane Smith that then autofills her info (from the youth 2 fields)?

I hope this is clear, please let me know if it is not. Thanks for your help!

Benjer

[eos]

I think a better structure would be to build a table with individuals (flag which are youths and which are parents) with personal information, and a second table Families, which ties together the families and is the right place to enter all contact infos for this family only once (assuming the youths still live at home ;-), but without any youth1, youth2 etc fields. That's what related tables are for - same general principle as with a contact database, where family = company and person = employee.

First create the family entry with all relevant, then enter the person's data and choose a family.

This way you can show a youth (or any person) on a form layout, and per the Family relationship you can display all family-specific infos plus all the other family members (by equaling familyID and excluding the individual's ID). No need to enter and maintain information more than once.

Have a look below at this simple implementation, with some calc fields for nicer display and scripts to move between entries...

Note that, in addition to the familyID in the person record, there's also a join table for families/persons, because I couldn't figure out if there's a way to display the "other" family members of a given person without it. So this join table would make the familyID field in the persons table irrelevant, was I was to lazy to restructure. So be sure to enter a join record for each person AND fill in the familyID field in the person table.

Cheers,

Oliver

ChurchMemberFamilies.fp7.zip

Edited November 20, 2007 by Guest

[Søren Dyhr]

What I would like to do is be able to do is have the Youth table autofill information

It depends on what you mean by autofill, it's stumbling near that you wish to jeopardize referential integrity here. Relational databases builds on the principle that a piece of data only reside in one single location, but instead are show referenced.

I have here solved the matter with a recursive structure, using mergefields ... it's obvious that some kind of validation should be introduced to prevent overwriting "backwards" ... this is ONLY preliminary thoughts - a lot needs to get straightened before I seriously would think it is the solution to the problem!!!

--sd

Church.zip

[Benjer]

Thanks! The only thing that I really am missing is how to create a report that will serve as a phone list that would list each family as follows (for example)

Joe and Jane Smith

123 South Lane

Hampton, IA 51223

(123) 456-7777

Birthdays

Joe: January 5th, 1971

Jane: February 9th, 1972

Suzie: March 6th, 1996

Mark: June 19th, 1997

How can I get a report to do this?

Thanks,

Benjer

[eos]

Thanks! The only thing that I really am missing is how to create a report that will serve as a phone list that would list each family as follows (for example):

Joe and Jane Smith

123 South Lane

Hampton, IA 51223

(123) 456-7777

Birthdays

Joe: January 5th, 1971

Jane: February 9th, 1972

Suzie: March 6th, 1996

Mark: June 19th, 1997

How can I get a report to do this?

Thanks,

Benjer

[ I all wrote this whole stuff below and than noticed that you useFM 8, where the List () function isn't available :

Either use this custom function http://www.briandunning.com/cf/542 ,

or set up a script which gathers these infos and puts them into a global (which you'd have to trigger manually each time a change was made),

or upgrade to FM9 : ]

Create a new calculation field in which you make heavy use of the List () function, some new calculations and relationships. Let's take apart your example:

• Joe and Jane Smith

Set up a relationship from Families to a new TO of the join table that filters on parents; for this to work, you have to re-create the flagParent... field from Persons in the join table and make sure it has the correct flag set. Create a dummy global in Families that you fill with the ParentFlag and point it to the Flag field in the join table (and as second relationship operator, of course, the familyID). Now you put a TO of the Persons table "past" the join TO and do a List ( Surnames ) on that TO. This gives you a return-delimited list of the parent's surnames of that family. Substitute the return for " and " and append the lastName, and that's your first line. (This also works with a single parent, as there is no return to substitute, but it wouldn't look as expected with three or more parents...)

• 123 South Lane

Hampton, IA 51223

(123) 456-7777

This comes straight from the Families record itself and can be combined in a plain calculation.

• Birthdays

This is just a text constant; wrap in in a TextStyleAdd ( ) to make it bold

• Joe: January 5th, 1971

Jane: February 9th, 1972

Suzie: March 6th, 1996

Mark: June 19th, 1997

Contrary to the first line, here the desired relationship already exists, but you have the create a new text calculation in Persons to give each person a birthday string as shown above. (Consult your manual for how to format the date string in the desired way.) In Families, you just do a List () on that new calculation field (and now there's no substitution, as a carriage-delimited text block is exactly what you wanted).

Now that you have everything in place, combine it in a new calculation field (with the right amount of carriage returns strewn in ;-), and you're done.

Here some notes:

• The lists that List () returns are always sorted according to the sort order of the (last) relationship you travail in the relationship diagram, not the sort order of any portal.

• I don't know how strict you community is, but note that in my earlier example I created a family name field in the Persons table to take care of the case that a couple might not be married (or a children might come from an earlier marriage and have another name). I'd only fill in that field if it is different from the family name and leave it empty otherwise. To create the correct first line of your desired example in this scenario (eg Robert Smith and Jane Miller), you'd have to modify the first calculation, but I leave that to your imagination...

Hope this helps!

Cheers,

Oliver

Edited November 23, 2007 by Guest