So, you need to retrieve from the database all customers with the Name = ‘John’ OR the Email = ‘[email protected]’ in order to make something with each record from the resulting dataset. In other words you need to use a Multicolumn OR filter. What is the best way to do this in NAV?
Please do not do how I recently saw in an existing development: filter first on Name, get the dataset, make a repeat-until for this dataset, then filter on Email, get again a new dataset and make a second repeat-until for it.. This is not good for performance.
Instead you can use the following option:
- Filtergoup(-1): the code with this option would be:
WITH Customer DO BEGIN
FILTERGROUP(-1);
SETFILTER(Name,'John');
SETFILTER("E-Mail",'[email protected]');
IF FINDSET THEN
REPEAT
MESSAGE('Customer No: %1, Customer Name: %2',Customer."No.",Customer.Name);
UNTIL NEXT = 0;
END;
This will translate at SQL Server level in the following query:
So, you can retrieve the whole needed data set using only one query.
