You can make a calculated field and use it in queries and queryviews. I'll demonstrate how to achieve this using a standard field and lookup. You can do the conversion to your custom fields.
COUNTRY and is bounded to
LK_COUNTRY. The displayed text field is
COUNTRY. What I will show you is how you can also have a column containing the
COUNTRYSHORT value available in
Insert the calculated/virtual field definition inside
SYS_FIELDS with this SQL.
insert into SYS_FIELDS (K_TABLE, K_FIELD, K_LABEL, NAME, ISCUSTOM, DATATYPE, DATALENGTH, USAGE, LOOKUPASSOCIATE)
values (10000, 900, 10000900, 'V_COUNTRYSHORT', '1', 'A', 3, 'P', '=select COUNTRYSHORT from LK_COUNTRY where K_COUNTRY=COMPANIES.COUNTRY')
We use an inline expression to construct a sub-query. This is easier than the join operation.
Refresh the database connections and you should be able to select the field in the query builder. You can also translate the field label.
And this is the query result