Update a Field of the current Key

ta5ta5 Member Posts: 1,164
Hi experts

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

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

Best Answer

Answers

  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    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;
    
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • ta5ta5 Member Posts: 1,164
    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_GuzekSlawek_Guzek Member Posts: 1,690
    edited 2017-12-08
    I'm sorry, I did not read carefully the whole post and somehow missed the second part :blush:

    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
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • David_SingletonDavid_Singleton Member Posts: 5,479
    edited 2017-12-10
    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.
    David Singleton
  • ta5ta5 Member Posts: 1,164
    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_GuzekSlawek_Guzek Member Posts: 1,690
    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



    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    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

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • David_SingletonDavid_Singleton Member Posts: 5,479
    edited 2017-12-11
    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 :wink::wink::wink:

    Charging the customer 8 hours for a one hour job just doesn't fit with my way of thinking.
    David Singleton
  • ta5ta5 Member Posts: 1,164
    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_SingletonDavid_Singleton Member Posts: 5,479
    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).

    David Singleton
  • ta5ta5 Member Posts: 1,164
    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.
  • ta5ta5 Member Posts: 1,164
    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
  • NavNabNavNab Member Posts: 181
    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 :wink::wink::wink:

    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 :smile: . 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_SingletonDavid_Singleton Member Posts: 5,479
    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.

    David Singleton
  • ta5ta5 Member Posts: 1,164
    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 B)
Sign In or Register to comment.