Export project filter for data entity is not working

(0) Share

Report

Posted on by DELDYN

304

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.

All responses (9)

Answers (0)

Martin Dráb 230,445 Most Valuable Professional on at

Like (0)

Report

Export project filter for data entity is not working

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.
DELDYN 304 on at

Like (1)

Report

Export project filter for data entity is not working

I've asked Martin a question
Waed Ayyad 6,408 Super User 2024 Season 2 on at

Like (0)

Report

Export project filter for data entity is not working

Hi DELDYN,

Is your issue resolved?
DELDYN 304 on at

Like (0)

Report
Export project filter for data entity is not working

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
Martin Dráb 230,445 Most Valuable Professional on at

Like (1)

Report
Export project filter for data entity is not working

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.
DELDYN 304 on at

Like (0)

Report

Export project filter for data entity is not working

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
Martin Dráb 230,445 Most Valuable Professional on at

Like (0)

Report

Export project filter for data entity is not working

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.
DELDYN 304 on at

Like (0)

Report

Export project filter for data entity is not working

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
Martin Dráb 230,445 Most Valuable Professional on at

Like (1)

Report

Export project filter for data entity is not working

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