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

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).

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?

 

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.

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