Calculating a result from multiple records

[creativelifemedia]

Hi,

First please accept my apologies if this has been asked before, but I can't seem to find an answer to my specific problem.

Within my database if have a "Jobs" table and a "Tasks" table. When a user inputs a Task, they select a job name, and cost, and the task gets sorted by ID into a portal in the relevant Jobs record.

What I'd like to be able to do, is to calculate the total cost of all tasks completed for a particular Job and display the result in a field on the Jobs layout.

Is this possible? I have attached a erd of my tables.

Hope this all makes sense,

Best wishes and thanks

[bcooney]

Create a calc field in Jobs, TotalCost = sum ( jobs_Task::Cost).

"When a user inputs a Task, they select a job name"

Hope that's not the case. They should be selecting a JobID!

You can avoid the user needing to select a JobID if you create a new task from the Job Form.

On Job Form layout

Button: New Task

Set $jobID; Jobs::pkJobID

Go to Layout Task Form

New Record

Set Tasks::fkJobID; $jobID

[creativelifemedia]

Thanks for the quick reply! The solution was obvious, sorry. I didn't realise that Filemaker would auto calculate records that had the correct ID.

Yes, you're right, they select the Job ID. Actually I'm using the popup select script that you wrote to select the ID :-)

I think I had a reason for not wanting to create new Task records directly from the Job layout, but I can't remember what it was now. I guess I can have both.

Thanks Again!