0

NAV field names from field nos

Michael Hylands

July 20, 2020 17:10

None

Follow

How do I access the information_schema.columns table in JET so I can determine the Field Name from a Field No?

Here is the simplest form of the query but it returns a #VALUE! error:

=@NL("First","information_schema.columns","column_name")

I've tried both my NAV datasource and a generic data source that allows access to the NAV system tables and both say that "information_schema.columns" is an invalid table.

I can successfully query the table in SQL just cannot find a way to access it through JET. Working SQL query:

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'NAV$Purchase Header' and ORDINAL_POSITION = '3'

Thanks!

• Facebook
• Twitter
• LinkedIn

3 comments

• 0
  
  Bryan Robinson July 21, 2020 21:14

  I don't believe that the INFORMATION_SCHEMA tables are part of the NAV database, but rather part of SQL.  Therefore, Jet cannot access any table that is not a table within your NAV instance.  While Jet converts queries to SQL queries, not all SQL functionality is mirrored within Jet Reports.

  You can always use the Browser to review field details to find the field number but it is the reverse of what you want.  You need to know the field name and then can discover the field number.

• 0
  
  Michael Hylands July 22, 2020 18:38

  Thanks Bryan,

  I know how to find them, trying to report off the NAV Change Logs and it reports the Field No.

  Guess I'll have to pull the Field No. and Name data into my own table and do a secondary lookup after the JET report runs.

• 0
  
  Michael Hylands July 23, 2020 23:23

  Found a workaround - create a Rapidstart Package for the table of interest then you can query NAV$Config_ Package Field to lookup Field Name from Field No (Field ID).