1 2 Previous Next 18 Replies Latest reply on Apr 4, 2017 10:22 AM by BERGSTEN

    Optional Fee Checkbox/Field; problem with calculation re: Total field * .02

    BERGSTEN

      I am going a bit nuts because I know this is a simple function.

       

      Sometimes our clients pay via credit card. As such, we like to implement a fee (2% of the Total).

       

      I set up a check box Invoices::Credit_Charge with a "value list" of "CREDIT CARD". When that check box is clicked, another field, CC_Charge appears and is supposed to calculate 2% of the total, and then add it to the total.

       

      As it stands, I had the calculation set as:

       

      If ( Payment_Status ≠ "CANCELLED" and Credit_Charge = "CREDIT CARD" ; Total * .02 )

       

      I am clearly missing something because I either get a ? in the box (breaking my Total field) or I get 0 (depends on whether or not I add ; 0) to the end of the calculation.

       

      I've tried a (case    ) but I think that's for more than one stipulation.

       

      I was basing this off a calculation that we use for our Taxable check box, which works perfectly, which is:

       

      If ( Payment_Status ≠ "CANCELLED" and Taxable = "Yes" and Subtotal ; (Subtotal + AddlDayCharge - Discount_Total) * .0625 ; 0  )

       

      I had been substituting things in hopes that it would work in this context - but I'm clearly off track somehow.

       

      Thanks in advance.

        • 1. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
          taylorsharpe

          Question mark can mean the calculation is not logical.  But it can also mean that the resulting value does not fit in the box of the field on the layout.  Is it possible that if you made the field box bigger, you would see the results?

          • 2. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
            BERGSTEN

            I had thought that- but the total would have only been $4 and the field was big enough for 3 digits (on the example I was working on)

            • 3. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
              taylorsharpe

              Are the fields number fields?  Have you tried it in Debugger.   If so, display each field separately to see that they make sense, especially the Total field.  See what is in there before doing the calc. 

              • 4. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                BERGSTEN

                Unfortunately we don't have Advanced, so I don't believe I have access to the debugger.

                 

                Yes, they are number fields.

                 

                What has me thrown is at some point, when I first started working on adapting the Tax calculation, it worked. I did something along the way that has since stopped it from giving me the right number. I'm going crazy because from what I understand, this calculation should work.

                • 5. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                  Jason Wood

                  BERGSTEN wrote:

                   

                  I had thought that- but the total would have only been $4 and the field was big enough for 3 digits (on the example I was working on)

                  But did you actually try enlarging the field?

                   

                  Might be splitting hairs here but if it's formatting as currency it's a minimum of 5 characters. If it's not formatted as currency then there could be many more digits after the decimal.

                   

                  Usually, any time you do a calc on a currency field that could result in more than 2 decimal places, you should remember to include a Round(). For example, if your tax field does not round, then even while it may look right while formatting for 2 digits on screen, it's going to throw off summary reports. If there is more than one unrounded currency field, you could end up with a situation where your invoice total is off by a penny due to rounding!

                  • 6. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                    markpelleymounter

                    Your If statement in the calculation is incorrectly formatted:

                     

                    If ( Payment_Status ≠ "CANCELLED" and Credit_Charge = "CREDIT CARD" ; Total * .02 )

                     

                    There is no definition to tell the calc what to do if the calc calc is false.

                     

                    Try

                     

                    If ( Payment_Status ≠ "CANCELLED" and Credit_Charge = "CREDIT CARD" ; Total * .02 ; 0 )

                     

                    (you had a missing ; followed by the alternative

                     

                    This is now saying...if Payment Staus is not "cancelled" AND Credit Card Charge is "Credit card" get the total and times is by 0.02, otherwise 0

                    • 7. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                      BERGSTEN

                      Thanks Jason, I did enlarge it to be safe- didn't make a difference. Something's weird because if I click the check box, I get a ? in the field where the fee should be calculated and a ? in my Total field as well.

                       

                      Hey Mark, Thanks. I had that originally but when I clicked the box it would only give me 0. I've adjusted the calculation again to match yours exactly and I'm getting a ? (in the calculation field as well as the total field).

                       

                      This is mind boggling.

                      • 8. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                        Jason Wood

                        BERGSTEN wrote:

                         

                        Something's weird because if I click the check box, I get a ? in the field where the fee should be calculated and a ? in my Total field as well.

                        I understood that the "Total" field was BEFORE credit card surcharge. You noted that you include it in the calculation for the credit card surcharge. It sounds like the Total field is also referencing the surcharge field. This is circular and won't work.

                         

                        You need a total before and after the surcharge OR if you just want one "Grand Total" field, then the surcharge field needs to incorporate the same math that gives you the pre-surcharge total.

                        • 9. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                          philmodjunk

                          If ( Payment_Status ≠ "CANCELLED" and Credit_Charge = "CREDIT CARD" ; Total * .02  ; Total )

                           

                          Is what would make sense to me, but doesn't explain the ?. If you click into that field and the ? is still a question mark, you are getting an evaluation error. If you see a value when you click into the field, it's just not sized or formatted correctly to fit the current field size.

                           

                          Assuming an evaluation error, break down the above calculation into it's component parts and look at each value separately to see if you can spot anything not right. This is easy to do with advanced by setting up watch expressions in the data viewer, but you can also define a set of calculation fields and place them on your invoice layout to see what is what.

                           

                          I'd set up the following calculations in calculation fields:

                           

                          Payment_Status ≠ "CANCELLED"

                          Credit_Charge = "CREDIT CARD"

                           

                          I'd also put total on the layout if it isn't already there as well as making another copy of the Credit_Charge field and removing the value list formatting from this copy so that it is an edit box. This will allow you to inspect the actual data in the field, not just see if "Credit Card" is a row of data in the field--which is all that a selected check box tells you.

                          • 10. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                            BERGSTEN

                            Jason-

                             

                            I need to add the 2% surcharge to the grand total. The total field = FieldX + Field Y + Field Z to come up with the grand total  with the Surcharge field being one of them.

                             

                            The way our database is set up- there's a subtotal field for all of the equipment listed on the invoice. But some clients require delivery, additional labor, shipping costs etc- so we have a "labor link" edit box where those random/various charges can be manually entered (and automatically added to the Total field). If I simply ask the Credit Charge calculation to work with the subtotal field, I will not get 2% of any additional services rendered- which is why I put the total field in the credit charge calculation.

                             

                            In short, I thought that if I had a calculation that found 2% of the total, I would be able to add that field to the total calculation. It worked for me briefly so I know it can be done- but something has broken in my effort to clean it up.

                             

                            Phil- I did click the ? in both the Credit_Charge calculation field and the total field and neither of them showed me the proper value.

                             

                            Let's just say I didn't want to make the calculation dependent on whether the order is cancelled, or if the check box is clicked. In the event I just wanted to get 2% of the total field, Total * .02 would be all I need right? Because I've even tried that and it doesn't work :/

                            • 11. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                              Jason Wood

                              If you have 2 calculation field that reference each other, just think about what you're asking FileMaker to do. When one field updates, the other has to update, which renders the original incorrect and now it has to update, and repeat until infinity. Since you can't wait for infinity (i.e.: system crash), you get a "?"! ;-)

                               

                              If your Total field is =subtotal+whatever+tax+somethingelse+creditcardsurcharge

                               

                              Then the creditcardsurcharge field has to be = If ( [logic to determine if surcharge applies] ; ( subtotal+whatever+tax+somethingelse ) *.02 ; 0 )

                              • 12. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                                BERGSTEN

                                Just to be clear the Total field is:

                                 

                                If (Payment_Status = "CANCELLED" ; Subtotal - discount_total + AddlDayCharge + Tax + LaborCharge + TransportTOTAL + Hotel_Charges)

                                I had added  + CC_Charge

                                 

                                But those are all the fields I need 2% of

                                 

                                **I did adjust my calculation just now to what you suggested Jason- and it gave me a flat 0. no ? but no calculation either..

                                • 13. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                                  philmodjunk

                                  Then you have an infinite recursive calculation loop and that will produce the evaluation error.

                                   

                                  You are referring to a field in your If calculation that produces a value that is then used in the total field's calculation.

                                   

                                  You need to treat "total" as a sub total so that you can compute a grand total as the sum of

                                   

                                  FieldX + Field Y + Field Z + surchargefield

                                   

                                  Where total is still FieldX + Field Y + Field Z, surchargefield is the above If calculation and a grand total field uses the above expression.

                                  • 14. Re: Optional Fee Checkbox/Field; problem with calculation re: Total field * .02
                                    markpelleymounter

                                    If you

                                     

                                    Then I would have the calculation field (call it cc_charges) be

                                     

                                    If (Credit_Charge = "CREDIT CARD" ; (SumTOV * .02); "0" )

                                     

                                    Then your "Grand Total" would be SumTOV + cc_charges

                                    1 2 Previous Next