A Virtual Column set to TYPE=Text always returns NaN

hi,
looks like this:


formula is this:
LOOKUP([STAFF MEMBER], “STAFF LIST”, “E-Mail Address”, “DATE START”)
where DATE START is concatenated text from another Date column in STAFF LIST
but it always displays like this:

when (deleted and) created TYPE is always Number and I change to Text.

I have other APPS with VC’s that calculate from columns in the same table they are in,
but this time i need to read from another table in the APP, is this the reason it fails?

cheers,
Mike

an update, team.

i moved the formula i’m trying to get to stop desplaying NaN:
LOOKUP([STAFF MEMBER], “STAFF LIST”, “E-Mail Address”, “DATE START”)
to a Text TYPE column in the table, as opposed to Virtual,
and still get the same error!

column “DATE START” in table “STAFF LIST” is Text Format and static values like:
Mon 11/11/2019

stumped!

And further,

if i change my formula to read another text column like this:
LOOKUP([STAFF MEMBER], “STAFF LIST”, “E-Mail Address”, “DATE START”)
it works fine

apologies for the typo:

And further,

if i change my formula to read another text column like this:
LOOKUP([STAFF MEMBER], “STAFF LIST”, “E-Mail Address”, “LOCATION”)
it works fine

Interesting,

in the Columns definition,
if i move my formula from INITIAL VALUE and put it in FORMULA,
or viceversa
the TYPE changes automatically from Text to Number. ?
so i am forced to change it to Txt.

just to test, i changed the TYPE to Date, and it gave this error:
The expression is valid but its result type ‘Number’ is not one of the expected types: Date
so, the LOOKUP yields a number result from a text column…

back to TYPE Text, even this fails with NaN:
CONCATENATE(" ",LOOKUP([STAFF MEMBER], “STAFF LIST”, “E-Mail Address”, “DATE START”))

!?

Please try to use LOOKUP([_THISROW].[STAFF MEMBER],“STAFF LIST”,“E-Mail Address”,“DATE START”)

1 Like