Getting a non-idexable calc into a indexable one?

[ibiubu]

I have a calc, that by its nature, will not allow me to turn on indexing. I need to be able to create a value list based on the results of the calc. But I cant create the value list because the calc field it is based on cant be indexed.

Is there a way around this? I have tried to re-do the calc in such a way that I will be able to turn indexing on, but have not been successful. So how can I use it in a value list?

LR

[jrp]

You can never index a calculated field. I believe that's rule you can't break. (I could be wrong, and would LOVE to know how).

But - you can make your field a normal, indexable field, then put your calculation into a script or auto-enter value. This way, the calculation is done *to* the field, which should still give you the data you need, while allowing the field to be indexed.

[Kurt Knippel]

Of course you can index a calculation, however it must be based upon stored fields (no globals, related fields or unstored calculations).

To solve the actual problem you can create another field which is simply a text or number field (depending upon the type of result) and use the replace function to store te result of your calculation.

You then base the value list on this new stored field. Just remember to update this field periodically.

[ibiubu]

So if the calc field is called "Summary" and the text field is called "Result" how would the replace function be formatted to pull the text from one and put it in the other? And if the text field set to a text type, not a calculation, where does the function reside? Also, is this type of function dynamic enough that if the calc field changes content then the function keeps the text field updated?

LR

[Kurt Knippel]

Match the result type of the calculation to the type of the stored field (i.e. a calculation that results in a number would be stored in a number field)

Simply use the replace function on on the Result field and set it to the Summary field.

The calculation stays in the calc field, it is just the you will periodically need to update the Result field with the current value of the summary field.

[ibiubu]

So what action needs to take place to periodicaly update the result field with the current value of the summary field?

So if the existing calc field is "Summary" and the result field is "Result"

So I have a third field with the calc Replace(text, start, size, replacement text)

replacement text would be replaced by Summary?

Replace(text, start, size, Summary)

text would be Result?

Replace(Result, start, size, Summary)

Not quite sure what to set start and size to?

Also, when data in "Summary" changes, does the Replace(text, start, size, replacement text) automatically update/change the "Result" field? I will have multiple users using the database, and data could be changing on a regular basis to where the original calc field "Summary" could change on a dialy basis.

LR

[CobaltSky]

I think CaptKurt meant to refer to the Replace script step (which from v6 is re-named 'Replace Contents') rather than the Replace function.

With the 'Replace Contents' (formerly 'Replace') script step, you can update the contents of your indexable text field with the contents of a calculation (or with the contents of another field) by selecting the 'Replace with calculated result' option within the specify dialog for the script step. Since this script step works on the current found set, you need to precede it with a 'Show All Records' step to ensure it is successful. You should also note that it will skip over any records currently being edited by another user in multi-user mode.

Using this approach, you need to run the script whenever you want to refresh the values of your value list.

However a better approach my be to set the indexable field to update automatically whenever a relevant event occurs. This would be done by setting the indexable field as a look-up (to copy the contents of your calculating field via a self-join based on recordID).

The trigger for the lookup should be based on a stored value local to the current record which, when it changes, will cause a refreshing of the lookup of the calculated value. This can be something specific to the nature and purpose of the calculation (eg a local value associated with it) or something as general as the record modification time. Once you select a suitable event to trigger by, you should create a primary key field for the trigger called 'cTrigger.key', with a formula along the lines of:

Case(IsEmpty(YourChosenTriggerField), Status(CurrentRecordID), Status(CurrentRecordID))

You then need a stored calculating field ('RecordID') of number type with the formula set to Status(CurrentRecordID) and a self-join relationship called 'ValueListUpdate' which matches cTrigger.key with RecordID.

If you then set up your indexable field as a lookup to copy the value of your unstored calculation based on the 'ValueListUpdate' relationship, it will be refreshed automatically each time the value you have chosen as the basis of your trigger changes (eg if it is a field which auto enters modification time, every time anything on the record changes).

[Kurt Knippel]

Great idea Ray!

Flushing this idea out a little bit might make a good article "Storing and Updating UnIndexable Calculations". Wanna give it a shot?

[CobaltSky]

Hi Kurt,

I'm happy to flesh it out into an article.

Where were you thinking of it being placed/posted/published?

[Kurt Knippel]

In the Articles forum of course!

[CobaltSky]

OK gotcha. Will do!

[ibiubu]

I agree. you ought to go into some detail on this. Basically it is a way to trigger a script whenever a field changes. i have seen this possible with a plug-in, but never with calculation. You ought to give some detail on it, and maybe a sample file. This would be GREAT.

LR

[BobWeaver]

It's not really triggering a script, but it's as close as you can get to it in Filemaker without a plug-in. The same technique is used to generate audit trails. On that topic, there are a couple of sample files posted in the sample file forum that may be of interest.

[ibiubu]

The trigger solution above worked perfect. It allowed me to have a calc field that perfoms a pretty complicated calculation that can not be stored/indexed, and be able to have its contents copied to another field that can be stored/indexed. And the copying from the calc field to the other field takes place automatically anytime the calc field changes.

This now allows me to have a value list in another related database that can be based on the previously unusable non-stored calc by now having it based on the field with the copied calc information.

Works like a charm. If anyone is interested, based on the instructions above, I created a quick test file to make sure I understood how it worked. The file is located at http://63.226.105.147/web/Trigger.fp5.zip

LR

[CobaltSky]

Hi,

Glad you've had some success with the technique I suggested. That's good!

However, bear in mind that it has a limitation insofar as it will only trigger off changes which are local to the current record.

So, for example, if the trigger field references a global value, only the current record will update when that value is changed. If the trigger field references a value in another file (via a relationship), the triggering may be limited to changes amde to the local value from a field placed on a layout in the current record.

It is for this reason that I suggested choosing another value (perhaps a general value such as modification time) as the point of reference for the trigger.

The table of field dependencies that FileMaker maintains in the background and uses to track cascading recalculations is confined to stored calculations. Therefore it is important to note that although the approach can work very well in some instances, it is theoretically imperfect in that a value in another file can change remotely and this will not necessarily trigger the relookup.

In implementations where this limitation is of concern, a combined approach which includes a refresh script (eg based on a Replace Contents script step) as an adjunct to the triggering technique is a possible solution. This can either be triggered periodically by users or run routinely at start-up. shut down or linked to some other action.

In cases where data on the current records is changing frequently, an added measure of this type is unlikely to be necessary.