I want to create a relationship from File A to File B, based on the result of a calculation in File A. The problem is that this calculation cannot be indexed because it is a Case calc based on the result of another Case using a selfjoin :

c_Activity = Case (selfjoin = 1;No stock,Stock Available).

c_Relation2key = Case (c_Activity = No Stock;"",Product_ID)

As this record is based on a self join, I want this record to be updated in all the lines of my line items where the Product_ID is used.

I Did read the article on indexing not indexable field but cannot imagine how to make it work on my file.

Please help...

The simplest way is to use a script to set another field to the result of this calculation. Since this other field can be indexed you will be okay. But, you will need to run the script whenever the data in the calculation changes.

OK for the script set up a new field with the resut of this last calculation. I had this in mind and was planing to use do-script plug-in to execute it whenever the user exit a field "quantity" (order, puchase order,...).

But looking to the article in the Article Threads, I wondered if there was another way to acomplis this.

Thanks for the help, Bob.

Depending on your situation, you may also be able to use a lookup field, and hence avoid having to run a script, but it gets a bit tricky and doesn't work in every case.

Very good analysis Bob.

I did perform a lookup from the residual quantity shown in the Product File as a first time. Then a case status to show the Product ID if Qty>0 or not.

But as you said, this lookup didn't actually updated correctly (some records where, some not).

What I did is making a lookup from the residual quantity using a selfjoin from the line item, and it works perfectly.

Thank you very much again for your clever analysis.

Now, can you remember me (I already saw a post on this) how you set a lookup to automatically lookup ?

Suppose FieldU is an unindexable field and you want to transfer its value to FieldX which is an indexed lookup field. You will need to have these fields:

SerialNo -- autoenter serial number field

ModTime -- Autoenter modification time

LookUpKey -- Calculation = (ModTime*0)+SerialNo

Create a relationship called sjLookup with the primary key (left side) field: LookUpKey, and match (right side) field: SerialNo

Then define FieldX to lookup its value from FieldU via this relationship.

Now, here's the catch. It will only be triggered when something is changed within the current record. Changes in a related record won't cause the lookup to trigger, and changes to any other record in the current file won't cause a relookup either. So, this may not help you if you want the lookup field to update when one of those things happen. In that case, you need to use a script.

So, this may not help you

It surely does. Thanks muchy