Hi,

Is it possible to create a calculated field in which the result depends on the context?

I have a table which is linked to two tables, but in two different context.

Let say that physically there's a table D and a table E. Table D lookups in table E.

Table D is linked to table C

There's another occurence of the related tables D and E in the schema which is linked to table B.

Table C is linked to table A

Table B is linked to table A

So I repeat the question Is it possible to create a calculated field in table D in which the result depends on the context?

Example of the calculation:

if the foreign key is C then

lookup in E

else if the foreign key is B then

lookup in E2

else

do nothing

end if

For visualization purpose I attaced an image

This part is not clear:

if the foreign key is C

In general, a calculation field's context is hardwired in the formula's definition. This is a necessity, since a calculation must return a consistent result, regardless of where user happens to be.

However, you could have two lookups, and select between between them - IF you can find a criteria by which to select between them. For example, if records in D are arranged in such a way that only one of the lookups will return a value, you could just concatenate the two results.

You can add a B2 and an E3 and relate them to D1, the same way B1 and E2 are related to D2.

The workaround I've done is quite awkward, but I duplicated the table structure. This way, D2 can lookup in E2.

The problem stays in the maintenance, each time I need to modify D I have to replicate the modification in D2 manually...

Thanx DJ it worked like a charm!