1 Reply Latest reply on May 23, 2011 9:15 AM by philmodjunk

    Self Expanding Categorisation

    IsaacFileMaker

      Title

      Self Expanding Categorisation

      Post

      Hi there,

      I hope someone can spare a little time to help me out on this - thank you very much in advance.

      I would like to have two fields, one called "Category Name", and the other called "Parent Category". This is so that with two fields, I can get the whole hierarchy of categories and sub categories. I guess this is linked to normalisation. This is the backend database portion. On the layout, I would like to get a list view with an Excel Pivot Table sort of format - that the top most category is on the left column, and then it groups the next sub-category column to its right, and then that groups the next sub-category to its right so forth until there are no more sub categories. So I guess I am trying to expand a normalised form.

      I've thought of the concept in SQL and it would be a parent (leftmost) column displaying values of "Category Name" generated from a WHERE criteria selecting those categories with empty "Parent Category" values, and then the next field would be a column generated from another WHERE criteria where "Parent Category" = the column on its left's value, and so on and so forth. Like a recursive column generation to expand out the data I have in the "Category Name" and "Parent Category" table which are linked logically already cause each record has a category name value with a value that reflects its parent category.

      a) Which normal form is this type of normalisation found in?
      b) How can I get my expanded form in list view and in table view?
      c) Is there any way I can use drop down menus when I click on a table view cell just like Access? 
      d) Is there any way I can select the value list I set in the validation of a table view cell to quickly populate a cell?
      e) Is there any way I can turn on autocomplete for table view cells just like Access and Excel where it would offer to complete the value with the existing values I typed in the column already? 

      Thank you very much for you time and help on this, I appreciate it very much!!

      -----------

      Updated Semi Answer (see image for table model):

      In a brand new table, for list view, I would put 4 drop down fields. The first field would be called "Top Category" and its value would be either blank or a lookup from the "Parent Category". The second field would be called "Sub-Category 1". This is where the "secret" of the method is - there is a relationship linking "Parent Category" as FK from this table to the table containing my original PK "Parent Category" and "Category Name" - thus forming a parent-child table relationship, where the brand new table is the child. My "Sub-Category 1" drop down would then have its value list based on the parent table's "Category Name". And because the relationship ensures that only the results from the matching records of "Parent Categories" are churned up, the "Category Names" that come up will be the top most level when I set the FK "Parent Category" to blank.

      Next I would do the same, for "Sub-Category 2", creating a relationship from "Sub-Category 1" to "Parent Category" of the parent table, and the value list for "Sub-Category 2" would be once again the "Category Name", and because of the relationship, it would churn up the relevant sub categories for a selected "Sub-Category 1".

      The problem with this is that it is not recursive and limited to how many "Sub-Category x" fields I create. Is there any way to dynamically generate "Sub-Category x" fields such that there is always only one extra field for me to fill up - and once that is filled, another one would come up?

      I still don't have a solution for table view and actually table view interests me the most. 

      filemaker_forum.png

        • 1. Re: Self Expanding Categorisation
          philmodjunk

          If you are using an older version of FileMaker, I suggest upgrading to FileMaker 11. FileMaker 11 has a filtered portal feature that makes such "cross tab" style reports a bit easier to set up.

          Please keep in mind that FileMaker does "vertical" with much less work than it does "horizontal". If you can work with this layout format:

          Parent Category 1
             Sub Category 1-1
             Sub Category 1-2

          Parent category 2
             Sub category 2 -1
             Sub category 2 -2

          and so forth

          It's much simpler to design the layout.

          With Filtered portals, you can use Portals to set up your columns of data and then use portal filters to control what data is shown in each column. This requires a list view layout as Table View won't work for this approach. It's also limited to the total number of portal based "columns" of data you put on your layout. You can get some added flexibility, however, by using scripts to manipulate how the portals filter data to get different sub sets of your total data in different columns. (This can simulate a "horizontal scroll" of your data to name one example.)

          It's difficult to go into much detail here on how to do this as you only identified what you want as your column headers and haven't indicated what data will go into your "cells" nor what criteria will be used to organize your data into rows on this layout.