Virtual Column of value from reference list

[R41Z0R]

Hi there,

I have a problem getting a function done. I have two tables, one table has my customers with a customer-id that is unique, the other tables holds contracts of customers with a reference value customer-id. The reference is working and so on.

The table contracts has a column called “contracttype” of type enum with the values: Main, second, third.

Now I want to have a virtual column in my main table customer called “main contract” that gives me the name (other column in contract) of that contract.

My formula looks like this, but returns multiple values, I don’t know how i make that double selection.

SELECT(contract[contracttype], [customer_id] = [_THISROW].[customer_id]) <-- Now how i filter it by the term “Main”.

I tried this:
SELECT(contract[contracttype], AND([customer_id] = [_THISROW].[customer_id]), contract[contracttype] = “Main”) <-- But tells me Cannot compare List with Text.

I’m happy for any help, thanks.

[Bahbus · Accepted solution]

R41Z0R:

SELECT(contract[contracttype], AND([customer_id] = [_THISROW].[customer_id]), contract[contracttype] = “Main”)

contract[contracttype] is the List of all [contracttype]s within the table contract.

SELECT(
    contract[contracttype], 
    AND(
        [customer_id] = [_THISROW].[customer_id], 
        “Main” = [contracttype]
    )
)

[R41Z0R]

Amazing, thanks for the tipp, works like a charm