Skip to main content

I have and “hours” table whose rows consist of the following relevant fields:

  1. An email address of an event participant
  2. Number of hours participation in an event
  3. Year of the event participation

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

  1. Grouped the rows by email address
  2. Created a new field, called “yrs active” that is defined by the formula “arrayunique(year)”.  On the email address group by line, it indicates the number of unique values found so I assumed I could apply formulas or filtering that operated on just those unique values.
  3. Created a second field, “In 2022 and 2023” defined by the formula, AND(FIND("2022", {Yrs Active}), FIND("2023", {Yrs Active}))thinking that at the group by level, if the unique values contained both 2022 and 2023, the logical formula would return True.  But that did not work.
  4. I then tried filtering such that “yrs active” contains 2022 and 2023, but that resulted in 0 rows which qualified. But if I filter such that “yrs active” contains only 2022, then I get correct results.  If I filter for 2022 or 2023, I get the correct selection.

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

 

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

 


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

 

 

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

 

 


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'. 

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'. 


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

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


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()

Reply


Terms & Conditions