11 Replies Latest reply on Jun 27, 2014 10:35 AM by philmodjunk

    Field Not Calculating Properly.



      Field Not Calculating Properly.



           Have a good one for you this morning. So I have this field that is set as a calculation field. with this as the formula:

           (WorkOrders::OD Decimal + .07* Pi-2.4 * WOs__STRIP_ASSEMBLY_ITEMS::WT Average+ .0049999)*(WOs__STRIP_ASSEMBLY_ITEMS::WT Average*.283 *12*WOs__STRIP_ASSEMBLY_ITEMS::Length Used)

           in Watch Values window it's showing: 2286.595159014260564

           But the value that it's calculating as part of the field is: -204.636153485739436

           I have tried several things to see if I can figure out what the problem is. One of them for example was taking the field and setting it to a Number with a Calculation. When I did this and told it to replace field contents with the calculation it worked and it worked perfectly. But, when I change the filed back to a calculation field and copy and pasted the formula in. It went back to giving me the -204.6361534... number.

           I'm at a loss. Can anyone help me figure this out?




        • 1. Re: Field Not Calculating Properly.

               Your calculation uses table occurrence names to refer to fields from two different occurrences: WorkOrders and WOs__STRIP_ASSEMBLY_ITEMS.

               What is the relationship between them?

               What is the "context" table occurrence selected in the Specify Calculation dialog for this calculation field?

               What kind of field is WT Average? is it a summary field?

          • 2. Re: Field Not Calculating Properly.

                 I find when calculations don't come out as expected it's often the context. In Data Viewer watch the context is the TOC of the layout that is selected. Where a calc field you can set the context the calculation evaluates in at the top of the calculation dialog. 

                 So my first check would be to look at that. If that doesn't work isolate each field reference in your calculation and see if it has the expected value.

                 A useful tool for this is 2empowerFM Developer Assistant by Dracoventions.  When installed it puts a little dialog on top of FileMaker. You select any term or field reference in your calculation dialog and click the "Evaluate" button in 2empower and it will show you the value.  I really love this tool, it saves us so much time. Not only does it allow you to evaluate calculations it let's you do finds in scripts and the "Relationship Graph" We have FM Files with hundreds of TOC's in the graph and this saves us tons of time looking for specific TOC's.  It's pricey but well worth the money in the time you save. (I'm not affiliated with this company in any way.)



            • 3. Re: Field Not Calculating Properly.

                   The Two Tables are related directly through WO ID

                   The Context is a WorkOrders for the layout and then a portal showing "WOs__STRIP_ASSEMBLY_ITEMS"

                   WT Average is a Calculation field with the following formula:

                   Case (
                   Middle ( Wall Thickness; 5; 1) = "" ; Wall Thickness ;
                   Middle ( Wall Thickness; 5; 1) = "-" ; (WT Left Calc +  WT Right Calc) / 2;
                   Could it be that the WT Average isn't calculating before it's trying to resolve the equation?
              • 4. Re: Field Not Calculating Properly.

                     oh, I forgot to mention it seems to be only doing it in one of 3000+ records. I've looked at all the other fields and everything is fine. But this one record seems to have something amiss.

                • 5. Re: Field Not Calculating Properly.

                  Middle ( Wall Thickness; 5; 1) = ""

                       This term looks wonky to me. The only way you get a NULL out of Middle() is if the length of "Wall Thickness" is less than 5 characters. 

                       Isn't "Wall Thickness" a number field? Are you using a Text function on a number?  

                       I know FM isn't strongly typed and you can do a lot of text functions on numbers and number functions on text but I try not to rely on them. I sometimes find you get unexpected results.


                  • 6. Re: Field Not Calculating Properly.

                         Thanks guys! You helped me think it through...

                         The problem was there were two records with the same WO ID. One of them didn't have all the values populated. We deleted the errant record and everything calculated properly!

                         As always, you guys rock!

                    • 7. Re: Field Not Calculating Properly.

                                The problem was there were two records with the same WO ID.

                           One of the possible causes I had suspected. Be advised that the fact that you had two such records indicates possible serious issues with your data model.

                      • 8. Re: Field Not Calculating Properly.

                             oh, don't get me started on the data model...

                             Wait, that would assume there is a model...

                             There isn't... But I'm working on it

                             I inherited this monster and I'm trying to fix it. 4 years of accumulated scripts to move data around in the database instead of having proper relations... Yeah don't get me started.

                        • 9. Re: Field Not Calculating Properly.

                               An immediate short term fix is to add a "unique values" validation rule on this field to help catch accidental duplication of values.

                               A long term solution may be to carefully replace this field with an auto-entered serial number field for use as the primary key to link to other tables. You can retain this field as a data field in the parent table if you need it, but not use it as a match field in your relationships. This assumes that your "work order number" comes from a source external to your database, is manually typed in, or is a calculated value of some sort.

                          • 10. Re: Field Not Calculating Properly.

                                 Right now I'm doing 2 things at once with this database. Trying to patch it and keep it functioning and such as it is. And at the same time refactoring it to have a better data model and more closely tie it to the business model so that it functions in a logical and reasonable way.

                                 Thanks for your help!

                            • 11. Re: Field Not Calculating Properly.

                                   I hear you loud and clear. It can be like modifying a car while still driving it down the freeway. surprise

                                   In my case, it's a large database first created by me as a consultant when FileMaker 3 was the current version. I've learned a lot about relational database over the years and FileMaker has gained a lot of better capabilities.

                                   But inserting those improvements into the existing database, without bringing down the database or locking users out at the wrong time or just confusing them with the interface changes continues to be an ongoing challenge--especially since the various regulatory agencies that we have to keep happy keep changing their requirements on us...