Looping through tables and fields in NAV

Hello,

It’s NAV 2013 R2. I’d need to loop through NAV tables to find fields that are disabled (Enabled = ‘No’) getting data into somewhere as csv, excel, report, etc.

The result could be as ‘table id’, ‘table name’, ‘field id’, ‘field name’, ‘Enabled’ = ‘No’

I could loop through tables and fields in SQL but it seems as there is no property as ‘Enabled’ for fields there. If somebody knows if it could be there then please advise.

I found a nice NAV code as below and tried to use temp table and integer to get the data to show it in a simple report to transfer it to excel after. But, there is nothing showing with an error that the record is already open. Could anybody help with the code and how to get the data.

https://dynamicsuser.net/nav/f/developers/12751/looping-through-the-tables-and-fields-data

Thanks

Hi,

Customize Page 7702 Fields or Save as Custom Page.

Do Following Changes -

  1. Add Fields that you need.

  2. Change Source Table View (Page Property to) - WHERE(Enabled=CONST(No))

  3. Save Page.

Run Page and you will get what you need.

I hope it helps. Let me know if you have any questions.

Regards,

Saurav Dhyani

https://saurav-nav.blogspot.com/

Hi Saurav,

Thank you for your suggestions. I’d need a loop through all tables and fields as I would do in SQL looping through sys.tables and sys.columns but there is no ‘Enabled’ property for a field and no FlowField available. So, I’d like to replicate that SQL looping in NAV .

Here is a code below that if working for a first table looping through all columns well but it’s not going to the next table making the nested loop through fields endless for the same first table. Could you please take a look.

Documentation()

OnInitReport()

OnPreReport()

OnPostReport()

Integer - OnPreDataItem()

RecRef.GETTABLE(TempXXX);

IF RecRef.ISTEMPORARY THEN BEGIN

TempXXX.DELETEALL;

END ELSE

ERROR(‘The TempXXX is not temporary!’);

Integer - OnAfterGetRecord()

TempXXX.RESET;

TempXXX.INIT;

varTables.SETRANGE(varTables.Type,varTables.Type::Table);

IF varTables.FINDSET(FALSE,FALSE) THEN BEGIN //FIND(’-’)

REPEAT

varRecordRef.OPEN(varTables.ID,FALSE);

IF varRecordRef.FINDSET(FALSE,FALSE) THEN BEGIN //FIND(’-’)

REPEAT

varFields.RESET;

varFields.SETRANGE(varFields.TableNo, varTables.ID);

IF varFields.FINDSET(FALSE,FALSE) THEN BEGIN //FIND(’-’)

// *** looping successfully through all Table’s fields BUT it’s endless as it’s not going to the next Table *** //

REPEAT

IF NOT varFields.Enabled THEN BEGIN

TempXXX.XXX1 := varTables.ID;

TempXXX.XXX2 := varTables.Name;

TempXXX.XXX3 := varFields.“No.”;

TempXXX.XXX4 := varFields.FieldName;

TempXXX.XXX5 := varFields.Enabled;

TempXXX.INSERT;

END ELSE

CurrReport.SKIP;

UNTIL varFields.NEXT=0;

END ELSE CurrReport.SKIP;

UNTIL varRecordRef.NEXT=0;

END ELSE CurrReport.SKIP;

UNTIL varTables.NEXT=0;

END ELSE CurrReport.SKIP;

Integer - OnPostDataItem()