Accounting-structure and portals
I hope one of you pro's can help me a little - My newbie ideas seem to keep bumping into things that fm CAN'T do, and I lack the experience to figure out the work-arounds:
I'm creating a finance solution with accounts and transactions and so on. I thought i could make:
(Table1)-"Accounts": "__pk__AccountID", "AccountName"
(Table2)-"Transactions": "__pk__TransactionID", "_fk_Transaction_to_ACCOUNTid", "_fk_Transaction_from_ACCOUNTid", "TransactionAmount(Number)", "TransactionTitle", "TransactionDate"
and then somehow relate the two in the relationship:
"__pk__AccountID" = "_fk_Transaction_to_ACCOUNTid" OR "__pk__AccountID" = "_fk_Transaction_from_ACCOUNTid"
but fm only allows additional restrictive parameters (only "AND") in the edit relationship dialogue.
The functionality I'm looking for is:
(1) The ability to manually enter the transaction data ONLY ONCE (not once negatively in one account and once positively in another) and just choosing the FromAccount and the ToAccount from pop-ups.
(2) To have the TransactionAmount show with the correct prefix (+/-) depending on if whether the account is sending or receiving the amount.
(3) To have the Accounts be non-static. E.g. the user has to be able to add new accounts, so it can't be something with a table-occurence for each account.
(4) To have one portal on the Account-Layout showing both the Transaction-records where the account is the sender AND the records where it is the receiver, sorted by TransactionDate. Similar to what you see when looking at a bank-account-transcript for some period, with both incoming and outgoing amounts mixed together
I'll greatly appreciate any suggestions.