How do I finess Filemaker Pro?
October 26, 2007 6:56 AM   Subscribe

How do I get Filemaker Pro to look up dates from one table and put them into another?

Ok. I have a database, created in Filemaker Pro 7.
Each record in the database has fields corresponding to names and dates (plus a laundry list of other things). Below, I have produced a mock-up of what I am trying to do, simplified for ease and (hopefully) clarity (I can't get the tabs or spacing to work here, but I think you can get the idea). For each record, there is a "Name" field, a "Date1" field, a "Date2" field, and a "Date3" field.

Usually, I want to produce a list, which sorts by name, and presents each of the dates for each name.
George 10/10/03 4/4/04 3/3/07
Hank 9/9/04 7/7/07 8/8/07
John 6/6/06 11/11/07 12/12/07

That's easy enough. But I also want to be able to produce a list which sorts by date (all dates), and gives the name associated with each date.
10/10/03 George
4/4/04 George
9/9/04 Hank
6/6/06 John
3/3/07 George
7/7/07 Hank
8/8/07 Hank
11/11/07 John
12/12/07 John

Ideally I'd also like to have a note associated for each date also be displayed. This would mean:
George 10/10/03(a) 4/4/04(g) 3/3/07(x)
Hank 9/9/04(g) 7/7/07(a) 8/8/07(g)
John 6/6/06(x) 11/11/07(x) 12/12/07(a)

and
10/10/03(a) George
4/4/04(g) George
9/9/04(g) Hank
6/6/06(x) John
3/3/07(x) George
7/7/07(a) Hank
8/8/07(g) Hank
11/11/07(x) John
12/12/07(a) John

I suspect it's probably not that tricky, but I just can't get my head around the relational aspect of databases, and I don't currently have a manual, and the Help is frustratingly vague. Not knowing quite what to ask also makes it difficult to search for answers on the web, so I'm turning to the AskMe crowd. Anyone an expert, with a flair for explaining things such that a simpleton can grasp? I did google, and I did search Ask Me, but I don't know the terminology, in order to successfully phrase a search to come up with something useful. Also, I might not know something useful if it bit me in the ass...

Bonus question (or, on second thought, maybe it's the question): Can anyone recommend a book, or training of some kind to help understand the more complicated workings of Filemaker Pro? I've been using Filemaker for years, but I know I'm only scratching the surface of what it can do. FWIW, I'm currently in Japan; access to books or training in English is not impossible, but ... complicated.

Thanks kindly.
posted by segatakai to Computers & Internet (10 answers total) 2 users marked this as a favorite
 
Okay let me see if I understand this:

You have one table with the name field and three date fields. (And maybe some note fields, too?)

You can easily display the information from each record as a list (name, date1, date2, date3) but you want to break things apart and list by each separate date?

Are you moving the data from one table to another (as implied by your question) or are you wanting to just do subsummary displaying?

If what you are wanting to do is move data into other tables and then do reporting, you may want to normalize the tables and separate dates from names, then recombine the data into your reports.

Does your tables have primary key fields in place already?

Let me know some more details on your current structure and I'll see what I come up with.
posted by jazon at 9:02 AM on October 26, 2007


As jazon said, it sounds like you need to normalize your data.

For example, you might want three tables:

Names (1 record per name; Fields used: Name)
Dates (1 record per date; Fields used: Date, keyName)
Notes (1 record per note; Fields used: Note, keyDate)

I've uploaded a small sample file using your data here.

I've made this example very simple and just used your existing Names data as keys, but in real life you'd want to use a separate key field, such as a serial record ID. This value needs to exist in both the Names records and the corresponding Dates records. For the Notes table, the model is the same, and it relates back to the Dates table via a keyNotes field; I used the values in the Date field as the key in this example, but again, in real life you'd want an ID that was unique for a given Name+Date combination.
posted by mosk at 10:58 AM on October 26, 2007


Response by poster: mosk and jazon: thanks for your comments. Ley me try to be a little more specific:
I currently have only one database that I have been working with for a few years already. There are 34 records, which basically represent individuals I have been working with. There are currently 101 fields, representing the individuals names, dates visited, the "kind" of each visit, images of them and their surroundings, their addresses, their working processes, etc.
So far, I have been printing reports that are sorted by name. On page one, an individual's name is at the top, and all of their info follows. The next page is another individual, etc. Depending on the situation, the order of the info, and/or what info is included, varies. This is all done simply enough in layout mode.
Now, I want to display all my visits to all of these individuals in one chronological order. In some cases, I'd also like to include a note about the kind of visit. It is my assumption that I can only do this by building a relationship somehow. If it's possible to do this more simply, without creating another table, I'm all for it. I actually have ten date fields, but now I want all those dates in one 'super-field', but still carrying the relationship to the name of the individual.
mosk, thanks for going to the trouble of creating a file for me. In your example, there is only one date per name, as far as I can tell. I looked at the relationship graph, and I think I understand what you are doing, but it's specifically the problem of having more than one date that is vexing me.
jazon, can you tell me more about what you mean by "normalize the tables and separate dates from names, then recombine the data into your reports"?
Thanks again.
posted by segatakai at 4:31 PM on October 26, 2007


Best answer: Segatakai, I'm putting the kids to bed now, but will write up a more complete explanation later tonight.

Short answer is you want a one to many relationship: one individual to many dates. That's what my example shows, but I did not build a portal to allow you to display that info, nor did I buld a subsummary report to compile that info, 'cause I assumed you understood how to do that. But the structure is the same, and it's there in the example.
posted by mosk at 7:45 PM on October 26, 2007


Segatakai, mosk is getting you on the right track so I'll step back to let him keep going. While I've used FileMaker for quite awhile, mosk actually works for the company so you'd be getting assistance more directly from "the source".
posted by jazon at 8:33 PM on October 26, 2007


Best answer: Well, I used to work for FileMaker (and, ironically, wrote the some of very same FMP Help topics on relationships that you found so frustratingly vague...small world, huh?) I don't work there anymore, but let's see if I can do a better job of explaining things this time ;-)

First of all, here's a very good (if technical) explanation of what is meant by data normalization. It isn't FileMaker specific, but it does explain the term well.

In your case, you have a single table with a mixed bag of (non-normalized) data. Your records are organized by individual (1 record = 1 person) but each record also contains multiple, complex data sub-sets, like the multiple date sets on which you are trying to report. In order to properly represent this data, it needs to be rearranged and split out into separate records in several other tables to better match the actual complex relationships that exist between these related sets of data.

For example, for a given name (George), you have three dates (10/10/03; 4/4/04; 3/3/07), and each date has a different note associated with it (10/10/03[a]; 4/4/04[g]; 3/3/07[x].

These dates and notes should be their own records in one or two separate tables. They could be separate fields in the same table (but NOT the same table as your Names table) or as separate records in two separate tables; which approach is best depends on if there is a 1:1 relationship between Dates and Notes. If there is, then they can stay in the same child record, but if there isn't they need to be in separate tables.

Unfortunately, in your current data model they are bound together, and all exist on the same level as the record belonging to the individual. Stated another way, you have three records (George, Hank, John), but your reports need to include nine records to describe the data set:

10/10/03 George
4/4/04 George
9/9/04 Hank
6/6/06 John
3/3/07 George
7/7/07 Hank
8/8/07 Hank
11/11/07 John
12/12/07 John

Your current structure cannot support this.

To accomplish what you wish to do, you need to normalize your data by extracting the complex values and moving them into separate, related tables, where they can exist as separate individual records, children of the parent record. Note that your current complex values may currently exist as separate fields, separate values within a single field, or separate values in a repeating field. All of these are equally bad for your purposes and need to be corrected.

So how do you do this, you might ask? Well, it's both easier than you think and harder than you imagine.

First, the easy part: FileMaker relationships are super-simple once you understand the basic thrust of how they work. I know you weren't impressed with the Help topics, but I am going to urge you to give them another chance. Pay particular attention to the topics, "About Planning a Database", "About Relationships", and "About Single Criteria Relationships." Search for thgem by name, print them out, and read them carefully. The last one in particular has an example you can build that will show you how these elements work, but do read all three before you start building, or you will get lost.

Once you wrap your head around how they work, you'll realize that relationships and multiple tables per file are the Greatest. Things. Ever. They are a fantastic tool and really open up a lot of avenues for better organizing and reporting on your data.

Now the hard part: your current data structure is a mess, and it will require some adept data manipulation to break this data out as necessary. There are two reasons for this: first, you won't really know/understand how relationships work until you fight your way through it the first time, a task made much easier with data that is already normalized, and second, you will need to use some techniques you probably haven't used before (though if you are using FileMaker 9, it has the ability to import directly from one table to another within the same file, which will make this process easier).

In overview:

A. Determine the correct data structure to normalize all data you need to report on (this is based on studying the Help topics I mentioned and then sitting down with some note paper and literally drawing it out, identifying tables names, the data fields in each table, and the key field for each table, as described in the "Planning a Database" Help topic.

B. Pick a data type that needs normalization (e.g., Dates), and begin a series of exports as follows only the fields that belong to the data set you are exporting (plus the key field, of course):

Name (or other unique identifier)
Date1
Other fields that belong with the Date1 sub-set
(export all 34 records)
save as NameDate1.fp7

Name
Date 2
Other fields that belong with the Date2 sub-set
(export all 34 records)
save as NameDate2.fp7

Name
Date 3
Other fields that belong with the Date3 sub-set
(export all 34 records)
save as NameDate3.fp7

etc., until you have exported all necessary combinations of Name and Date field.

C. Import these records into your new Dates table, one file at a time, until you have imported them all. If you are using FMP9, you can avoid the step of exporting and instead do this as a series of imports using the current file as the source, and just specifying the fields in the parent table that you want to import into the child table.

D. Repeat for each type of data that needs normalization, such as phone numbers, notes, events, etc. They all will go in separate child tables, and link back to the Names table. I don't know enough about your details to advise you further on this aspect of the solution.

E. Once the data is normalized, you can approach the reporting correctly by creating your reports using layouts based on the most granular data item you need for a given report, and then bringing in other elements that exist higher in the data structure relationally. For example, a report from the Dates table would give you a separate record for each date, while the (related) Name value can be displayed directly from the related parent record, just by dropping the related field on the report layout.

Whew...OK, that's a ton of information, but it is also the correct way to do this. Unfortunately, you are paying the price for some decisions made (or not made) several years ago. The corrections needed to get you your reports need to take place at a fundamental level, which is why this is so hairy. But trust me, this is the correct way to rectify the situation, though you should obviously do this on a copy while you are learning!

Alternatively, you can do the above steps but export to a separate file (exactly as described above, just to a separate file) and do your reporting from there. This doesn't solve the root problem of a poorly organized file structure and non-normalized data -- it's not much of a go-forward solution -- but it does allow you to create normalized data sets for reporting purposes. I don't recommend this, but it will work if all you care about are the reports.

OK...I'm going to stop now. That's a lot of data to absorb. I hope this makes sense and is helpful.
posted by mosk at 3:00 AM on October 27, 2007


er..."you" in first sentence meant OP, not Jazon...
posted by mosk at 3:01 AM on October 27, 2007


Response by poster: mosk: WOW!! Thank you so much for your thoughtful and detailed explanation. I've just printed everything out and will take it to bed with me (it's midnight here). After I've had another look with fresh eyes tomorrow, I'll report back.
Thank you thank you!
posted by segatakai at 7:24 AM on October 27, 2007


(mosk - thanks for fielding this one. And I only made the guess you were still with FM based on the aboutMe at your well.com page; I actually debated writing that guessing you might not be with them any more).

Still, excellent response!
posted by jazon at 12:22 PM on October 29, 2007


Response by poster: Again, thank you both for your help. The hint about the portal turned out to be exactly what I was looking for, and the info about normalization indeed opens new worlds of possibility.
Much appreciated.
posted by segatakai at 6:13 AM on November 5, 2007


« Older Open Source Event Management Software?   |   How to shorten an MP3 without clipping the ends? Newer »
This thread is closed to new comments.