Search an entire NAV database with 10 lines of code

So a friend and I talked about how to search for data in NAV across all tables, and I said, I can do that in 10 lines of code 🙂

That’s a “Show Up or Shut Up” moment. So here goes:

Here presented in an older version of NAV, just to show that this works in most versions:

The idea is simple:
1. Loop through all tables
2. For each table, create a RecordRef
3. Loop through all records in the RecordRef
4. Do a text search with STRPOS inside the FORMAT(RecordRef) text string.

The secret here is that the FORMAT command, when used on a Record or a RecordRef returns a TAB separated string with all the fields from the record.

If you want case in-sensitive search, use LOWERCASE on the SearchValue and the output of the FORMAT command.

This is not fast, optimized or anything other that very complete and only 10 lines of code 🙂

2 thoughts on “Search an entire NAV database with 10 lines of code”

  1. Nice, but your FORMAT(R) only returns the first 1024 chars as far as I know. Otherwise you couldn’t do it in 10 lines, you say?

    Challenge accepted!

    OnRun()
    SearchValue := ‘NaV’;

    Tables.SETRANGE(“Object Type”,Tables.”Object Type”::TableData);
    IF Tables.FINDSET THEN REPEAT
    R.CLOSE;
    R.OPEN(Tables.”Object ID”);
    IF R.FINDSET THEN REPEAT
    FOR FldNo := 1 TO R.FIELDCOUNT DO
    IF STRPOS(UPPERCASE(FORMAT(R.FIELDINDEX(FldNo).VALUE)),UPPERCASE(SearchValue)) 0 THEN
    ERROR(‘Found in %1\Found in field: %2\\%3’,R.RECORDID,R.FIELDINDEX(FldNo).NAME,FORMAT(R));
    UNTIL R.NEXT = 0;
    UNTIL Tables.NEXT = 0;

    I might bend/abuse a single C/AL guideline a tiny bit, but please notice I even had to add an R.CLOSE in my NAV 2009R2, but I also made it case insensitive for free 🙂

    1. Hi Peter,

      FORMAT in NAV2016+ returns as much as you need. Text variables are converted into DotNet String variables with a maximum of a million characters (32 bit limit for wide characters in 16bit).

      Other than that, I would not call UPPERCASE(SearchValue) for each single field, that’s quite an big overhead instead of doing it once at the beginning 🙂

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.