How Tos

• I saw a lot of times that a lot of people use the different ways to get the data from the database in a not really performant way. Or they write statements that are not needed and this way they create some confusion in the code.
  This stimulated me to write this blog to explain with examples how (not) to use the different statements and when (not) to use them.
  Most points are with SQL in mind. But some of them are also valid for native DB.
  
  
• CLEAR, INIT and RESET: Lets start with the difference between these 3:
  
  
  • TheTable.RESET: this is used to remove all FILTERS (also in different FILTERGROUP's) from a record-variable. It also resets the SETCURRENTKEY to the primary key.
    IMPORTANT: DO NOT USE this statement for clearing the fields in a record. It will NOT clear them.
    
    
    • VERY WRONG: If the record has some data in it before the RESET-statement, it will keep these values.

      TheTable.RESET;
      TheTable."Primary Key" := Some Value;
      TheTable."Field N" := Some Value;
      TheTable.INSERT(FALSE);

    • CORRECT:

      CLEAR(TheTable); // also clears the primary-keyfields
      TheTable."Primary Key" := Some Value;
      TheTable."Field N" := Some Value;
      TheTable.INSERT(FALSE);

  • TheTable.INIT: this statement clears all fields in the table EXCEPT THE PRIMARY-KEY FIELDS! It does NOT touch the FILTERS or CURRENTKEY! So if you want to insert a new record, keeping the primary key-fields already in the record-variable, this is the command to use.
    
    
  • CLEAR(TheTable): this statement does a TheTable.RESET AND a TheTable.INIT AND clears the primary keyfields. I almost always use this statement to INSERT a new record. It is the only command that also resets the CHANGECOMPANY-statement.
    
    
• GET: This command is the best to use if you want to search the record using its primary key. You can also filter on the primary key and use FINDFIRST. The SELECT-statement sent to SQL is the same. But the GET requires less coding and is easier to read. No RESET-SETCURRENTKEY-SETRANGE are needed. The GET does NOT EVEN consider them.
  
  
  • WRONG (because it creates confusion):

    TheTable.RESET;
    TheTable.SETCURRENTKEY(...)
    TheTable.SETRANGE/SETFILTER
    TheTable.GET(...);

  • CORRECT:

    TheTable.GET(...);

  • In case you want to do a GET and even if you don't have a record, you want to continue with your code, you can do this:
    
    
    • Version 1

      CLEAR(TheTable);
      IF TheTable.GET(....) THEN ;

    • Version 2

      IF NOT TheTable.GET(....) THEN
        CLEAR(TheTable);

    Both versions will have a clean record in case they don't find a record. No dirty data will be in it after this code. Personally I prefer the first version: I prefer FIRST to clean the record and THEN to get the data (if available).
    
    
• The other commands need filtering, so before I start explaining them, I give a good programming practice that is valid for all of them. To make the code easy to read/interpret/review/fix/... , it is best to put the commands influencing the filtering together starting with a RESET. This makes sure you never have to start searching if some other part of the object has put some filters on it and if these filters should be there or not in your part of the code. I always use them together. This makes sure to other programmers I didn't forget to put the SETCURRENTKEY.

  TheTable.RESET;
  TheTable.SETCURRENTKEY(...); // even if you want to use the primary key
  TheTable.SETRANGE(...) or TheTable.SETFILTER(....)

• Some remarks
  • FIND,FINDSET opens a cursor on SQL. Opening a cursor is quite heavy, so if you can avoid it, it is better.
  • The following commands are examples to get info when you DON'T want to lock records. Later I give more info if you want to lock records.
  • FINDSET gets the first N records in 1 go. N is defined in File=>Database=>Alter=>Tab advanced=>Caching=>Record Set.
    
    
    
• FIND('-'): This statement should not be used anymore (there are a few exceptions I will explain later), because there are better statements like FINDFIRST and FINDSET.
  
  
• FIND('+'): This command should not be used anymore (there are a few exceptions I will explain later), because there are better statements like FINDLAST.
  
  
• FIND('='): This statement is a little strange. It almost works like the GET using the primary key values to find a record. You use it by filling up the fields of the primary key and only then doing a FIND('='). It ALSO considers the filters on the record! It can be useful if you already have the record you need in the variable, and you want to refresh it.
  
  
• FIND('>') / FIND('<'): This bases itself on the current values in the primary key of the record to get the next or previous record, it ALSO considering the filters. Better use NEXT or NEXT(-1).
  
  
• FIND('=<>'): This is the worst of all. It first tries FIND('=') and if it doesn't find a record, it does a FIND('<') and if it doesn't find anything, it does a FIND('>'). TO BE AVOIDED!
  
  
• FINDFIRST: This is THE statement to use if you only need the first record. But NEVER use it if you need to loop records.
  
  
• FINDLAST: This is THE statement to use if you only need the last record. But NEVER use it if you need to loop records.
  
  
• FINDSET: This is THE statement to use if you want to get a set of records. It will get the first N records in one go. If the actual number of records is smaller, it will have all the records on your client at once. N is defined in File=>Database=>Alter=>Tab Advanced=>Caching: Record set
  
  
• ISEMPTY: This is THE statement to use if you want to check if there is at least one record in the (filtered) table.
  
  
• COUNT: This statement is used if you want a precise count of the number of records in the (filtered) table. It is heavier for the server to execute than the COUNTAPPROX.
  
  
• COUNTAPPROX: This statement is used if you want an approximate count of the number of records in the (filtered) table. It can be used for a progressbar. It is lighter for the server to execute than the COUNT.
  
  

• You need to check if there are ANY records in your (filtered) table, but you don't need any of them to work on. There are a lot of possibilities that all work, but performance can be a big problem
  
  
  • (VERY) WRONG: because it counts ALL records

    IF TheTable.COUNT > 0 THEN ... // or COUNTAPPROX

  • WRONG: It returns a SET of records to the client

    IF TheTable.FINDSET THEN ...

  • (LESS) WRONG: Until some versions ago (I don't remember which), you would use this because there was no alternative. This opens a cursor on SQL.

    IF TheTable.FIND('-') THEN ... // or FIND('+')

  • (STILL) WRONG: It doesn't open a cursor in SQL, but it still returns 1 record (if there is at least 1 available)

    IF TheTable.FINDFIRST THEN ... // or FINDLAST

  • CORRECT: it NEVER returns a record. It just returns a boolean to say there are/are not records.

    IF NOT TheTable.ISEMPTY THEN ...

• You want ONY the first/last record (if it exists), but NEVER more.
  
  
  • VERY WRONG: It returns a set of records to the client

    If TheTable.FINDSET THEN ... // to get the last record , you would need
    to put ASCENDING(FALSE) before calling FINDSET (later more on this
    because it is even MORE WRONG than VERY WRONG)

  • WRONG: This opens a cursor on SQL.

    If TheTable.FIND('-') THEN ... // or FIND('+')

  • CORRECT: it doesn't open a cursor and it returns only 1 record

    If TheTable.FINDFIRST THEN ... // or FINDLAST

• You want ALL records in ASCENDING order
  
  
  • VERY WRONG: The FINDFIRST does not open a cursor and returns ONLY the first record. The first time NEXT is called, NAV sees it hasn't a cursor and creates it in that moment.

    IF TheTable.FINDFIRST THEN
      REPEAT
        ...
      UNTIL TheTable.NEXT = 0;

  • WRONG: This will give you 1 record at a time.

    IF TheTable.FIND('-') THEN
      REPEAT
        ...
      UNTIL TheTable.NEXT = 0;

  • CORRECT: This will give you the first N records in one go, and after that 1 by 1.

    IF TheTable.FINDSET THEN
      REPEAT
        ...
      UNTIL TheTable.NEXT = 0;

• You want ALL records in DESCENDING order
  
  
  • MORE WRONG than VERY WRONG: It will generate a run-time error (try it out to know the error-message)

    TheTable.ASCENDING(FALSE);
    IF TheTable.FINDSET THEN
      REPEAT
        ...
      UNTIL TheTable.NEXT = 0;

  • CORRECT: version 1. Use this version if possible. It uses a FINDSET to get the records in a faster way and save them in a temptable. Then it loops the temptable in DESCENDING order. This can be useful if the number of records is <= N (the number of records returned by FINDSET in one go). It is also best NOT to use this if there are a lot of records because all those records will be first saved in a temptable and thus on the client.

    IF TheTable.FINDSET THEN
      REPEAT
        // save the records in a temptable
        tmpTheTable := TheTable;
        tmpTheTable.INSERT(FALSE);
      UNTIL TheTable.NEXT = 0;
     
    // now loop the temptable
    tmpTheTable.RESET;
    tmpTheTable.ASCENDING(FALSE);
    IF tmpTheTable.FIND('-') THEN
      REPEAT
        ...
      UNTIL tmpTheTable.NEXT = 0;

  • CORRECT: version 2

    TheTable.ASCENDING(FALSE);
    IF TheTable.FIND('-') THEN
      REPEAT
      ...
      UNTIL TheTable.NEXT = 0;

• First and for all some general advice for writing to the DB
  
  
  • Start a transaction as late as possible (meaning : first read all the data without locking anything and after that, start writing).
  • Lock as little as possible (if you change 1 record in a 10MB records table, why lock all those records if you can only lock 1?).
  • Make your transactions as fast as possible (putting SLEEP-statements in a transaction is VERY (!) BAD).
  • Make your transactions as small as possible (ok, not too small : if you need to change 1000 records in a table, don't put a COMMIT after each write).
  • Make your transaction so, that each transaction contains all writes it should do in 1 block or not at all.
    
    
• IMPORTANT REMARK:
  
  
  • LOCKTABLE (or FINDSET(TRUE,...)) works differently from LOCKTABLE in native. In native it blocks the table completely. In SQL, it means that from that point on, all records read in that table will be exclusively locked, BUT it will not lock records in other tables. It is also possible some other records are locked because of the locking mechanism in SQL on which we have no control at all (at least not in C/AL).
    
    
• You want to modify 1 record.
  
  
  • WRONG : This will generate 2 (!) SELECT statements in SQL. The first SELECT is a NO-LOCK and will be executed for the GET-statement. The second SELECT will be EXCLUSIVE-LOCK and will be executed for the MODIFY. After this, an UPDATE-statement will be executed. The second SELECT is needed to exclusively lock the record, to check if no one else changed the record between the GET statement and the MODIFY statement.

    // You don't have a LOCKTABLE on it (a LOCKTABLE on another table will
    NOT lock current table
    TheTable.GET(...); // or also FINDFIRST,FINDLAST
    TheTable."Some Field" := 'Some Value';
    TheTable.MODIFY(FALSE);

  • CORRECT: The GET will generate a SELECT with EXCLUSIVE-LOCK. The MODIFY will generate an UPDATE-statement

    TheTable.LOCKTABLE;
    TheTable.GET(...); // or also FINDFIRST,FINDLAST
    TheTable."Some Field" := 'Some Value';
    TheTable.MODIFY(FALSE);

• You read 1 record and optionally you change it.
  
  
  • CORRECT in case the chance is quite low that you need to change the record so you avoid locking when it is not needed.

    // You don't have a LOCKTABLE on it (a LOCKTABLE on another table will
    NOT lock current table
    TheTable.GET(...); // or also FINDFIRST,FINDLAST
    IF to be changed THEN BEGIN
      TheTable."Some Field" := 'Some Value';
      TheTable.MODIFY(FALSE);
    END;

  • CORRECT in case most of the time you need to change the record to avoid a second SELECT in SQL

    TheTable.LOCKTABLE;
    TheTable.GET(...);  // or also FINDFIRST,FINDLAST
    IF to be changed THEN BEGIN
      TheTable."Some Field" := 'Some Value';
      TheTable.MODIFY(FALSE);
    END;

• You read a set of records and you need to change ALL records
  
  
  • VERY WRONG: first you need to use FINDSET (see before for more info on this). You are using the same record-variable to change the field. It can happen that you skip records, that you process records more than once, that you get into an endless loop! And also you didn't lock the table...

    IF TheTable.FINDFIRST THEN // or also FIND('-')
      REPEAT
        TheTable."Some Field" := 'Some Value';
        TheTable.MODIFY(FALSE);
      UNTIL TheTable.NEXT = 0;

  • WRONG: you didn't lock the table, so the MODIFY will create an extra SELECT

    IF TheTable.FINDSET THEN
      REPEAT
        TheTable2 := TheTable;
        TheTable2."Some Field" := 'Some Value';
        TheTable2.MODIFY(FALSE);
      UNTIL TheTable.NEXT = 0;

  • CORRECT: You lock the table WITH the first TRUE in the FINDSET. The second TRUE is only needed in certain circumstances but I advice to use it always.

    IF TheTable.FINDSET(TRUE,TRUE) THEN
      REPEAT
        TheTable2 := TheTable;
        TheTable2."Some Field" := 'Some Value';
        TheTable2.MODIFY(FALSE);
      UNTIL TheTable.NEXT = 0;

• You read a set of records and you need to change SOME records
  
  
  • The VERY WRONG and WRONG from previous point are also valid here.
    
    
  • WRONG: This example was correct for previous point, but not here because this blocks ALL records you read. LOCKING records is memory intensive and also costs performance because if the extra work on the server and because other users might be blocked by this session even if they want to change other records. It is also a reason for the dreaded DEADLOCKS.

    IF TheTable.FINDSET(TRUE,TRUE) THEN
      REPEAT
        TheTable2 := TheTable;
        TheTable2."Some Field" := 'Some Value';
        TheTable2.MODIFY(FALSE);
      UNTIL TheTable.NEXT = 0;

  • CORRECT: You first read the records and the ones you need to change, you put in a temptable WITHOUT LOCKING ANYTHING.

    // You do NOT have a LOCKTABLE on table "TheTable"!
    IF TheTable.FINDSET THEN
      REPEAT
        // save the records in a temptable
        IF (Record has to be changed) THEN BEGIN
          tmpTheTable := TheTable;
          tmpTheTable.INSERT(FALSE);
        END;
      UNTIL TheTable.NEXT = 0;
     
    // now do a LOCKTABLE. This means that all records of that table that are
    read now, will be EXCLUSIVELY locked
    CLEAR(TheTable);
    TheTable.LOCKTABLE;

    There are 2 ways to loop the records:
    
    Method 1:

    // now loop the temptable. This code uses the version of each record in
    the temptable to change the record in the table. If one of the records
    has been changed in the mean time, it will generate an error. (remark:
    the MODIFY will also generate a SELECT EXCLUSIVE-LOCK before the UPDATE-
    statement. This is necessary because NAV has to check if the version of
    the record is still the same.)
    tmpTheTable.RESET;
    IF tmpTheTable.FINDSET THEN
      REPEAT
        TheTable := tmpTheTable;
        TheTable."Some Field" := 'Some Value';
        TheTable.MODIFY(FALSE);
      UNTIL tmpTheTable.NEXT = 0;

    
    Method 2:

    // now loop the temptable. This code retrieves the record again (with
    EXCLUSIVE-LOCK!) and then changes it. If the record was changed between
    the first read and now, it will NOT generate an error on the MODIFY
    because you will have received the latest version.
    tmpTheTable.RESET;
    IF tmpTheTable.FINDSET THEN
      REPEAT
        TheTable := tmpTheTable;
        TheTable.FIND('='); // or TheTable.GET(primary key). Both commands
    are possible but you need to be sure you don't have filters on TheTable
    (the CLEAR(TheTable) before takes care of that.
        TheTable."Some Field" := 'Some Value';
        TheTable.MODIFY(FALSE);
      UNTIL tmpTheTable.NEXT = 0;

    
    
    
• Using filtergroups: Filtergroups are useful for putting multiple filters on the same field or to hide filters from users.
  
  
  • Some advice: Only start using filtergroups from 10 and up. Filtergroups 0 to 6 are reserved (see the C/SIDE reference guide for more info), but I would keep some extra filtergroups free for the future (until some versions ago, filtergroup 6 was NOT reserved).
    
    
  • Important: multiple filters on the same field behave as AND between them.
    
    
  • Example 1: you want to hide a filter from the user to avoid he can change it

    TheTable.RESET;
    TheTable.SETCURRENTKEY(....);
    TheTable.FILTERGROUP(10); // changes to a filtergroup that the user will
    NOT see
    TheTable.SETRANGE(....);
    TheTable.FILTERGROUP(0); // change back to the default filtergroup. The
    one in which the user may do want he wants
    FORM.RUNMODAL(0,TheTable); // the user will not be able to change the
    filters

  • Example 2: You need to put multiple filters on a field. E.g. You want all records in which a certain field starts with an A and ends with a B and somewhere in the middle is a C.
    
    
    • Of course you might create this SETFILTER. But how would you SETFILTER more complex queries?

      TheTable.RESET;
      TheTable.SETCURRENTKEY(...);
      TheTable.SETFILTER("The Field",'A*C*B');
      IF TheTable.FINDSET THEN

    • The same example with FILTERGROUP

      TheTable.RESET;
      TheTable.SETCURRENTKEY(...);
      TheTable.FILTERGROUP(10);
      TheTable.SETFILTER("The Field",'A*');
      TheTable.FILTERGROUP(11);
      TheTable.SETFILTER("The Field",'*C*');
      TheTable.FILTERGROUP(12);
      TheTable.SETFILTER("The Field",'*B');
      TheTable.FILTERGROUP(0);
      IF TheTable.FINDSET THEN

      
      
      
• Reading/inserting/modifying/deleting records in another company than the current one.
  
  
  • IMPORTANT: Remember that ONLY that record will be in the other company. Any other record-variable accessed by variables in the code in that record or in the Tablerelation of a field will be on the CURRENT company.
    
    
  • (VERY) WRONG: you should NEVER use triggers when inserting/modifying/deleting a record that is in another company. All the code behind the triggers/Tablerelations will work on the CURRENT company.

    CLEAR(TheTable);
    TheTable.CHANGECOMPANY('Some Other Company');
    TheTable.VALIDATE("Field 1",'Some Value');
    TheTable.VALIDATE("Field 2",'Some Value');
    TheTable.VALIDATE("Field 3",'Some Value');
    TheTable.INSERT(TRUE);

  • CORRECT: to insert a record into another company (MODIFY and DELETE work in the same way).

    CLEAR(TheTable);
    TheTable.CHANGECOMPANY('Some Other Company');
    TheTable."Field 1" := 'Some Value';
    TheTable."Field 2" := 'Some Value';
    TheTable."Field 3" := 'Some Value';
    TheTable.INSERT(FALSE);

  • CORRECT: Reading 1 record in another company

    CLEAR(TheTable);
    TheTable.CHANGECOMPANY('Some Other Company');
    TheTable.GET(....);

  • CORRECT: Reading/deleting/modifying 1 or multiple record(s) in another company

    CLEAR(TheTable);
    TheTable.CHANGECOMPANY('Some Other Company');
    TheTable.SETRANGE("Field 1",'Some Value');
    TheTable.FINDSET;
    REPEAT
     
    UNTIL TheTable.NEXT = 0;
     
    //Or
     
    TheTable.DELETEALL(FALSE); // No triggers!!!!!
     
    //Or
     
    TheTable.MODIFYALL(FALSE); // No triggers!!!!!

    
    
    
• You want the total inventory of a certain item in a certain location. There are 2 methods to do the same thing. Both have pro- and contra.
  
  
  • CORRECT: (PRO : you don't need to know the index ; CONTRA : normal fields you have to assign a value (or do a GET) and flowfilters you need to filter)

    CLEAR(recItem);
    recItem."No." := '1000'; // or also recItem.GET('1000');
    recItem.SETRANGE("Location Filter",'BLU');
    recItem.CALCFIELDS(Inventory);
    MESSAGE('%1',recItem.Inventory);

  • CORRECT (PRO : always filtering and not assigning values ; CONTRA : you need to know the index on which the SIFT-field has been defined):

    recItemLedgerEntry.RESET;
    recItemLedgerEntry.SETCURRENTKEY("Item no.","Location Code");
    recItemLedgerEntry.SETRANGE("Item No."','1000');
    recItemLedgerEntry.SETRANGE("Location Code",'BLU');
    recItemLedgerEntry.CALCSUMS(Inventory);
    MESSAGE('%1',recItemLedgerEntry.Quantity);

    
    
    
• If you need 2 or more recordvariables of the same record, you can also use an array in stead of 2 variables. The 2 variables work independently of each other like using 2 variables. Some examples.
  • recRecordVar1.RESET; => recRecordVar[1].RESET;
  • CLEAR(recRecordVar1); => CLEAR(recRecordVar[1]);
  • recRecordVar1.SETRANGE(... => recRecordVar[1].SETRANGE(...
    
    
  • You can also use a variable to indicate the element. An example:
    intSomeInteger := 1;
    recRecordVar1.RESET; => recRecordVar[intSomeInteger].RESET;
    
    
    
• The array technique can also be used on temporary record-variables. They work also independently of each other like on a real table. BUT THERE IS ONLY 1 TEMPTABLE! This means that if you create a record with element [1], it will be available in element [2]! An example (This is the very first code on the temptable, so there are no records in it!).

  CLEAR(recRecord[1]);
  recRecord[1]."No." := '1');
  recRecord[1].INSERT(FALSE);
  recRecord[2].RESET;
  recRecord[2].FINDFIRST;
  MESSAGE('%1',recRecord[2]."No."); // this will show the value of the record I
  created in element [1]!

  
  
  
• Make a total amount per "Gen. Bus. Posting Group" and "Gen. Prod. Posting Group" of table 15:"G/L Entry".
  
  
  • In SQL it would be easy:

    SELECT "Gen_ Bus_ Posting Group","Gen_ Prod_ Posting Group",sum(Amount)
        FROM dbo."CRONUS International Ltd_$G_L Entry"
        GROUP BY "Gen_ Bus_ Posting Group","Gen_ Prod_ Posting Group"
        ORDER BY "Gen_ Bus_ Posting Group","Gen_ Prod_ Posting Group"

  • But in C/AL we can't use SQL-statements (or we must use ADO), so there is another way. I advice to always use this way to do some summing in C/AL.

    recGLEntry.RESET;
    recGLEntry.SETCURRENKEY(...); // try to get a key that can be used to
    find the filtered data in a fast way
    recGLEntry.SETRANGE(.....); // put your filters
    IF recGLEntry.FINDSET THEN
      REPEAT
        // "tmpGLEntry" is the temptable we will use to store the grouping
    totals
        tmpGLEntry.RESET;
     
        // try to get a good key for the filters
        IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Bus. Posting Group") THEN
          IF NOT tmpGLEntry.SETCURRENTKEY("Gen. Prod. Posting Group") THEN ;
          
        // I filter on the records for which I want to group the records
        tmpGLEntry.setrange("Gen. Bus. Posting Group",recGLEntry."Gen. Bus.
    Posting Group");
        tmpGLEntry.setrange("Gen. Prod. Posting Group",recGLEntry."Gen. Prod.
    Posting Group");
     
        IF NOT tmpGLEntry.FINDFIRST THEN BEGIN
          // I didn't find a record, so I have to create a new one.
          // Remember that to insert a record, you have to respect a unique
    primary key.
          // This is done, because EACH "recGLEntry" I read is unique, so I
    can just insert
          // "recGLEntry" in my temptable.
          tmpGLEntry := recGLEntry;
          tmpGLEntry.insert(FALSE);
        END
        ELSE BEGIN
          // I found the record with combination I wanted, so I add the
    field(s) I am SUMming
          tmpGLEntry.Amount += recGLEntry.Amount;
          tmpGLEntry.MODIFY(FALSE);
        END;
      UNTIL recGLEntry.NEXT = 0;
     
    // In the temptable I now have records that contain the SUM for the
    combination I wanted.
    // Of course if you want to sort your data in a certain way, you will
    need a key for it and with SQL you can put property MaintainSQLIndex to
    FALSE.
    // Another way is to create a new table with all the fields and keys you
    need and use that as
    // temptable. You DON'T need to license a new table that you use ONLY
    as temptable!
     
    // Now you can just read the temptable and do what you want to do with
    your totals
    tmpGLEntry.RESET;
    FORM.RUNMODAL(0,tmpGLEntry);

 Print This Page

Author:	Alain Krikilion (alias kriki)
Date:	01/08/2009
First release:	26/07/2009
Views:	17163
Avg. rating:	(rated 143 times)
Discuss this How To in the forum (55 reactions)

	SMS this title to a friend
	Mail this title to a friend

Rate this HowTo

- Please do not vote for the same HowTo more than once. - Please be objective.    poor    excellent