KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: How to get record numbers via SQL
PRODUCT: 4D | VERSION: 11.4 | PLATFORM: Mac & Win
Published On: November 5, 2009
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

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.