The record number is the absolute/physical record number for a record. It is automatically assigned to each new record and remains constant for the record until the record is deleted. Record numbers start at zero. They are not unique because record numbers of deleted records are reused for new records. They also change when the database is compacted or repaired.

There is no direct way to get the record numbers via SQL but the following approach can be used:

1. First create a method that can be called via SQL. This function can be called for example "num_rec" but must match the name used in the SQL code.
   
   

   ` start num_rec method C_LONGINT($0;$1;$2;$3) C_POINTER($Table;$Field) $Table:=Table($1) $Field:=Field($1;$2) QUERY($Table->;$Field->=$3) If (Records in selection($Table->)=1) $0:=Record number($Table->) End if ` end of num_rec method



2. The method created in step 1 must have the "Available through SQL" attribute checked via the method properties.
   
   



3. With the above method available via SQL you could then do a SQL query as shown here:
   
   

   ` start of sample query method ARRAY LONGINT(tmpArray;0) $numTable:=Table(->[Partners]) $numField:=Field(->[Partners]ID) Begin SQL    SELECT {fn Num_Rec(:$numTable,:$numField,ID) as NUMERIC} AS RecNum    FROM PARTNERS    WHERE Partnership_ID=1    INTO :tmpArray End SQL ` end of sample query

   
   
   The above code in step 3 first sets up some variable outside of the SQL block of code that are used within the SQL code; those variables are $numTable and $numField.
   
   Inside of the SQL block of code, the SELECT statement is calling the Num_Rec method using the "{fn }" keyword and passing in three variables: the table number to search, a field to search, and a unique value to use when searching. In this example the ID field is a unique value.
   
   Using that information, the Num_Rec method queries the table and returns the record number which the SQL code then uses as the record number.