Search returns wrong results when running in server/client environment. No problems in stand alone.

I have two related tables: Items(ID,...) and Transactions(ItemID, Date, Quantity). I want to compute the total quantity of transactions of an item before a specific date. So I add a global in Items (gEndDate) and add another relationship Transactions_OnDate where ID=ItemID AND gEndDate >=Date

I then add a calculated field in Items Qty_OnDate = Sum(Transactions_OnDate::Quantity.

Everything works perfectly. I see correct records in both portals. I am also able to search in the calculated field Qty_OnDate.

BUT when I move the database to server, portals and records display correct amounts but when I search for Qty_OnDate >0 after changing global gEndDate I get the wrong results: I get results of previous search, as if the Qty_OnDate has not updated even though I see correct results. If I make any changes to a record, then Qty_OnDate is updated properly and that record appears correctly after find.

Any ideas?

I enclose the simple example file.

Server FMS9 (9.0.3.326) (Mac), Client: FMPro 9.0v3 (Mac)

InventorySimplest.zip

Probably you can find answer via FileMaker topics: "Solving calculation problems" or read Help. I remember that I have get same info there while ago.

imho, the field Qty_OnDate cannot be searched. It is dependent on a relationship that does not resolve in Find mode.

What do you mean by "Resolve in Find mode"? Also, which types of relationships don't resolve in Find mode?

I've seen a similar issue reported here:

http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&thread.id=18608

I am curious to know what happens if you change the formula of Qty_OnDate to =

Let ( trigger = gEndDate ; Sum ( Transactions_OnDate::Quantity ) )

I have already tried that, it made no difference.

By the way, I forgot to mention, in case you try to server the file, that the example I uploaded had the "Don't Display in Open Remote File Dialog" checked

And if you precede the find with Refresh Window [Flush cached join results]?

OK, I just tried this, it made no difference

do the global fields have the same value in stand alone mode as they do in a hosted mode?

James

http://www.soliantconsulting.com/blog/category/filemaker/