Conditional Between Ref or Enum

[JP-AN]

Hi, I have a problem solving a formula to achieve the following functionality. In the "Movimientos" table, I have a column called "Especie" (Reference to the "Especies" table).

What I want to achieve is that the user selects a value for the "Especie" column (Ref) if the value of "Tipo de Movimiento" column is equal to "Ingreso".
Now, if "Tipo de Movimiento" column has a value equal to "Egreso", "Especie" should take the value from another table, which I am obtaining with the following formula: LOOKUP([_THISROW].[contrato], "contratos", "id_contrato", "ESPECIE_CONT").

I tried with :
IF([Tipo de Movimiento] = "Ingreso", [Especie], LOOKUP([_THISROW].[contrato], "contratos", "id_contrato", "ESPECIE_CONT"))
But is not working, jeje...


I appreciate the help you can provide. Regards.

[TeeSee1 · Accepted solution]

You cannot dynamically change the referenced table because it is statically defined in the table column definition. So you need to create two different columns and control "show_if" based on "Tipo de Movimiento"