3 Replies Latest reply on Apr 2, 2012 4:01 AM by Embee

    Accounting-structure and portals



      Accounting-structure and portals


      Hi fmp's,

      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.


      - Embee

        • 1. Re: Accounting-structure and portals

          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/

          • 2. Re: Accounting-structure and portals

            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

            - Embee

            • 3. Re: Accounting-structure and portals

              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!

              - Embee