Perform Find on a Calculated Field

[RyanESmith7]

I feel like I am about to ask the most rookie question ever... but here goes anyway...

I'm performing a find on a calculated field that is supposed to be a non-zero number (it is actually a count). I basically want to find all records where "Needs Review" is true (non zero).

Here's the calculated field definition:

When I perform find on "Needs Review" ">0", I always get no records found, even though there are some there. 

What am I doing wrong?

Thanks,

Ryan

[Steve Martino]

Enter find mode, put an asterisk * in the field.  It will find any record that has a value in that field.

 

Here's a link to help you with finds:

http://help.filemaker.com/app/answers/detail/a_id/13715/kw/find operators/session/L3RpbWUvMTQ3NTE4MzIzMS9zaWQvWS1OdzNUX20%3D

Edited September 29, 2016 by Steve Martino

[RyanESmith7]

Thanks for the reply, Steve, but that returns records that are 0 as well. I only want records that are greater than 0.

[comment]

What is the result type of the calculation field? And while we're at it, why does it require the "0 +" part?

[RyanESmith7]

The result type is Number. I had a problem with a calculated field returning nulls for a report one time, so I threw the "0+" in my calculated fields since then and have kind of kept the habit. Especially for fields I plan on using as a booleans.

[comment]

Well, I can't reproduce your problem based on your description.

 

19 minutes ago, RyanESmith7 said:

I had a problem with a calculated field returning nulls for a report one time

Perhaps that has something to do with it.

[bruceR]

What do you see when you enter find mode; click in the field; choose the command "Insert from Index" ?

[comment]

19 minutes ago, BruceR said:

What do you see when you enter find mode; click in the field; choose the command "Insert from Index" ?

The field is unstored; is there any doubt that "Insert from Index" will be grayed out?

[bruceR]

Somehow I missed the calc definition. Yes; it won't have an index.

[rwoods]

Is the calculation result type set to 'Number'? If you have it set to 'Text' that can give very unexpected results.

Also, uncheck the 'Do not evaluate if all referenced fields are empty' to see if that helps.

Otherwise, I would try a 'Recover' on the file.

I have seen this sort of problem on files we run where we have fields that are a result of the Sum of lots of related fields. I sometimes wonder if there is an obscure bug in FileMaker around this, but then I remember that most 'bugs' turn out to be 'user misunderstandings'!!

[RyanESmith7]

This is starting to look like a FMS bug to me. I have similar code in another database on the same server, and it too is not finding records where a calculated result (number) is > 0.

That exact same code is running on one of my databases that's on an older FMS server (version 13) and it is working fine there.

I thought I was going crazy... this should be working and I'm pretty sure that it WAS working for a long time, but now I'm seeing it on multiple database in different scenarios, but all one one server that is hosted.

Server Version

14.0.4.412

 

Edited September 30, 2016 by RyanESmith7

[comment]

15 minutes ago, RyanESmith7 said:

This is starting to look like a FMS bug to me.

You did not mention this was a served file. I recall similar issues being reported before, regarding search in unstored calculation fields of served files. If I remember correctly, the solutions involved:

• updating the server software to the latest subversion;
• restarting the server.

 

 

 

Edited September 30, 2016 by comment

[RyanESmith7]

Thanks, comment. When I started investigating this, I thought I was doing something wrong 'cause I saw it in some new code. But now that I see it affecting old code, too, I'm putting 2 and 2 together. I'll reach out to my vendor and see if they can update and restart the server.

[RyanESmith7]

Confirmed. Downloaded one of the DBs to my local PC and the code works there. Doesn't work on hosted.