0 votes

Here's the context:

I have a field called F_FUNCTION in the table CONT_COMP. This field is an integer value refering to the lookup table LK_CONT_COMP_FUNCTION. In this lookup there is obviously the field F_FUNCTION and there's no problem getting its value.

But in the very same lookup, there's another field called F_STATUS (for each F_FUNCTION there is a F_STATUS). This field contains values like "gold"/"silver"/"aucun" and I'd like to get those values in my custom view.

Lookup picture

So my goal would be to make a join between the CONT_COMP table and the LK_CONT_COMP_FUNCTION lookup table so I can have access to values, stored in other columns of the lookup table, in my custom view.

Is there a simple way to achieve that?

asked in Efficy/ Client side by (116 points)

1 Answer

+2 votes
Best answer

Hi Alexandre

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.

Companies contains 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 COMPANIES.

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.

Query Edit

And this is the query result
Query Result

answered by (7k points)
selected by
Thanks for the solution ! The only thing that needs to be updated if necessary is the length of the LOOKUPASSOCIATE field, as 64 characters might be too short for the subquery to fit.
what are the "USAGE" acceptable values and meaning?
For calculated/virtual fields, the usage is 'P'. I believe it comes from PROCEDURE
If LOOKUPASSOCIATE starts with '=', Efficy will use it as inline expression. If the '=' is omitted, it will call it as a SQL Function. Parameter value is passed using K_LOOKUPFIELD and K_LOOKUPTABLE.

You don't need to know other values, imo.
Ok so i ve test it It was kind of tricky to get how the K_LOOKUPFIELD and K_LOOKUPTABLE are passed but i think i got it.

do the field HAS to be prefixed by V_ or is it the P usage that will define it as "virtual"?
VERY GOOD NEWS:
i add 2 fields in my sys_fields : one called V_TEST that return an integer from a proc and one called R_V_TEST that return the nvarchar correspondant to the integer from a a proc as well.

when you add in the query builder V_TEST, it display the R_V_TEST result because it works like a lookup and so the cool filter via checkboxes feature works in the query result.

that s dope. cheers
Glad you succeeded. "V_" prefix is just an agreement, USAGE='P' does the magic
1,192 questions
1,455 answers
1,755 comments
327 users