Membership/subscription app: help with current/expired subscriptions [RESOLVED]

[Stevebenson223]

Hi

I'm building a membership app and I need to show a list of members with current subscriptions and a list of members without current subscriptions. I've tried several different approaches involving multiple fields and calculations but it's getting too complicated. I'm now thinking there's probably a simple, straightforward way to make this work using the subscription purchase date field and pre-filtering the lists in some way.

If the period of subscription ran for one year from the purchase date, or ran Jan to Dec, I think I could manage this. But the app is aimed at the amateur theatre community, whose subscriptions run from a fixed date for one year (e.g. 1st April 2021 to 31st March 2022). A member can purchase a subscription at any time during this period but it will always expire on the fixed date. So even if they purchase a subscription on 30th March 2022 it will expire on 31st March 2022!

Has anyone any ideas on how to pre-filter the list satisfactorily?

Thanks.

 

[troester]

You can use a pre-filter
type "Query" and some mySql date functions
type "Eval" doing it with php

Or if you want to show the subscription end also to the user you can use a calc element or a php form plugin to calculate the end date and use this in the prefilter.

 

[Stevebenson223]

Thanks @troester
I'm nearly there!

I found some php code on Stack Overflow which will calculate expiry date correctly:
$d = new DateTime(date('Y').'-03-31');
if ($d < new DateTime()) {
$d = new DateTime((date('Y')+1).'-03-31');
}
return $d->format('d/m/Y');

So this is in a calc field as my expiry date (with "Only calc on save" set to YES).
And I can use this field to pre-filter my lists (at least the current subscriptions).

The only slight problem is the code calculates from today. Ideally, I need it to be based on the purchase date field, which may not be the same as today. Any suggestions how I can tweak the above code to achieve this please? I'm a beginner with php.

Thanks,
Steve

 

[Stevebenson223]

So... I spent TWO DAYS figuring out how to update the above php code so it will calculate from the purchase field. And I did it! It calculates correctly and shows the correct expiry date.

Great joy!!

But... when I tried to use this field to pre-filter my list and show all current subscriptions, it doesn't work. Of course it doesn't because this is a calc field which is a TEXT field not a DATETIME field.

I tried to create a jdate field instead and use the same php code... but that didn't work.
I tried to create a jdate field which uses the "Default" section to pull in the above calc field... but that didn't work either.

Great woe.

To reiterate: I have used a calc field to calculate an expiry date from a purchase date field. And now I need to use that expiry date field in a pre-filter to compare against today (and other dates).

Are there any kind souls out there who can help?
I am staring into the abyss....

 

[Stevebenson223]

The mist is clearing....
I am a fool. I had the answer to this in a previous posting of mine. You CAN evaluate a text field against a date in a filter IF you format it correctly... i.e. Y-m-d.
So now my list works beautifully.