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 .insert then .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 ;-)
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().
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.
Not according to the documentation: docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/recordref/recordref-findset-method
@@Hougaard See my earlier answer where i test this. I think the documentation is either wrong or misunderstood. FINDSET will only go forward, but it can do it in a reversed dataset.
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.SetCurrentKey('Name'); Customer.Ascending(False); Customer.SetFilter('Erik*'); if Customer.FindSet() then Repeat // blahblah Until Customer.Next(1) = 0;
Nope, FINDSET can only retrieve records in ascending order. docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/recordref/recordref-findset-method
Actually you can use "SetAscending", but this will sort the resulted records before doing the FindSet. Findset will still retrieve the records from top to bottom, but the result will be different.. docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/record/record-setascending-method
@@Hougaard Hi Erik, You are right - you cannot make FINDSET go backwards. But that is (with all respect :-) ) not the correct answer to @jayleferm. You CAN reverse the dataset with "Record.SETASCENDING" like shown below (and I would be very surprised if the RecordRef.ASCENDING() method did not also work like this - but I have not actually tested that ;-) ). This way FINDSET will stil go forward, but in a reversed dataset. The outputs from my BC 14.0.43286.0 (aka BC14 CU13 platform) are shown in the comments. From this it is clear that the Integer table is running in the range -1000000000 ..1000000000 (which is what one would expect) and you can reverse the rows with a "Integer.SETASCENDING(Number,FALSE); ". Also the findfirst and findlast is to confirm that we actually gets the first and last Integer rows from our FINDSET-commands. (I had to test this to be 100% sure, because if you were right, it would probably break a lot of my code "in the wild" ;-). And now it is here if anybody else wonders). --- CLEAR(Integer); Integer.SETASCENDING(Number,TRUE); Integer.FINDSET; MESSAGE('The findset (SETASCENDING=TRUE) number is %1',Integer.Number); // The findset (SETASCENDING=TRUE) number is -1000000000 CLEAR(Integer); Integer.SETASCENDING(Number,FALSE); Integer.FINDSET; MESSAGE('The findset (SETASCENDING=FALSE) number is %1',Integer.Number); // The findset (SETASCENDING=FALSE) number is 1000000000 CLEAR(Integer); Integer.FINDFIRST; MESSAGE('The findfirst number is %1',Integer.Number); // The findfirst number is -1000000000 CLEAR(Integer); Integer.FINDLAST; MESSAGE('The findlast number is %1',Integer.Number); // The findlast number is 1000000000 --
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!
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?
Hi Erik, First, thank you for your RUclips 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 ?
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?
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?
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?
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.
I just have a field from a page that that was changed by the user, but hasn't been saved yet. When I use GET/FIND, I am losing the user's value as it is being reset to what it was before. I was hoping this video would help
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!
In some cases (using to iterate large tables or not having indexes/keys in the SQL table), it could make sense using isempty() before findset(). But then you don't need to use the if statement again with the findset() method after the line "if isempty() then".
Using ISEMPTY before a FINDSET will just perform an extra (unneeded) SQL command on the SQL server. So that would be wasting performance imho. If that could actually speed up things, i would think that the SQL optimizer would do it internally before trying to get the dataset for you FINDSET command anyway. Note: The SQL optimizer is a part of the SQL Server analyzing your queries and coming up with the best possible execution plan for it. it even uses statistics of the data in the tables for this and considers all enabled indexes - even if you did not state them with a SETCURRENTKEY in C/AL. And the best part: You don't even have to enable the SQL optimizer - it is just sitting there optimizing all your queries :-).
I have used if isempty before set based operations like deleteall, modifyall as well as findset in scenarios where I do not want to inadvertently escalate locks on a table unless absolutely necessary.
Just wondering on your opinion on a very common pattern I end up using all the time. CustLedgerEntry.setcurrentkey("Customer No."); if CustLedgerEntry.findset() then repeat CustLedgerEntry.setrange("Customer No.",CustLedgerEntry."Customer No."); DoSomethingWithTheCustomer(CustLedgerEntry."Customer No."); CustLedgerEntry.findlast(); CustLedgerEntry.setrange("Customer 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?
@@Hougaard LOL. Probably yes, but if it's Bonkers and it works...? Maybe it makes more sense to ask how would you do this task? (essentially find UNIQUE list of customer Nos. from a set of Ledger Entries)
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 ?
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?
@@Hougaard Usually, i would consider to create an own index, if i expect that the findset command results in a high quantity of records in the recordset, even if i use it rarely. Is this a kind of good practice or should i prefer to use setcurrentkey without a proper index in this case?
@@Hougaard I always thought that ISEMPTY is "cheaper" (in meanings of sql server performance) because it doesn't transfer any records to NAV while findfirst/findlast, find and findset always result in the transfer of records from sql server. So, if i only need to know if any record exist, i always should use isempty.
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
Great video on the Find functions👍
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 .insert then .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 ;-)
Finally the Findset() parameters made total sense to me! thank you!
Glad it helped!
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().
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.
Not according to the documentation: docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/recordref/recordref-findset-method
@@Hougaard See my earlier answer where i test this. I think the documentation is either wrong or misunderstood. FINDSET will only go forward, but it can do it in a reversed dataset.
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.SetCurrentKey('Name');
Customer.Ascending(False);
Customer.SetFilter('Erik*');
if Customer.FindSet() then
Repeat
// blahblah
Until Customer.Next(1) = 0;
Nope, FINDSET can only retrieve records in ascending order. docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/recordref/recordref-findset-method
Actually you can use "SetAscending", but this will sort the resulted records before doing the FindSet. Findset will still retrieve the records from top to bottom, but the result will be different..
docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/methods-auto/record/record-setascending-method
@@Hougaard Hi Erik, You are right - you cannot make FINDSET go backwards. But that is (with all respect :-) ) not the correct answer to @jayleferm.
You CAN reverse the dataset with "Record.SETASCENDING" like shown below (and I would be very surprised if the RecordRef.ASCENDING() method did not also work like this - but I have not actually tested that ;-) ). This way FINDSET will stil go forward, but in a reversed dataset.
The outputs from my BC 14.0.43286.0 (aka BC14 CU13 platform) are shown in the comments.
From this it is clear that the Integer table is running in the range -1000000000
..1000000000
(which is what one would expect) and you can reverse the rows with a "Integer.SETASCENDING(Number,FALSE);
".
Also the findfirst and findlast is to confirm that we actually gets the first and last Integer rows from our FINDSET-commands.
(I had to test this to be 100% sure, because if you were right, it would probably break a lot of my code "in the wild" ;-). And now it is here if anybody else wonders).
---
CLEAR(Integer);
Integer.SETASCENDING(Number,TRUE);
Integer.FINDSET;
MESSAGE('The findset (SETASCENDING=TRUE) number is %1',Integer.Number); // The findset (SETASCENDING=TRUE) number is -1000000000
CLEAR(Integer);
Integer.SETASCENDING(Number,FALSE);
Integer.FINDSET;
MESSAGE('The findset (SETASCENDING=FALSE) number is %1',Integer.Number); // The findset (SETASCENDING=FALSE) number is 1000000000
CLEAR(Integer);
Integer.FINDFIRST;
MESSAGE('The findfirst number is %1',Integer.Number); // The findfirst number is -1000000000
CLEAR(Integer);
Integer.FINDLAST;
MESSAGE('The findlast number is %1',Integer.Number); // The findlast number is 1000000000
--
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!
DISTINCT is not concept that's supported in AL, if I need to do it, I add the distinct result afterward in a temporary table.
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?
Awesome, Heisenberg !! :)
Hi Erik,
First, thank you for your RUclips 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 ?
Find() never loaded a set
Yes, the set size is defined on the service tier.
@@Hougaard Thank you Erik !
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?
Great question, the order of preparation calls doesn't matter.
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?
A temporary record is being kept in pure memory, only for your session, so any locking on a temporary table in meaningless.
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?
That was NEVER good for performance. The most important parameter is how many SQL calls your code will make, findset will make just one :)
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.
How would the system know if it's different without loading it from the database first?
I just have a field from a page that that was changed by the user, but hasn't been saved yet. When I use GET/FIND, I am losing the user's value as it is being reset to what it was before. I was hoping this video would help
This is the video that'll help you ruclips.net/video/y9jDYChvrIw/видео.html
What would be the benefit from starting at the bottom of a list instead of the top when looping through records?
In some cases, you need to process data from the newest to the oldest (posting dates).
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!
In some cases (using to iterate large tables or not having indexes/keys in the SQL table), it could make sense using isempty() before findset().
But then you don't need to use the if statement again with the findset() method after the line "if isempty() then".
No, if you're using findset is because you really want the data.
Using ISEMPTY before a FINDSET will just perform an extra (unneeded) SQL command on the SQL server. So that would be wasting performance imho.
If that could actually speed up things, i would think that the SQL optimizer would do it internally before trying to get the dataset for you FINDSET command anyway.
Note: The SQL optimizer is a part of the SQL Server analyzing your queries and coming up with the best possible execution plan for it. it even uses statistics of the data in the tables for this and considers all enabled indexes - even if you did not state them with a SETCURRENTKEY in C/AL. And the best part: You don't even have to enable the SQL optimizer - it is just sitting there optimizing all your queries :-).
I have used if isempty before set based operations like deleteall, modifyall as well as findset in scenarios where I do not want to inadvertently escalate locks on a table unless absolutely necessary.
Just wondering on your opinion on a very common pattern I end up using all the time.
CustLedgerEntry.setcurrentkey("Customer No.");
if CustLedgerEntry.findset() then
repeat
CustLedgerEntry.setrange("Customer No.",CustLedgerEntry."Customer No.");
DoSomethingWithTheCustomer(CustLedgerEntry."Customer No.");
CustLedgerEntry.findlast();
CustLedgerEntry.setrange("Customer 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?
That looks absolutely .... (sorry) bonkers?
@@Hougaard LOL. Probably yes, but if it's Bonkers and it works...? Maybe it makes more sense to ask how would you do this task? (essentially find UNIQUE list of customer Nos. from a set of Ledger Entries)
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 ?
Check out the Query object type...
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?
It can be done, and is an acceptable solution for sortings that you rarely use.
@@Hougaard Usually, i would consider to create an own index, if i expect that the findset command results in a high quantity of records in the recordset, even if i use it rarely. Is this a kind of good practice or should i prefer to use setcurrentkey without a proper index in this case?
can you provide video on encryption.
Encryption in what context?
@@Hougaard Have some credentials were users inserts it so when user submits needed to be encrypted and also decrypted that data simultaneously..
If isempty () is a "hidden" find, i guess count() is a "hidden" find too?
Ahh, not really, it not "finding" any records, IsEmpty() is just the "false" part of any find* commands....
@@Hougaard I always thought that ISEMPTY is "cheaper" (in meanings of sql server performance) because it doesn't transfer any records to NAV while findfirst/findlast, find and findset always result in the transfer of records from sql server. So, if i only need to know if any record exist, i always should use isempty.