Hello, Gliders,
I’m working on my membership tracking page for nonprofits. I have built two sheets as follows:
I successfully identified the most recent gift for each member in order to calculate the membership expiration date (MATH column of "Most recent gift date + 365).
Now I need to bring the current membership level into the Member table AND be able to filter on it.
- Tried ROLLUP - failed because member level field isn’t date or number.
- Tried LOOKUP - failed because it returned the array of all payments–can’t identify and isolate the last payment.
I welcome suggestions on how to identify and return the current member level based on last payment amount. Whatever that resulting field is also needs to be accessible in a SINGLE VALUE and IF/THEN field for filtering through a choice component.
Thank you for helping with this puzzle.
Mona
Probably easiest way is to do a multiple relation of users to gifts and then use a single value column to grab the last item from the relation (given that the sheet is sorted in chronological order).
1 Like
As I was composing my post, I was thinking @Robert_Petitto would know what to do.
I didn’t follow your suggestion exactly because I don’t have a same-table relation to build to fit this scenario., However, your response inspired this:
Member Payments Table:
Create IF/THEN column that returned Member Level name based on payment amount–Friend, Sponsor, Patron, Benefactor.
Member Table:
Created SINGLE VALUE column of Get Last from (TABLE) Member Payments (COLUMN) Member Level of each payment. This resulted in each unique Member row having the correct current member level. This is what I couldn’t accomplish before.
Then the multi-filter system I learned from you worked perfectly in the Member Table:
- TEXT ENTRY column to select filter option
- SINGLE VALUE column of Get First from TEXT ENTRY column
- IF/THEN column IF SINGLE VALUE current member level = SINGLE VALUE filter selection THEN true.
Thank you, @Robert_Petitto. Wish I had just asked you 24 hours ago.
2 Likes
@Robert_Petitto always knows what to do!
2 Likes
Not always, but when I do, I’m glad others can benefit.
…seems like a Dos Equis meme waiting to happen…
3 Likes
