Indexing calculated fields

[Matthew F]

I would like to index a calcualted field to speed up sorting. However my calculation references a related record and thus can't be indexed. Any suggestions on how to work around this?

[Fenton]

The only way to get the field to be indexed is to somehow get that related data into the local file. Yeah, it's a pain. However, there may be some way to do it during data entry. That's the best time, as bringing in the data for 1 record, even if it's complex, takes little noticeable time.

There a lookups and there are scripts. It may be that a lookup can do it, with no further steps needed. We'd need more understanding of your files to say which direction to go. You may also decide that it's more trouble than it's worth, just for a sort.

Another possibility is to minimize the found set being viewed. Of course, this doesn't help much with printing scripts, which have to print what they need to print (duh; it's getting late :-)

Even if you do get the data locally, you still have to go into the Storage options for the calc field and set it back to indexed; it won't do it automatically, and it will stop you if a referenced field is still related (or a global).

[Matthew F]

Consider one database to track individual encounters with a customer which has related fields to a second database which contains customer identifying information. A cancellation date can be entered via the encounter database but is actually a related record to the customer database. In this way it doesn't matter which encounter record for a customer is being viewed: They all will show whether the account is terminated.

When opening the encounter database a script step routinely finds all of the active accounts for a given salesperson (i.e. with empty cancellation date fields) then sorts and summarizes them by the customer ID. The find and sort is slow because status of each account must be recalculated each time.

I'm not sure how to use a lookup here because the encounter database and the customer database is not a simple 1:1 relationship.

[Jim McKee]

hi mfero ...

Ray Cologon ("Cobalt Sky") has written an excellent article that addresses the exact situtation you've encountered. Ray's techniques helped me to develop a structure that got me through what seemed like a total road-block.

There are times when you simply have to get relationship-based calculated values into indexable fields to enable reasonably efficient searches. Ray's article will step you through the most elegant way to accomplish this.

Good luck!

[Matthew F]

Jim,

Thanks for the reference. Ray's article has some interesing ideas. I might break down and create a script to facilitate this but I was hoping to avoid relying on user input to create a valid index.

The "automated" method of generating a lookup is interesting but I can't get it to work. The problem seems to stem from the fact that the match field is a case statement with identical results regardless of whether the condition is satisfied 'case(isEmpty(triggerField), status(CurrentRecordID), status(CurrentRecordID))' I don't understand how this should work since a relookup is triggered only if a relationships match field changes.

Am I missing something?

[CobaltSky]

Hi mfero,

Yes, essentially, a lookup is refreshed when the field on which it depends is changed.

If you test this, however, you will find that a relookup occurs even if you overwrite the field with the same value!!

Similarly, when the field which triggers the lookup is a calculation, the lookup will be refreshed *whenever the trigger field is recalculated* regardless of whether the result of that recalculation is the same as the prior result.

Thus setting a calc so that it references another field causes it to recalculate whenever the referenced value is edited - and that recalculation in turn causes any lookups based on it to be refreshed. So the calcs you're puzzling over are set to always produce the same result - because it is the mechanism of recalculation that prompts the refreshing, not the result per se.