637211566 {[FK REPLACEMENT FAILED]} issue in joining two table

Finance and Operations/AX Developer Forum
1

Hi friends,

i am getting issue in joining two table InventTransferTable and LogisticsPostalAddress

when i debug the same got the value of InventTransferTable.ToPostalAddress value as ‘637211566 {[FK REPLACEMENT FAILED]}’ and when i pass the this value to LogisticsPostalAddress.RecId then nothing found in the query.

this Query Not giving result while checking mannual then data exist in the table, this query also working on SQL server

select InventTransferTable join LogisticsPostalAddress

where InventTransferTable.ToPostalAddress == LogisticsPostalAddress.RecId &&

InventTransferTable.TransferId ==“WCL16TN000039”;

Any help in this regard pls.

Thanks in Advance.

2

Look closely at the address with RecId == 63721156. If it exists but it’s not returned by the select statement, the only explanation I can come with is that it’s not valid for the current date. Check values of ValidFrom and ValidTo fields to confirm that.

If you want to query date-effective tables for specific dates(s), use select validTimeState(myDateFrom, myDateTo).

3

Thanks martin for your quick response.

Actually you are right, to fetch record i have to use validTimeState(dateFrom, dateTo) in query but problem when i have to join this table with multiple tables.

single table query worke fine

select validTimeState(dateFrom, dateTo) * from LogisticsPostalAddress where LogisticsPostalAddress.RecId == 5637211566

but when i want to use it in join then it give syntax errors

select recid from InventTransferTable join validTimeState(dateFrom, dateTo) from LogisticsPostalAddress

where InventTransferTable.ToPostalAddress == LogisticsPostalAddress.RecId &&

InventTransferTable.TransferId ==“WCL16TN000039”;

Thanks

4

It’s valid for the whole select statement; you can’t set it on individual joined tables.

select validTimeState(dateFrom, dateTo) RecId from inventTransferTable
join validTimeState(dateFrom, dateTo) from logisticsPostalAddress
    where logisticsPostalAddress.RecId == inventTransferTable.ToPostalAddress
       && inventTransferTable.TransferId =="WCL16TN000039";