4 Replies Latest reply on Jan 19, 2009 8:49 AM by ninja

    Calculation problems



      Calculation problems





      I'm new to findmaker and I hope someone can help me with this. I have the tables you can see above (and some more not concerning this question).


      The first one, Prov (=Sample in english) contains information about different samples, like when and where they were taken. The second table, Prov_art, contains the species (Art) found in each sample and the number of individuals of each species(Antal) for each sample respectively. The third table contains a long list of species and information about them, like what family they belong to.


      What I want to do is many things, but I'll start to ask about a few of them, so maybe I can figure out the rest by myself.

       How can I:

      1. Count the number of individuals in one sample that belong to family A or B ?

      2. Most families have a value that I can use to calculate an index. For each sample I want a sum of those values for all the families that have at least two individuals present in that sample. Example: 3 species in the sample belong to family A, two species to family B, and four species to family C. I want to sum the value for A,B and C. ValueA+ValueB+ValueC=sum. So it's independent of the number of species or individuals.

      3. Count the number of families in one sample that have atleast two individuals present in that sample?


      Oh, maybe I should add that I need these things for further calculations. I'm calculating an index for each sample and the numbers I get from the questions above are a part of that calculation. So just the final calculation needs to be saved in a field. For example in that calculation I need to divide the sum from q.2 with the result from q.3. 



      If you don't understand what I mean, please ask. And thanks in advice for reading my long question and maybe answering it. :)

        • 1. Re: Calculation problems

          Hi Zinna,

          Thanks for the post.


          I don't have time for a long answer...but just so you have food for thought for the weekend, it seems that you'll be using the script function




          quite a bit...you could set a variable like "SetField [No_in_Fam_A;Get(Foundcount)]

          and march your way through a series of finds to total up the numbers that you need.


          Not as complete an answer as I would like, but I hope its enough to get the wheels turning.


          • 2. Re: Calculation problems



            Thank you for your post.


            I see the relationships are set up, so you may want to include a portal on your layout.  This allows you to see information from other tables.  For example, in your Layout with fields from "Prov", pull down the View menu and select Layout Mode.


            If you are using FileMaker Pro 10, there will be a number of icons along the top of the screen.  Placing your cursor on these icons will display the name of the tool (after a second or two).  Four icons to the right of the oval tool is the Portal tool.  Click on the portal tool and draw a box on the layout where you'd like to see related information.  When finished, FileMaker will ask you for what table you want to relate to, so select "Prov_art".  Then, you will be prompted for fields from that table.  Select the Family field and whatever other fields you want to display.


            Go into Browse mode, and you will see the related records for each Sample.  At this point, you can perform a Find for Family=A, create a new request and enter Family=B and press find.  This will find all records in the current sample set where it belongs to either Family A and/or Family B.


            You can create a calculation field that counts (or sums) the contents of the portal.  For example, if there are five related entries, the Count () function returns 5.  If the values for those related records are 2, 3, 4, 5, and 6, the Sum () function returns 20.


            This should get you pointed in the right direction.


            If you need clarification for any of the above steps, please let me know.



            FileMaker, Inc. 

            • 3. Re: Calculation problems


              And thanks for your answers. Some other things came inbetween so I haven't had the time to work with this until yesterday. I got help from a friend that is good at programing, but never has used filemaker before. Together we (well, mostly he) came up with the script you can see at the bottom of this message. It calculates what I asked for in question 2 and 3, and does the dividing so I get the results I want. But! there is one thing we didn't solve.

              The "Perform find" function in the beginning is not working as we want it to. I would like this script to be connected to a button on a layout for the "Prov"-table. So that when you're finished with entering data you press the button and get the index calculated for you (in this layout there is a portal where you enter the species). The find-function should find the prov_id that was "active" on the prov-layout where the button is. Hope you can help :)


              I'm working with Filemaker 9.




              • 4. Re: Calculation problems

                Howdy zinna,


                It looks like you haven't defined a formal "FIND" nor have you entered find mode.

                I'm not sure what the "Go to related record" does for you...though I'm sure you have it there for a purpose outside the find that you're trying to do.


                If the GTRR is there as part of your find, replace it and "Perform find" with


                - Enter Find Mode

                - Set Field [Prov:: Prov_ID; $$prov_id]

                - Perform Find


                The concept is to enter find mode, tell it what you're looking for and in which field, then perform the find.  Use all three steps.


                If the GTRR is for something unrelated to the find...leave it in...

                If I missed the whole boat on what you're after...the concept will still get you what you're after...