Looking UP a Value from Column in Related Table

[Scott_Hall]

Tables are:

Service Location

Service Location Repair

Waste Form (is a part of Service Location Repair)

Based on the selection in the [Waste Type] Column in the Waste Form I need to grab a price value from the Service Location table if possible. Otherwise grab the value from the Service Location Repair Table.

For instance in [Waste Type] column of the Waste Form if the enum selection is “Bio” I need the expression to fetch the price value from the “Bio” column in Service Location table ideally, otherwise in the Service Location Repair table if necessary. I have a virtual column set up in the Waste Form table to calculate the price then based on the quantity.

If you can tell me the type of expression (LookUP or Any Select …) this requires i think I can figure it out. Or combination of expression types and the order they should be written.

Thank you for any help.

[Steve]

Try:

ANY(
  LIST(
    LOOKUP(
      LOOKUP(
        [_THISROW].[Service Location Repair ID],
        "Service Location Repair",
        "Service Location Repair ID",
        "Service Location ID"
      ),
      "Service Location",
      "Service Location ID",
      "Bio"
    ),
    [Service Location Repair ID].[Bio]
  )
  - LIST("")
)

This expression is roughly equivalent to:

ANY(
  LIST(
    [Service Location Repair ID].[Service Location ID].[Bio],
    [Service Location Repair ID].[Bio]
  )
  - LIST("")
)

except you can’t chain dereferences (as in [Service Location Repair ID].[Service Location ID].[Bio]). Hence the nested LOOKUP() calls. Nested LOOKUP() calls are expensive and should be avoided in virtual column app formulas, but are mostly tolerable elsewhere.

The ANY(LIST(item, item, ...) - LIST(""))) construct provides a default list: LIST(item, item, ...) constructs a list of possibilities, - LIST("") removes any blank possibilities, and ANY(...) gives the first of the remaining non-blank possibilities.