### Title

Computing for a Field that references another Field

### Post

Good morning everyone! I'm currently working on a project where I need to compute for a certain value which involves other field values. I have a table called R_Data which contains Job# (of type text), B_Depth, H_Depth, RPM, Flow, Bottom, D_Start, D_End, Mode, and Meters as fields. I also have SN (serial number which is auto create) and construe (always returns "True" value).

Meters will compute using the logic/formula of "Meters = If(D_Start = "True" and D_End = "False"; <subtract H_Depth value of the current record/row to the H_Depth value of the record/row where D_End is "FALSE").

I created a recurrent table of R_Data and called it R_Data 2 and set up relationship as:

R_Data::Job# = R_Data 2::Job#

R_Data::construe = R_Data 2::D_End

R_Data::SN < R_Data 2::SN (and sorted R_Data 2 in Ascending order using SN)

This approach needs indexing so I indexed the fields defined in the relationship. My problem is "Meters" field won't compute properly since it has a formula that tests if D_End is equal to "FALSE", but since D_End is uses the formula/logic "D_End is = IF Bottom is "TRUE", check IF the next value of Bottom is "FALSE" then it returns "TRUE" otherwise returns check IF Mode is not equal to the previous value of Mode, and if Mode is not equal to the previous value of Mode it returns "True" else it returns "False".

My problem is that since I need to have D_End to be indexed so it will return the value of H_Depth but it keeps on reverting D_End to a Unstored because Mode is unstored calculation.

Is there any other way to do this math or if there is a way I can correct my math or relationship issues using this approach?

Any help/ideas will be highly appreciated.

To get the field to be indexed, you may need to change the field into a text field and use a trigger controlled script to update the field when a value in the other record is changed such that the value needs to change from true to false or the opposite.