Good Article How to use RecordRef, FieldRef, and KeyRef

Good Article from Emiel Romein

OnRun()
// Unmark as text to test code.

SearchString := 'can';

//Search(DATABASE::Contact,SearchString);
//Search(DATABASE::Customer,SearchString);
//Search(DATABASE::Vendor,SearchString);

//CountRecords;

//MESSAGE(KeyName(DATABASE::"Sales Header"));



Search(TableNo : Integer;String : Text[30])
// This little sample of code will search for a record that matches a filter. In this case it will search either Contact, Customer
// or Vendor for a record that matches the string filter on the Search Name field.

SearchFieldNo := 3; // This is the number of the Search Name field in tables Contact, Customer and Vendor.

SearchIn.OPEN(TableNo); // Create an RecRef referring table with same number as var TableNo. Can be any table in Navision.

//SearchIn.OPEN(DATABASE::Customer,true); // Open a temporary RecRef. Nice fact, this fieldref does not contain any records.
//SearchIn.open(database::customer,false,CompName); // Open a RecRef in another company.

SearchInFld := SearchIn.FIELD(SearchFieldNo); // Create an FieldRef of a field in the table opened above.
SearchInFld.SETFILTER('%1',STRSUBSTNO('@*%1*',String)); // Place a filter on the FieldRef.
                                                        //  @ = Non case sensitive, *%1* = Any part of field.

// Becaurse SearchInFld is referring to the same table as SearchIn, every filter that is placed using this FieldRef also applies
// to the RecordRef.
// To be short, I can now see if records exist within filters.

SearchIn.FIND('-');

// Off course you can now loop the RecRef using repeat's, until's and next's

// Unfortunately it is not possible to open forms based on recordref's. So if you want to show a list of records found, you will
// have to program a form.open for each possible search record. See below for a sample.

CASE TableNo OF
  DATABASE::Customer :
  BEGIN
    Customer.SETVIEW(SearchIn.GETVIEW); // Use this function to set the current sort order, key and filters on a table.
    FORM.RUNMODAL(0,Customer); // Make is a lookup if you want to be able to select a record.
  END;
  ELSE
    ERROR(Text000);
END;



CountRecords()
// This little sample of code will count the records in a table using only one variable.

Object.SETRANGE(Type,Object.Type::Table); // Only Tables have records. What?! You didn't know that?
Object.SETRANGE(Object.ID,1,10); // For this sample, count only records in tables 1..10.
Object.FIND('-'); // IF THEN? Hopefully you have some object in your database!

REPEAT
  Table.OPEN(Object.ID); // Open Record reference to current table.

  MESSAGE(Text000,Object.Name,Table.COUNT); // Show you what was found.
UNTIL Object.NEXT = 0;



KeyName(TableNo : Integer) KeyString : Text[1024]
// This little sample of code will return the name of a key of any table in Navision.

KeyNo := 1; // Keynumber of wich we want to get the name.

Table.OPEN(TableNo); // Create a record reference to a table.
Key := Table.KEYINDEX(KeyNo); // Create a key reference to the key we want to get the name from.

IF NOT Key.ACTIVE THEN // Don't do anything if the key is not active. But you could if you want to.
  EXIT;

FOR i := 1 TO Key.FIELDCOUNT DO BEGIN // Run this loop as much times as there are fields in this key.
  Field := Key.FIELDINDEX(i); // Create a fiel reference to the (#i) field in the key.
  KeyString := STRSUBSTNO('%1%2%3',KeyString,Separator,Field.NAME); // Create a string wich sums all key fields, using the fieldref
                                                                    // name as field name.
  Separator := ',';
END;