Conversion failed when converting the varchar value 'ONLINE DISCOUNT' to data type int
I believe you need to use the InventoryCD field rather than InventoryID. The InventoryID is an integer assigned internally by the system so it is giving an error when you are trying to match it to the text string 'ONLINE Discount'. I believe there are two solutions you could use in this case.
The first would be to add the InventoryItem table to your GI and then join the ARTran table to the InventoryItem table through the InventoryID. You can then update your formula to say if the InventoryItem .InventoryCD = 'ONLINE DISCOUNT'.
The other option would be to lookup what the internal InventoryID is and then just update your formula so that it is equal to this value rather than 'ONLINE DISCOUNT'.
Thanks for the response Brandon... I employed this method -> "The first would be to add the InventoryItem table to your GI and then join the ARTran table to the InventoryItem table through the InventoryID. You can then update your formula to say if the InventoryItem .InventoryCD = 'ONLINE DISCOUNT'."
I am somewhat new to Acumatica and having some issues with the naming and display conventions... oddly enough when the ARTran.InventoryID is used with non-integer values in the conditions section it works with no errors.
Acumatica does some behind the scenes magic to map CD text values to ID internal numeric values when you directly reference a field in GI, whether it be in conditions or results. However this doesn't work if you try to use an ID field directly in a formula as you did. The field needs to be selected from the Data Field dropdown (and not entered as a formula).
If you use the ID field in a condition, you can also click the From Schema option and then select the value from a list. This is much more user friendly as you can directly see the selected CD value in your query criteria rather than and ID value which would be meaningless. If after selecting a value with the From Schema option enabled you untick From Schema, you will then see the internal ID value in the query criteria. This is an easy way to lookup ID values in queries if you need to.