9 Replies Latest reply on Feb 11, 2017 12:01 PM by philmodjunk

    Sum of repeated fields

    hapivo

      Hi there.

      I have 3 repeated fields  Component, price and content in%. I would like calculate the price of the mixture. So I have created another repeated field Calc (=Price*content in mixture) and the field Price (=Sum ( Calc)/100). But it is too complicated. Is there any possibility to specify calculation of the price directly in the field  Price without creating and using repeated field Calc.

       

      ComponentPricecontent in mixture (%)Calc
      barley 1033330
      wheat1533495
      corn2034680
      100%15,05

      many thanks for your help Ivo

        • 1. Re: Sum of repeated fields
          user19752

          There is only 3 repetition, so you can repeat 3 times as

          (Price[1]*content in mixture[1] +

          Price[2]*content in mixture[2] +

          Price[3]*content in mixture[3] ) /100

          • 2. Re: Sum of repeated fields
            siplus

            Doing it correctly is even more complicated, but it will pay back in time.

             

            You should have

             

            - a mixture table (mixture ID, mixture name)

            - an ingredient table (Ingredient ID, name, currentPrice)

            - a recipe table (recipe ID, fk_Mixture, fk_Ingredient, qty ) holding all the ingredients involved in a specific mixture

            • 3. Re: Sum of repeated fields
              hapivo

              Many thanks, but it is  only simple example, the repeated fields have 40 repetition

              • 4. Re: Sum of repeated fields
                hapivo

                Yes I have recipe table and ingredient table with relationship between tables.

                • 5. Re: Sum of repeated fields
                  siplus

                  Then add the following fields in the recipe table:

                   

                  PriceCalc = Recipes_Ingredients::CurrentPrice * qty / 100

                  SumPrice = Total of PriceCalc (summary field)

                   

                  and when sitting on the mixture table display the field mixtures_recipes::SumPrice which will reflect the total price of your mixture.

                  • 6. Re: Sum of repeated fields
                    hapivo

                    This kind of solution I'm using now. But I need to sum not only price field in the mixture but approximately 250 another characteristics of mixture I'm looking for more practically way - to spare 250 repeated fields Calc.

                    • 7. Re: Sum of repeated fields
                      siplus

                      Well then you might need a substructure in a middle, like a whole group of ingredients that you add or not to your mixture.

                       

                      Like pizza margherita: add group PizzaBase, add group TomatoSauce, add element mozzarella.

                       

                      Group TomatoSauce is made of tomatoes, basil, salt, pepper, olive oil.

                      • 8. Re: Sum of repeated fields
                        hapivo

                        Sorry but I don't understand exactly what  do you mean it.

                         

                        siplus wrote:

                        Well then you might need a substructure in a middle, like a whole group of ingredients that you add or not to your mixture.

                         

                        Like pizza margherita: add group PizzaBase, add group TomatoSauce, add element mozzarella.

                         

                        Group TomatoSauce is made of tomatoes, basil, salt, pepper, olive oil.

                        If I need such type of calculation of repeated fields for example in Excel, there is simple calculation in the cell

                        exc.example.tiff

                        is some suitable possibility for calculations field?

                        • 9. Re: Sum of repeated fields
                          philmodjunk

                          There is a sum function in FileMaker. You can look it up in help. But FileMaker is not a spreadsheet application, it's a relational database. It works differently.

                           

                          And you should not be using repeating fields for this.