GetNthRecord is set up wrong, it needs this syntax: GetNthRecord ( FIeldname ; recordnumber )
It cannot selectively match to a record by a value in a field. It has to refer to another record by recordnumber.
Instead, you may need to set up a relationship that allows your current record to access data in another record in the same table. This would require setting up a "self join" relationship using a new Tutorial: What are Table Occurrences? of your table in manage | Database | relationships or you'd need to use ExecuteSQL to access the desired record.
I was thinking if that could work which obviously didn't having a GetNthRecord without the correct syntax. If I create a table recurrence of my existing table, do which relation should I use or can I just use my current relation by having ID# >-------- ID#?
You'll need a relationship that matches either matches to the one record your calculation needs to use to calculate this value or that always makes that related record the FIRST related record. A relationship that only matches by an id number field isn't likely to work the way you need for this.
I don't know enough about how your tables are designed to be specific at this point.
So I made a recurrence of my existing table where my fields are, I used the relationship like TABLE1::ID# >-------<REC|TABLE1::ID# and TABLE1::Meters >------<REC|TABLE1::Meters, TABLE1::B_Depth >------<REC|TABLE1::B_Depth and TABLE1::H_Depth >------<REC|TABLE1::H_Depth. Then I used a formula to compute for Meters using:
If(Start = "True" and Rec|TABLE1::End = "False"; ( H_Depth - Rec|TABLE1::H_Depth);0 ))
I know there is something wrong in here but can't identify what or what I need to do to make this subtract the correct value.
Please start over and describe exactly how your table is structured and how you plan on using it. If you use relationships to match to different data, you aren't likely to be also using Get ( RecordNumber )
when you work with Get ( RecordNumber ) and GetNthRecord, you are working with records within the current found set. (and the calculations, BTW, must be unstored calculations or they won't update.)
If you use relationships to access data in another table, you are now matching to a record whether or not it is part of the current found set and your relationship must work to access the correct record out of all the records currently in your table.
To start over. I have a table (Table name is Project) which contains ID#, B_Depth, H_Depth, Measures, Hours, D_Start, D_End, Time, and Date as fields.The D_Start and D_End gives a computed result "TRUE" or "FALSE". If the value of D_Start is "TRUE" it will take the value of H_Depth (in the current record number) and will subtract it to the of H_Depth where the next value of D_End is "TRUE" (hence my thinking before of using IF(D_Start = "TRUE"; GetNthRecord(H_Depth; <RECORD NUMBER OF THE NEXT D_End = "TRUE">) - H_Depth). But using that syntax is not going to work because of how we use GetNthRecord and Get(RecordNumber) syntax. So, the idea is to check first if the value of H_Depth is "TRUE" in the current record number and looks for the next value of D_End = "TRUE" and takes the value of H_Depth in that record number where D_End = "TRUE".
Does ID# serve as the primary key? Is it a different, unique value in every record in the table? Or do all the records in a given set have the same value in this field?
and will subtract it to the of H_Depth where the next value of D_End is "TRUE"
And the problem here is that next value. Let's say you have several thousand records in your table. How do we find the one record that we want with that value. Looking for "True" isn't enough as there could be many records with the value true. There might even be more than one record with the value "True" in the set of records that we are working with and that also has to be handled.
I need broader view of how you intend to use this data and what it represents. What I think I am looking at here is that for a given "project" you are boring a hole or tunnel that takes time to bore (Does not look like you are drilling through lumber here..)
And you then have a set of records for the same "Hole" that each records data on the depth of the bore.
Is that correct? Or at least something similar?
If that is right, you need two different ID fields, One that identifies the project or the specific hole being bored and one that uniquely identifies each record. If we have both of those defined in the table, we can set up a relationship that matches to all records for the same Hole that have a specific value (True) in a different field and that has a Primary Key (serial number) greater than the current records. This relationship can then be sorted in descending order.
That relationship will then be able to access the next record of the group that has "true" as a value.
Get (RecordNumber) and GetNthRecord will not be used in this approach.
Please also note that it's possible that a completely different approach might work that uses summary fields.
You are spot on where our engineer wants to use this search for. The ID# is assigned for a specific job, so if I have 5 different jobs I would have 5 different ID#s assigned for each bore and could have thousand of records for 1 job alone. So I need to create a new field that would have a serial number, right?
That's the idea. For new records, it's just a matter of defining a field and selecting the serial number field option on the auto-enter tab in field options. But if you have existing records for which this needs to work, you'll need to show all records and use Replace Field Contents to assign the serial numbers to them.
Then your relationship would look like:
YourTable::JobID = YourTable 2::JobID AND
YourTable::constTrue = YOurTable 2::D_Start AND
YourTable::SerialNumb < YourTable 2::SerialNumb
constTrue is a calculation field that always returns the same value, "True" in this case if you are still using text in these fields instead of a number for the boolean value. YourTable 2 is a new Tutorial: What are Table Occurrences? of YourTable and you'd double click the relationship line to open the Edit Relationship dialog where you'd specify an ascending sort order on YourTable 2::SerialNumb.
This will link to the records in your table that have the same JobID, True in D_Start and have a serial number greater than the current record. The sort then makes the related record with the smallest serial number, the first such related record. Now your calculation can refer to a field in YourTable 2 to access the "next" record.
I did all the changes with my table and the relationship between my Table and the Table Occurrence and used this calculation for Meters:
If(Drilling_Start = "True" and Drilling End = "False"; GetNthRecord(Rig_Data 2::Hole_Depth; Get(RecordNumber)+1) - Hole_Depth;"" ))
and the result for this calculation but the result is a " ? ", should I use a different approach for my calculation?
I deleted all the existing record and imported a new set of data (thinking that it was the old data that was causing it) but now instead of the "?" I'm now getting some numbers but they are way to far from the right answer and as the record number goes bigger, the results becomes "?". I'm not really sure how to get this one running correctly.
I have stated before that with this method you DO NOT use get ( RecordNumber ). Nor do you use use GetNthRecord.
If you post your exact relationship so I can get the names right, I'll take a stab at what the calculation will look like.
Here's a sample of the relationship between my table
The relationship is defined as