Help

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.

Lookup and Rollup returning 0- array/string issue?

Topic Labels: Formulas
1772 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
kuovonne
18 - Pluto
18 - Pluto

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!