Share
Report
32Hello everyone.
I'm trying to join 2 data sources in D365FO to build a view, but require to have Relations to be done as a condition.
In short, I need to add additional relation to ON statement when item is discontinued to make sure no other Size is selected.
In SQL it looks like the below code.
SELECT * FROM CustomerItem i
JOIN AvailInventory a
ON
i.itemBarCode = a.itemBarCode
AND i.InventLocation = a.InventLocationId
AND
CASE
WHEN IOVDISCONTINUED = 1 AND i.INVENTSIZEID = a.INVENTSIZEID THEN
1
WHEN IOVDISCONTINUED <> 1 AND i.INVENTSIZEID = a.INVENTSIZEID THEN
1
WHEN IOVDISCONTINUED <> 1 AND i.INVENTSIZEID <> a.INVENTSIZEID THEN
1
ELSE 0
END = 1
WHERE i.ITEMBARCODE = '1234567' AND
a.ONHAND > 0 AND a.PdsDispositionCode = 'AVAILABLE'
AND i.ACCOUNTNUM = '123456'
Does anyone have an experience with building views with dynamic join or something like that?
Thank you all in advance for your input and experience. It's always appreciated.
Alex
All responses (
Answers (
