Options

What does that OnFindRecord code do anyway?

DenSterDenSter Member Posts: 8,304
For instance on the sales order form:
IF FIND(Which) THEN
  EXIT(TRUE)
ELSE BEGIN
  SETRANGE("No.");
  EXIT(FIND(Which));
END;
what is that for anyway?
«1

Answers

  • Options
    DenSterDenSter Member Posts: 8,304
    Wouldn't it be much better to do:
    IF GET("Document Type","No.") THEN
      EXIT(TRUE)
    ELSE BEGIN
      SETRANGE("No.");
      EXIT(GET("Document Type","No."));
    END;
    
    The 'Which' variable always seems to be '><=', which in my customer's case is taking forever, due to having thousands of sales orders. So instead of doing the lame table scan, I would just get the record using the primary key values.
  • Options
    DenSterDenSter Member Posts: 8,304
    edited 2007-01-18
    I don't get that code at all, what the hell does it do???? ](*,) ](*,) ](*,)

    Can I just wipe it out?
  • Options
    Alex_ChowAlex_Chow Member Posts: 5,063
    Hmm... Never thought to look there for performance enhancement.

    Have you implemented this in your client site? Is there significant performance improvment after you made this change?
  • Options
    DenSterDenSter Member Posts: 8,304
    heck yeah, it takes like 10 seconds just to open the form WITH that code, and comes up right away without it :shock:

    Haven't done this in production yet, because I can't figure out what the code does, but I have a copy of their live database and it makes that kind of difference.
  • Options
    couberpucouberpu Member Posts: 317
    DenSter wrote:
    For instance on the sales order form:
    IF FIND(Which) THEN
      EXIT(TRUE)
    ELSE BEGIN
      SETRANGE("No.");
      EXIT(FIND(Which));
    END;
    
    what is that for anyway?
  • Options
    jmjm Member Posts: 156
    Hi Daniel,

    i asume this code is useful, if you made a filter on the "No.", left this form,
    and another user deletes your "filtered" Sales Order.
    Without this code you get an error, if you open the form, and the form will be cloesed.

    Another guess is that it is useful, if you havea filter on the "No.", and you create a new Sales Order with F3.
    br
    Josef Metz
  • Options
    ara3nara3n Member Posts: 9,256
    Also the Get function ignores any filters. The Record filters are still intact after you call this function.
    So the get statement would not give the same result as find
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    David_CoxDavid_Cox Member Posts: 509
    DenSter wrote:
    For instance on the sales order form:
    IF FIND(Which) THEN
      EXIT(TRUE)
    ELSE BEGIN
      SETRANGE("No.");
      EXIT(FIND(Which));
    END;
    
    what is that for anyway?

    You can look at sales documents from various places, like the customer card, with other filters applied, view the form from the customer card press F3 and the customer detail is filled in, this requires the filters to be intact.

    The Code:
    If there are filters applied and the record is found then exit True

    If not clear the filter on the Document Number only, so we still have any other filters applied "Document Type" and "Sell-to Customer No." etc:
    Then exit if finding a record.

    If we change this to a get statement we will lose the other keys and filters!
    Analyst Developer with over 17 years Navision, Contract Status - Busy
    Mobile: +44(0)7854 842801
    Email: david.cox@adeptris.com
    Twitter: https://twitter.com/Adeptris
    Website: http://www.adeptris.com
  • Options
    DenSterDenSter Member Posts: 8,304
    I know what each individual line of code does Dave, I have been doing this for quite a while :mrgreen: I was just not sure what the purpose of the snippet was.

    What it is for is when the record that the form last looked at doesn't exist anymore, it removes the filter on the "No." field and finds another record, so that the form never errors out when it tries to open with a record that doesn't exist.

    The reason why it was so slow in my customer's database was that the 'Which' parameter is always set to "><=', which causes a table scan on SQL Server, and on a table with more than 3000 orders that takes a while. I modified the isolation level of the query by using GET and FINDFIRST instead, and that has sped up the process significantly.

    I changed it to:
    IF GET("Document Type","No.") THEN
      EXIT(TRUE)
    ELSE BEGIN
      SETRANGE("No.");
      EXIT(FINDFIRST);
    END;
    
    If the form is called from somewhere else, than it knows which record to look for, so GET should still work. If GET returns true then there is a record within the filter and there is no need to do anything with any filters. If GET returns false then it resets the filter on the "No." field and finds the first one.

    Before code change: 10 seconds
    after code change: 3 seconds

    Still trying to find the remaining 3 seconds :mrgreen:
  • Options
    ara3nara3n Member Posts: 9,256
    Try
    If not isempty

    Instead of get
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    DenSterDenSter Member Posts: 8,304
    I did, that doesn't work :) why it doesn't I don't know but it shows up empty both in the Card form and the list form. The additional 3 seconds is in the caption class translate function, at least that's what the client monitor says.
  • Options
    krikikriki Member, Moderator Posts: 9,090
    I think it is best like this:
    IF FIND('=') THEN
      EXIT(TRUE)
    ELSE BEGIN
      SETRANGE("No.");
      EXIT(FINDFIRST);
    END;
    

    The FIND('=') is better then GET in this case:
    Imagine you have written code to call this form and:
    -you have a record available in it for some reason.
    -you do a RESET,SETCURRENTKEY,SETRANGE... on the record-variable AND the record you currently have does NOT fall into the filters.

    In your case, the GET will find a record, then it will try to show it on the form, but the filters will not allow it, so they will refind a record (at least this is what I think will happen).
    So with a FIND('=') this does not happen, it goes directly to the ELSE part.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    couberpucouberpu Member Posts: 317
    I have 3 company in my database and user bounce back & forth among these 3 company. But not all 3 company has same customer nor item sets. I used to have only
    SetSecurity(FALSE);EXIT(FIND(Which));
    
    In form 10011 and constantly having troubles.
    Would this
    SetSecurity(FALSE);
    IF FIND('=') THEN 
      EXIT(TRUE) 
    ELSE BEGIN 
      SETRANGE("No."); 
      EXIT(FIND(Which));
    END;
    
    be better? :?:

    Best,
    CouberPu
  • Options
    krikikriki Member, Moderator Posts: 9,090
    I think an EXIT(FINDFIRST) or maybe also EXIT(FINDLAST) is better then EXIT(FIND(Which)).
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    MbadMbad Member Posts: 344
    Does this not remove the ability to use ctrl+end to get the last record etc? Thats one of the reasons for the broad filtering afaik.
  • Options
    DenSterDenSter Member Posts: 8,304
    kriki wrote:
    I think an EXIT(FINDFIRST) or maybe also EXIT(FINDLAST) is better then EXIT(FIND(Which)).
    I tried that too, and that doesn't work either. When you open the list form and doubleclick on one of the records, the card form jumps right back to the first record in the filter.
  • Options
    krikikriki Member, Moderator Posts: 9,090
    DenSter wrote:
    kriki wrote:
    I think an EXIT(FINDFIRST) or maybe also EXIT(FINDLAST) is better then EXIT(FIND(Which)).
    I tried that too, and that doesn't work either. When you open the list form and doubleclick on one of the records, the card form jumps right back to the first record in the filter.
    True :oops:
    Try this:FIND(Which) in case Which IN otherwise a FINDFIRST.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    MbadMbad Member Posts: 344
    edited 2007-01-19
    DenSter wrote:
    kriki wrote:
    I think an EXIT(FINDFIRST) or maybe also EXIT(FINDLAST) is better then EXIT(FIND(Which)).
    I tried that too, and that doesn't work either. When you open the list form and doubleclick on one of the records, the card form jumps right back to the first record in the filter.

    My point was that its the find('=') in the first line that messes things up, not the findfirst in the end. That only desides what record comes up if you find nothing in your filter. Basicly if you do the faster search that increased the performance you will loose the ctrl+end feature and will have to scroll through all the records to find the one you look for if you dont know the No. or a wery good filter.
  • Options
    DenSterDenSter Member Posts: 8,304
    Nope, that does the same thing, it jumps right to the first record when you select a record from the list. Apparently, coming from the list form with Action::LookupOK sets Which to '=><'. Here's what I did (locGotIt is a boolean):
    IF Which IN ['-','+','<','>','='] THEN
      locGotIt := FIND(Which)
    ELSE
      locGotIt := FINDFIRST;
    IF locGotIt THEN
      EXIT(TRUE)
    ELSE BEGIN
      SETRANGE("No.");
      EXIT(FINDFIRST);
    END;
    
  • Options
    MbadMbad Member Posts: 344
    Think you just lost your performance gain again :-/
  • Options
    DenSterDenSter Member Posts: 8,304
    kriki wrote:
    In your case, the GET will find a record, then it will try to show it on the form, but the filters will not allow it, so they will refind a record (at least this is what I think will happen).
    Interesting.... no that's not what happens. The GET finds the record, and displays it in the form even though it doesn't fall in the filters. I had a sales order for salesperson A in the Card, and set a filter for salesperson B. Then close the form and re-open it, and the form will show the order for salesperson A, even though the filter still says salesperson B.
  • Options
    DenSterDenSter Member Posts: 8,304
    Mbad wrote:
    Think you just lost your performance gain again :-/
    Actually no, because Which always seems to be '=><', so it always does a FINDFIRST. The form performs very nicely, it just only wants to display the firt order in the list :mrgreen:
  • Options
    DenSterDenSter Member Posts: 8,304
    Mbad wrote:
    you will loose the ctrl+end feature
    I've had that screwed up a couple of times, but with GET that still works. Right now the GET is the best option for the customer. They have a lot of order entry people and they need a fast form. We'' figure out how to make it work when we run into the filtering problem later.
  • Options
    johannajohanna Member Posts: 369
    DenSter, I have the same problem about OnFindRecord cause slow performance.
    So, what is the solution you delivered to your customer?
    Is it :
    IF GET("Document Type","No.") THEN
      EXIT(TRUE)
    ELSE BEGIN
      SETRANGE("No.");
      EXIT(FINDFIRST);
    END;
    
    ?

    Thanks.. :D
    Best regards,

    Johanna
  • Options
    DenSterDenSter Member Posts: 8,304
    That was 5 years ago, I don't remember what the final solution was. There is a lot of information in this topic though, you'll have to try the options and see what works for you.
  • Options
    ppavukppavuk Member Posts: 334
    I think best way to solve - it to decrease a number of open documents in sales header table. Normally they should gone at some point - and while you have couple of hundreds (and even couple of thousands!) of documents - this would not cause performance issue.

    If you have performance issue wit this code - I would advise to investigate SQL setup, and network infrastructure. Couple of thousands records must not cause any performance issue in our times.

    BTW, how many documents do you have in t36? How many users work with documents?
  • Options
    ppavukppavuk Member Posts: 334
    wrong process, I'll say. :) can't imagine a situation when it is necessary to hold 400k documents in sales header. It is not an development issue :)
  • Options
    DenSterDenSter Member Posts: 8,304
    I didn't say it was the right process, I'm saying Johanna is talking about that problem in the other post
  • Options
    ppavukppavuk Member Posts: 334
    I just made a point that having 400k sales orders is something wrong :) Some issues should be solved by changing process, not code, you know. Ok, I do not know anything about their business, but i can't imagine a 400k of open sales docs in any business at all, unless the business is Amazon or E-bay :)

    Navision is not designed to hold 400k of open sales documents - this is the answer.
Sign In or Register to comment.