Fully aware searching unstored calculated fields is not best practice, but this is a limited dataset, and the slower search is acceptable for what we're doing.
The search I'm doing fails only when permissions are limited, and only on FMS.
So the field at issue (A1) is an unstored calculated field in Table A.
The overall database is TableA with fields A1 & A2, TableB with B1 & B2.
TableA::A1=ExecuteSQL("SELECT B1 from TableB where B2=" & TableA::A2,"","")
None of the 3 additional fields are calculated, and all are indexed.
Consider two permissions sets:
User1 [Full Access]
User2 View Only all fields
User1 can search field A1, all results as expected, local and hosted.
User2 can search A1 as well, but only if the DB is local, not hosted on FMS
User2 on FMS cannot search A1. Any search results in all records or none. A1 contents are viewable, but searches fail.
Any ideas why?