1 2 Previous Next 17 Replies Latest reply on Apr 25, 2012 10:47 AM by philmodjunk

    Calculations not working since Upgrading to FM Pro 2012

    SteveStaves

      Title

      Calculations not working since Upgrading to FM Pro 2012

      Post

      This past weekend, we upgraded my client from Filemaker v6 to 2012 and so far, almost everything works like a charm.  He has an odd issue though on the following 2 formulas:

      Case(Quantity A: = " " ; " "; Quantity A: >= 1;  (Case( Delivery Code? = "b"; ((a. Paper Weight A:  + b. Paper Weight A:) * Delivery Freight per LB Rate:) + Delivery Freight Base Rate:)))

      a. Paper Total Cost A: + a. Press Total Costs A: + a. Ink Total Cost A: + a. Prepress Total Charges: + a. Bindery Total Qty A: + a. Farmed Out Total Quantity A: + Delivery Charge Quantity A: + b. Paper Total Cost A: + b. Press Total Costs A: + b. Ink Total Cost A: + b. Prepress Total Charges: + b. Bindery Total Qty A: + b. Farmed Out Total Quantity A:

      Basically, when the area marked a is filled out and b is not, the total comes up as a ?.  When a and b are filled in, the total is just fine and accurate.  Typically area a will be filled out and b will be left blank so having a ? as the total because b is blank is causing him major headaches.  In the previous version (FM Pro 6) this worked without error.

      Is there something that he needs to do in order to get the totals to disaply even if any one field is blank?  i.e.  A blank field should be a 0 (Zero) so 1 plus o is 0 but here 1 + blank is ?.

        • 1. Re: Calculations not working since Upgrading to FM Pro 2012
          philmodjunk

          What is the name of the field used to define the first calculation? (Want to know if the second calculation refers to it or not.)

          1 + empty field should evaluate as either 1 or blank just like in FileMaker 6 (controlled by the "do not evaluate if all referenced fields are blank option) and earlier. A question mark result means FileMaker cannot display the value. It could be due to invalid data such as dividing by zero or it could be that the field is too narrow to display the value in the field. You can check this by clicking into the field. If it's simply a matter of needing a wider field, clicking into the field will make it pop out to a larger size and you'll see the value stored in the field. If it remains a question mark, there is invalid data in the field.

          • 2. Re: Calculations not working since Upgrading to FM Pro 2012
            SteveStaves

            I did think of that and we tried to both check and uncheck the box labelled "do not evaluate if all referenced fields are blank option".  In the fields a. Paper Total cost and b. Paper Total cost (as an example) the fields are wide enough for $99,999.00.  If we enter in 10 in the first and leave the second blank, we get a ? but if we type in 10 into both then the total is displayed as $20.00.

             Should "do not evaluate if all referenced fields are blank option" be checked or unchecked?

            Sorry - Missed one of your questions:  The 2 formulas are not tied together.  They both have the same issue.

            • 3. Re: Calculations not working since Upgrading to FM Pro 2012
              philmodjunk

              Neither option will return a ?. It depends on the result you want. If you want blank to evaluate as zero, the clear the check box.

              What happens if you get a question mark and click into the field with a question mark? Do you see the value?

              If so, you simply need to resize the field to make it a bit wider here.

              • 4. Re: Calculations not working since Upgrading to FM Pro 2012
                SteveStaves

                Just connected back to the client and if I click on the field with a ? - There is no value.  All "do not evaluate if all referenced fields are blank option" are checked off.  Just looking at:

                a. Paper Total Cost A: + a. Press Total Costs A: + a. Ink Total Cost A: + a. Prepress Total Charges: + a. Bindery Total Qty A: + a. Farmed Out Total Quantity A: + Delivery Charge Quantity A: + b. Paper Total Cost A: + b. Press Total Costs A: + b. Ink Total Cost A: + b. Prepress Total Charges: + b. Bindery Total Qty A: + b. Farmed Out Total Quantity A:

                It is straight math but yet, if any of the b. fields are blank we get a ?.  This all worked in v6 - Something happened in the change over to 2012.

                • 5. Re: Calculations not working since Upgrading to FM Pro 2012
                  philmodjunk

                  The expression should evaluate the same in both versions and a single empty field should evaluate as zero whether or not the check box is selected.

                  Try editing this expression slightly such as adding a + 0 to it. Do you get any error messages when you click OK to close the dialog?

                  Recover your file and see if the recovered copy produces the same result. It may be that there is file damage that only affects your file when converted to FMP 12 format.

                  • 6. Re: Calculations not working since Upgrading to FM Pro 2012
                    SteveStaves

                    Where should I add the a + 0?  I do not hink it is corruption as it is only these calculations that appear to not work?  If either field is filled in on their own, then the ? appears but when both are filled in then perfect result - Odd indeed?

                    An odd question - When I set up the databases on his server 9Server 2008), I installed Filemaker server 2012, imported the databases and opened all.  From his computer running Filemaker Pro 2012. we click on File - Open Remote and browse to the server which lists all the mounted databases.  I am assuming that is the correct way to open them.  However, is it possible that his relationship links may be off somewhere even though everythgin appears to be working?  I am not sure how this could affect this calculation but I am grasping at straws now.

                    • 7. Re: Calculations not working since Upgrading to FM Pro 2012
                      philmodjunk

                      not "a + 0", just "+ 0" Undecided and it can be appended to the end of the expression. This should force Filemaker 12's script editor to re-interpret this calculation's expression and I'm curious to see if it triggers any error message. This is an extreme long shot so I don't really expect any error messages nor a change in how the field evaluates.

                      File corruption comes in many forms. I strongly recommend that you try a recover. For one thing, this will rebuild all field indexes to your file and the behavior of a field with a corrupted index can be unpredictable so if nothing else, we will have ruled out some possible issues by running the recover. Please test the recovered copy even if the recover does not indicate that any problems were found and corrected.

                      Open remote--either from the file menu or through a script is the only way you should access a hosted copy of FileMaker Pro. Since none of the fields in these calculations refer to fields from a related table, I can't see any reason why a relationship would be a factor, but then any one of these fields could in turn be a calculation field that references a related table and I would not see that from what you have posted thus far in this thread.

                      • 8. Re: Calculations not working since Upgrading to FM Pro 2012
                        SteveStaves

                        Unfortunately, no difference.  All existing quotes that were created before the conversion shows the values correctly.  Only the new quotes since the upgrade show a ? if both a and b values are not filled in?  I am not that familiar with expressions etc but is there any way to make the two a case statement i.e. If no values in a then 0 and i no values in b then o and add the two together?

                        e.g ...
                        (a. Paper Total Cost A: + a. Press Total Costs A: + a. Ink Total Cost A: + a. Prepress Total Charges: + a. Bindery Total Qty A: + a. Farmed Out Total Quantity A: + Delivery Charge Quantity A:) 

                        So if this has no data, then the value is 0 and add it to the next which needs the same rule i.e. if no value then it is also 0?

                         (b. Paper Total Cost A: + b. Press Total Costs A: + b. Ink Total Cost A: + b. Prepress Total Charges: + b. Bindery Total Qty A: + b. Farmed Out Total Quantity A:)

                        • 9. Re: Calculations not working since Upgrading to FM Pro 2012
                          philmodjunk

                          That can be done, but there's no guarantee it will work. IsEmpty ( FieldName ) can be used as a test to see if a field is empty of data.

                          Let's do a different test first.

                          If you put all the individual fields listed in these calculations--or just those that appear in both, on the layout. Do any display a question mark. The names suggest that some of these fields are calculation fields and I wonder if one of them is producing an invalid result--which is then passed on to these two calcualtion fields and produces the ? you are seeing. Pay especial attention to any that use division to compute a value.

                          • 10. Re: Calculations not working since Upgrading to FM Pro 2012
                            SteveStaves

                            Same issue ... here are a couple of the actual formulas:

                            (b. Ink Total Cost per M: *
                            ((b. Press Total Impressions A: / b. Ink Total Number of Passes:) / 1000))
                            + b. Ink Total Minimum Charges:

                            (a. Ink Total Cost per M: *
                            ((a. Press Total Impressions A: / a. Ink Total Number of Passes:) / 1000))
                            + a. Ink Total Minimum Charges:

                            Basiclly, each one of the items from above are formulas very similar to these two.  If the a. one are filled in, there are ? marks on the b fields and cive versa.  If you fill both a and b in, the total is correct and there are no ? marks.  It is odd, too odd?  Their a. filed are basically costs per page and their b. fields are costs per booklet.  Because of the fact that if you fill in any field in a, the totals are correct and any in b the totals are correct and if you have both, the totals are correct then the issue is not formula based?

                            • 11. Re: Calculations not working since Upgrading to FM Pro 2012
                              philmodjunk

                              Notice the division used:

                              / b. Ink Total Number of Passes:

                              and

                              / a. Ink Total Number of Passes:

                              If there is no data in these fields, you can get division by zero and this will produce a question mark. That invalid result will then produce a question mark in each additional calculation that refers to these calculation fields.

                              What have you selected for "do not evaluate if all referenced fields are empty"?

                              This option should be selected for these calculations so that you get an empty field instead of division by zero--but it will only make a difference if all the fields in the calculation are blank. If even one field has data, you'll still get division by zero here if these "total number of passes" fields are empty.

                              • 12. Re: Calculations not working since Upgrading to FM Pro 2012
                                SteveStaves

                                That makes sense but then, WHY was this working perfectly in version 6?  The calculation was not changed at all when I upgraded the database.  How would we get around the divide by zero error then?  Is there a way to make the default value 0 or something to that effect?

                                 

                                Oh, and by the way, thank you for your ongoing EXCELLENT help. 

                                • 13. Re: Calculations not working since Upgrading to FM Pro 2012
                                  philmodjunk

                                  Is the "do not evaluate if all referenced fields are empty" option selected?

                                  I don't know why you are getting a change in results here. It really should evaluate the same in both versions as this part of FileMaker has not changed.

                                  If this is an expression where at least one field contains data but the divisor--the field used to divide--can still be empty, then you should modify your calculation with an expression such as:

                                  If ( Not IsEmpty ( a. Ink Total Number of Passes ) ; YourExpression here / a. Ink Total Number of Passes ; 0 )

                                  • 14. Re: Calculations not working since Upgrading to FM Pro 2012
                                    SteveStaves

                                    On all expressions/formulas the box "Do not evaluate..." is checked off.  Is it all possible that because he is using a : in the expression name, that it is causing issues? 

                                    If I understand, the total expression sould read:

                                    If ( Not IsEmpty ( a. Ink Total Number of Passes ) ; (a. Ink Total Cost per M: *
                                    ((a. Press Total Impressions A: / a. Ink Total Number of Passes:) / 1000))
                                    + . Ink Total Minimum Charges: ; 0 )

                                    Sorry for asking for clarrification but I am not a formula person :)

                                    1 2 Previous Next