Learn how to use the right Find* function in Business Central

[chengxiaoxia]

Thank you, Erik. I followed one of your videos and was able to successfully install a BC container. Now I am happily coding using AL language

[hicamajig]

Thanks for the optimization tips. I will definitely be doing some rewriting to speed up our lookups after this. Did not consider that isempty included a lookup so always did a find('-'), findfirst(), findlast() to check. Will also finally have a case to switch all the find('-') loops over to findset().

[Xavi]

Great video Erik !
The worst cases I could see are :
* modifying a record during a findset/repeat/until next on a field that is part of the filter criteria -> breaking the cursor/loop
* modify a record if insert is failing : "if not <rec>.insert then <rec>.modify" -> I don't like it (but I can see used in the standard base app)
* used nested repeat/until in place of query
More to come if you want ;-)

[jyotsnaoberai]

Finally the Findset() parameters made total sense to me! thank you!

[greatscott]

I was given to understand FIND('-') "chunks" the data and FINDSET() gets all the data at once. The difference being that if you expect you may break out of the loop you should use FIND('-') so you don't get more data than you need. This would be particularly important on large datasets (e.g. Item Ledger Entry with millions of lines). If you have a "finite" set of records (invoice lines for a specific invoice or G/L Account then FIND('-') and FINDSET() would work much the same but FINDSET() would be technically faster because it is not a cursor like FIND('-') returns. Thoughts?

[Mariocvos]

Hey, cool video!
I believe you can use the FINDSET command also for looping through the set of records from the last one "upwards" (as with the FIND('+') and NEXT(-1) loop) - you just need to set ASCENDING to FALSE on the record variable.

[alexandrsh]

Love your videos and this one is not exception!
However, it would be great to see in this video an answer on a simple question: how to receive a distinct values from the table? There are so many find* commands... Is there something like SELECT DISTINCT in SQL?

PS:
Many thanks for sharing your knowledge!

[maartengerritsen]

Thank you Erik for the video that explains clearly what-when to use and de-mystifies all the confusion that has been around since SQL has been introduced into NAV. Just a question about the 2 FindSet parameters. You said it is doing a locktable. So, should we use a FindSet(true, false) when modify a temporary record?

[jayleferm]

Very good explanation, Erik! But I've got one question concerning the sorting.

If FindSet() does not support to iterate from the bottom to the top, wouldn't it be possible using the Record.Ascending() method before the FindSet() method and let stay the parameter in Next() with a positive integer? And if yes, what about the performance?

For example:

Customer.Ascending(False);
Customer.SetFilter('Erik*');
if Customer.FindSet() then
Repeat
// blahblah
Until Customer.Next(1) = 0;

[MrUltrarebel]

First of all, great video again! Should you ever do a SetCurrentKey on fields that do not exist as an index? As this is possible and might it help as a hint for the SQL operation?

[anotherdev]

Thank you for the video.
I believe, Microsoft should give native support like writing and calling stored procedure in al for performance and ease for developers.
If we query like statement i.e. custmers name like 'Erik%', do keys (setcurrentkey) be helpful ?

[qnito]

Dear Erik. I have a question. What is the correct order to make findset? First the setcurrentkey, next the filters and last findset (of course findset always last) or first the filters and then the setcurrentkey or doesn't matter?

[antonionovoa]

Excellent explanation, I love your videos.
One question,
¿would you recommend using itsempty before a findset, just try to bring in records if the table is not empty or would that validation be redundant?

Example:
Cust.setrange("name", 'Erik');
If not Cust.itsEmpty() then
If Cust.findset() then
Repeat


Until Cust.Next() = 0;

Thank you for everything!

[vampy]

Years ago, someone told me, that if i can expect to step only through a small part of the records (repeat ... until (next = 0) or (Condition = true)), it would be better for performance, if i use find('-') instead of findset. Is this still a good practice repectively was it ever a good practice?

[SpyFr]

Hi Erik,
First, thank you for your Youtube contribution.

I used to believe that Find('-') in old Nav load the entire set of record considering the fillters.
Findset('-') loads sets of 250 records, right ? A parameter of the service.
Maybe I'm wrong. Could you confirm ?

[jackpeterson]

What would be the benefit from starting at the bottom of a list instead of the top when looping through records?

[joeycarlisle]

Great Video!

I still have a question that was unanswered. When using Find, Get, Next, it seems to always load the values stored in the database. I would like to load a specific record when it is different from the database. Is there a way to use these methods to accomplish this? Hopefully that makes sense.

[gertlynge]

If isempty () is a "hidden" find, i guess count() is a "hidden" find too?

[nikolailestrange]

Just wondering on your opinion on a very common pattern I end up using all the time.

No.");
if CustLedgerEntry.findset() then
repeat
No.", CustLedgerEntry."Customer No.");
No.");

CustLedgerEntry.findlast();
No.");
until CustLedgerEntry.next() = 0;

Should I use the Findset? Should I replace the FindLast? Should I create a Query (seems overkill sometimes)? Or just stick with this?