AnsweredAssumed Answered

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

Question asked by Melinda on Dec 10, 2009
Latest reply on Dec 14, 2009 by 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  --> 

 

 

 

Outcomes