Update a Field of the current Key

ta5

Hi experts

When updating a Field of the current Key (not the primary key) I feel a bit strange.

Have a look at that Code
TestRec.SETCURRENTKEY("First Name");
TestRec.SETFILTER("First Name",'M*');
MESSAGE('No. of Records to modify %1',TestRec.COUNT);
IF TestRec.FINDSET THEN REPEAT
TestRec."First Name" := 'James';
TestRec.MODIFY(FALSE);
NoOfModifyRecords += 1;
UNTIL TestRec.NEXT = 0;
MESSAGE('No of Records modified t %1',NoOfModifyRecords);

Depending on the underlying data, there might be 5 Records to modify, but the loop is exited after the first one.

Many times I've seen code, using a 2. instance of the same record, doing the MODIFY on that 2. instance.
Not bad, but how about using the parameters for FINDSET?
This seems to work like a charm. Any caveats with that? Help for the FINDSET function is IMHO a bit hard to understand.
Btw: I know I could have used MODIFYALL, but just for that simple example.

TestRec.SETCURRENTKEY("First Name");
TestRec.SETFILTER("First Name",'M*');
MESSAGE('No. of Records to modify %1',TestRec.COUNT);
IF TestRec.FINDSET(TRUE,TRUE) THEN REPEAT
TestRec."First Name" := 'Cindy';
TestRec.MODIFY(FALSE);
NoOfModifyRecords += 1;
UNTIL TestRec.NEXT = 0;
MESSAGE('No of Records modified t %1',NoOfModifyRecords);

Thanks a lot for your help.
Thomas

David_Singleton

ta5 wrote: »

A non data fix situation could be:
Set a filter on a date and a status on some interface journal, then process the records and change their status.

But you shouldn't be changing a status based on a status, there should be a different action triggering the change of status. I understand what you are saying, but I fin difficulty coming up with a valid business case.

Slawek_Guzek

This is because the data sets in NAV are dynamic. It means that the data you updated has an immediate effect on selected set

If for example, you order by an integer field, and you have records with numbers 1, 2, 3, 4, and 5, and you looping in that orer, when you update field 1 with 6 it is like you just have moved your loop pointer at the end ot the loop.

The proper way to update fields in such a scenarion is like this:

TestRec.SETCURRENTKEY("First Name");
TestRec.SETFILTER("First Name",'M*');
IF TestRec.FINDSET THEN 
REPEAT
 TestRec2 := TestRec;
 TestRec2."First Name" := 'Cindy';
 TestRec2.MODIFY(FALSE);
UNTIL TestRec.NEXT = 0;

ta5

Hi Slawek
Thanks a lot for answering and for clarification. As I have written, the trick with the 2. Record is quite well known, but I was a bit suprised from the fact, FINDSET(TRUE,TRUE) did also help.
Is using FINDSET(TRUE, TRUE) a good idea to solve that kind of problem?
Thanks in advance, very appreciated
Thomas

Slawek_Guzek

I'm sorry, I did not read carefully the whole post and somehow missed the second part

FINDSET(TRUE, TRUE) used to be much slower in versions pre 2013 than looping using one and writing the other. I have not tested this in never version, but vaguely remember some article/blog saying it does not have any (or maybe minimal) performance impact anymore

Personally I'm still using 2 record loop/modify, if I can't use MODIFYALL

David_Singleton

ta5 wrote: »

Is using FINDSET(TRUE, TRUE) a good idea to solve that kind of problem?

Yes FINDSET(TRUE,TRUE) is the way you should be doing it now.

ta5

Hi David and Slavek
Any caveats using FINDSET(TRUE,TRUE)?
Personally I have never used it in a real life environment, but I think it's time to move forward.
Sidenote: I have physically asked some other developers, none of them was aware of the new possibility.
Thanks
Thomas

Slawek_Guzek

The FINDSET(TRUE,TRUE) is not new, it is here since NAV 4 (or maybe NAV 5) as my memory serves me.

It's been introduced specifically to handle the loop scenarios like yours, but it used to be slow, very slow, in versions pre NAV2009. Apart from that I don't know any other drawbacks of using it.

Try to write some simple test, modify some 1 - 10k records using FINDSET(TRUE,TRUE) and the two-record trick. That will give you an ultimate answer.

Actually I might run such atest myself
Slawek

David_Singleton

ta5 wrote: »

Hi David and Slavek
Any caveats using FINDSET(TRUE,TRUE)?
Personally I have never used it in a real life environment, but I think it's time to move forward.
Sidenote: I have physically asked some other developers, none of them was aware of the new possibility.
Thanks
Thomas

As Slawek says, the only reason "we" don't use it is because we have our ways of doing things that we know and we stick to them.

Also I almost never would use this, because you simply should not have a data model that requires you to update a field in the key you are currently using. Better is to look at your data model and try to fix it. I actually can't ever think of a time I used this construct unless because I was fixing someone else's mistakes. In which case it's generally a one off situation, so speed is not generally as crucial as just getting the job done. I'd rather spend an hour to write code that only ever runs once, but takes 8 hours to run. Rather than spending 8 hours to make it run in an hour.

Slawek_Guzek

David_Singleton wrote: »

I'd rather spend an hour to write code that only ever runs once, but takes 8 hours to run. Rather than spending 8 hours to make it run in an hour.

I have quite opposite approach I'd rather spend 8 hrs to write a code which run for one hour, rather than spend an hour on it and left it running for 8 hrs. The reason is that, from my experience, it is almost never the case where a one-off code is really run only once...You need to test it.. maybe again...:)

Slawek

David_Singleton

Slawek_Guzek wrote: »

David_Singleton wrote: »

I'd rather spend an hour to write code that only ever runs once, but takes 8 hours to run. Rather than spending 8 hours to make it run in an hour.

I have quite opposite approach I'd rather spend 8 hrs to write a code which run for one hour, rather than spend an hour on it and left it running for 8 hrs. The reason is that, from my experience, it is almost never the case where a one-off code is really run only once...You need to test it.. maybe again...:)

Slawek

That just means you didn't do the analysis correctly the first time

Charging the customer 8 hours for a one hour job just doesn't fit with my way of thinking.

ta5

Hi
Thanks a ton.
I have fired quite an interesting discussion
Anyhow, so I would say, using the FINDSET(TRUE,TRUE) is the way to change fields that are part of the active key. In our company we have a kind of design pattern using 2 record variables (as mentioned above) but I think this pattern not needed anymore, at least not in normal situations.
Agree?
Regards and thanks for your input, thats the power of the community!
Thomas

David_Singleton

ta5 wrote: »

In our company we have a kind of design pattern using 2 record variables (as mentioned above) but I think this pattern not needed anymore, at least not in normal situations.
Agree?

I think first look at the database structure and ask yourself should the design if done correctly require changing an element of the key being used or could the model be improved. (Unless as mentioned that this is a one off data fix).

ta5

You are right, it's mererly for data fix situations.
A non data fix situation could be:
Set a filter on a date and a status on some interface journal, then process the records and change their status.

David_Singleton

ta5 wrote: »

A non data fix situation could be:
Set a filter on a date and a status on some interface journal, then process the records and change their status.

But you shouldn't be changing a status based on a status, there should be a different action triggering the change of status. I understand what you are saying, but I fin difficulty coming up with a valid business case.

ta5

You are right, most of the time it will be a data fix situation.
I think we can close the discussion on this. Thanks a lot for taking your time.

If anyone else has good or bad experience you are very welcome to comment on this.

Regards
Thomas

NavNab

David_Singleton wrote: »

Slawek_Guzek wrote: »

David_Singleton wrote: »

I'd rather spend an hour to write code that only ever runs once, but takes 8 hours to run. Rather than spending 8 hours to make it run in an hour.

I have quite opposite approach I'd rather spend 8 hrs to write a code which run for one hour, rather than spend an hour on it and left it running for 8 hrs. The reason is that, from my experience, it is almost never the case where a one-off code is really run only once...You need to test it.. maybe again...:)

Slawek

That just means you didn't do the analysis correctly the first time

Charging the customer 8 hours for a one hour job just doesn't fit with my way of thinking.

Hello all,

@David_Singleton @Slawek_Guzek : well, this is a quite interesting debate. I agree with both of you . In my humble opinion, the "correct" answer to this question is "It depends".

On the one hand, if you write some code to do some data migration and you have limited time to achieve your task then, the way to do it is like @Slawek_Guzek said.

On the other hand, if you don't have any critical requirements, then why bother to optimize the code if it will only run once. In this case, I'd rather do it as @David_Singleton.

@ta5

On another note, I generally use the pattern with 2 variables because I know it works no matter what NAV version you use.

David_Singleton

NavNab wrote: »

On the one hand, if you write some code to do some data migration and you have limited time to achieve your task ...

If you are doing a major data migration, then you should be doing as much prep work in the current production database to minimize the down time for upgrade, so in this case add a new field with an index on it and update that field in the production database and use it during upgrade to speed up the process.

ta5

On another note, I generally use the pattern with 2 variables because I know it works no matter what NAV version you use.

I like this, because actually this was the initial question. Sense or no sense, but from the technical point of view "nobody" seems to be comfortable with FINDSET(TRUE,TRUE).
But to be careful 'll probably stick with the 2 records pattern too