Help with calculation and table relationship
Good morning everyone! I've been struggling for a week now on how to make my math and table relationship work properly and I need help big time, the engineer I'm working with from another department thinks that I with almost 6 months of using FileMaker I can easily come up with a solution for this.
I am developing some kind of filter for him so he can do his job properly, here's the situation:
I have a table that has Job#, H_Depth, D_Start, D_End, Meters and Hours fields. Meters will compute for the meter value IF D_Start = "True" and D_Start = "False". METERs will subtract the value of the current value of H_Depth to the value of the next H_Depth where D_End is equals to "False". Which means, from the current row (record) where D_Start = "True" I will need to find a way how to go to the next row where D_End = "True" and take the value of the H_Depth from that row (record) and subtract the value of H_Depth before that row (record) where D_Start = "True", so I thought I could use the Get(RecordNumber) and GetNthRecord but I was wrong. Phil sugeested of using a recurrence of my table and edit the relationshop.
So I created a recurrence of my TABLE and called it TABLE 2 and edited the relationship as:
TABLE::Job# = TABLE 2::Job# AND
TABLE::construe = TABLE 2::D_Start AND
TABLE::SN < TABLE 2::SN
and sorted TABLE 2 with ascending order using SN.
The result here was much better than what I had originally but my problem now is it's not subtracting the correct value properly so I'm getting the wrong result (I can see that it is actually looking for the next value of H_Depth where the next value of D_Start = "True" where it should be looking for the next value of H_Depth where the next value of D_End = "True".
The sample below is an example of my data and the "11" value in the far right should be 0 since the next value of H_Depth where D_End = "False" is 0 (0 - 0 = 0 and not 11).
Any help here is deeply appreciated! Thank you guys!