9 Replies Latest reply on Mar 13, 2014 12:17 PM by Stephen Huston

    Search in a Calculation field performing incorrectly

    dlevinson

      This Find issue started yesterday, after working successfully for more than a year as part of a script step. Now, this sort of Find does not work at all:

       

      I have a calculation field that stores a number, its value is derived by subtracting from a Number field a combination of other Number fields and Calculation fields that produce numbers. The value of this field is correct (when you look at the output in Browse mode, the number is the correct number), but when I attempt to perform a Find, the results are not what I searched for. For example, ten of the records have a negative value for this field, but a Find of "< 0" returns no records. Or, a Find for "5" returns all the records where the first field referenced in the calculation equals 5, regardless of the value of the field I actually used in the Find. Again, this Find worked successfully for more than a year, but now does not, and no changes were made to the database in weeks. This is with FM Server 13 and both FM 12 and 13 clients. I tried removing the Calculation fields from the equation and simply wrote out the referenced equations, but once again, the output is correct, the Find is not.

       

      Since this field is part of a vital script step, I have created a temporary fix by adding a Number field to the table and a script step that replaces the value of the Number field with the equation I need and using the Number field for the search. However, I'd prefer to be able to search the Calculation field for its output, and I do not understand why I cannot.

       

      In working on this, I created a new calculation field, New_Field = (TABLE::Number_Field - SUM(table_TABLE2::Number_Field2)). Same issue, New_Field shows the correct value, but Finds are not retuning correct results. In fact, a search for New_Field = "0" returned all the records where Number_Field = "0", regardless of the value of New_Field.

       

      Any advice?

       

      **Update**

      I created a test DB with similar fields, tables, etc., and this type of search worked just fine, BUT once I uploaded this DB to FM Server 13, I ran into the same problems. I could reinstall FM Server tonight, but if anyone can tell me something else about this issue, that'd be great.

        • 1. Re: Search in a Calculation field performing incorrectly
          Stephen Huston

          I few ideas come to mind:

          1. Check that ALL of the fields referenced in the calc are strict number fields, and that the calc itself is set to return a NUMBER result rather than text.
          2. I wonder if performing a Find which is based on an unstored calc using related fields is encountering some of the same issues one gets when searching directly on related fields within a parent record, false positives when using some search symbols. Can you try these searches with fixed values in the search string instead of comparisons, and see if those return any incorrect results? ( i.e –5 instead of <0 )
          3. If it turns out that your search string symbols are at issue when working with related values, try an initial search with some other factor, then see if a Contstrain Found Set script step will let you refine to thte desired results.
          4. When searching on related values, keep in mind that <,≤,≥,>, and == don't always behave the same as in the parent record. Lack of a matching child record can even allow = (blank) to match parents with non-blank children, so one needs to use Omit >0 in some cases to achieve the same thing.
          5. Since v13 is new, the fact that this script worked for over a year  still means 13 is a recent change. There have been reports of behind the scenes script modifications which have caused editing a script in 13 to change behavior so the same script no longer works  when the file is opened in 12. The script steps still read the same, but running the script in 13 changed something under the hood. So I would try reprogrammning the scripted Finds from scratch in 13, and be sure the client for FMS13 is also FMP13.
          1 of 1 people found this helpful
          • 2. Re: Search in a Calculation field performing incorrectly
            dlevinson

            Thanks for the reply:

             

            Please note, it's not just scripts, but all Finds that seem to be affected:

             

            1. All references are numbers, or Calculations with Result As box set to Number.

            2. Tried the -2, no good; tried a Find where I copied -2 from the field, entered Find mode, Pasted -2 in the same field, Find, No results.

            3. Playing around with this...but it still doesn't behave correctly. I did a Find where Field = *, Constrain - Omit where Field ≥ 0 - nothing; or Constrain Find where Field = -2 - nothing. And for good measure, Constrain Find where Field = 10 - results reflect where calculation referenced field = 10, not the searched field.

            4. Played around with Omit, same issue

            5. It did work on v13 for a while, but now no Finds of this nature are working at all. I've reworked the script to search a Number field with replaced values instead of the Calculation field, but I'd rather return to the previous method. The client is 13, but I also tried it on a 12 client with the same results.

            • 3. Re: Search in a Calculation field performing incorrectly
              Stephen Huston

              If the data itself is correct, but the Find results of a correctly structured Find are not correct, there may be problems with the file's index of values. This can affect even unstored results.

               

              Can you stop serving the file, take it off the server, and locally use the recover commands to just rebuild the index of the file, then try it again. Sometimes this non-intrusive index rebuild-only using the recover tool fixes index problems affecting finds and calc results.

              • 4. Re: Search in a Calculation field performing incorrectly
                dlevinson

                I might be able to do that tonight, but I think the issue is related to the FM Server more that the DB file. I've attached a quick dummy DB with a structure similar to the issue at hand. Off server, I can search for the Calculated_Number and get results; On server, nothing. I played around with the indexing on this DB, reloaded, same issue.

                • 5. Re: Search in a Calculation field performing incorrectly
                  Stephen Huston

                  Thanks for the file to check out. Works fine locally, and I'm not able to serve it where I am right now. I did look at your data structure and found a couple of things to try:

                   

                  Turn on field indexing for your Key fields in both tables. The relationship itself can fail without that.

                   

                  Then try turning on storage/indexing for the non-calc number fields in both tables. At the moment they are all unindexed, which may have an impact on the calc results when served (performance, at least, though I wouldn't expect different results!?).

                  • 6. Re: Search in a Calculation field performing incorrectly
                    dlevinson

                    After your earlier comment, I decided to play around with indexing, that file had most of the indexing off, but so far no configuration of indexing has made a difference in the search results. If you are, or if anyone is, able to serve the file, and get cleaner results than I, I guess I will reinstall FM Server, though please note, I am on Windows Server 2008 R2 Standard; clients Windows 2007. Appreciate the help.

                    • 7. Re: Search in a Calculation field performing incorrectly
                      ch0c0halic

                      I put this file on FMS13 and the find performed exactly as expected. I am using the FMA13 client.

                       

                      Searches:

                      >0     failed

                      0       failed

                      -5      passed

                      <0     passed

                       

                      I then tried every number from -6 to +6 and the only one that worked is ‘-5’.

                       

                      I don't know why there would be a difference. But, this file is working as expected for me.

                       

                      FMS 13.0.1.224 on a Mac OS 10.8.5

                       

                      I did open it in FMA 13 before uploading. Maybe that made a difference?

                      1 of 1 people found this helpful
                      • 8. Re: Search in a Calculation field performing incorrectly
                        dlevinson

                        Well, not sure what the issue was, but I stopped the server, ran Microsoft Updates for the server (might as well), rebooted the server, and restarted the databases, and now all my searches work correctly. Some glitch somewhere in the system, I suppose. Not happy that it happened, but it only one day down. Thanks to anyone who tried to mimic my issue.

                        • 9. Re: Search in a Calculation field performing incorrectly
                          Stephen Huston

                          It probably wasn't even the patches, just the reboot.

                             Number 1 fix-all for tech support; PITA when it's the server machine.