10 Replies Latest reply on Dec 14, 2009 12:37 PM by Melinda

    Calculations, FM Pro 9 -- More help needed...

    Melinda

      Title

      Calculations, FM Pro 9 -- More help needed...

      Post

      I need some help related to this post: http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&thread.id=32369

       

      As a reminder, I am using Mac OS X, running FM Pro 9, over a FM server...  beginner to intermediate, though when it comes to defining calculations it has become clear that I am still hovering around the beginner level...  

       

      I have a layout that collects information in the following fields: AM Activity, Billable Days, Calibration (there are more, but unecessary, I think, to post here).  When I enter a class number (091012, for example) into the activity field, the following calculation works beautifully:

       

      Billable Days = ( ( not IsEmpty( Filter ( AM Activity ; "0123456789" ) )  * .5

       

      but I run into trouble if our class has multiple parts (each class is specified by a number, if it has multiple parts it gets labeled with a letter; 091012C, for example). The letter is problematic only when it  contains letters we use to code work that is not billable to a client, such as Calibration.  The calibration field works just fine and contains the following calculation:

       

      Days Calibrate = PatternCount ( AM Activity ; "C" ) * .5 

       

      I have tried a number of combinationsof calculations in attempt to filter out the letter (always the 7th character)...  here is one example:

       

      <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;o:DocumentProperties&gt;   &lt;o:Template&gt;Normal.dotm&lt;/o:Template&gt;   &lt;o:Revision&gt;0&lt;/o:Revision&gt;   &lt;o:TotalTime&gt;0&lt;/o:TotalTime&gt;   &lt;o:Pages&gt;1&lt;/o:Pages&gt;   &lt;o:Words&gt;17&lt;/o:Words&gt;   &lt;o:Characters&gt;98&lt;/o:Characters&gt;   &lt;o:Company&gt;Teachers Development Group&lt;/o:Company&gt;   &lt;o:Lines&gt;1&lt;/o:Lines&gt;   &lt;o:Paragraphs&gt;1&lt;/o:Paragraphs&gt;   &lt;o:CharactersWithSpaces&gt;120&lt;/o:CharactersWithSpaces&gt;   &lt;o:Version&gt;12.0&lt;/o:Version&gt; &lt;/o:DocumentProperties&gt; &lt;o:OfficeDocumentSettings&gt;   &lt;o:AllowPNG/&gt; &lt;/o:OfficeDocumentSettings&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:TrackMoves&gt;false&lt;/w:TrackMoves&gt;   &lt;w:TrackFormatting/&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:DrawingGridHorizontalSpacing&gt;18 pt&lt;/w:DrawingGridHorizontalSpacing&gt;   &lt;w:DrawingGridVerticalSpacing&gt;18 pt&lt;/w:DrawingGridVerticalSpacing&gt;   &lt;w:DisplayHorizontalDrawingGridEvery&gt;0&lt;/w:DisplayHorizontalDrawingGridEvery&gt;   &lt;w:DisplayVerticalDrawingGridEvery&gt;0&lt;/w:DisplayVerticalDrawingGridEvery&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:DontGrowAutofit/&gt;    &lt;w:DontAutofitConstrainedTables/&gt;    &lt;w:DontVertAlignInTxbx/&gt;   &lt;/w:Compatibility&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;276&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  /* Font Definitions */ @font-face      {font-family:Cambria;      panose-1:2 4 5 3 5 4 6 3 2 4;      mso-font-charset:0;      mso-generic-font-family:auto;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} @font-face      {font-family:&quot;Lucida Grande&quot;;      mso-font-charset:0;      mso-generic-font-family:auto;      mso-font-pitch:variable;      mso-font-signature:3 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ascii-font-family:Cambria;      mso-ascii-theme-font:minor-latin;      mso-fareast-font-family:Cambria;      mso-fareast-theme-font:minor-latin;      mso-hansi-font-family:Cambria;      mso-hansi-theme-font:minor-latin;      mso-bidi-font-family:&quot;Times New Roman&quot;;      mso-bidi-theme-font:minor-bidi;} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ascii-font-family:Cambria;      mso-ascii-theme-font:minor-latin;      mso-hansi-font-family:Cambria;      mso-hansi-theme-font:minor-latin;} &lt;/style&gt; &lt;![endif]  --><!--  StartFragment  -->

      Billable Days = If(Filter (AM Activity; "middle (AM Activity;7;1) ") ; (not IsEmpty ( Filter ( AM Activity ; "0123456789" ) ) )* .5)

       

      If I enter 091012 it populates the the billable days field with .5; no problem. If I enter 091012C both the billable days and calibration fields are populated with .5; problem...  it should only populate the billable days field. If I change the "not IsEmpty" to "IsEmpty" and enter 091012C it only populates the calibration field, but if I enter 091012, it leaves the billable days field blank... 

       

      I do need to be able to enter a "C" in the Activity field and still have it populate the calibration field, but anytime a number includes a letter, I want it to only read the numbers.  

       

      I appreciate the help!!

      <!--  EndFragment  --> 

       

       

       

        • 1. Re: Calculations, FM Pro 9 -- More help needed...
          philmodjunk
            

          I think you're over complicating things.

           

          will this work for you?

           

          Billable Days = ( ( not IsEmpty( Filter ( AM Activity ; "0123456789" ) and Right( AM Activity; 1 ) ≠ "C" )  * .5

           

          You might also want to consider recording such additional data in a separate field next to your AM Activity field. That approach eliminates a lot of the "Parsing" calculations that you've had to set up. For display/report purposes, you can always define a calculation field that concatentates multiple fields into a single string.

          • 2. Re: Calculations, FM Pro 9 -- More help needed...
            Melinda
              

            PhilModJunk wrote:  

            will this work for you?

             

            Billable Days = ( ( not IsEmpty( Filter ( AM Activity ; "0123456789" ) and Right( AM Activity; 1 ) ≠ "C" )  * .5

            This worked in the same way, still recording a .5 in both the calibration and billable days fields...  in the case that a letter (I'm sticking with "C" as example) follows the number, i need the "C" to be ignored by the function, and only record in the billable days field...   After spending more thought on this, I realized the change maybe needs to be made in the calibration calculation, not the billable days field.  I went back to the original billable days calculation:
            ( ( not IsEmpty( Filter ( AM Activity ; "0123456789" ) ) ) * .5)
            and wrote this one for the calibtration field:
             (Filter(AM Activity;"right;7;1"))  and  IsEmpty(PatternCount ( AM Activity ; "C" ) )* .5
             Now, if I enter 091025 or 091025C, a .5 populates the Billable days field (which is what I want it to do), but if I enter just the letter "C" I get a "0" in both fields -- In this case, I need it to populate the Calibration field with ".5"

            Thanks again... 

            • 3. Re: Calculations, FM Pro 9 -- More help needed...
              philmodjunk
                

              I still think putting the "C" in a separate field will make life easier for you.

               

              My last post had a parenthesis in the wrong place. It should have read:

              ( not IsEmpty( Filter ( AM Activity ; "0123456789" ) ) and Right( AM Activity ; 1 ) ≠ "C" )  * .5

               

              That results in 0 if there is a C is the last character in the field.

               

              for calibration, it can be even simpler:

               

              ( Right( AM Activity ; 1 ) = "C" ) * .5

              • 4. Re: Calculations, FM Pro 9 -- More help needed...
                Melinda
                  

                okay it is coming altogether now...  just one more (hopefully) question on this:

                 

                if this function works:

                 

                Left( AM Activity; 1 ) ≠ "C")*.5 

                 

                why won't this:

                 

                Left( AM Activity& PM Activity & LPM; 1 ) ≠ "C" )*.5 

                 

                The entire calculation is actually this:

                 

                Billable days=( not IsEmpty( Filter ( AM Activity & PM Activity & LPM Activity ; "0123456789" ) ) and Left( AM Activity; 1 ) ≠ "C" )  * .5

                 

                This works just great for the AM Activity, but I need the Left Filter to evaluate the PM activity field and LPM activity field and record .5 in the billable days field each time the calculationis true. 

                 

                Also, if you can recommend a good resource for function defintions, I'd be thrilled to have it!!

                 

                 

                 

                • 5. Re: Calculations, FM Pro 9 -- More help needed...
                  philmodjunk
                    

                  Hmm, you're using the "left" functions where I'm using "right"....

                   

                  Left (text ; 1 ) returns the leftmost (first) character in text. Thus Left ( "Apple"; 1) and Left ("Applesauce"; 1) will both return "A" as the result.

                   

                  In my example, I used "Right" to extract the last character entered.

                   

                  All of these parsing calculations could be eliminated if you restrurture your fields to put the number in a number (or text if you need leading zeroes) field and the text in text fields.

                  • 6. Re: Calculations, FM Pro 9 -- More help needed...
                    Melinda
                      

                    I discovered that I was doing things a bit backwards -- I need my calibration field to read the first character, and if t is a "C" to record .5 in the calibration field -- it is possible that someone might enter c-091025, and that needs to populate the calibration field, whereas 091025C needs to populate the billable days field -- the billable days field works as we need it to, except that I can't figure out how to include the PM and LPM Activity fields in that Left calculation... 

                     

                    I agree that adding a separate field to count for the letters might make things simpler, but I am creating this database in relation to a much bigger, already existing database, and I need to keep some of the fields consistent with what already exists.  

                     

                    I really appreciate all of your help!

                    • 7. Re: Calculations, FM Pro 9 -- More help needed...
                      philmodjunk
                        

                      To keep your fields "consistent", consider a calculation field that assembles the separate data into one field:

                       

                      NumberCodeField & CalibrationFlagField will result in 09876C if you enter 09876 in the number field and "C" in the calibrationFlagField.

                       

                      Now you have a combined field that matches your pre-existing database system, but can use the contents of individual fields in your calculations.

                       

                      If you are importing data from this database with this format, consider calculation fields that separate the combined code into separate fields as this may also simplify your calculations.

                      • 8. Re: Calculations, FM Pro 9 -- More help needed...
                        Melinda
                          

                        Okay, that makes sense!  I think I can put that together... BUT, in the meantime, I need to make the other calculation work because we have 3-4 months worth of data that I need to import from excel worksheets and the current cell entry is a combined number and letter field -- I don't have time to go back and edit the 3-4 months worth of data before importing...  and I really can't ask our instructor go back and edit them either!  :)

                         

                        Any ideas/suggestions about:

                         

                        if this function works:

                         

                        Left( AM Activity; 1 ) ≠ "C")*.5 

                         

                        why won't this:

                         

                        Left( AM Activity& PM Activity & LPM; 1 ) ≠ "C" )*.5 

                         

                        The entire calculation (right now) is actually this:

                         

                        Billable days=( not IsEmpty( Filter ( AM Activity & PM Activity & LPM Activity ; "0123456789" ) ) and Left( AM Activity; 1 ) ≠ "C" )  * .5

                         

                        This works just great for the AM Activity, but I need the Left Filter to evaluate the PM activity field and LPM activity field and record .5 in the billable days field each time the calculationis true.  I can't figure our how to write the PM and LPM into the second part of the calculation so that it will add the AM, PM, and LPM if one or some of them are true...  it seems like it should be fairly simple.

                         

                        M

                         

                        • 9. Re: Calculations, FM Pro 9 -- More help needed...
                          philmodjunk
                            

                          Already answered in previous post:

                           

                          Left (text ; 1 ) returns the leftmost (first) character in text. Thus Left ( "Apple"; 1) and Left ("Applesauce"; 1) will both return "A" as the result.

                           

                          In other words, since left (text; 1 ) only returns the first character, anything you concatentate on to the end (AM Activity& PM Activity & LPM) will be ingnored as this is just gluing additional text on to the end of the text string. If you want this result if any of these three fields start with "C" then you need to check each field separately.

                           

                          Left( AM Activity ; 1 )  ≠ "C" OR Left ( PM Activity ; 1 )  ≠ "C" OR  Left( LPM; 1 ) ≠ "C" )*.5 

                           

                          If all must start with "C", use AND instead of OR. 

                          • 10. Re: Calculations, FM Pro 9 -- More help needed...
                            Melinda
                              

                            it appears to be working!  i ended up having it write it out very, very long hand, but it does seem to be working as needed!  i have tried to put every combination of numbers and letters that might possible be input and it is giving me the numbers it should so THANK YOU for your PATIENCE and GUIDANCE!!

                             

                            Melinda