I have a table that is "DeviceType". This table is a list of device types, initial cost, replacement device, and a calculated field for replacement cost.
ID DeviceType InitialCost ReplacementDevice ReplacementCost 1 NUC 700 700 2 Stick 550 NUC 700 3 OPS 1200 1200 4 Tiny 1200 NUC 700
DeviceType is text
InitialCost is currency
ReplacementDevice is lookupfield to devicetype
replacementcost is calculated field.
My question is regarding the calculated field. Since I'm specifying the replacement device type for any particular device, I want the replacement cost to either reflect the initial cost if no replacement device is selected, or the initial cost of whatever the selected replacement device is.
Currently my calculated field expression is IIf(IsNull([ReplacementPlayer]),[InitialCost],??). The question marks are where I'm stuck. I don't know how to look up the initial cost of the selected replacement device. Any suggestions?