Hi all. I have a database that has a structure of Project > Task > Task Funding.

The Task Funding can have multiple records per year for a given task, and a project can have multiple tasks

I need to show a portal on the Project Level that would show a rollup of all the task funding amounts for related tasks by year.

I know how to rollup the funding years within a task (using a calculated key), but do not know how to show a year rollup across tasks for the project.

Can anyone please help? i have included a simple sample file. thank you!

project.zip

What is meant by "rollup"?

I know how to rollup the funding years within a task (using a calculated key)

Perhaps you should elaborate on that, so we know what's expected at the project level.

For the rollup of task funding year at the task level, i created two funding self-join relationships:

Task Funding Table___Task Funding 3 Table

fk_TASK ID===========fk_TASK_ID

year=================year

_____________________________

Task Funding Table___Task Funding 2 Table

calc key=============key__TASK_FUND_ID

_____________________________

where calc key= Case (TASK_FUNDING 3:_key__TASK_FUND_ID = TASK_FUNDING 3::_key__TASK_FUND_ID ; TASK_FUNDING 3::_key__TASK_FUND_ID )

If I display a portal on the task based on Task Funding 2, it will show me the rollup by year

The overall goal is to show on the project level a rollup of ALL the related task funding amounts by year. the example file i included tries to show this in a simple way

I hope that makes sense. thanks!

Surprisingly it does (make sense), although the calc key formula seems to be incorrect.

To do the same thing by year&project, you will need to add the ProjectID to the Task Funding table. Then you can define a similar self-join based on ProjectID AND Year - and the rest is the same.

A much more simple way is to produce a summary report of funding by Project/Year.

A much more simple way is to produce a summary report of funding by Project/Year.

And i couldn't agree more!! Unfortunately i have a requirement to show this on the browse screen.

Thank you for the input, ill give this a shot.

well, i tried this method, but it didnt seem to work.

i may be doing something wrong, as the concept is not entirely clear to me.

The concept is exactly the same as what you have described with the tasks. "it didnt seem to work" is not a useful description.

sorry, actually i had made a small error. it does work! thank you so much.

although there is a catch. the projects are going to be associated after the tasks are set up, by selecting the project from a drop down list on the task layout.

what is the best approach to associating the project id to the task funding for the relationship if this is a case? i dont *think* that i can use a calculation to grab the project id as the foreign key in the funding table, can I?

sorry if i seem naive, i develop sporadically. you've been a great help!

That is a very serious catch, I am afraid. Because you are right, the ProjectID in funding cannot be a calculation. That's the flaw in the method - it requires this redundancy.

If you can make the field a lookup and make sure a relookup in funding is initiated every time a task is assigned to a project, it could still work. Otherwise you should look for alternatives, e.g. a scripted summary, or a rather complex custom function.

Thanks for clarifying, i was afraid of that. I was thinking of having an "update tasks" button on the project layout that would trigger a script to populate the project id in the related funding tables. not ideal, but may work for the immediate needs.

You could try and hide this in the UI. For example, if the selection of the project is done from a portal/list instead of a drop-down, the same script can run the update.

In the same vein, if it's ok to produce the summary view "on demand", such as placing in a tab control panel, you could just run a summarizing script then - without needing the elaborate relationships structure.