Multiple foreign key relationships with customSelectExpression

[edulid]

Hello,

I have a question regarding (multiple) foreign key relationships in ds files.

I have 3 tables: A, B and C
A contains a foreign key fk_B_id pointing to B, and B contains a foreign key fk_C_id pointing to C. C contains a simple field "myField"

I have these fields in A.ds.xml:

<field name="fk_B_id" foreignKey="B.f_id" joinType="outer" displayField="B_text" useLocalDisplayFieldValue="true"/>
<field name="B_text" includeFrom="B.C_text" hidden="true"/>

Fields in B.ds.xml:

<field name="fk_C_id" foreignKey="C.f_id" joinType="outer" displayField="C_text" useLocalDisplayFieldValue="true"/>
<field name="C_text" includeFrom="C.myField" hidden="true"/>

I have problems defining C_text. If I leave it as shown: (<field name="C_text" includeFrom="C.myField" hidden="true"/> ) everything seems to work. When I include "A.fk_B_id" in a form, it shows the values of "C.myField" correctly.

But I need a customSelectStatement instead, something like:
<field name="C_text" customSelectStatement="case when 1 then C.myField else 'a text' end" includeFrom="C.myField" hidden="true"/>

As I read in the forum, customSelectStatement and includeFrom do not work together. But how can I achieve what I am trying to do?

Using smartgwt 6.0p power.

[Blama]

Hi edulid,

you need foreignKeys and includeFrom for join generation - even if you don't use the includeFrom field in your application.
Once you have your join, you can do customSelectExpressions as you like - at least, that's the case for me.
As the CSE will rely on the joined table's name, I also use relatedTableAlias in that case with the foreignKey field in order to be sure to have fixed table names even if SmartGWT join-naming changes or if you include a 2nd join to an already joined table (here, without relatedTableAlias, you don't have control over the generated tables name. And there will be a generated name, as the names need to differ).

So, what I would do: in B.fk_C_id use a relatedTableAlias that is the tableName of C.
In C.myField do you customSelectExpression with table-name prefix (customSelectExpression ="case Cname.myfield.... end"). This will then work when joined and also when using table C alone.

That way you don't have customSelectExpression and includeFrom in the same field.

Best regards
Blama