Help

Lookup and Rollup returning 0- array/string issue?

Topic Labels: Formulas
896 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Lauren_Briskin
6 - Interface Innovator
6 - Interface Innovator

Wow, I swear I've asked this AND seen it answered, but can't find it. 

I've created a junction field that contains the sponsor name and a single select field for the year: Screenshot 2023-02-20 at 8.00.45 AM.png

On the table where the sponsors originate, I want to show the MAX year. I've tried lookup and rollup and various suggestions from the forum, but I always get "0." Here's the current set up, based on a suggestion from @kuovonne in a previous thread. Instead of a rollup field, I have a lookup, then a formula to output MAX.

Screenshot 2023-02-20 at 8.02.43 AM.png

The lookup is great, but you can see the formula always shows "0." The formula I'm using is 

MAX(ARRAYUNIQUE(ARRAYFLATTEN({years Rollup (from junction table)})), ", ")
 
I've also tried a rollup field:
  • Using MAX(Values) gives an error.
  • Using ARRAYJOIN(values) gives a list of the numbers, but the forula field still shows 0.

I know I'm missing something obvious, but can't figure out what it is. Thanks!!

 

4 Replies 4

MAX() works on numbers. Your single select values look like numbers but are not. You can either use a number field for your year, or you can use a formula field to convert your year into a number using VALUE({years}). Then roll up the number.

thanks- would you show me mwhat option two might llook like? 

Lauren_Briskin
6 - Interface Innovator
6 - Interface Innovator

hi @kuovonne ! Would you mind showing me a sample of this? Thank you!

Lauren_Briskin
6 - Interface Innovator
6 - Interface Innovator

I'm still struggling with this if anyone has any thoughts!