I have 3 tables

-Big-Project table

-Sub-project table

-Worker table

1. A Big-Project record contain multiple Sub-project records.

2. A Sub-project record contain Worker + hour spent.

The goal is to show the total of hour Worker spend on each Big-Project (Worker can work on multiple Sub-project)

Is there a way to do that?

please help

project.zip

It can be done, but not quite the way you think: from the context of the Workers table, all projects are the same - unless you filter the relationship by one selected project at a time.

The simplest method would be to produce a report from the Sub-projects table, summarized by big-project and by worker.

BTW, your relationship between Big-projects and Sub-projects is not set correctly: it should match on Bigprojectid - and there's no need for a Subprojectid field in Big-projects.

thanks comment

Yes, its simple to get the report out.

However i wanted to display it inside portals.

Is there a way to do it via portals? tell me what i'm thinking wrong. relationship? add tables?

thanks for correcting my bigprojectid mistake.

Yes, it can be done with portals, if you need this. I already hinted at one way: add a global gBigprojectID field to the Workers table, and define a relationship to another occurrence of Sub-projects matching on BigprojectID AND WorkerID. Use this relationship to sum the hours in the Workers table.

The trick here is to keep the gBigprojectID synchronized with the currently viewed project. This is easy to do in version 10 and higher with script-triggers; in earlier versions, you can use a script-triggering plugin, or force the user to navigate by buttons.

The other way (a.k.a the "Ugo method") is a bit more complex. See if this example helps:

http://fmforums.com/forum/showpost.php?post/312845/

thanks for the "Ugo method"

i get it to worked, eventhough i still dont know why.....LOL!

It's rather complicated just to get that grouped result in a portal.

i didnt wanna do it with script trigger, seemed more complicated than ugo method.

thanks comment