The views and opinions expressed in this blog are those solely of the author(s) and do not necessarily reflect Microsoft’s current policy, position, or branding. For official announcements and guidance on Dynamics 365 apps and services, please visit the Microsoft Dynamics 365 Blog.
Now Available in Community - New TechTalk Videos for 2020
2020 release wave 1 Discover the latest updates and new features to Dynamics 365 planned through September 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 Timeline
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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:
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"];
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.
Business Applications communities