3 Replies Latest reply on Feb 22, 2009 8:45 PM by sapphire_1

    count ID of repeating field???

    sapphire_1

      Title

      count ID of repeating field???

      Post

      Hello,

      I am very new to file maker and currently using file maker pro 9 advanced.

      I have a repeating field called 'Types of food' which has 3 repetition.

      I need to summarize the types of food for example:

      Types of food   Product1     Product2     Product3

      Record 1          milk              egg           bread

      Record 2          cocoa         coffee          egg

      Record 3          cocoa           milk            milk

      into something like this:

      milk         3

      egg         2

      bread      1

      cocoa      2

      coffee     1

      I've tried using subsummary but what I get is:

      milk            1

      egg

      bread

      cocoa        1

      coffee

      egg

      cocoa        1

      milk

      milk

      I also tried using GetRepetition() function, but what I get is summary for repetition1 in a report, summary for repetition 2 in another report and summary for repetition 3 in another report.

      Can anybody help me?

      Thank u.

       

       

        • 1. Re: count ID of repeating field???
          raybaudi
            

          Hi sapphire

           

          now that you are at the very start of your DB, do a gift to you by NOT using repeating fields in situation like this.

           

          You need to have 3 tables ( minimum ) : Orders, OrderLineItems and Products.

           

          In the Orders table you'll have, amoung others, these fields:
          IdOrder
          Date

           

          In the OrderLineItems table you'll have, amoung others, these fields:
          IdOrder
          IdProduct

           

          In the Product table you'll have, amoung others, these fields:
          IdProduct
          description

           

          In a layout showing Orders fields, put a portal showing fields of OrderLineItems and the field Product::description.


          In the relationship graph put a check in 'allow creation of records' from the side of OrderLineItems.

           

          Now, when you want to add a product to the order, all you need is write the IdProduct into the portal.

           

          So the result will be many records for each product of an order and NOT many repeatition of a repeating field.

          • 2. Re: count ID of repeating field???
            sapphire_1
              

            Thank u 4 ur guidance.

            Your help is deeply appreciated.

            But, just curious, is there really no solution using current repetition field?

            Because I already have thousands of records (each record represent different factory).

            So, if I'm gonna apply what u teach me, do I have to export my DB and import it back?

            Sorry, because I really am new and anything seems quite simple 2 u might look so complicated 2 me.

            At least, I can have better structure for my new DB from now on.

            Thank u again.

             

            • 3. Re: count ID of repeating field???
              sapphire_1
                

              Thank u so much.

              I already manage to rearrange my DB and now, I can do my reports smoothly.

              I will remember for not using the repetition field to store data again.