I’m using a very simple code:
Assign SalesHeader table to record ref
FieldRef := RecRef.FIELDINDEX(FieldNo); where FieldNo = 3.
That is supposed to return field 3 (SalesHeader.No.) but instead it’s returning field 2 SalesHeader.Sell-to Customer No.!!
And when I use the debugger and zooms into RecRef variable then it incorrectly lists “Sell-to Customer No.” as the 3rd field and “No.” as the 2nd. field!
I’m using NAV 2009 SP1 on SQL Server 2008.
Did anyone else experience this?
Hi Erik,
What are your’e first and second fields? Maybe the recordrefs always assigns the primary key fields to the first field Nos?
That could be a SQL “Feature”… did you try the same on a Native DB?
-edit- Off course that’s just a guess…
Hi Erik,
I checked it in NAV (Native and SQL) and the result is the same. I also checked Field 1 and Field 2 and those are “Document Type” and “No.”, so apparently NAV puts the primary key fields first in the Fieldrefs
I have also confirmed in a 2009 SP1 database.
MyRecordRef.OPEN(36);
MyFieldRef := MyRecordRef.FIELDINDEX(2); // MyFieldRef.NAME returns No.
MyFieldRef := MyRecordRef.FIELDINDEX(3); // MyFieldRef.NAME returns Sell-to Customer No.
No mention of this fact anywhere in the internal C/SIDE Help.
Mine was in 2009 R2 by the way [:)]
It’s really strange, I have used RecRef and FieldRef a lot and I have never before noticed this behavior.
Is anyone reporting this to MS?
Actually, this would be a non-documented feature and not a bug.
I mean, the fact that RecordRef.FIELDINDEX() implicit uses the primary key.
Specifying which key (with KEYINDEX) to loop through allows us not to be bound by the actual order of the fields in the table and that’s cool 
Of course MS could have mentioned that the primary key is used if we use FIELDINDEX directly on a RecordRef.
I can see your issue if you want to loop through the fields using the fields’ real order in the table.
But you should use a Field record for that purpose to be sure of the field ID.
/Søren
It all makes perfect sense, actually…
RecordRef is a pointer to a record (and not the table/fields), viewed directly in the database, and records are stored with the primary key fields as the first fields. This can also be seen when doing this query in the SQL Server Management Studio:
SELECT * FROM [DBNAME].[dbo].[Company$Sales Header]
GO
So FIELDINDEX gives us the field on a given position in the record and is not a reference to the field ID in the table.
Hehe Søren, ok this is a good one! Your “theory” would have been fine if it was only working this way under SQL Server, but you see the exact same behavior under native. [:)]
But otherwise your assumption is fine, expect that if you try the same thing with table 37, then field 1, 2, 3 and 4 is now returning Doc.Type, Doc.No., Vendor No. and Line No.
Sorry - I was wrong. Table 39 does return Doc.Type, Doc.No. and Line No. as field 1, 2 and 3! So you are right [:(]
Correct. And this is not a new feature; I used it back in NAV 4.0.
RecRef.FIELD is when you refer to a field-id.
E.g. RecRef.FIELD(37) is “Invoice Disc. Code” (in table 36)
RecRef.FIELDINDEX is when you refer to the x-th field in a table.
E.g. RecRef.FIELD(36) is also “Invoice Disc. Code” - this field is the 36th field starting from “the top”.