How to provide an index across multiple tables

[Medusa]

Not sure this is the right place for this query. I'm a beginner with Filemaker but have some experience with databases.

I need an index/report which will cross multiple tables, let me explain.

Lets say I want the following tables.

a: Births

b: Deaths

c: Marriages

Each table would have a first and last name. I will need to be able to show a form or report which is an "index" of all "events" by name i.e.

Births table has the following:

Fred Jones 2 Feb 1945

John Jones 7 Jul 1973

Deaths table contains:

Fred Jones 15 Jan 1963

Anne Jones 12 Mar 1801

Marriages table contains:

Jane Jones 21 Aug 1847

Sally Jones 14 Sep 1891

Ken Jones 18 Apr 1921

I need an index report which shows ALL entries, how would I structure this?

Anne Jones 12 Mar 1801

Jane Jones 21 Aug 1847

Sally Jones 14 Sep 1891

Ken Jones 18 Apr 1921

Fred Jones 2 Feb 1945

Fred Jones 15 Jan 1963

John Jones 7 Jul 1973

Any help greatly appreciated.

[comment]

It looks like you should have two tables: People and Events. The People table would have fields for:

• PersonID

• FirstName

• LastName

and the Events table would have:

• EventID

• PersonID

• EventType

• EventDate

The relationship between these two tables would be matching on PersonID. Then you can produce the above report from the Events table (with the names coming from related fields in People).

[mr_vodka]

I think that you should have one table for your people and then another table that captures the event type, date, and the personID. You may eventually want even a third table depending on your needs.

*Edit: Sorry Michael didnt see your post ???

Edited May 6, 2009 by Guest

[Medusa]

Thanks for the quick suggestions guys, there is actually going to be more event types than this and much more detail but I think simplifying things in this way has made the solution more obvious.

Thanks again.

[Medusa]

I knew this was too simple an example. I have a BIG complication.

So, each event can have multiple Persons i.e.

Birth: Contains the Person born, the Father & Mother. So for arguments sake 3 People.

Marriage: Has Bride, Groom, Brides Father, Grooms Father & 2 witnesses. 6 people.

You get the picture.

I would like the Name index to show ALL people whether bride, groom, witness etc etc.

Now the only way I can see this working is with a portal for the people in an event and a ROLE for each person. BUT I don't really like this solution because I'd like to have forms that look like a birth certificate, a marriage certificate etc.

Any ideas, how I might achieve this?

Thanks for any further input.

[David Jondreau]

You can have as many layouts as you want. One for entry, one for Marriage printing, etc, etc.

[comment]

the only way I can see this working is with a portal for the people in an event and a ROLE for each person

I don't know the purpose of your solution, but that seems like a solid approach in general. It would definitely be the right way to go in a genealogical solution, for example.

Another option would be to have a 'supertype' table of Events, with a 'subtype' table for each event type - but I doubt very much that would be worth the added complexity in this case.

Now I hesitate a little, because I can see the problem with the form and I do NOT want to suggest you add a filtered relationship for every possible role (although that would certainly work). On the other hand, I am not sure if I can explain the alternatives within the confines of a forum message. Could you perhaps show us a few examples of these certificates?

[Medusa]

This link below will show some examples of certificates from where the data may originate.

http://www.maesbury.org/broadplace/ancestors.html

FYI I am researching a surname study and as such record all instances of a given surname. So I need to record all data from many sources of information, birth certificates, marriage certs, census returns, wills etc.

Many kinds of events with many people and roles. I would like as a minimum to be able to report an index of all names.

Unlike other genealogy databases I am not trying to link families together in to trees, but just record the references to the individuals. I may have a birth cert, marriage cert for the same person, but do not need to use the same person ID for both events, I just need to know the details of each event.

Hope this makes sense.

[comment]

I am afraid those pictures do not answer my question - which was how do YOU need to organize YOUR forms. If you can live with output like:

CERTIFIED COPY OF AN ENTRY OF BIRTH:

Name: Baby Doe

Name of Father: John Doe

Name of Mother: Mary Doe nee Roe

Date of Birth: 1/1/1900

Place of Birth: Anytown, USA

etc.

then you shouldn't have any problems using the above structure of:

Events -< Roles >- People

[Medusa]

Well Ideally I'd like to enter data in a table format, almost Excel like.

Name: Date: Father: Mother: Place:

John Jones 21 Jan 1857 Frank Jones Martha Jones London

Fred Jones 03 Mar 1861 Frank Jones Martha Jones London

Jane Jones 17 Jul 1901 John Jones Elsie Jones Hull

[comment]

I believe that is possible - but not at all easy to set up, as I hinted earlier.

Let me suggest a simpler alternative, but this will only be suitable if you work in two stages: first enter all your data, then do the analysis.

---

Note to myself: global repeating field for roles, repeating field for names.

Edited May 8, 2009 by Guest

[Medusa]

By entering ALL data before analysing do you mean all data per record. OR are you saying I'd have to have recorded every peice of information before I analyse.

This is an ongoing data collection excercise. New information emerges continuously but each peice is a new record.

I'm pretty new to this so sorry if I'm being dumb.

Another posibility I wondered about (though don;t have the skills as yet to implement is the possibility of having a seperate "index" table. The idea being I could strucutre tables for each record source and copy appropriate information to an index table on saving a record.

I don't like this for a couple of reasons. Duplication of data and the handling of the index update (creation of records, are there multiple people to enter in the index, how to handle changes etc).

Look forward to hearing your ideas on repeating global fields.

[comment]

Alas, i meant enter all data first, then create an index (by duplicating the data).

What would happen if you created the index anew every time it is needed? IOW, how large do you expect this to be, and how many people will be using it at the same time? (As you can see, I am struggling to find a compromise within the realm of your current abilities.)

[Medusa]

Unfortunately the data entry will be continuous.

Seems like the only option is to develop source independant solutions (seperate tables for each source) and then analyse/search each seperately. NOT ideal.

Thanks for your help anyway.

[Medusa]

10's of thousands of records but single user only.

[comment]

No, that doesn't seem like a good approach at all. I did understand the data entry is an ongoing process. I am trying to see if it would be practical to generate the index "on demand" - but you haven't answered my questions.

EDIT:

Haven't seen your latest - let me think about this.

Edited May 8, 2009 by Guest

[comment]

I am afraid I don't have a really good solution for you. I think it boils down to two options:

1. Use repeating fields for the data entry, and generate the index as needed (see the attached for an idea how this might work). With tens of thousands records it might take a while to re-generate the index - but it's still worth considering.

2. Define many relationships between Events and Participants, one for each possible role (this will also require a "constant" calculation field in Events for each possible role). It's rather tedious method, but I don't see any other way you could enter/view data in a grid-like layout, and avoid data duplication at the same time.

EventIndex.fp7.zip

[Medusa]

Many thanks for the attachment, whilst not perfect this does look like a workable solution, and since it's only for my own use I can live with a little awkwardness in having to rebuild the index.

I will give your other suggestion of multiple relationships a go and compare.

Again, many many thanks for sticking with me on this one and for your time and effort with the attachment.

[comment]

One more thought: you could reduce the number of times a full rebuild is called for, by having another script that updates the index with data from the current record in Events only, or from records that have been modified after the last full import.