Help

Renew Date and Status from Table with last payments

Topic Labels: Formulas
Solved
Jump to Solution
397 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Scott_Brasted
7 - App Architect
7 - App Architect

Hello again,

I am trying to puzzle out the best way to know if a member of an organization has lapsed in their membership dues and when they were due.

I have a payment table with records of each payment by members with the date of the payments. I want to list members who have not paid in 12 months since last payment and, list them as lapsed in the renew column.

Added problem. The table lists all payments over time. So each member could have many entries of payments in the table. I only want to test on the latest payment per member. Yikes!

Anyone have any thoughts on this?

Thanks in advance, Scott

1 Solution

Accepted Solutions
Josh_Colina
6 - Interface Innovator
6 - Interface Innovator

Hi Scott! Including here a couple screenshots of how I set-up my test base to replicate your issue. In my Members table, I added a Rollup field that summarizes the Payments table's date field and uses the aggregator MAX(values) to return only the most recent date. Then I built my status field in the Members table using this basic formula

IF(
  DATETIME_DIFF(
    TODAY(),
    {rollup},
    'days'
  ) > 365,
  "Lapsed",
  "Paid"
)

I may want to add a few additional lines to the formula to correctly flag members who have made no payments, among other statuses, but hopefully this helps you with resolving your problem!

Screen Shot 2023-10-05 at 11.35.52 PM.png

Screen Shot 2023-10-05 at 11.36.18 PM.png

Screen Shot 2023-10-05 at 11.36.34 PM.png

See Solution in Thread

2 Replies 2
Josh_Colina
6 - Interface Innovator
6 - Interface Innovator

Hi Scott! Including here a couple screenshots of how I set-up my test base to replicate your issue. In my Members table, I added a Rollup field that summarizes the Payments table's date field and uses the aggregator MAX(values) to return only the most recent date. Then I built my status field in the Members table using this basic formula

IF(
  DATETIME_DIFF(
    TODAY(),
    {rollup},
    'days'
  ) > 365,
  "Lapsed",
  "Paid"
)

I may want to add a few additional lines to the formula to correctly flag members who have made no payments, among other statuses, but hopefully this helps you with resolving your problem!

Screen Shot 2023-10-05 at 11.35.52 PM.png

Screen Shot 2023-10-05 at 11.36.18 PM.png

Screen Shot 2023-10-05 at 11.36.34 PM.png

Scott_Brasted
7 - App Architect
7 - App Architect

Zippidy Doo Dah! That worked great. Thanks!!! And I learned about Max(values) too.