How to access the value of a REF?

[gcor71]

Help needed for what is probably a super obvious question.

I have a column L_1st_nearest which is REF to a source table called LOCATION_DATA.
A typical value of the REF is "5th Portsmouth".
I want to shorten that to "5th".

I have set up a new virtual column to do this.  Using formula:
LEFT([L_1st_nearest], (FIND(" ", [L_1st_nearest]) - 1))

This doesn't work because the formula is operating on the row ID not the value of the RowID in the LOCATION_DATA table.

How do I access the value in the LOCATION_DATA table?

Like I said - fairly sure it's simple, but drawing a blank.  Help appreciated.

[WillowMobileSys · Accepted solution]

You would use dereferencing to get the actual value from the LOCATION_DATA column and you would do so like this:

[L_1st_nearest].[LOCATION_DATA]

Insert this into your expression instead of just [L_1st_nearest]

PRO TIP:  Add another column to your reference table named something like [SHORT_LOCATION_DATA] and assign it the App Formula expression to shorten the value like this:

LEFT([LOCATION_DATA], (FIND(" ", [LOCATION_DATA]) - 1))

This keeps the logic to shorten the data in a single common place - should it even need to be updated.  When you want grab this value instead then dereference like this:

 [L_1st_nearest].[SHORT_LOCATION_DATA]

 And you're done!

[gcor71]

Simple, elegant and beautifully explained.  Thank you!