Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Can't index a field - driving me nuts!

Dale Johnson

Recommended Posts

I've got a calc field called rKgFallAvg I want to use on the right side of a self-join relationship.


It is simply:

"K" & LookupFallAvg


Originally, I got the FallAvg from a related Norms file. I changed it to a lookup so it could be indexed. Indexing is on.


When I go to Storage options for rKgFallAvg I can turn indexing on. Coming back to Define Fields it says the field is indexed.


When I leave define fields and then come back to define fields it is changed to Unstored and indexing is off when I go to Storage options.


Very frustrating confused.gif


Any idea why this is happening to a nice guy like me? laugh.gif


Seppaku (sp?) beckons.



Link to comment
Share on other sites

Hello Dale,

The problem you've encountered is not all that uncommon. For reasons which are obscure, FileMaker does not permit a field to be indexed when it is based on a field which itself draws data either from a global field or from outside the file (as in your case).


Not only is it unclear why this should be the case (and some would argue that that in itself is a bug), but as you've found, FileMaker fails to warn you of a problem and simply dumps the index unceremoniously when you leave the Define Fields dialog. Even if the original indexing prohibition isn't a bug, the failure to warn you before dumping the index probably is...


Be reassured that you are not by any means the first person to get caught by this idiosyncracy.


Now that you're reassured, you'll be looking for a solution. Here are a couple of options:


1. Redefine the 'rKgFallAvg' field as an auto-enter calculation instead. This is very easy to do, but has the drawback that the result will not update if/when you refresh the values on which the lookup is based (which may or may not be an issue, depending on hoe the data is used...)


2. Set an unstored calculation to prepend the 'K' in the related 'Norms' file and then define the 'rKgFallAvg' field as a lookup based on the same relationship as the LookupFallAvg field, to copy in the result of the new calc in the related file.


3. Create a self-relationship based on matching the LookupFallAvg field to itself and then set up a new lookup field to copy the value of the (unstored) rKgFallAvg calc based on the new self-join relationship. The use the additional lookup as the right side of the relationship you are wanting to build.


There are a few other options you could explore, but one of the above three should set you on a path towards a suitable way to resolve your current dilemma. wink.gif

Link to comment
Share on other sites

Thanks for the explanation and the suggestions Ray. They are most appreciated.


I've been using various calculated keys for many of my calcs lately and they are so cool when I don't run into the indexing problem.


I've decided, for this case, and because the situation repeats for many other tests to use a scripted find on Kindergarten and FallAvg not empty to get the count and then use a SetField step to populate a global with the found count. That will eliminate the need for all the new calc fields and relationships.


I've just ordered FMP 6. By your comments, I assume the bug you mention is still there as well?


Cheers - Dale

Link to comment
Share on other sites

  • Create New...

Important Information

Terms of Use