Summary field from related table

[CGproducer]

I've tried searching the forums but am not finding anything that sounds exactly like my situation - I'm not greatly skilled with FM, so if I am missing an obviously better approach to this, please let me know.

I have a multi-table relational db set up to track artists' hours on various tasks as they build assets for the cg film we are working on. I have one table, asset_tasks, that holds every task (modeling, rigging, etc.) for every asset. So a sampling of the records looks like this:

asset - task - actual hours

box - modeling - 10

box - rigging - 12

house - modeling - 5

house - rigging - 3

The asset list and the task list are both populated from their own separate tables. I also have another table to enter artist's hours (called "hours"), something like this:

asset - task - artist - date - hours

box - modeling - jsmith - 10/1/06 - 2

box - rigging - jdoe - 10/2/06 - 4

box - modeling - jsmith - 10/2/06 - 3

house - rigging - jjones - 10/3/06 - 7

What I want to do is have the "Actual Hours" field in the first table (asset_tasks) be auto-populated with a summary from the hours table. This is the clearest way I can express what I'm thinking of:

asset_tasks::hours_actual = SUMMARY of hours::hours WHERE asset=my.asset AND task=my.task

Does this make any sense to anyone? The idea is the coordinator can enter an artist hours on a particular task on a particular asset on a particular date, and it goes into the hours table. These hours are then added into the total actual hours for that asset and task on the asset report, which comes from the other table, asset_tasks.

Any help would be greatly appreciated.

Thanks,

Tommy K.

LAIKA Entertainment

[bruceR]

You want the sum function, which is a calculation. But you're mixing up query language and FileMaker relationship definitions. If you define the related table occurrence as AssetHours then you want sum(AssetHours::Hours)

[CGproducer]

Thanks for the quick response. Sorry, I know that was a strange amalgam of SQL, javascript and Filemaker syntax. That was just the best way I could describe it.

I was originally trying to do something like you describe, but I couldn't figure out how to get a summary of, say, just the hours for the modeling task for the asset box. I don't just need AssetHours, I need AssetTaskHours. And I need to put this information in a field in a different table (asset_tasks). Can I relate both the asset and task fields and then use the sum function as you describe?

The other thing is I was having problems getting a summary of anything from another table, related or not. It would generally result in a ? in the field.

Thanks again for your help,

-Tommy

[CGproducer]

Hey sweet! That worked. Though it only worked if I made the field type Calculated, not with a number field with a calculated value.

Thanks a bunch!

-T