Wrong behavior when run on remote server

[j.s]

Hi

I eventually get a wrong found set when searching records with an unstored calc field > 0. However, the error only occurs, when the database is opened from a remote server. When it runs locally, i have never encountered the problem yet.

The search criterions are a bit complex. Besides a few parameters for the base table record, there also is a criterion that claims a calc field to be > 0. This calc field counts the matching records in a related table using Count(related field). The underlying relationship is multi-value, including normal and global fields. The calculation works fine, it never yielded a wrong value in browse mode. Yet, the found set resulting from the search eventually includes results with calc field = 0. This error is not fully reproducable, but if it happens, its always the same few records that are affected.

Meanwhile, I spent two days analyzing the error. I extracted the problem from the main software. I recovered the file, had indexes recovered. I redrew the relationship, simplified it, played with field types, redefined the search criteria, deleted and reimported the table data, deleted and reimported the base table, exchanged layouts -- but nothing ever got rid of the error. I slightly start believing that this is a 'FileMaker bug'.

I now have tiny demo database ready that produces the error, when it's run on a remote fmserver 11v3 mac and accessed from either fm pro adv 11v3 win or fm pro 11v3 mac. If i launch the demo locally on fm pro adv 11v3, everything works fine.

The fmserver machine is well-established. It hosts about a dozen of databases, including big ones, and overall performance is satisfying. I mention this, because the speed of that little demo leaves much to be desired, if hosted by the server. No surprise, there's quite a bunch of joins to be evaluated.

About the demo file (attached):

Its a monthly overview of work assignments. There is a set of employees (table Employees) and a set of work places (table WorkPlaces). A third table (EmployeesXplaces) defines the potential work places for each employee. A forth table (JobSheets) stores the actual work assignments. With the provided sample data, the error typically occurs, when displaying assigned employees in Jan 2012 (employee nr 7 is listed in work place nr 1, although it is never assigned there during Jan12).

Im very glad about any hint. Thank you very much.

Jürg from Switzerland

monthlyWorkPlanA.zip

[bcooney]

I'll take the time to look at this later today, but if it helps, the first thing I'll look for is that you are not setting required global values when you host the file. Do any of the relationships depend on a global value?

[j.s]

Thank you for your help, bcooney!

Do any of the relationships depend on a global value?

Yes, they do. In fact, the critical relationship is a date range join using 2 global calculations (plus other criteria). But these fields are always well-set before entering find mode.

The relationship works in browse mode. And even in find mode, i can see that those 2 global calcs contain the desired values.

I also converted the global calc fields into normal global fields to see, if it makes a difference. It didn't..

[bcooney]

I think you'd benefit by studying this technique for a cross-tab: http://edoshin.typepad.com/bits_and_pieces/2006/12/crosstab_report-1.html

I don't have time yet to really dive in, but I've never trusted global calcs, so my gut tells me that's where the problem lies.

[Vaughan]

Yes, I agree with Barbara: the problem is the global calculations. Change them to "normal" unstored calcs, but base them on global fields if required.

[j.s]

I've already replaced the global calcs with normal globals, it didnt work any better.. I also replaced the global calcs with normal unstored calcs based on the navigation global fields --> Negative, i still get records with cNumberOfJobSheets = 0, when searching for cNumberOfJobSheets > 0. Although personally, i dont think the best of global calcs either, i doubt for once, its them who cause the problem.

Today, i shared the project (FM Network sharing) and immediately the error occurred. There seems to be a correlation: it works as long as the file is neither hosted nor shared..

Today, i started looking for technically similar constellations in other FM projects i made. Within short time i managed to provoke the same error in other dbs too, when performing finds on related fields based on multi-key relationships that depend on a global field. I therefore conclude that the sample file i provided here is not corrupted, but rather FM is unable to deal with such kind of query. I'd probably bite the bullet and go for alternatives, if not the resulting found sets were mostly correct. I tried to find a statement in the FM documentation or somewhere on the web, but failed. This forum is my last hope.. :-)

Thank you for your hints

[j.s]

I think you'd benefit by studying this technique for a cross-tab: http://edoshin.typepad.com/bits_and_pieces/2006/12/crosstab_report-1.html

Thank you, Barbara. Thats an elegant approach that will certainly help me one day. However, i dont think it's suitable for my current work:

* My layout is not a print report, but rather a planning tool. Each little square (field) must be clickable and jump to the corresponding job sheet (employee, place, date). I wouldnt know how to get this done with repeated fields.

* Lots of conditional formatting is added in the final version, based on other individual joins per square (e.g. public and personal holidays, special events, collisions). I dont think i can get this done with repeated fiels, if not creating many of them ("flag arrays").

Of course, this is not evident from the sample i provided, because i slimmed it down to minimum to avoid distraction and unnecessary sources of error.

[bcooney]

I think that repeating fields may help you more than you think. GetActiveRepetition ( ) is the key, and sending that as a script parameter.

[j.s]

I think that repeating fields may help you more than you think. GetActiveRepetition ( ) is the key, and sending that as a script parameter.

That's what I would have assumed, too. But it doesn't work. I get a 0 (zero) all the time, using Get(ActiveRepetionNumber).

Either way, i doubt that the repeated fields approach would solve my problem of retrieving invalid search results..