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 🙂

Post navigation

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

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 🙂