5 Replies Latest reply on Jul 26, 2013 7:39 AM by heath

    Calculation field to filter results...

    heath

      Title

      Calculation field to filter results...

      Post

           Hello-

           I need to make a calculation field that will filter a count of records.  So if I have 2000 records of students and I want a count of only the records with the number 7 in the age field, how do I go about this without using a portal. 

           I have tried setting up a calculation field called KidCount wiht the following calc.:    IF ( Age=7 ; Count (ChildNames))

           I get no answer....

           Any help would be appreciated...

            

        • 1. Re: Calculation field to filter results...
          philmodjunk
               

                    how do I go about this without using a portal.

               It would help to know why a portal cannot be used for this.

               This appears to be a classic "selective sum" or "sum If" question. There are a number of possible solutions, some use portals some don't.

          Sum_Calculation based on condition

               An option not mentioned in that thread is to use ExecuteSQL to selectively count your records.

          • 2. Re: Calculation field to filter results...
            heath

                 The problem with the protal is that once I have obtained the number of kids that are say age 7, I need to then use that number in another calculation field.  From what I have been reading and have tried to no avail is that results from one line portals as such can not be used, they are display only.  As for SQL, well, that's a whole other ball of wax...  I would prefer to keep it in a field that can be used for furhter calculations...

                 Does this make sense?

                  

            • 3. Re: Calculation field to filter results...
              GuyStevens

                   This can actually be done very simply using some relationships.

                   You could do this on an age that you enter into a field or an age range.

                   The count of the students can be used without using a portal.

                   I made a little example but I did add the portal so you can see the actual records. But those portals aren't neccesary.

                   Just make sure you get the count out of the correct table occurrence.

                   For the count I created a summary field that counts the ID field. This ID field is never empty so it gives you a correct count.

              https://dl.dropboxusercontent.com/u/18099008/Demo_Files_FMP12/Count_Age.fmp12

              • 4. Re: Calculation field to filter results...
                philmodjunk

                     It's not that you can't use a portal, it's that you can't use a filtered portal to get a value that is accessible.

                     In any case, as DaSaint and I have both suggested and demo'd via different links, the trick is to set up the relationship so that it automatically filters to match only to the records you want to count.

                • 5. Re: Calculation field to filter results...
                  heath

                       Hi Guys -

                       Sorry, I was out of the office.  Thanks to both of you for your help. 

                       DaSaint - thank you very much for taking to make an example DB for me to see -- AWESOME!!!