Or isn't what you want here anyway, though OR type relationships are possible in Filemaker.
What you need are two separate relationships between your accounts and transactions tables.
In Manage | Database | relationships, make a new table occurrence of Accounts by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.
We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.
Now you can create a relationship between transactions and the original occurrence of Accounts for one of your two fk fields and then create a second relationship between transactions and the new occurrence of accounts in order to link it by the second fk field.
Please note that this does not violate your requirements for 3) above as you do not have a separate occurence for each account. Instead, you have one occurrence for debits and a second occurrence for credits. You can add as many records to accounts as you need, one for each account and it still works.
4) since all the transaction records exist in a single table (just as all account records exist in a single table), this can be done, but it needs yet another relationship and this one is an OR type relationship. It can be done either with a special calculated key in transactions or with the X operator and a portal filter expression.
A calculation field, cFkList, defined in Transactions as: list ( _fk_Transaction_to_ACCOUNTid ; _fk_Transaction_from_ACCOUNTid )
Then this relationship:
Accounts::__pk__AccountID = TransActionsToFrom::cFkList
will match to records with a matchinf value in _fk_Transaction_to_ACCOUNTid OR _fk_Transaction_from_ACCOUNTid
Alternatively, you can use this relationship:
Accounts::anyField X AllTransactions::AnyField
Without a portal filter, a portal using this relationship will list all records from Transactions. This portal filter expression would limit it down to just those with a matching Id in one of the two fk fields:
Accounts::__pk__AccountID = AllTransactions::_fk_Transaction_to_ACCOUNTid OR
Accounts::__pk__AccountID = AllTransactions::_fk_Transaction_from_ACCOUNTid
For more on Table Occurrences, see: Tutorial: What are Table Occurrences?
For some good suggestions on how to better organize your table occurrences in the manage | Database window, see: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/
Phil, you're my new favourite person - thank you so much for your time!
I had gotten as far as the two occurences for accounts, but then I was stuck. Knew it had to be doable, but didn't know how, hence the appeal for suggestions, or, well, it probably should have said corrections.
To you (and all your fellow fm-pros on the forum); it really is very inspiring and encouraging for fm-novices like myself, to get such great support from people who know what they are talking about.
Thanks again - i'll be back with more ignorant questions
And yet another thank you for the link to sixfriedrice. The anchor/buoy methodology explanation was - for me - the "missing piece" to more fully understanding TO's and how to use them, even if I don't end up using a/b.
To anyone else who is trying to understand table occurences: Read the two posts linked to by Phil!