Renew Date and Status from Table with last payments

[Scott_Brasted]

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

[Josh_Colina · Accepted solution]

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!

 

 

 

[Scott_Brasted]

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