11 Replies Latest reply on Feb 2, 2010 3:11 AM by HugoLidia

    Calculation help needed

    HugoLidia

      Title

      Calculation help needed

      Post

      "Claims Open : " & Case ( Status="Open" ; Get ( RecordNumber);0 )& " out of " & Get ( FoundCount) & " Total Claims"

       

      I need to replace Get ( RecordNumber) with a statement that will give the total number of Claim Records with an "open" status and I can't find the right function.

       

      Any ideas?

       

       

        • 1. Re: Calculation help needed
          mrvodka
            

          You can have another calc that uses a formula of: Status="Open"

           

          Then you can use a self join and use a Count () or Sum () of this field.

           

          Or you can create an unstored calc field that returns the string "Open" and create a self join that keys from that calc field to the Status field and then use Count (), Sum (), or even a summary field to count. 

          • 2. Re: Calculation help needed
            HugoLidia
               Thanks Mr_Vodka, but if I can't get determine what is the correct calculation to use in my existing calculation, how does it help to create another field that requires a calculation with "status=Open". If it won't work in one it is not going to work in another calc field. Guess I must be missing something here, so feel free to correct me.
            • 3. Re: Calculation help needed
              philmodjunk
                

              if the value of the Status field is "Open", then Status = "Open" returns 1. If it is not, it evaluates to 0.

               

              A summary field, count or sum function that adds up the contents of this field will then count the number of open records.

              • 4. Re: Calculation help needed
                deltatango
                  

                Your formula won't work because the way it is set right now, it is only performing itself based on the current record.

                 

                You need to make a relationship so that you can count the number of records in the related table. 

                • 5. Re: Calculation help needed
                  HugoLidia
                    

                  Maybe I haven't explained the problem sufficiently.

                   

                  I have a calculation field on a table which I want to use to calculate the number of records with Status="Open".  These records are currently being viewed in a portal which all works fine, except for this missing value.

                   

                  Using Count() always returns 1. 

                   

                  "Total Nº of Claims : " & Get ( TotalRecordCount ) // This works fine to get the total number in the table. 

                  Can I not simply replace the Get(TotalRecordCOunt) with a function that allows me to specify a condition that Status="Open"?

                  • 6. Re: Calculation help needed
                    deltatango
                      

                    Yes you can but that means that either your calculation is wrong or your relationship is wrong.

                     

                    First you have to create a relationship, which in this case sounds like if would be a cartesian (the x symbol) which will connect any record from the main table to the related table.

                     

                    What the other person was saying is that in the related table, you need to make a calculation (we'll call it StatusOpen) that goes like this:

                     

                    If(Status = "open";1)

                     

                    THEN, in the main table where you want the count you need to create a calculation that is:

                     

                    Count(RelatedTableName::StatusOpen) 

                     

                     

                    This will count how many fields have 1 in the related table. 

                    • 7. Re: Calculation help needed
                      deltatango
                        

                      HugoLidia, study this file, it explains my previous post.

                       

                      http://saturnprods.com/fm/statOpen.fp7 

                      • 8. Re: Calculation help needed
                        HugoLidia
                          

                        Thanks deltatango finally managed to get a look at this and your suggestion works a treat.

                         

                        The one thing I am finding difficult to adjust to with Filemaker is that to do a calculation one nees to create extra fileds all over the place.  In other products, these do not need to be stored or calculated in the table but can be done on one's form and makes the whole issue easier to get one's head around.

                         

                        Anyway, another minor issue resolved - thanks.

                        • 9. Re: Calculation help needed
                          deltatango
                            

                          Welcome to the wonderful world of Filemaker PRO.

                           

                          :P 

                          • 10. Re: Calculation help needed
                            philmodjunk
                               In everything there are trade-offs. The one advantage I've encountered in filemaker's table based calculation fields is that I can use the same field on multiple layouts. If I later determine that I need to change the calculation, I change it once in the table definition and don't have to track down multiple layouts or SQL queries and make identical changes to each like I used to do with MS Access...
                            • 11. Re: Calculation help needed
                              HugoLidia
                                 Buts that's MS Access.  In full blown programming languages you can have calculations stored globally so you only need to change in one place - simpler, and better structured