9 Replies Latest reply on Apr 19, 2009 11:25 PM by synergy46

    Chart of accounts

    synergy46

      Title

      Chart of accounts

      Post

      I have an accounting background but have just a little experience with FM.

       

      I am using FM 10 ADV.

       

      I have a Membership database and need to setup a chart of accounts to track member payments of dues and contributions.

       

      So, I have these tables:

       

      Members:

         MemberID.pk

         LastName

          etc

       

      MemberTransactions:  (A Join Table)

         MemberID.fk

         AcctID.fk

       

      Accounts:

          AcctID.PK

          Num  (Account Number)

          Description

          Category  (Contribution, Dues, Assessment etc)

       

      The above works but setting up a layout with Members as it's basis and a portal based on Accounts produces an Accounts listing that reflects each Members history of activity.    This is fine for a Member's history but it seems that I should have a table that has one record for each account NUM?? Yes??

       

      Do you have any ideas on how I can do this?

       

      Thanks

       

       

       

        • 1. Re: Chart of accounts
          davidhead
            

          I played around with a structure for this. I thought I knew what you were after but, on playing with it, I found it to be a bit different to what I had in mind.

           

          So what would you like?

          When you look at a member record, you would like to see a list of all the accounts?

          And for each account, possibly the total amount of transactions?

          And possibly a count of those?

          And maybe to be able to filter the portal by period?

           

          Let me know more about what you are expecting or wanting to see.

          I have a sample file. 

          • 2. Re: Chart of accounts
            synergy46
              

             

            So what would you like?

            When you look at a member record, you would like to see a list of all the accounts?

             

            I was thinking that a DROPDOWN list of existing accounts would be best. And,  b) a 'transactions' table for each member's account transactions.

             

            I haVE THE transactions table working but con't figure out how to get Chart of Accounts (which has accountNUM, Description etc) to show just one account for each record.   For example, if a user selects the Dropdown AccountNUM and selects 100 that account would be accessed from the Chart of Accounts and the appropriate AccountNum would be entered in the MemberTransactions (JOIN) table.

             

            But, I can't get that part to work.  I suspect there is something fundamental that I am missing in my understanding.

             

            Your ideas are appreciated.

             

            • 3. Re: Chart of accounts
              davidhead
                

              I am still a little confused by what you want.

               

              Is this what you want:

               

              You have a Member layout displaying individual member records (form view).

              You want to select an account from a drop-down list.

              When an account is selected, the portal shows a list of all transactions for that member for that account.

               

              Am I right?

              • 4. Re: Chart of accounts
                synergy46
                  

                 

                You have a Member layout displaying individual member records (form view).

                You want to select an account from a drop-down list.

                When an account is selected, the portal shows a list of all transactions for that member for that account.

                 

                Am I right?

                 

                Sort of.  Members is a one to many related to Transactions.  Accounts is a one to many related to Transactions.

                Accounts produces each members transactions perfectly.  A member can have numerous entries for the same account; it does this and it works well.

                 

                But, I want a Chart of Accounts that his each account number

                and it's description entered only once.  Can I send you the file?

                 

                 

                • 5. Re: Chart of accounts
                  davidhead
                    

                  Yes, I understand the basic relationships:

                   

                  ACCOUNT ---< TRANSACTION >--- MEMBER

                   

                  But now you are saying:

                    "I want a Chart of Accounts that his each account number and it's description entered only once" 

                   

                  Up until now you have been talking about members - now you are saying you want a chart of accounts? Do you want this chart of accounts for a member? Obviously, you can very easily get a chart of accounts from the ACCOUNT table.

                   

                  I really can't send the file until I understand what you want. 

                  • 6. Re: Chart of accounts
                    synergy46
                      

                    Yes, you have the structure perfectly understood.

                     

                    I am looking for a Chart of Accounts that holds only ONE Account Number per row because when the Accounts Table it used as a Chart of Accounts, the descriptions can get our of syn with the account numbers.  For example:  User enters account 100 with a description of "Dues".  Later, the user enters account 100 with a description of "Regular Dues".  To my Accounts table they look like different accounts.  If I had a single table with only one account per reccord and each NUM was unique I don't think I would have this problem.  Or, am I kidding myself?

                     

                    Thanks

                    • 7. Re: Chart of accounts
                      TKnTexas
                        

                      Tables you would have are:

                       

                      Members

                      Transactions

                      Chart of accounts

                       

                      You want to make a value list on GL.acct number and use description to display second field.  

                       

                       In transaction records, you will attach the drop down to the GLNum field.  You might use the option to hide/show.  After a time people will know the most often used numbers.  The drop down (if auto) will be a pain.  

                       

                       

                      • 8. Re: Chart of accounts
                        davidhead
                          

                        synergy46 wrote:

                        Yes, you have the structure perfectly understood.

                         

                        I am looking for a Chart of Accounts that holds only ONE Account Number per row because when the Accounts Table it used as a Chart of Accounts, the descriptions can get our of syn with the account numbers.  For example:  User enters account 100 with a description of "Dues".  Later, the user enters account 100 with a description of "Regular Dues".  To my Accounts table they look like different accounts.  If I had a single table with only one account per reccord and each NUM was unique I don't think I would have this problem.  Or, am I kidding myself?


                        Sorry but I am getting more and more confused by your descriptions. Lots of questions...
                        "I am looking for a Chart of Accounts that holds only ONE Account Number per row..."
                        - do you want to see this on screen or on paper? Surely your Accounts table gives you this? Accounts records should have a unique account number. There should never be two records with account number 100.
                        "User enters account 100 with a description of "Dues"."
                        - where does the user do this? on an Accounts layout? are they creating a new accounts record? or a new transaction? 
                        "To my Accounts table they look like different accounts."
                        - so from this I can assume that the records above were not entered in the Accounts table. Why would they look like different accounts? Because they have different descriptions? If so, the user should not be entering descriptions, just using the account number. 
                        "If I had a single table with only one account per reccord and each NUM was unique..."
                        - you should have this already - it is called the Accounts table!
                        Please try to explain so I can understand what you have and what you want. Whenever you describe something, explain the context of that process - which table, which layout, etc. I am very confused but trying to help! 
                         
                         
                         

                         


                        • 9. Re: Chart of accounts
                          synergy46
                            
                          "I am looking for a Chart of Accounts that holds only ONE Account Number per row..."
                          - do you want to see this on screen or on paper? Surely your Accounts table gives you this? Accounts records should have a unique account number. There should never be two records with account number 100.
                           I am looking to see the accountNum on screen.
                          "User enters account 100 with a description of "Dues"."
                          - where does the user do this? on an Accounts layout? are they creating a new accounts record? or a new transaction? 
                          The user does this in a portal with the ability to 'add new records'.
                          "To my Accounts table they look like different accounts."
                          - so from this I can assume that the records above were not entered in the Accounts table. Why would they look like different accounts? Because they have different descriptions? If so, the user should not be entering descriptions, just using the account number. 
                          I am trying to protec the integrity of the program from inadvertent user errors.  The way it is now is that a user can change the Description and then Accounts will show up Account 113 - Dues 113 and Account 113 - 113 Dues and will subsequently show as different accounts in the NUM dropdown.  See this link for an example:  CLICK HERE
                          "If I had a single table with only one account per reccord and each NUM was unique..."
                          - you should have this already - it is called the Accounts table!  
                          Not really.  See the last .jpg in the link above.  The way it is currently setup is that Accounts shows each entry for each member.  Each member can have multiple entries for any one account.   Hence, Accounts shows multiple account entries.
                          Please try to explain so I can understand what you have and what you want. Whenever you describe something, explain the context of that process - which table, which layout, etc. I am very confused but trying to help!
                          Thanks for your efforts.  I hope the link above helps clarify what I am trying to do.  I can send the file if you will tell me where.
                           
                          Thanks again