14 Replies Latest reply on Oct 28, 2013 3:30 PM by DranLang

Help with calculation and table relationship

Title

Help with calculation and table relationship

Post

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!

• 1. Re: Help with calculation and table relationship

The column you see before the column where 11 is located  is D_End Column and the column before D_End is D_Start column.

• 2. Re: Help with calculation and table relationship

And which column is "job number" and which column is SN? The values in those fields are also part of the relationship match fields so we need to see those values as well in order to determine why the relationship is not matching to the expected record.

• 3. Re: Help with calculation and table relationship

The very first column is job number, the 3 column from the job number is the H_Depth column and last in this screenshot is the SN.  Meters column is where 11 is and followed by Hours column where the value is 0.08.

• 4. Re: Help with calculation and table relationship

Apologies on loosing track of your original thread--where I think I misread by looking at the wrong page and then posted a fully out of line response, but can you post the calculation that is returning the value 11 just so I have all the details in one place?

And what data type are the fields that show "True" or "False"? are they text or number?

Is constTRUE set to return the same data type?

• 5. Re: Help with calculation and table relationship

If(Drilling_Start = "True" and Drilling End = "False";  Rig_Data 2::Hole_Depth - Hole_Depth;" ")                 is the formula I used which returns 11.  construe is of field type text and those fields that shows "True" or "False" are all of the same field type (Text).  Attached below a another screenshot which includes the value of H_Depth showing 11 as the value.

• 6. Re: Help with calculation and table relationship

It's really hard to figure out which column is which in your screen shot due to the lack of column labels.

What I see shows that your relationship is working as it was designed to work. Your relationship matches to a record that:

has the same project ID (all here do)
A serial number greater than 581396
the text "True" in column 20

The only record that meets that criteria is the Last Row in your screen shot.

Thus, your calculation subtracts the value of column 3, Row 2 (0) from Column 3, Last Row (11) to produce a result of 11 as shown in your screen shot.

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".

And that's the key here, we have the wrong match field being paired with construe. It should match to D_End instead of D_Start--so you need to modify your relationship accordingly.

PS. Get ( RecordNumber ) and GetNthRecord could be used, but that takes a different approach and has a number of drawbacks avoided with this relationship based method. The other approach would compute different values just by finding and sorting records differently where this approach will show the same result regardless of how you set up your found set. The record in question could be the only record in the found set and the computed value still accesses the same data from the related record. The other approach can also hit an upper limit where more than a certain number of records in the foundset results in the last records in the found set returning a ? instead of a computed value.

• 7. Re: Help with calculation and table relationship

I did tried changing the relationship  and used consTrue to match D_End but I'm still getting a wrong result as shown in the screenshots below.  Though the first result of Meters is equal to zero which is true ( 0 - 0 = 0 ) but for the second result the value for meters should be 2.59 and not -11.  Below are 2 screen shot that shows the data to where the values of H_Depth is taken from.

• 8. Re: Help with calculation and table relationship
/files/8c391df089/5.1.png 572x740
• 9. Re: Help with calculation and table relationship

It would be useful to add the serial number field from Rig Data 2 as another column in this view. That would identify the specific record to which it is linking and that could provide a useful clue.

Is the serial number in a field of type number or text?

• 10. Re: Help with calculation and table relationship

I included the SN field from Rig Data 2 and it's showing <Index Missing> as displayed below and serial number field is of type number. I also tried text as the field type for the serial number field and gave the same result.

• 11. Re: Help with calculation and table relationship

Field type should be number, or it won't sort correctly.

This clearly shows a problem. Looks like indexing has been disabled for this field for some reason. Open Manage | Database | Fields, find and double click the serial number and see if you can enable indexing on this field on the storage tab.

Ideally, this field should be an auto-entered serial number field and should automatically start indexing when you add it to a relationship.

If this field looks to be correctly indexing check this same setting on each of the other match fields in the relationship.

• 12. Re: Help with calculation and table relationship

For some reason my results won't come out correctly, I followed your instructions and it did made sense and theoretically it should give me the correct answer but it doesn't.  I also tried to have the serial number for RIg Data 2, but it doesn't show any values, i's just showing me blank/empty boxes compared from yesterday's result (<Index Missing>) before I indexed the serial number field.  I also checked and made sure that the fields in the relationships are all indexed, I also tried using "All" and "Minimal"  indexing but didn't have any effect on the results of Meters.

I know you said that there are other approach but this approach of yours is the most consistent and reliable one, Do you think I should a different approach?

• 13. Re: Help with calculation and table relationship

I think you need to trouble shoot the relationship.

You'll need to check each field's value, data type and the relationship definition carefully. It appears that your current relationship does not match to any records in the table.

If you get totally stymied, consider making a copy of your file, deleting all but a 10 or 15 records and then upload it to a sharing site such as drop box with a link posted here so that I can download and take a quick look at it.

• 14. Re: Help with calculation and table relationship

Thanks Phil, I just found out one possible reason why the Meters field won't compute properly it's because one of our engineer keeps on changing the formula and relationship without telling me, and until now they haven't decided which formulas.

I'll leave this as it is right now until they've made up their minds which way to go, I'm not touching this one time for me to move to a next project I have in line for this system I'm trying to build for them.

Thanks a lot Phil, you've been very helpful.