More FIND problems

[Courtney]

I posted back in October about a scripted find that was no longer working the way it used to. I actually just updated the script to find in a different way to get around that problem.  

Now I have another scripted find that has stopped working as it used to. This one is searching in a calculation field for any records where the value is greater than 0, and it's returning zero results, even when I can plainly see that many records do have a value there.  Curiously, when I close the files on the server and just open them directly on my PC (unshared), the find works fine, but when shared through the server, the find does not work - whether running it through the script or just directly using CTRL+F from the layout. The field I'm searching is defined as Calculation type, Unstored, from a related table, calculating the Sum of a numeric field in all related records.

I have run a consistency check on this file this morning after the problem came up, and no errors were found. I also made a compacted copy of the file and experienced the same problem with that file.

Any ideas as to why this might be happening?

[LaRetta]

17 minutes ago, Courtney said:

The field I'm searching is defined as Calculation type, Unstored, from a related table, calculating the Sum of a numeric field in all related records.

Hi Courtney!

What is the table name of the parent (the layout you are on) and what is the table name of the child (the related table you are searching)?  Is it a one-to-many relationship as one parent has many children?  Are you viewing the results of this field on the parent layout?

When I briefly read your other post, I wondered if you had this issue as well.  If you have multiple child records but you have a FIELD and not a portal on the parent, you will see only the first related record.  So if you search the child table, it will find records with values but the first related may have no value.

I realize you can't produce your real file so you will need to describe the relationship, table names, fields names so we can help you further.

Another thing to keep in mind ... searching an unstored related field is not a good idea.  'Calculating the Sum of a numeric field in all related records' is not very clear.  So please provide details as to the purpose of this process as well.  In truth, if you could create a simple file with the relationship and only the necessary fields to produce your problem, it would help us help you.  This is also the best practice when you run into problems.  When you create a new file you 1) isolate the issue so you can clearly see it, 2) enter only a few records so the results are obvious and 3) eliminate whether your issue is a corrupted file.  Note that Recover does not always repair files; in fact, a recovered file should not be used again.

So the easiest for you is to create a file for us to review.  We can then quickly help you.  :-)

[Courtney]

Ok, I created a clone with no records and just added 1 company and 2 projects, plus my navigation icons. I set up a user ID and password of ADMIN/admin with full access.

Parent table is: Proposals -- Field name in question is Total Approved Budget
Child table is : Proposal Line Items -- Field name in question is Approved.
It is a one-to-many relationship.
Total Approved Budget is a calculated sum of the field "Approved" from the table "Proposal Line Items" - the line items appear in the layout "Proposal Detail", which is where I'm running this Find from.

I've only given each project one line item for this sample.

Project 1 is not approved, so there is no value in the "Total Approved Budget" field on the layout "Proposal Detail" (Budget tab).

Project 2 is approved for $10,000, so that value appears.

When I search for ">0" in the field Total Approved Budget, I get zero records returned if I am sharing the file via our server. If I take the file offline and access it directly without sharing, I get the appropriate 1 record returned.

 

BDclone.fmp12

I failed to address the purpose of this. I'm running a report in a related file (our time billing system) that looks for all projects that have a status of "active" and with a total approved budget over zero. We then compare the approved budget amounts to the dollar value of the time billed against each project in the current billing cycle to ensure we're on target and not exceeding our budget.

The thing that's really confusing me here is that this has worked, literally for years. It worked last week. Today it does not work. No changes have been made to the scripts, the field definitions, the layouts, nothing.

[LaRetta]

4 hours ago, Courtney said:

When I search for ">0" in the field Total Approved Budget, I get zero records returned if I am sharing the file via our server. If I take the file offline and access it directly without sharing, I get the appropriate 1 record returned.

Thank you for providing the file and such a complete explanation - it makes it easier for us.  :-)

Yes, it should work and the find works for me when on FMS 14.0v4 ... in your file, I find one proposal.  When you say 'our server' do you mean FMS?  If so, what version and updater are you using for server and for clients?  If you don't mean FMS and you mean a file server, that is a big problem.   I also notice that you have some custom privileges.  I don't see that they would restrict this find unless the limited calculations in your served file are different than the file you presented but I wanted to mention it. 

So this particular file fails to return the one proposal for you (when manually finding for >0) when served and you are full access?

[Courtney]

Yes, I have full access, and it fails to return the one proposal that it should when served. (Manually searching for >0 in Total Approved Budget.) I did not change anything in this clone from the served file except to strip out our client and project data. (I also put this clone up on my server, shared/hosted it, and then opened it and continued to experience the same problem.)

I'm not sure that I understand your question about the server setup fully. The hardware and setup of servers is something I'm not very knowledgeable about in general.

The admin console info says we're using FileMaker Server 12, v 12.0.4.405.

This has has happened on at least 3 different clients today, including my own, which is a Windows XP Pro environment, with FileMaker running FM Pro Advanced v 12.0v4.

The actual server machine where the files are served from is running Windows Server 2008 R2 Standard, SP 1.

[LaRetta]

On the server I just wanted to be sure you were using FileMaker Server which you are.  :-)

Run an updater on FMS - download file from here:  FMS 12.0v5 and see if that solves it.  It may and it may not.

[Courtney]

I'll have to put in a ticket to update that, I think.

In the meantime, you mentioning an update reminded me that I have FMP 14 Advanced on my laptop from DevCon, so I used it to log in to my original file (not the clone) on the server, and I still get zero results when performing this find.

[LaRetta]

Your client version, even if 14, is still accessing FMS12.0v4 server.  And FMS is what fetches your found records and passes them back to you.

If updating the server still does not resolve it, it might be Windows-specific issue with 12.  XP is listed as acceptable (and your laptop is probably newer Windows version than XP) but clearly something is wrong so if XP fails on FMS 12.0v5 with Windows Server 2008 R2 Standard SP 1, which is specifically listed in 12's requirements then it is probably a bug and should be reported here:  TechNet - Report An Issue.  If you post on TechNet, give them the file and all the specs you've provided me.  

But first, update that server and please post your findings here as you work through resolution so that others with same problem can find the answers.  It might also be time to suggest that management upgrade since XP has security issues and is being deprecated anyway.  Moving up to 14 would also be good investment for them.

Hang in there, Courtney.  These types of incompatibility issues are never fun to work through.

One more thing ... you mentioned 'putting in a ticket for upgrading FMS.'  IMO, most network folks know squat about FileMaker and they do not even know to close all files before performing server maintenance.  If you are not able to handle it yourself then I suggest that you gather specific steps needed for such an upgrade.  You can ask the questions here in Server forum if you need help with it after reading the online documentation about the upgrade.

Then you can give the network folks a checklist of steps in proper FileMaker handling.

[Courtney]

Thanks LaRetta - I will update as I work through this.

[Courtney]

Almost 2 months later, I'm still pushing for this update, and corporate is making it as difficult as possible. They now want me to provide a detailed plan for rolling back from FileMaker Server 12.0v5 to 12.0v4 if there is any incompatibility with the update. I'm not sure how this would be done. Could anyone point me in the right direction?

(Would it simply be a matter of backing up the server before we begin, and then restoring the backup if there's a problem?)

[Courtney]

Finally jumped through enough hoops to get this update approved, and it appears to have solved my problem. Thanks to everyone for reading and offering advice!

[LaRetta]

Hi Courtney,

Sorry I hadn't responded to your prior post - I hadn't gotten an email notice of it (or if I did, I missed it).  In fact, I notice now that I'm  I was not set to follow this thread at all and I should always get email in threads I've posted onto.  Anyway, I am pleased that you were able to work through this issue.

Edited March 26, 2016 by LaRetta