Relationship help please! - Rollup of years

[deego55]

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

[comment]

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.

[deego55]

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!

[comment]

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.

[deego55]

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.

[deego55]

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.

[comment]

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

[deego55]

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!

[comment]

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.

[deego55]

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.

[comment]

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.