5 Replies Latest reply on Jul 17, 2014 12:10 PM by philmodjunk

    Conditional calculations

    AlexandrosKaralakis

      Title

      Conditional calculations

      Post

           Hi,

           I am doing a research in economics. I have a table with many millions of records. The table goes like this:

           Fields: Animals, Plants, Letters, Numbers

           Records:

           tiger tomato A 1

           tiger tomato A 1

           lion banana B 2

           lion banana B 2

           1. I want to create another table that will conditional sum or average or whatever from my first table. E.g. sum all numbers if Animal is "tiger", plant is "tomato" and letter is "A". Is this done with calculation field or with a script?

           The second table will look like this: 

           Fields: Animals, Plants, Letters, Sum

           Records: 

           tiger tomato A 2

           lion banana B 4

           2. In order to populate some fields of my second table i am using a script that copies values from another table and creates records by pasting them. It works but it is slow because of the number of records. Is there any faster way than a script with functions like "loop", "go to Layout", "Go to Record", "copy", "Go to another layout", "paste", "end loop"

        • 1. Re: Conditional calculations
          philmodjunk

               No need for a second table. A summary report can produce these values right in your existing table.

               Define a summary field to compute the total of Numbers.

               Create a new layout to be viewed in List View.

               Delete the Body layout part.

               Replace the body layout part with a sub summary layout part "when sorted by Letter".

               Put the Animals, Plants and Letters fields in this layout part. Add the new summary field to this part in place of Numbers.

               Sort your records by Animals, Plants, Letters and you'll get one row for each unique combination of values in these three fields with a sub total shown in the Numbers column.

          • 2. Re: Conditional calculations
            AlexandrosKaralakis

                 1. The calculations should be multiple field conditional. I dont need the sum of all values

                 2. I need the second table in order to use it with Matlab software. The second table has to have a specific structure. In the first four fields of the table are the constants that the rest of the fields should use in order to find the specific values from the first table and do the necessary calculations (sum or average, etc.).

            • 3. Re: Conditional calculations
              philmodjunk

                   1) this will not show the sum of all values. It will show the sub total for each unique combination of values in the other three fields.

                   2) This data can be exported in the one row to  each group format shown in the report if you use the "group by" option when exporting this data.

              • 4. Re: Conditional calculations
                AlexandrosKaralakis

                     Thanks a lot. I think that this is what I was looking for. Is sorting going to work with 5.200.000 unique combinations of the 4 constant fields?

                • 5. Re: Conditional calculations
                  philmodjunk

                       It should, but sorting that many records won't happen in the blink of an eye.

                       The time needed for any sort algorithm to sort records will be a function of the number of items to sort and it's not a linear relationship. FileMaker's is probably proportional to NLogN where N is the number of items to sort.