The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
Jan 28, 2025 04:21 PM
I have and “hours” table whose rows consist of the following relevant fields:
There will be separate rows with the same email address for each event where hours are recorded.
I want to create a query (filter, group by,…) that answers this question. Which people (email address) participated in at least one event in both 2022 and 2023.
Following one suggestion, I
Solved! Go to Solution.
Jan 29, 2025 04:21 AM
Hi,
The formula not working because in a single table, records 'doesn't know' about each other. The common approach to all kind of 'vertical' totals, grouping, creating pivot table is - to create other linked table. There can be a lot of such tables including temporary..
You need to ungroup you table, then just duplicate email field and make it linked
In a new table, you can add Year as lookup and filter as you need
Jan 31, 2025 12:37 PM
Well, I just asked LLM to create a small table with email, hours and year.
I think you don't need to change anything.
But in cases like this you can't choose text format because data type and format are different things.
You need to explicitly convert numeric data into text or vise versa.
To get text data you can add another field with CONCATENATE({Lookup field}) - it will convert array to a text string. Or, in source table you can change formula. YEAR(DATE) is number , but YEAR(Date) & `` is text, just add empty string or wrap it into CONCATENATE()
Jan 29, 2025 04:21 AM
Hi,
The formula not working because in a single table, records 'doesn't know' about each other. The common approach to all kind of 'vertical' totals, grouping, creating pivot table is - to create other linked table. There can be a lot of such tables including temporary..
You need to ungroup you table, then just duplicate email field and make it linked
In a new table, you can add Year as lookup and filter as you need
Jan 29, 2025 07:39 AM
Thank you Alexey - I was able to follow your explanation and the solution worked. The only peculiarity was that your where clause used the "contains" operator, but I was not given that option. When I added filters for "year lookup", my only options were logical operators such as ==,>,<, empty, etc. I thought perhaps this is due to something be a number versus text but was not sure? I attached a screenshot of the filter drop down and the year lookup field
Jan 29, 2025 11:50 PM
Yes, you are right. Lookup is computed field and it depends on format in source table. I intentionally made the Year field in text format, because I wasn't sure how to filter 2 different years with "=". But as we can see, in Lookup, '=' works in the same way as 'contains'.
Jan 31, 2025 08:37 AM
Thanks for confirming; helps me to improve my airtable proficiency. It is curious to me that "is" or "=" works since the lookup is a kind of list or array of values and those operators would suggest an exact match to the entire list rather than "contains". Anyway, I tried to duplicate what you did, but when I try to change the format of the "year" field it does not provide "text" as an option. The Year field in the original table was actually a formula that extracts year from a date (see screenshot attached) and I could not find a way to make either that field or the field in the copied table a text field
Jan 31, 2025 12:37 PM
Well, I just asked LLM to create a small table with email, hours and year.
I think you don't need to change anything.
But in cases like this you can't choose text format because data type and format are different things.
You need to explicitly convert numeric data into text or vise versa.
To get text data you can add another field with CONCATENATE({Lookup field}) - it will convert array to a text string. Or, in source table you can change formula. YEAR(DATE) is number , but YEAR(Date) & `` is text, just add empty string or wrap it into CONCATENATE()