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?
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.)
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?
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.
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.
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!
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.
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.
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.
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!
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...