Display value in calculated field

[Farnsbarnes]

Does anyone know how to access the display value of a field when using a formula inside a calculated field with sql?

The critical line for me is below.  In my table the field 'Column_values' is referenced to another table, but displays text in place of the underlying reference data.

 So far I can only manage to access the references, not the text display values.

WHEN CHARINDEX('[@myexample]','[@field:Column_values]') > 0 THEN ...

 

Other than the above I've tried '[@lookupfield:Column_values]',   '[@lookupvalue:Column_values]',  and all kinds of other combinations, but I just can't get the right syntax.

Any help much appreciated.

 

[Farnsbarnes]

Many thanks for your reply @Vitalikssssss

What a huge shame this is, and a strange omission by Caspio given that values can be looked up in HTML blocks, JS code, etc.  I appreciate your advice with the View and I'll follow it, but it's going to be detrimental on performance as my records run to the low millions.  How I wish Caspio would expand its sql capability in calculated fields.

Best

Farnsbarnes

[Vitalikssssss]

Hi @Farnsbarnes,

I am afraid that it is not possible to reference a look up value with any syntax.

I can recommend creating a View to join your tables (parent child) and use this view as a datasource for the Datapage. 

You will be able to reference the parent field by using a parameter picker.

Hope this helps.

Regards,

vitalikssssss