Request for Relational Database Help

[Cargoplex]

Hi folks,

I'm fairly new to Filemaker Pro and have been busy creating various databases for my department at work. The database I'm working on right now is basically just a way for myself and the other 3 coworkers in my department to fill out a daily production log for each of the projects we're working on.

You can see the layout for the daily production log with a sample entry here:

Daily Production Log Sample

Each of us can access our table by clicking our name in the top right corner of the layout. We will be creating a new record in our table each day, and each table should be sorted by entry date. The data entry part and configuring the 4 separate tables came out perfect.

Now, my next task is to create a report layout that displays each person's log on one page for any given day. I'm basically planning to just shrink the chart layout and fit four of those onto one page.

My question is... What is the best method for creating this report layout? I want to be able to select this report layout, select a certain date, and then have everyone's production log showup on one page for that given day. I tried messing around with the relational graphs, but I think I got a little lost. The idea of syncing each table to a single date field has also got me baffled.

If anyone could help me out with this, I'd really appreciate it. Thanks!

[Fenton]

The normal advice would be to not have 4 tables, just to put your name in each entry. But that's not really the question.

I don't see the problem in linking to each day with a date field. You would enter a date into a global field, then have 4 portals, 1 for each person's table, the relationship to the date field in the table. The fields would be retargeted to use the relationship (instead of just belonging to their native table). Hopefully you're doing this in v.7.

This layout is going to have to be on someone's table (so theirs would just be the date self-relationship, ie., same table).

But really (I can't resist), this should be all done in one table, with the person's name auto-entered into each record, from either their user name, or better, their account name. If, in the fullness of time, you decide to extend the functionality of this database (like adding up the total hours, relating to a Jobs table, etc.), the separate table for each person is going to be a pain.

But right now you just need to deal with a global date field relationship to the date field in each table, with a portal for each.

[Cargoplex]

Thanks for your advice Fenton, but I'm still not getting the results I need. The global date field made each table contain the same date, but I was not able to perform a find for a specific date and have only records for that date show in each table.

The reason I created 4 tables is because everybody's work is tracked seperately. Each person's daily entries should be completely unrelated to each other, except for the date of the entry. I assume I need this control in order to create a report that can display each person's record for a given day indepentently. Take a look at the layout view of my chart here:

Layout View

Each person's table is basically a duplicate, containing all of those exact fields. That way, there is a P1 Name for each person, a P2 Name for each person, etc.

Hopefully I am making sense. I apologize if my explaination is shuffled.

By the way, I am using FileMaker Pro 7 on a Mac running OS 10.3, but these databases will be used primarily on PCs running Windows2000.

[bruceR]

"The reason I created 4 tables is because everybody's work is tracked seperately. Each person's daily entries should be completely unrelated to each other, except for the date of the entry. I assume I need this control in order to create a report that can display each person's record for a given day indepentently. "

Your assumption is incorrect and this is an important and very basic design issue. This data should be in one table, you can easily report on it any way that is necessary. You have already described a substantial duplication of effort problem - what if you make layout changes? You have to repeat them for every employee. Another enormous problem is the P1-P2 thing. You need to create a proper relational design, or find someone who can do it for you. The project data should be in another table and the P1-Px stuff should be records in this table. This is a classic one to many relationship.