Hello 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
