I'm stuck, is this idea possible in Filemaker?

[jgerton]

For my simplified example, I have a table with 56 fields for each record and another table with 2 fields for each record. The working copy will have other fields related to the volume of work done for each task. And each worker gets a new record each day.

In the first table, The first field is the record identifier, like a worker name. All the rest are the data applicable to the identifier, like all their individual tasks.

In the second table the first field will be the worker name and the second field will be a single task.

Now I want to let the first table have the workers name and all their tasks. Then I want to let the second table to make a record of workers name and one of their tasks. Because you could end up with one worker that did 55 little tasks or 1 all day task. Also the volume of work completed in each task will be summed up each month, regardless of which worker did the work.

Is this even possible?

Later on I will be making a table three that grabs the data in table one, and it will be similar to table two, however different criteria.

Here's what I can visualize, it has Table1:

record1-> field1, field2, field3, field4, field5, field6..and so on.

Now table two has:

record1-> Table1::field1, Table1::field2

record2-> Table1::field1, Table1::field3

record3-> Table1::field1, Table1::field4

record4-> Table1::field1, Table1::field5

record4-> Table1::field1, Table1::field6

...and so on.

[Fitch]

Why would you want tasks in table 1 at all? Seems like you would keep all task info in table 2. Display this info in a portal in table 1. I guess I don't really know what your question is... can you relate workers to tasks? The answer is yes.

PS: DON'T use a person's name to build relationships. Use an auto-entered ID number. Names can change.

[transpower]

Do you have a finite set of repeating tasks? If so, your architecture should consit of three tables: 1) individuals, 2) tasks, 3) join. Each record in the join table will have one individual, one task. Then you can display the tasks as a portal in the individuals table.

[jgerton]

PS: DON'T use a person's name to build relationships. Use an auto-entered ID number. Names can change.

Thanks for the tip. I may have been to general in my example. I don't actually use the names that way. In fact I don't even use the names. I use numbers and sometimes when a temp worker comes along to assist, they get a four digit number instead of a three digit. So I can tell how many times additional workers were required.

[jgerton]

Do you have a finite set of repeating tasks? If so, your architecture should consit of three tables: 1) individuals, 2) tasks, 3) join. Each record in the join table will have one individual, one task. Then you can display the tasks as a portal in the individuals table.

Now, I'm curious, say I use three tables. To begin with I don't have a finite set of tasks. The individuals will be assigned tasks differently each day, and new tasks come along each day. And new individuals come and go often. The date is also a factor here, I will be consolidating the data monthly. Does the date go into the table with the individual, the tasks, the join, or a fourth table?

Also, do the portals export easily if I have to create a CSV file? That will be a step I will do monthly to submit it to another company that requires it in CSV.

Is that more of a layout issue that a database schema issue?

[ESpringer]

The date would go in the join file. Basically, the persons file tracks everything that's stable over time for a person, a task-kind table tracks everything that is specific to a general kind of task (there might be info about where it's always done, how much time it takes each time, or who generally oversees it, whatever), and the join file indicates "person-doing-task" data, including the when, specific hours spent, comments on performance, etc. You don't *have* to have a task-kind record for every kind of task, but the advantage of task-kind records is easy layout-based reports of the following sorts: how many people did work of kind xyz, and what's the total number of hours they spent doing it? If you have a one-off task (change a lightbulb), you can just have an "other" task type, and a field in the join file for "Other Task description"

At any rate, I concur with transpower and Fitch: you should *show* tasks in a layout for the person's overview record -- via a portal -- but specific info about what-was-done-by-whom-when-how should be stored in the join file, what I'd call a "doings" file, as it were. You could usefully tap into this file in many ways, say to see exactly what's being done today. (This couldn't easily be done via the person's file if each task were going on in a separate field.)

You can easily export CSV from the join file... (Or is it that you want your CSV records to be one record per person, yet listing all tasks done by that person? That would be awkward; you couldn't include any *depth* on any given task if you set it up to use one field per task, and you'd have to guess at some maximum number of tasks and then rework your fields if someone did n+1...) What exactly is the nature of the report you do in CSV?

[jgerton]

So far this information has been really helpful. Got me past a mental block. Thanks for all the input so far.

Now I am finding that formatting data input for time is not the most intuitive. Atleast on my first go around here. I expected to use a field for start and finish times and then a time calc field that had the duration. Simple idea, until I discovered the format for time had to have the : separator and the am or pm specified at the end.

Does Filemaker have a way to let users just type 715a instead of 7:15am? Yet still let me use the entry in calculation duration of a task.

[jgerton]

I worked on it a while, took several tries to get it to calculate correctly. But here it is.

TimeCalc.zip