Populate virtual reference column with values from another table if they meet criteria

[Vitaliy_Slusar]

I’m a beginner. The goal is to have a virtual, reference column called [Period] in my “Transactions” table to be populated by values from the [Period] column (primary key) in the “Periods” table. The relevant [Period] will be chosen by making sure [Date] in “Transactions” falls between [Start date] and [End date] in “Periods”.

It was quite easy to achieve the opposite effect - to see all Related Transactions of a Period with this expression in a VC -

FILTER(“Transactions”,
AND(
[Date] >= [_THISROW].[Start date],
[Date] <= [_THISROW].[End date]))

Everything I’ve tried resulted in various errors (too long of a list), but I want to be able to drill down on the period when looking a transaction detail.

Extremely grateful in advance,
V

[hru]

Hi @Vitaliy_Slusar, Welcome to this community…

Maybe what you need is just something like below:

CONCATENATE("M"&MONTH([Date]))

for the reference column [Period] app formula, ref to “Periods” table.

[Steve]

Vitaliy_Slusar:

Everything I’ve tried resulted in various errors (too long of a list)

You’re going to need to list some, as you haven’t otherwise provided enough information.

[WillowMobileSys]

Vitaliy_Slusar:

The goal is to have a virtual, reference column called [Period] in my “Transactions” table to be populated by values from the [Period] column (primary key) in the “Periods” table.

I am gathering that you want to show “Related Periods” in the Transaction table?

That formula should be something like:

FILTER("Periods", AND ([Start Date] <= [_THISROW].[Date],
                       [End Date] >= [_THISROW].[Date]
                  )
 )

I assume you tried this with the VC defined as LIST type? If so what was the issue?