What does that OnFindRecord code do anyway?

DenSter

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?

DenSter

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.

DenSter

I don't get that code at all, what the hell does it do???? ](*,) ](*,) ](*,)

Can I just wipe it out?

Alex_Chow

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?

DenSter

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.

couberpu

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?

jm

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.

ara3n

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

David_Cox

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!

DenSter

I know what each individual line of code does Dave, I have been doing this for quite a while 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

ara3n

Try
If not isempty

Instead of get

DenSter

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.

kriki

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.

couberpu

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

kriki

I think an EXIT(FINDFIRST) or maybe also EXIT(FINDLAST) is better then EXIT(FIND(Which)).

Mbad

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.

DenSter

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.

kriki

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.

Mbad

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.

DenSter

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;

Mbad

Think you just lost your performance gain again :-/

DenSter

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.

DenSter

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

DenSter

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.

johanna

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..

DenSter

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.

ppavuk

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?

DenSter

See Johanna's other topic: viewtopic.php?f=23&t=56588

ppavuk

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

DenSter

I didn't say it was the right process, I'm saying Johanna is talking about that problem in the other post

ppavuk

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.