3 Replies Latest reply on Feb 11, 2009 11:25 AM by Lonny

    Grouping records with same field



      Grouping records with same field


      I have a table listing all of our products. Each record is a product with fields "catalog n" (1,2,3...) and "variation code" (a,b,c...). The table listing all records therefore looks like this:

      1 - a

      1 - b

      2 - a

      2 - b

      2 - c

      3 - a


      I would like to be able to filter the list of products so that only the different catalog numbers are shown (say in a portal), and clicking on each lists the available variations, something like this:


      1 - a,b

      2 - a,b,c

      3 - a


      Sorry for not being too clear, still learning FM...


        • 1. Re: Grouping records with same field

          I would suggest that you would be better off having a separate table for variations. So you have one table for products which contains a record for products 1,2,3... And another table for each variation of each product. With your example data, you would have three product records and six variation records.


          When this is done, you can create a calculation field in the Product table to display the list of variations:


          Substitute ( List ( prod_VAR::variation ) ; ¶ ; ", " )


          This calculation will show all related variations separated by commas.


          In the products portal, you would display two fields - the product number and the variations list. 

          • 2. Re: Grouping records with same field


            Yes, making a new table makes perfect sense. The table I currently have is the "variatons" one, so I need to make the "products" table, guess I will have to populate manually?

             Also, if the user inputs the data into the "product" table, is it possible to make a button/script to "add variation" so that the new record created in the "variation" table has the same catalogue number as the initial product?

            • 3. Re: Grouping records with same field
                 I came to think that probably it is possible to use a variable to copy a filed from one record to a field in a different record and table. Is it correct?