“in” operator in D365FO

Yesterday Michael Fruergaard Pontoppidan published a brief blog post New capability in X++ : The In operator. He mentioned that this feature went unnoticed by most, which is my case too. And I didn’t find anything even when I explicitly looked for more information.

Anyway, such an operator can be very handy and I’m quite sure that everybody who knows IN() operator in T-SQL sometimes missed it in X++.

In short, it allows you to check if a field value is in a set of expected values. For example, if I want to find all sales orders with status either Delivered or Invoice, I can do this:

SalesTable st;
container statuses = [SalesStatus::Delivered, SalesStatus::Invoiced];
 
select from st
    where st.SalesStatus in statuses;

The generated code is what you had to do when there was no ‘in’ operator – it uses OR:

SELECT * FROM SALESTABLE T1
    WHERE (((PARTITION=123) AND (DATAAREAID=N'dat'))
    AND ((SALESSTATUS=3) OR (SALESSTATUS=2)))

I wanted to know what else I can do with this operator and because I didn’t find any documentation, I tried a few things by myself. Note that my environment has platform update 20; it might behave differently in different versions.

I wondered if I can’t use a container directly, instead of putting it to a variable. It would make things simpler if the set is known at design time.

select count(RecId) from st
    where st.SalesStatus in [SalesStatus::Delivered, SalesStatus::Invoiced];

The editor didn’t show any error, but compilation blew up completely:

Abnormal termination with unhandled exception. Exception key: a59d89f7-fe80-4ab3-a420-9a6ba9a30bca. System.NullReferenceException: Object reference not set to an instance of an object.

Hmm, let’s try something else. Which data types can I use? What about a set of string values, which is a common scenario?

container ids = ["S01", "S02", "S03"];
 
select st
    where st.SalesId in ids;

This fails already in the editor. The compilation error is: Types ‘str’ and ‘container’ are not compatible with operator ‘in’.

The last thing I tried was using ‘in’ operator outside a query. I didn’t expect it to work and it indeed doesn’t. This doesn’t compile; it fails with ‘)’ expected.

if (highestStatus in statuses)

The ‘in’ operator is useful and I’m glad it’s been added. But I see space for improvement, especially the ability of using it with string fields would be very useful.