For a given record in Institutions, can their be both Debit and Credit transactions? And you want both in the same portal?
If that's what you want, define a calculation field,cMultiKey, in Transactions as:
List ( _fkDebitID ; _fkCreditID ) // the two foreing keys to your debit and credit institutions.
Select Text as the result type.
Then these relationships can serve to make your portal happen:
Institutions::__pkInstitutionID = Transactions::cMultikey
To get a balance calculation, you'd need a summary field or sum function that totals this calculation field:
If ( Institutions::__pkINstitutionID = _fkDebitID ; Amount ; -1 * Amount ) //assumes debits are postive values. Reverse result terms to make credits positve.
Thanks so much for your reply:
Yes, any given record in INSTITUTIONS is allowed to be a debit or credit. I'm ignorant of MultiKey but am going to read a bit about it. I sounds as if it's similar to the use of the pilcrow symbol (¶) in FMP, is it?
I'm not exactly sure what your schematic is showing but I'm assuming that DebtInstitutions and CreditInstitutions are TOs of Institutions and that there is a relationship between all three and Transactions. Is this the case?
Sorry, left out this part of my post: For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
I sounds as if it's similar to the use of the pilcrow symbol (¶) in FMP, is it?
Take out the word similar. That's EXACTLY what a multikey field is.
I used List ( field1 ; field2 )
Field1 & ¶ & Field2
produces exactly the same result.
Again, thanks. I'm in the process of making this a many-many.
I'm getting something that works... for the most part.
Attached is an image of a 'bench work' portal. Please ignore col: DspCR, DspDB. They will be used for displays later.
KillList is a MultiKey using 'List'
ToFrom is a MultiKey using a pilcrow. As you can see, there seems to be consistency between the two MultiKeys
'marker' is PKinstitutions which is a result of KillList; one of two fields (FKinstCredit, FKinstDebt)
PROBLEM (other that having too much in the checking account):
As you can see, when 'marker' is not the same as the portal's key the math logic reverses itself for the balances.
Is there any formula that is able to compare to the currently displayed portal's id field or is this the wrong tack?
As you can see, there seems to be consistency between the two MultiKeys
As I said before, both calculation methods produce identical results.
I can't really follow the details of what you have set up. What are the relationships that you are using.
Thanks SO much for sticking with me...
Here are two screen shots that will hopefully help. Some of the fields I set up were just for diagnostics...
Does this mean that you have a layout based on TBL_Institution_121107? (See Layout setup | Show Records from)
And your portal is to TBL_Transactions?
Yes, to both of your questions. You can see one of my portal pages on my 4/13 post.
It's interesting; as you can see, the math goes 'bad' when the PK of the transaction is less-than the PK of the institution. I can sense that I'm close; I'm just ignorant as to what formula/calculation to use to accomodate the math 'switch'.
Again, thanks for sticking with me.
Does the multi-key have one or two values in the field in any of the records of transactions?
If there is two values in the multi-key, it matches to two recorrds and when you put:
Your relationship matches to two records, but only the value of the field from the "first" related record is returned to the calculation. If there is no sort order specified in the relationship, the "first" related record will be the first one to have been created in the table.