Sometimes you have a custom "while select" statement and you need to have one or more of the where clause fields in the statement to be optional. In this example, I will use a simple method to display the customer account and group for customers.

The method below displays the customer accounts for customers and uses the _custGroup argument to filter customers that match the customer group. The method works as expected but there is one drawback: it can only show customers for the specified customer group.

So, what if we want to show info for all customers with this method? Can't we just call the method and pass an empty string? The answer is yes, that's possible but will not work as one would expect. The problem with this is, that if you call the method and pass an empty string for the customer group, the while select statement will return all customers where the customer group is empty. Since the customer group field is mandatory, and all customers have a customer group, the query will return nothing.

Show customer info method

//show customer info with mandatory argument
public static void showCustomerInfo(CustGroupId _custGroup)
{
    CustTable custTable;

    while select custTable
        where custTable.CustGroup == _custGroup
    {
        info(strFmt("%1 - %2", custTable.AccountNum, custTable.CustGroup));
    }

}

How do we solve this problem? Your first thought might be to simply duplicate the while select statement in the method for each argument used in the where clause. In this example two statements, one statement with, and another statement whithout the customer group where clause. And simply add an "if" statement to check if the customer group argument has a value or not. Yes, this will work but this is definitely not the way to go. The reason is because if you have a large while select statement with many lines and a method with multiple arguments. The code will be hard to read and error prone when modifications are done to it.

Optional argument

So, what is the solution? By using the Boolean OR operator in the while select statement. The where clause for customer group is extended with an Boolean OR to contain two parts. The first part of the Boolean OR evaluates the method customer group argument variable, if this part of the Boolean OR evaluates to TRUE, the second part of the OR is simply ignored, effectively ignoring the where clause part for customer group in the second part. However, if the first part of the Boolean OR statement evaluates to FALSE, the second part of the boolean OR is then evaluated which effectively then applies the where clause of the customer group to the statement. Making the where clause optional and having compact and readible code.

//show customer info with optional argument
public static void showCustomerInfo(CustGroupId _custGroupId = '')
{
	CustTable custTable;

	while select custTable
		where (_custGroupId == '' || custTable.CustGroup == _custGroupId)
	{
		info(strFmt("%1 - %2", custTable.AccountNum, custTable.CustGroup));
	}

}

Complete example

You can run the class using the SysClassRunner class.
e.g. https://[your-environment].operations.eu.dynamics.com/?mi=SysClassRunner&cls=MyTestJob

public final class MyTestJob
{
    public static void main(Args _args)
    {
        CustGroupId custGroup;
        Dialog dlg = new Dialog('Enter customer group');
        DialogField fldCustGroup = dlg.addField(extendedTypeStr(CustGroupId));

        if (dlg.run())
        {
            custGroup = fldCustGroup.value();
            MyTestJob::showCustomerInfo(custGroup);
        }
    }

    //show customer info with optional argument
    public static void showCustomerInfo(CustGroupId _custGroup = '')
    {
        CustTable custTable;

        while select custTable
            where (_custGroup == '' || custTable.CustGroup == _custGroup)
        {
            info(strFmt("%1 - %2", custTable.AccountNum, custTable.CustGroup));
        }
    }

}