weird relationship glitch
In TableA, I have a calc field, which I'll call "Answer" here, with an output of either 1 or 0.
In a directly related TableB, I have a calc field, which I'll call "Query", whose formula is TableA::Answer.
Searching for Answer = 1 in TableA, I find 1,913 records. Searching for Query = 1 in TableB, I find 106. But, all the data in TableA came from TableB, and no records in TableB have been deleted.
Yet every record in TableA where Answer = 1 finds a value (correctly, according to spot check) from an independent field in TableB. Though the TableB/TableA relationship is respectively one/many, neither eyeball nor self-join relationship finds any duplicate values in the link fields (a straightforward ID number) on either side.
It's possible to calculate the value of Answer from data in TableB, and from that to find the 1,807 TableB records in which Query = 0 even while a portal to TableA shows Answer = 1 (this is consistent when I change the Query formula to TableA::Answer = 1). I can't see any difference between the TableB records where Query gives the "right" result and those where it doesn't.
For the present I'm able to find the results I need, but some of the necessary data inTableB is temporary and eventually I won't have that option. Meanwhile the anomaly is driving me nuts.
I hope this is clear: probably I've left something important out of the problem description here, which I expect is something crucial to the problem itself.
Thanks in advance for all comments & suggestions!
FTR: using FMP Adv 11.0v1 on a standalone MBP running MacOS 10.5.8; have used FM since version 3.