Doing some math on related column values from another table on a condition on another column

[JamesWB]

Hi, I have a table called Loop where I want to create a virtual column that does a subtraction on two fields from a related table called Device. There are always two devices, one where [Device][Zone] = "Safe" and the other where [Device][Zone]="Hazardous".

In the virtual field, [Loop][SubDeviceC], I want to look at two other columns in Device that vary depending on Zone.

For the device where Zone = Safe, then the column [Device][Co] = is set to a decimal value.
For the second device where Zone = Hazardous, then the column [Device][Ci] = is set to a decimal value.

I want to do the sum [Device][Co] - [Device][Ci] for the for the virtual column in Loop on the linked devices.

Loop has a list column to the Related Devices. Device is linked to Loop via a Ref column, Loop.

It feels like this should be simple, but perhaps it isn't?

.   

[TeeSee1 · Accepted solution]

You can obtain the value of any field from the table associated with the related xxx field by something like the following.

LOOKUP(INDEX([Related Devices],1), "Device", "Device Table Key", "Co") will get you the value of the field [Co] for the first element of the [Related_Devices] field.

If you do not know which element (first or second) belongs to which type of zone, then you want to check the zone type first and use IF() to get the right calculation formula.

Hope this helps.