A saved search is created to show item fulfillments created from sales order, along with the sales order amount in base currency and consolidated, COGs amount in base currency and consolidated and the consolidated exchange rate.
The issue is the formula used to calculate the consolidated exchange rate is Formula (Currency) as following : Case when {appliedtotransaction.amount} != 0 then {appliedtotransaction.amount} / {appliedtotransaction.fxamount} End, but as it depends on the sales order amount, if the sales order's amount is zero, then the result of this column is null. So I've edited it to try to pull different amount to calculate the consolidated exchange rate by using the amount debit and amount debit foreign currency of the item fulfillment as the following: Case when {appliedtotransaction.amount} != 0 then {appliedtotransaction.amount} / {appliedtotransaction.fxamount} WHEN {appliedtotransaction.amount} = 0 THEN {debitamount} / {debitfxamount} ELSE 0 End, but it didn't work.
Here is the saved search criteria used:
Results Tab:
And the Consolidated Exchange Rate is Per-account
But then I've changed this criteria, Applied to Transaction: Fulfillment/Receipt Status (Line Level), to make the saved search pull the amount debit from the item fulfillment gl impact. As this criteria Applied to Transaction: Fulfillment/Receipt Status (Line Level) make the saved search show item fulfillments created from the sales order only. I've replaced it with created from type as sales order, but will show the consolidated exchange rate as null as well.
If I removed this criteria, this will show the consolidated exchange rate but will also result in showing multiple lines for item fulfillments even if they only one item.
Is there any idea how to solve this?