Simple but confusing relationship issue in portal

[arnie03]

I have what seems to be a simple relationship problem but am completely stumped by what to do. I have a database which include the tables 'jobs' and 'teams'

Relevant fields:

Jobs

Team 1

Team 2

Team 3

Team 4

All teams (calculated join of all fields above)

Hours

Teams

Team name

Total hours (summary of Jobs:Hours)

The relationship is All Teams = Team name

This was all good before I needed to include the hours (this is the expected hours that the team will work on the job in the next week).

This returns a total in the summary as it should. The only issue is when I need to have teams work on jobs for different amounts each week. I can have fields in the Job table Hours 1-4 that would match the team, but how do I actually have that data correctly display in the portal?

I can't think of a way of doing it without creating a portal for each team (there are 12 teams, so each team has a record in the portal) or a specific pair of fields in the Jobs table for each team. Neither of these solutions are ideal as they require coding for each team change (and they change quite a bit) rather than just editing a value list.

Any help, suggestions or workaround appreciated.

[arnie03]

Okay, I've solved this. For reference should anyone have the same or similar issue:

I created a calculation field in the Jobs table called 'This team hours portal'

Calculation: If ( Teams::Team name = Team 1 ; Hours Team 1 ; If ( Teams::Team name = Team 2 ; Hours Team 2 ; If ( Team List::Team = Team 3 ; Hours Team 3 ; If ( Teams::Team name = Team 4 ; Hours Team 4 ; "0"))) )

The summary field in the portal now totals this calculation field.

I knew it would be simple...eventually!