Using lookups with calculated fields

[SescoDev]

I have a custom table that I'd like to add a field to that concatenates the values from a related row in another table. For example, in a table that contains line items for an order, I want to add a field that displays the name and description of an item. The name and description exist on the Items table.  The 'Line Item' table has a relationship connection to the Items table and the Orders table.

My first instinct is to create a calculated field that concatenates the item name and item description, but I'd like to avoid adding those columns to the 'Line Items' table if possible.

What's the best way to accomplish this? 

[Stevieed · Accepted solution]

Not sure how the relationship in your system works, it sounds like you have 3 or 2 tables?

Anyway, use power fx formula column in the target table:

formula example:

concatenate('Item'.name, 'Item'.description)

feel free to add any fixed seperator in the concatenate function to divide the text

Never use calculated field, they are **bleep**

[SescoDev]

Thank you, that worked like a charm. Some things I learned in the process:

1) The related table needs to be a lookup column.

2) Use the name of the lookup column, not the name of the lookup table. Example:

If I have Table "Line Items," a table named "Products" with the columns "name" and "description," and a lookup column in "Line Items" called "item", then for a column in "Line Items" that returns the composite name and description, I'd use this formula:

concatenate('item'.name & " " & 'item'.description)

Thanks for pointing me in the right direction, I hadn't even realized that I could use PowerFX as a column type! This opens up a lot of exciting possibilities.

[Stevieed]

Yes when you created a lookup column you created a relationship, then power fx is able to explore the relationship like an object.

[Gprior]

OH MY! I've been searching for this specific help for weeks! Thanks a lot!!!! I confirm it worked perfectly... the idea that the relation between table is an object and it is flexible is mind blowing!! Thank you again!