Export project filter for data entity is not working

(0) Share

Report

Posted on by DELDYN

536

Hi,

I just want to know if this filter i put is doable via the UI or would i need to do it by code.
I'm exporting this entity: /InventTransferOrderHeaderEntity/

I've read an article about advanced query filter, so i've put this filter on the field TransferOrderStatus
https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/fin-ops/get-started/advanced-filtering-query-options#advanced-query-syntax

((TransferOrderStatus = /Shipped/) && ((ReceivingAddressLocationId= /1234/) || (ShippingAddressLocationId = /1234/)))

However, the range is not working as expected, because when i export the entity i get nothing

i created a new transfer, i've put the ReceivingAddressLocationId as 1234 and the status is shipped, but the export is showing nothing.

I have the same question (0)

All responses (9)

Answers (0)

Sort by

Martin Dráb 239,055 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

This is a duplicate - it's already being discussed in Advanced filtering query syntax.

Was this reply helpful? Yes No
DELDYN 536 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

it's not 100% duplicate, because i think no one will look at the other thread as there are already lots of comments. At first i was facing an error in the syntax, but now no error appears, however, the export is returning 0 records.
So i just want to know if it's doable via the UI or not?
i know i've asked this already in the other thread, but i didn't get any answer

Was this reply helpful? Yes No
Martin Dráb 239,055 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

All right, if you don't want to discuss this question in the other thread anymore, mentioned it there. You can add a link to this thread. Also, you're saying that your syntax error has been resolved, but I see no verified answer there. Please look at it.

I wouldn't focus on UI at all - I would test the query. I would create a runnable class with the query, because it'll make testing much easier and you can easily share the class with others. And I would check what SQL code you get.

Was this reply helpful? Yes No
DELDYN 536 on at

Like (0)

Report
Copy link

Link copied!

Hi Martin,

how to get the query of the export project?

I tried to verify the answer in the other thread but it keeps loading

Was this reply helpful? Yes No
Martin Dráb 239,055 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
You can do something like this:
public static void main(Args _args) { Query query = new Query(); QueryBuildDataSource qbds = query.addDataSource(tableNum(InventTransferOrderHeaderEntity)); qbds.addRange(fieldNum(InventTransferOrderHeaderEntity, TransferOrderStatus)).value(queryValue(InventTransferStatus::Shipped)); qbds.addRange(fieldNum(InventTransferOrderHeaderEntity, ReceivingAddressLocationId)) .value("((ReceivingAddressLocationId = '1234') || (ShippingAddressLocationId = '1234'))"); info(query.getSQLStatement()); QueryRun qr = new QueryRun(query); while (qr.next()) { InventTransferOrderHeaderEntity header = qr.getNo(1); info(header.TransferOrderNumber); } }

By the way, I noticed that TransferOrderStatus doesn't need to be in the string expressing, that's why I simplified your solution by moving it to a separate range.

Was this reply helpful? Yes No
DELDYN 536 on at

Like (0)

Report
Copy link

Link copied!
Hi Martin,

I think i know what the issue is.
I just replaced ReceivingAddressLocationId with ReceivingWarehouseId and ShippingAddressLocationId with ShippingWarehouseId
and the filter is now working.

I also amended your runnable class with these new fields, however I've noticed that the infolog for "info(query.getSQLStatement());" doesn't contain the filter and the while loop returned un-needed records. However, if i hover over the query itself, i can see the filter -- why is that?

query:
SELECT * FROM InventTransferOrderHeaderEntity(InventTransferOrderHeaderEntity_1)
WHERE ((TransferOrderStatus = 1)) AND ((((ReceivingWarehouseId = '1234') || (ShippingWarehouseId = '1234'))))}

query.getSQLStatement():

SELECT T1.ATPDELAYEDDEMANDOFFSETDAYS,T1.ATPDELAYEDSUPPLYOFFSETDAYS......etc FROM INVENTTRANSFERORDERHEADERENTITY T1 WHERE ((((((((PARTITION=5637144576) AND (DATAAREAID=N'AA')) AND ((PARTITION#2=5637144576) OR (PARTITION#2 IS NULL))) AND ((PARTITION#3=5637144576) OR (PARTITION#3 IS NULL))) AND ((PARTITION#4=5637144576) OR (PARTITION#4 IS NULL))) AND ((PARTITION#5=5637144576) OR (PARTITION#5 IS NULL))) AND ((PARTITION#6=5637144576) OR (PARTITION#6 IS NULL))) AND ((DATAAREAID#6=N'AA') OR (DATAAREAID#6 IS NULL))) ORDER BY T1.TRANSFERORDERNUMBER

Was this reply helpful? Yes No
Waed Ayyad 9,090 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi DELDYN,

Is your issue resolved?

Was this reply helpful? Yes No
DELDYN 536 on at

Like (1)

Report
Copy link

Link copied!

I've asked Martin a question

Was this reply helpful? Yes No
Martin Dráb 239,055 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Please show us your current code and give us an example of a record that you wanted to filter out but it's returned by your query.

Was this reply helpful? Yes No