Reference to secondary field

AppSheet AppSheet Q&A
1

Hello everyone. Query about expressions.
I have two tables.

A table is of dates (read only), where the [Row Id] is the date itself since it is unrepeatable and unique. It has 4 fields.
[RowId], [Month] [Year] [Period]
The Month, Year, and Period values are calculated by themselves.

Month → MONTH([DATE])
Year–>YEAR([DATE])
Period–>CONCATENATE(Month;“-”;“Year”)

Another table that is for salaries where there is a field called Period (Ref Type) and from where I want to be able to select a period from the dates table.

Use the following suggested value:

UNIQUE(
ORDERBY(
SELECT(
Dates[Period],TRUE()
),[Date]
)
)

But all the suggested values have a warning with an exclamation point.

The label, in the dates table, is Date.

Does anyone know how I can get that listing?

Thankyou

2

A question. Is the period field in the Salaries table a simple TEXT type and NOT a ref type?

2 Likes
3

Hi @TeeSee1 good day. In the salary table, the period field is of type Ref.

4

So you’re pulling a non-key column in as options for a Ref column. Yes obviously there would be yellow exclamations in that case. Not sure where your misunderstanding is here. Perhaps a review of how references, keys, and labels work?

https://help.appsheet.com/en/articles/961426-references-between-tables

https://help.appsheet.com/en/articles/1023086-what-is-a-key

https://help.appsheet.com/en/articles/961466-row-labels

1 Like
5

I know, I’m trying to extract a non-key listing, but that was on purpose. Is it possible to do that? Is there an expression to do it? I have dates of 40 years in a list, a formula that calculates months, another that calculates years and another that calculates periods in successive columns. The periods are concatenations of months and years, which greatly reduces the list since it is monthly and they are all unrepeatable. I need to bring the periods. What way do I do it? Thank you

6

Just change the column type to Text, instead of Ref?

2 Likes
7

Thanks Marc!

1 Like