Need help selecting a value from another table

Tin_Nguyen1 · 09-03-2020 07:10 AM

I have Table one with products with prices. However, each product has two prices depending on the type.
For example, product 1 with type 1 = $35, product 1 with type 2 = $50.
I have invoice table. In the form, I select type 1/type 2 in one field. I also select product 1.
How can I select the right price from Table one, with type and product constraints? I can’t seem to figure out the right expression. Please help.

Steve

Try something like:

ANY(
  SELECT(
    product-table[Price],
    AND(
      ([Product] = [_THISROW].[Product]),
      ([Type] = [_THISROW].[Type])
    )
  )
)