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

AppSheet AppSheet Q&A
1

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.

2X_b_bd1b4e0ca0244a5222b1dde3e6a576ec5c7febdf.jpeg
2X_b_bd1b4e0ca0244a5222b1dde3e6a576ec5c7febdf.jpeg2964×1656 399 KB

Extremely grateful in advance,
V

1 Like
2

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.

3

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.

4

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?

1 Like