find mode bug: request works on FMP7 client, not on server

[Owen Mathews]

The problem in a nutshell: I can do all the finds that I want in my database when I open it locally in my FM7 Client (updated to v3, on OS X Tiger), but when I open it via FMNet on a Windows-based FM7 Server, some of the finds that worked on the client give me "no records match that set of requests."

Two paragraphs of background before I describe the problem in detail.

I've been developing a moderately ambitious database project to keep track of computers, who they're assigned to, the issues with them, etc. I wanted a way to keep track of the history of assignments of a computer (e.g. computer X was assigned to person A from 1/1/2000 to 8/1/2000, then then to person B from 8/1/2000 to 12/10/2002, and then to person C from 12/10/2002 until the present) and worked that out fine. Then I wanted to be able to browse through the computers and see the most current owner, which I also was able to do using a global field and joins that used it (it's named gSentinelDate, if you're digging into the structure).

The toughest part was getting Find mode to work properly. If you open the Computer file I attached, you can search in the Assignment box for a particular person, location, or group that the computer is assigned to, and it will automatically pull up the computer based on the most current assignment data. (I did this by adding an extra TO of the assignment table -- you'll see ComputerCurrentAssignment and ComputerCurrentAssignment 2 in the relationships graph -- and putting in the same global field to "point backwards" towards the computer table.)

Now the problem. When these files are shared on the FMP7S, I can still do finds on computer data such as the barcode, serial number, and other items in the blue box on the left (in the first layout, Computer Detail) and get the appropriate records. But when I try to put criteria in the field labeled "currently assigned to:", it returns no records. Again, works fine on the local machine, doesn't work on the server.

What's your opinion? Can you reproduce the behavior? Can you get it to work on your server? Is this a bug?

For now I'm implementing a scripted workaround, but it's not nearly as flexible as being able to use actual find requests.

I've attached the files in question (you can get full access as Guest), though I have not taken the time to prune the database down to a minimal reproduction of the behavior.

test_case.zip

[-Queue-]

Try using ComputerCurrentAssignment 2::AssignedToName instead of ComputerCurrentAssignment, which includes a global in its relationship to CCA 2, so that it cannot be used as a child table, only a parent one. There seems to be no need to reference the CCA table anyway, since the relationship to it from CCA 2 is the same as the one from ComputerSentinelDate to CCA 2, minus the global field.

[Owen Mathews]

Thanks for the advice, but I already went down that path during my development. That's the situation I described in my "toughest part" paragraph. I need the extra TO of ComputerCurrentAssignment so that I can use the global to do the find from assigned name back to the computer table and get the correct information. The self-join lets me eliminate spurious records that are found when searching for a particular name.

To see what I'm talking about, switch the "currently assigned to:" field in Computer Detail so that it draws from ComputerCurrentAssignment 2 as you suggested. Now go into find mode and search for "oubs". Why did it pull up Mary Buczek? Go to the All Assignments layout and you'll find the answer. Now do a find for "oubs" in the AssignedToName field. You'll see three results. Lance has had three computers. Your find pulled the first available record into the results. But it's obvious from the table that Lance no longer has that computer (computer1341)--he gave it up on 8/5/05. The current owner, if you now do a find on that computer ID, is Mary Buczek. That's how she ended up in your results.

If you switch back to my system (using ComputerCurrentAssignment::AssignedToName), you can do a search for "oubs" and it will pull up the correct record.

By adding the second TO, I can require that only records where the end date is 12/31/4000 will match a find from the "currently assigned to:" field. No second TO, no capability to find current owners by name.

(Took me a long time, as I mentioned, to figure this one out. FM can be an odd beast sometimes.)

[-Queue-]

Try changing Computer::gSentinelDate to a non-global calculation date field of

Date( 12; 31; 4000 )

Then use CCA 2::AssignedToName.

[Martin Brändle]

Global fields behave differently on a server. See the FileMaker Help.

[Owen Mathews]

Excellent idea, queue. That worked like a charm for almost everything. I'm still having trouble, though, when I try to search for certain values. I'm trying to figure out why. Searching for people works fine, but when I enter a location (e.g. Lab C), it works on my local machine but not on the server.

I'm verifying with our server admin that it's not using a separate machine to store the files.

[Owen Mathews]

For the sake of completeness, I'm posting my solution after talking to FMP tech support. We discovered that it had to do with files being accessed via the initial layout. All files that had TOs that were "touched" in the relationship graph by the original layout were opened when the DB was opened on the server, and therefore finds of those data worked. Finds in other files did not.

The solution was to add a small login script that opened("hidden") and then closed any files that were not already accessed implicitly via the relationship graph from the initial layout.