11 Replies Latest reply on Dec 2, 2009 3:42 PM by mrvodka

    Calculations, FM Pro 9 -- HELP!!

    Melinda

      Title

      Calculations, FM Pro 9 -- HELP!!

      Post

      First, I am using Mac OS X, running FM Pro 9, over a FM server... AND, I have learned a great deal from many of you, but still consider myself a novice with FM...

       

      I have the following calculation assigned to a specific field (AM Activity):

       

      If ( AM Activity  ≠  "C" or "E" or "H" or "O" or "P" or "T" or "X" or IsEmpty ( AM Activity ) ; (Service DB::Daily Rate - Service DB::Per day travel included in daily rate) * .5 )

       

      There are two similar calculations assigned to similar fields (PM Activity and LPM Activity)

       

      Basically, the calculations serves to record .5 day of work if any number is entered in the fields.  At present our numbers follow a pattern of 091001, 091002, 091003, etc...  For some reason, if a number contains more than one "1" it is adding .5 for each "1"...  so, 091001 is recording a "1" instead of a ".5" and 091011 records a "1.5" instead of a "1"...  

       

      The ".5" records in another field (Billable Days) with the calculation:

       

      PatternCount ( AM Activity & PM Activity & LPM Activity ;   AM Activity & PM Activity & LPM Activity ≠ "C"or "T" or "O"  or "P" or "H" or "X" or "E" or "F") * .5

       

      Any ideas?  Suggestions?  I am hoping this is a REALLY simple fix -- if I need to clarify anything, please let me know!  

       

      THANK YOU so much for any help!!

       

      Melinda

        • 1. Re: Calculations, FM Pro 9 -- HELP!!
          Sorbsbuster
            

          I may be about to learn something here, but my understanding was that the natural English-language-syntax you've used should be stated more long-hand as:

           

          If ( AM Activity  ≠  "C" or AM Activity  ≠  "E" or AM Activity  ≠  "H"...) etc.

           

          Alan

          • 2. Re: Calculations, FM Pro 9 -- HELP!!
            Melinda
              

            Thanks for your reply Alan -- up until i imported a file today that included the number 091031, the calculations have been working just fine... and I should say that I CAN have duplicates of any other number and the calculation works just fine -- 091033, for example, registered as .5 as it should have.

             

            It seems as though the number "1" is being read as ".5" and added together, but I can't see where I have told it to do that...

             

            Melinda

            • 3. Re: Calculations, FM Pro 9 -- HELP!!
              ninja
                

              Please further clarify:


              Melinda wrote:

               

              I have the following calculation assigned to a specific field (AM Activity):

               

              If ( AM Activity  ≠  "C" or "E" or "H" or "O" or "P" or "T" or "X" or IsEmpty ( AM Activity ) ; (Service DB:: Daily Rate - Service DB:: Per day travel included in daily rate) * .5 )


               

              So the definition of AM Activity references itself in its own calc...twice?  How could it have a "C" or an "E", etc. if you're setting it to a difference between two field values times 0.5?

               

              I think you have the definitions and field titles mismatched in your post...(I hope so)...could you revise and clarify? 

               


              • 4. Re: Calculations, FM Pro 9 -- HELP!!
                philmodjunk
                  

                I believe that "E" or "H" or "O" or "P" or "T" or "X" will always return True as each of these string constants are non-zero.

                 

                Try putting just that expression in a calculation field set to return number and see what happens.

                 

                You need to rewrite your expression in the form:

                 

                table::field ≠  "C" or table::field ≠  "E" or ≠  "H" .... and so forth.

                • 5. Re: Calculations, FM Pro 9 -- HELP!!
                  Melinda
                    

                  Thanks Ninja!

                   

                  The instructor has an option to enter a letter to represent the kind of work that was done that day -- if he/she was not instructing that day.  Days of instruction are referenced by the course number (091031). 

                   

                  If the instructor enters a letter "C" in the AM Activity field, for example, .5 drops in the field "Calibration"... and it does.

                   

                  If the instructor enters a number "091031" in the AM Activity field, .5 should drop in the "Billable Days" field...  and it does as long as there is only one "1" in the course number.

                   

                  I did copy the wrong calculation in my post...  I don't have a calculation for the AM Activity field, so the calclaution in the "Days Billable" field must be where the hiccup is:

                   

                  PatternCount ( AM Activity & PM Activity & LPM Activity ;   AM Activity & PM Activity & LPM Activity ≠ "C"or "T" or "O"  or "P" or "H" or "X" or "E" or "F") * .5

                   

                   

                   

                   

                  • 6. Re: Calculations, FM Pro 9 -- HELP!!
                    philmodjunk
                      

                    Melinda, you've still got problems with your expression.

                     

                    PatternCount ( AM Activity & PM Activity & LPM Activity ;   AM Activity & PM Activity & LPM Activity ≠ "C"or "T" or "O"  or "P" or "H" or "X" or "E" or "F") * .5

                     

                    Will always simplify to:

                     

                    PatternCount ( AM Activity & PM Activity & LPM Activity ; 1 ) * .5

                     

                    Which means the function will count the number of times the digit 1 appears in your three concatentated fields and then multiplies that count by 0.5.

                     

                    AM Activity & PM Activity & LPM Activity ≠ "C"or "T" or "O"  or "P" or "H" or "X" or "E" or "F"

                     

                    will always evalate to 1 because "T" or "O"  or "P" or "H" or "X" or "E" or "F" will always be a true statement and a result of True is the value 1 in Filemaker calculations.

                    • 7. Re: Calculations, FM Pro 9 -- HELP!!
                      Melinda
                        

                      oh boy!  i'm confused.  i understand what you are saying, but i am at a loss about how to fix it...  i need the activity fields to generate a .5 day in the billable days field regardless of the number input in the activity field.  is there a way to write the expression to record a .5 day if any number is found --  the course numbers are always 6-7 digits if that makes a difference.

                       

                      thanks again... 

                      • 8. Re: Calculations, FM Pro 9 -- HELP!!
                        philmodjunk
                          

                        ( Not IsEmpty( Filter ( AM Activity & PM Activity & LPM Activity ; "0123456789" ) ) ) * 0.5

                         

                        Filter strips out everything but the numeric digits

                         

                        Not IsEmpty returns true if there are any such digits. True equates to 1, False equates to 0.

                        • 9. Re: Calculations, FM Pro 9 -- HELP!!
                          Melinda
                            

                          THANK YOU!!  I have been working on this ALL day and just when I thought I'd have it, it didn't translate quite how I needed...  I was solving parts, but struggling with putting it all together.  SO, my final calculation for the Billable Days field ended up like this:

                           

                          ( not IsEmpty( Filter ( AM Activity ; "0123456789" ) ) ) * .5   + ( not IsEmpty( Filter ( PM Activity ; "0123456789" ) ) ) * .5 + ( not IsEmpty( Filter ( LPM Activity ; "0123456789" ) ) ) * .5

                           

                          I have entered every possible combination of data that i could come up with and it seems to be working perfectly!

                           

                          I appreciate your explantion of  "Not is Empty" and "Filter" very much!

                           

                          Again, THANK YOU...  I can move on to the next challenge now!

                          • 10. Re: Calculations, FM Pro 9 -- HELP!!
                            philmodjunk
                              

                            Applying a litte Algebra to your last expression, you could simplify it to:

                             

                             ( ( not IsEmpty( Filter ( AM Activity ; "0123456789" ) ) ) + ( not IsEmpty( Filter ( PM Activity ; "0123456789" ) ) ) + ( not IsEmpty( Filter ( LPM Activity ; "0123456789" ) ) ) ) * .5

                             

                            If you want.

                            • 11. Re: Calculations, FM Pro 9 -- HELP!!
                              mrvodka
                                

                              I believe that this could be shortened up to:

                               

                              .5 * ( not not AM Activity + not not PM Activity + not not LPM Activity )