6 Replies Latest reply on Aug 18, 2016 9:28 AM by realgrouchy

    Nesting categories?

    K.Feete

      Title

      Nesting categories?

      Post

           So for the past year I've been gradually converting our Quickbooks, Bento, and random-Excel-spreadsheet data into a nice central Filemaker database, and it's been going well. But I'm up to moving over the books, and I just cannot get my head around how to replicate the categories.

           The way Quickbooks categories work, if people don't know, is you have a category -- say, "Repair and Maintenance". R&M has subcategories "Buildings" and "Machinery", and then you might have subcategories "Housing" and "Farm" under Building, and so on. Each transaction in your bank ledger has a category or categories assigned to it, and when you pull up a category report you'll see the breakdown as well as a grand total -- you can see how much you spent repairing your housing and the grand total for R&M on one easy page. I need to maintain this basic functionality so I can compare this year's financials against past years (and also because the owner is not a computers person, so the fewer new tricks I have to teach him, the better.)

           I know this can be done with Filemaker. I vaguely feel it can be done easily. But for whatever reason I am having a real block with seeing how to build tables that will nest in this fashion, and doing it in a relatively clean, easy to maintain structure.

           If anyone has some suggestions that would get me past this mental hump, I'd be most grateful.

        • 1. Re: Nesting categories?
          philmodjunk

               Mulling this over as a Type...

               If you used two Tutorial: What are Table Occurrences? of the Categories table, you could set up these relationships:

               Categories-----<SubCategories

               Categories::__pkCategoryID = SubCategories::_fkParentCategoryID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               In theory, that would enable you to set up unlimited "trees" of categories and sub-categories.

               Question:

               Using your example categories and sub-catetories, should it be possible for the user to assign a transaction to a "root" category such as Repair and Maintenance" or should they be limited to only selecting a "leaf" category such as "Housing" or "Farm"?

          • 2. Re: Nesting categories?
            K.Feete

                 I'd prefer to allow people to assign transactions to the "Root" categories -- almost everything is sub-categorized now, but the root is a handy way of indicating "I didn't actually know what this was for, someone should look at the entry and properly categorize it." So preferable, but not necessary if it's making life difficult.

                 I am following you so far with the table occurrences (and argh, yeah, should have figured that out -- TOs are a huge blind spot for me) but I'm trying to figure out how to use these to neatly order themselves into category and sub-category. I suspect summaries (another of my blind spots) are going to be involved, but again, any nudges in the right direction would be much appreciated....

            • 3. Re: Nesting categories?
              philmodjunk

                   Yes, I am thinking in terms of a summary report, but you are right about the sorting issue. I think that selecting a "root" value will greatly complicate this issue when it comes down to computing a sub total for that same "root" category.

                   Frankly, I'm still thinking this one through. Structuring the Categories via a self join was the easy part--especially as I discussed several versions of that in another thread recently.

                   I'm putting this thread in favorites so that I can find it again. If a bright idea pops into my skull, I can then come back here and share it...

              • 4. Re: Nesting categories?
                davidanders

                Tutorial: What are Table Occurrences?
                Tutorial: What are Table Occurrences?
                      

                Tables, table occurrences, and relationships
                http://www.soliantconsulting.com/blog/2009/11/tables_table_occurrences_relationships

                      

                • 5. Re: Nesting categories?
                  philmodjunk

                       Before I try something fancy, let's look at a simple approach first that may get close to what you need.

                       If you have a field for selecting the Category in your transactions table, you can set up a summary report with either of the following two formats:

                       #1

                       Category A          Category A total
                          Transaction 1 for category A
                          Transaction 2 for category A
                           and so forth...

                       Category B           Category B total
                          Transaction 1 for category B
                          Transaction 2 for category B
                          and so forth...

                       And so forth....

                       #2

                       Category A          Category A total
                       Category B           Category B total
                       And so forth

                       Either or both are possible without doing anything terribly elaborate. where this becomes problematic is that it treats all category values the same.

                       A transaction selected for "repair and maintenance" is treated just like a transaction selected for "Farm" and you won't see a total that combines the transactions for Farm with the transactions for Repair and Maintenance.

                       But if we limit the categories that we can choose for a transaction to "leaf" categories, this becomes pretty close to what I think you want, the trick would be to sort the records into an order that groups categories under a sub heading that identifies the "parent" category for that set of transactions and then we can get a total for that parent category such as a total for Buildings and a total for Repair and Maintenance.

                       Such would seem possible up to a certain maximum number of levels of Categories and sub categories....

                  • 6. Re: Nesting categories?
                    realgrouchy

                    I had a similar problem, and this is the only thread that seems to come close to addressing it.

                     

                    I've created a testcase database (attached) based on PhilModJunk's advice which also does some sorting and indenting in lists. I'm not sure if I used the best methods of achieving this result, but it seems to work for me; hopefully it's useful for anyone else who comes across this.

                     

                    - RG>