Computing for a Field that references another Field
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.