1 Reply Latest reply on Jul 20, 2014 10:11 PM by nihmbrisby






           I have 3 fields, CO-PAY, AMOUNT BILLED AND NET EXPENSE

           I need to have the net expense equal the amount billed minus the co-pay and have done that,  but in some instances the amount billed is zero, so I do not want the net expense to become a negative number.  Any help would be appreciated.  Calculations are a nightmare for me.



        • 1. Re: Calculations

               I think you'll want to use a 'logical function' in your calculation.  You can read a detailed introduction to these functions in filemaker on page 215 of the functions reference guide (https://fmhelp.filemaker.com/docs/13/en/fmp13_functions_ref.pdf)

               Long story short- logical functions allow filemaker to decide when to do something.  In your case, you want filemaker to decide whether or not the Net Expense is equal to Amount Billed - Co-pay.  We can use the "If" or the "Case" function to accomplish this.  I prefer case so I will base my example on it.  

               If you look up the case function in the filemaker calculation window, you will see this: Case (test1 ; result1 {;test2; result2 ; ... ; defaultResult}).  First of all, note that parameters enclosed in curly brackets '{ }' are optional. So let's look at the mandatory parameters first:

               Case (test1 ; result1)

               It's quite simple.  Filemaker will evaluate 'test1.'  If test1 is true, the case function returns 'result1'.  If it is false, filemaker returns an empty result (ie nothing).  For example, Case (1+1=2 ; "Red") returns the characters: "Red."  The optional parameters allow you to add more tests and include a default result if no tests are true.  The most important thing to remember is that once Case() evaluates a true argument, it is *done*.  It returns the corresponding result and does not evaluate any other tests (if any remain).  I've pasted 2 more examples from the manual below, but first let me address your situation.  Consider setting Net Expense to equal the following calculation:

               Case ( Amount Billed - Co-pay > 0 ; Amount Billed - Co-pay)

               In this scenario, Net expense will never equal a negative number.  That being said, if co-pay was a flat fee and not a percentage, then you may run into scenarios where net expense is not evaluated despite the presence of a non-zero 'Amount billed.'  But we could easily build on the case to take that possibility into account.

               Here are the two examples from the manual:

          Case(Score >= 90;“Excellent”;Score > 50;“Satisfactory”;“Needs Improvement”) displays Excellent when the score is 90 or above, Satisfactory when the score is between 50 and 90, and Needs Improvement for any other score.

          Case(Shipment Method=”Ground”;2;Shipment Method=”Air”;10) returns 2 when the Shipment Method field contains Ground, and returns 10 when the Shipment Method field contains Air.