Compare 2 records
i’ll present here a pair of tips to compare two record variables to check if they are the same, and i’m not talking about that they’ve the same key, but that they are totally equal, every field.
Actually, such a function does not exist in NAV (it’s not built-in, at least). Instead, I found a pair of methods to check if the records are equal. Both methods have their pros and cons, so I’ll post them both.
Then, I’ll explain my observations and it will be up to you to choose the best one for your specific case.
IsDifferent function: (the second function)
Inspired (ok, almost copied 
) from function logmodification of codeunit 423 - Change Log Management.
This function compares all the NORMAL fields one by one, and if the value is different, it returns false.
IsDifferentWithFormat function: (the first function)
This one is all mine. 
It uses the format function on the recordref variables and compares the results.
It uses an array of 5 text1024 and compares the results 1024 by 1024 characters. If both the format of rec and xrec are blank, it means we have reached the end of record, so the function exits false (records are equal).
I did some tests on performance of both these functions, and it turned out that usually the fastest is the one with FORMAT instruction, due to the minor number of loops. The other function is faster only if it’s one of the first fields that is different (or if the table has just a few fields).
The implementation is the same for both functions, (just pass 2 evaluated recordrefs to compare) so it’s up to you to choose the best function for you! THIS DOESN’T WORK, UNFORTUNATELY (SEE COMMENTS), STICK TO ISDIFFERENT FUNCTION FOR LONG TABLES (BUT IT WILL PROBABLY TAKE A LOT OF TIME), AND USE FORMAT(REC) ON A NORMAL TABLE VARIABLE FOR SMALL TABLES.
I’m going to upload the objects ASAP, now I’ve to go back to work.
See you next time!
October 10th, 2011 at 1:42 pm
Now my mind is running around trying to come up with different scenarios where I could have applied this in the past!
October 10th, 2011 at 2:58 pm
Have you tried this?
if Format(Rec) = format(Rec2) then
message(’Is equal’)
else
message(’Is not equal’);
October 10th, 2011 at 3:07 pm
I’m sorry, but your code doesn’t work. (Tested in R2)
FORMAT never returns more than 1024 char, so you will ned to use FieldRef to make sure it supports all records…
Not actually tested with your code. I just made a new table with 10 Text250 fields, and ran this code:
r.F1 := PADSTR(’1′,250,’X');
r.f2 := r.f1;
r.f3 := r.f1;
r.f4 := r.f1;
r.f5 := r.f1;
r.f6 := r.f1;
r.f7 := r.f1;
r.f8 := r.f1;
r.f9 := r.f1;
r.f10 := r.f1;
message(format(strlen(format(r))));
message(format(r));
October 10th, 2011 at 3:15 pm
@ara3n: yes, it works, but if i want the function to be used in the whole application without rewriting it everytime, i need recref. You can say: it’s just a format(rec) = format(rec2). This leads to my answer to peter –>
@peter: you have to run my code, because i use an array of 5 elements of text1024 (i wrote it in the post), so you can compare up to (1024*5) characters per single record, and if i am correct, the max size of a record in nav is 1024*4.
<–back @ara3n: I want to avoid to create and compare arrays every time i need this function.
@Me: it’s better to post the objects before publishing an article…
October 10th, 2011 at 3:43 pm
@peter, i just tested your code…mmh, I’ve to check it but I think you’re right and I have to trash this article…too bad…
October 10th, 2011 at 3:52 pm
Yes, the 1024 char limitation in NAV is often a problem
Here the worst part is, that it doesn’t fail when doing a FORMAT(rec) - it simply skips the last part of the string and gives a false Ok. But I guess it would be worse if they suddenly made it fail. It is not easy to please everybody
October 10th, 2011 at 3:58 pm
at least, it works for tables smaller than 1024 characters…but for those tables, we can simply format the rec…so, useless function
October 10th, 2011 at 4:22 pm
I’m gonna try with dotnet interoperability and xmlports…what do you say?
I’ve never tried to do it, but probably the performance overhead will be even higher than comparing field by field…
October 10th, 2011 at 5:27 pm
It looks like you are trying to make a very complex solution to a very simple problem. Why not simply use the simple function from the ChangeLog?
If performance is very important for you, it can be optimize by checking if STRLEN(FORMAT(Rec) < 1024-250. Then your just have to compare FORMAT(Rec) with FORMAT(Rec2). If the string is longer, then it needs to compare it field by field.
October 10th, 2011 at 5:33 pm
In my dreamworld, I’d like to use the same function for everything, but it appears it’s not (easily?) possible. For my specific case, performance is VERY important, thus, i’m going to compare some long fields in my table one by one
(if field1 = field2 then…). After that, i’ll blank these fields up and “FORMATCompare” the rest of the record variable.
It’s a dirty workaround, but i’ve tested some potential bad performance with the fieldref solution
October 10th, 2011 at 5:57 pm
I think the fastest generic solution is to add a text250 variable and add these lines in the beginning of the function:
FormattedRecRef := FORMAT(RecRef);
IF STRLEN(FormattedRecRef) < 750 THEN
EXIT(FormattedRecRef FORMAT(xRecRef);
And you can improve it even further by adding special rules based on table no. like the one you mention.
October 10th, 2011 at 5:59 pm
And it should of course be a Text1024 variable
October 13th, 2011 at 12:49 am
http://gotcal.com/index.php/2010/10/how-to-compare-records-in-dynamics-nav/