One portal for two relationships

[DeathRobot]

I have a table called Projects which has a dollar figure attached to it. I need to be able to debit an amount from one project and apply it as a credit to another project. I've created a relationship like so…

 

Projects --< CreditsDebits > -- Projects

 

where the CreditsDebits table has foreign id fields for "id_ProjectsCredit" and "id_ProjectsDebit" (both are the id field from Projects), as well as an "amount "field.

 

I'd like to show credits and debits in a single portal on a Project layout. Is this possible? I know I can have two portals (one for credits and one for debits), but I'd really like to combine these if at all possible.

 

Thanks!

Michael

[comment]

Define a calculation field in the CreditsDebits table (result is Text) =

List ( id_ProjectsCredit ; id_ProjectsDebit )

and use it as the matchfield for a new relationship (using another occurrence of the CreditsDebits table).

[DeathRobot]

Thanks very much, comment. I think I've almost got it, but still must have something wrong. I have three relationships:

 

Projects --< ProjectsCredit >-- Projects

Projects --< ProjectsDebit >-- Projects

Projects --< ProjectsCreditDebit (using your List solution)

 

(ProjectsCredit, ProjectsDebit, and ProjectsCreditDebit are the same table)

 

On the Projects layout I have a portal to ProjectsDebitCredit.

 

In the Projects table, I have a calculation that says:

 

* * * * *

Case (

     id = Projects » ProjectCreditsDebits::id_ProjectsCredit;

 

     Projects » ProjectCreditsDebits::amount & " credit from #" & Projects » ProjectCredits » Projects::jobNumber & " (" & Projects » ProjectCredits » Projects::projectTitle & ")";

 

     Projects » ProjectCreditsDebits::amount & " debit to #" & Projects » ProjectDebits » Projects::jobNumber & " (" & Projects » ProjectDebits » Projects::projectTitle & ")"

)

* * * * *

 

For a test, I created two records in the ProjectsCreditsDebits table; one which moves $500 from Project 1 to Project 2, and one which moves $250 from Project 2 to Project 1. When I'm on Project 1's record, I get two identical entries in the portal of:

 

$500.00 credit from #002 (Project 2)

$500.00 credit from #002 (Project 2)

 

and when I'm on Project 2's record, I get two identical entries in the portal of:

 

$500.00 debit to #001 (Project 1)

$500.00 debit to #001 (Project 1)

 

The $250 transaction doesn't appear on either record. What am I missing here?

 

[comment]

I am not sure what your calculation is supposed to achieve, but I suspect the fields in the portal are not from the portal's TO.

[DeathRobot]

Yes, you're correct. The calc was in the Projects table, not the ProjectsCreditsDebits table. Got it working now. Thank you!!