Hi guys,
May I ask, if I want to query using X++ with multiple string, just like IN clause in SQL Server, can I do that ? and what is look like ?
For example I want to query SalesTable, and I have a condition whether my source provide me a Sales Order Number, or not, and also possible the Sales Order Number provided is multiple.
Can we do it in single query ?
FYI, I'm using "select query" not the QueryBuilder.
Thanks,
You should stop hesitate and start using it, because it allows you to generate queries in a dynamic way, instead of having the structure hard-coded in X++ select statements. It's exactly what you need, IMO.
It's not the only way - you could also use SysDA framework (which is better for performance but awkward to use).
Hi Martin,
Which mean there is no other solution except QueryBuilder ?
Reason I hesitant to use it, is only for standard since the current code I'm handling is using Sql query.
Thanks
Use a query object and add a range for each value. For example:
Query query = new Query(); QueryBuildDataSource salesTableDs = query.addDataSource(tableNum(SalesTable)); salesTableDs.addRange(fieldNum(SalesTable, SalesId)).value(queryValue('SalesId1')); salesTableDs.addRange(fieldNum(SalesTable, SalesId)).value(queryValue('SalesId2')); QueryRun qr = new QueryRun(qr); while (qr.next()) { SalesTable salesTable = qr.get(tableNum(SalesTable)); }
Thanks for sharing the link. Is there any reason you are using select query instead of QueryBuilder.
Hi Mohit,
Thanks, Upon trying (also searching), it looks like it is only support Enum Values as the example. I have error when using to check my table which has multiple customer account, with something like this :
Container conCustomer; while select CustAccount from MyTableLine group by CustAccount where MyTableLine.Number == _Number { conCustomer = [MyTableLine.CustAccount]; } while select MySecondTable where CustAccount in conCustomer
The editor already threw error saying Types ‘str’ and ‘container’ are not compatible with operator ‘in’.
This is also mentioned in Martin Drab's blog : https://dev.goshoom.net/2018/12/in-operator-in-d365fo/
Thanks.
Hi Axel, As per this article IN operator is supported in F&O. This is interesting as I think it was not supported in previous versions. Let us know if it works for you.
André Arnaud de Cal...
291,979
Super User 2025 Season 1
Martin Dráb
230,848
Most Valuable Professional
nmaenpaa
101,156