How to retrieve a Text field from a many table to a related one table?

[General Ledger]

How do I retrieve a Text field from a (many) Sales Detail table to a related (one) Sales Header table?

Invoice number 123 appears once in the Header table but multiple times in the Detail table.
All records for invoice 123 in the Detail table will have the same value for the Text field.

Can I do a VLOOKUP from the Header table to the Detail table?

 

[scottsen]

This should work:

=FIRSTNONBLANK(SalesDetail[TheTextColumn], 1)

 

[General Ledger]

scottsen,

I didn't try this yet but it looks like it should work. I never heard of FIRSTNONBLANK before. I am assuming that the function starts at the top of the records and searches down for the first non-blank value. Therefore, the sort order of the records would impact the value returned. Correct?

G/L

 

[scottsen]

Reading back my answer was almost certainly incomplete, but I would need more details on the specific usage. FIRSTNONBLANK is just a trick to get around the fact that MIN/MAX don't work on Text fields. I was apparently assuming that you would be in the context of just 1 value of the text field.

In general to "lookup" the value in a related table, you just use =RELATED()

How/where you need this? calc column? calc field?