1 2 Previous Next 15 Replies Latest reply on Oct 22, 2013 1:18 PM by DranLang

    Search for the next record

    DranLang

      Title

      Search for the next record

      Post

           I'm working on a filter of some sort.  I currently stuck on the search process so I could subtract a value of a field with another value.

           In my table I have Id#, B_Depth, H_Depth, Start, End, and Meters.  If Start = true it will look for the value of H_Depth where the next value of End = True, then subtract it with the value of H_Depth in the current record number.

           Below is how my formula looks like but it's giving me  "?" instead of the actual difference of the two values.

           Let(R=Get(RecordNumber);
           If(R> 0;

           If(Drilling_Start = "True";GetNthRecord(Hole_Depth; Drilling End="True") - Hole_Depth; 0)))

           I'm sure it's my GetNthRecord that has an error and I don't know how to make it work.

        • 1. Re: Search for the next record
          philmodjunk

               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.

          • 2. Re: Search for the next record
            DranLang

                 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#?

            • 3. Re: Search for the next record
              philmodjunk

                   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.

              • 4. Re: Search for the next record
                DranLang

                     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(Get(RecordNumber)> 0;

                      

                     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.

                      

                • 5. Re: Search for the next record
                  philmodjunk

                       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.

                  • 6. Re: Search for the next record
                    DranLang

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

                    • 7. Re: Search for the next record
                      philmodjunk

                           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.

                            

                      • 8. Re: Search for the next record
                        DranLang

                             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?

                        • 9. Re: Search for the next record
                          philmodjunk

                               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.

                          • 10. Re: Search for the next record
                            DranLang

                                 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(Get(RecordNumber)> 0;

                                 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? 

                            • 11. Re: Search for the next record
                              DranLang

                                   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.

                              • 12. Re: Search for the next record
                                philmodjunk

                                     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.

                                • 13. Re: Search for the next record
                                  DranLang

                                       Here's a sample of the relationship between my table

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                  • 14. Re: Search for the next record
                                    DranLang

                                         The relationship is defined as

                                          

                                    1 2 Previous Next