Advanced findfirst/ findlast use

BgestelBgestel Member Posts: 136
Hi NAV community,

Please look at the following piece of code: you wil see that it is used to do a check per item no found in the itemledgerentries.

ItemLedgEntry.SETCURRENTKEY("Item No.",Open,"Variant Code",Positive,"Location Code");
  IF ItemLedgEntry.FINDFIRST THEN
    REPEAT
      ItemLedgEntry.SETRANGE("Item No.",ItemLedgEntry."Item No.");
      ItemLedgEntry.SETRANGE(Open,TRUE);
      ItemLedgEntry.SETRANGE("Location Code",Code);
      IF ItemLedgEntry.FINDLAST THEN
        ERROR(Text009,FIELDCAPTION("Bin Mandatory"));
      ItemLedgEntry.SETRANGE(Open);
      ItemLedgEntry.SETRANGE("Location Code");
      ItemLedgEntry.FINDLAST;
      ItemLedgEntry.SETRANGE("Item No.");
    UNTIL ItemLedgEntry.NEXT = 0;

Now the question: Is this optimal for sql, because there is a next statement( cursor ) and findfirst/findlast statements ( no cursor)

But what should be there instead.

?
:)

thx in advance.
**********************
** SI ** Bert Van Gestel **
**********************

Comments

  • kinekine Member Posts: 12,562
    1) It is not optimal !!!
    2) What you want to do? Call error if some open entry for selected location exists? For this you can just filter for any Open entry on this location and if NOT ISEMPTY than call the error...
    3) If you want it for another purpose, just use temporary table where you insert e.g. the item if not already there and add second loop based on this temp table...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • DenSterDenSter Member Posts: 8,304
    That code snippet tells me that you don't understand what FINDFIRST and FINDLAST actually does.

    Furthermore, this code really doesn't DO anything, other than raise and error the first (or is it last? I get confused) Item for which there are ILE records in that filter. When there's a whole bunch of such Items, you will only see one error because the transaction will be aborted.

    Please explain what you need to do and we'll give you some suggestions on how to approach it.
  • BgestelBgestel Member Posts: 136
    Hi kine, denster and all the rest,

    Nice off you to confirm that the person who coded this doesn't know what he is doing. The good thing is that i didn't code it :wink: .

    the bad news is that it is in NAVW15.00 database table 14 in the onvalidate trigger for Bin mandatory.

    What do you think about that :D . Should we call denmark and tell them they do not know what they are doing :D
    **********************
    ** SI ** Bert Van Gestel **
    **********************
  • kinekine Member Posts: 12,562
    Bgestel wrote:
    Hi kine, denster and all the rest,

    Nice off you to confirm that the person who coded this doesn't know what he is doing. The good thing is that i didn't code it :wink: .

    the bad news is that it is in NAVW15.00 database table 14 in the onvalidate trigger for Bin mandatory.

    What do you think about that :D . Should we call denmark and tell them they do not know what they are doing :D

    Yes, call them, because this is terrible code, mainly that it is not changed in SP1...

    You can correct the code to something like:
    ItemLedgEntry.SETCURRENTKEY("Item No.",Open,"Variant Code",Positive,"Location Code");
    ItemLedgEntry.SETRANGE(Open,TRUE);
    ItemLedgEntry.SETRANGE("Location Code",Code);
    IF not ItemLedgEntry.ISEMPTY THEN
        ERROR(Text009,FIELDCAPTION("Bin Mandatory"));
    

    But it seems that the code is written in this way because on Native DB it will not show the "Counting records" dialog... ](*,)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • DenSterDenSter Member Posts: 8,304
    Bgestel wrote:
    What do you think about that :D
    Mostly I feel very relieved that you didn't write that code, because you had me going there Bert :mrgreen:

    That is pretty bad code, and if it is indeed done for not showing a message in the native database server, then it should be put into a IF NOT RECORDLEVELLOCKING statement, and then put Kamil's code into the FALSE leg.

    Realistically though, that code is not going to be run on any regular basis, you should really set up the locations once, at the start of the implementation.
  • Alex_ChowAlex_Chow Member Posts: 5,063
    Yeah, making optimizations on fields that you'll never use is probably not a very good use of time...
  • ara3nara3n Member Posts: 9,255
    Alex Chow wrote:
    Yeah, making optimizations on fields that you'll never use is probably not a very good use of time...

    I constantly optimize code and indexes that I never use. :(


    it's the client that uses it.
    :mrgreen:
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Alex_ChowAlex_Chow Member Posts: 5,063
    ara3n wrote:
    Alex Chow wrote:
    Yeah, making optimizations on fields that you'll never use is probably not a very good use of time...

    it's the client that uses it.
    :mrgreen:

    Dang... so many people busting my balls these days... #-o
  • ara3nara3n Member Posts: 9,255
    :roll:
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
Sign In or Register to comment.