Stevebenson223
Member
Hi Fabrikers
I'm having a strange problem with a prefilter.
I'm building a membership app. My main tables are a members table and a subscriptions table. Members can purchase subscriptions, which expire after one year.
I have two main reports: Current Members and Expired Members.
Current Members is based on Members list and linked to Subscriptions list; it has a prefilter to display only those records where Subscriptions.Expiry Date is greater than today.
Expired Members is the same but prefilter says:
WHERE Subscriptions.Purchase Date IN SELECT MAX(subscriptions.date_time) FROM subscriptions WHERE 1 GROUP BY member AND Subscriptions.Expiry Date is less than today.
(NOTE: Subscriptions.PurchaseDate (label) is subscriptions.date_time element)
Both these reports have successfully shown the correct information for dummy test data so far. However, this morning I added some more test data, setting up some new members and a current subscription for each. Both reports were correct. Then I created some "past" subscriptions for previous years, just to have some subscription histories. The Expired Members report then incorrectly showed two of the members on the Expired Members report even though they also have a current subscription (and are correctly shown on the Current Members report as well).
All the other members are on the correct reports, even those with subscription histories. I can see no difference between these two members and the others. I have checked the database and all the data (expiry date, etc) is correct, so I can only conclude there is something wrong with my prefilter condition.
I tried deleting the two members from Joomla, then reinstating them and their subscription histories. At first all was correct, then ONE of them showed up again on the Expired report with an old subscription, even though they were still showing correctly on the Current report.
To summarise:
Member A has 3 subscriptions: 2019, 2020 and 2021. He correctly shows on the Current Members report;
Member B has 3 subscriptions: 2019, 2020 and 2021. He correctly shows on the Current Members report AND his 2019 subscription shows on the Expired Members report... incorrectly.
Can anyone spot any errors with my prefilter conditions which might be causing these apparently random inconsistencies?
Thanks,
Steve
I'm having a strange problem with a prefilter.
I'm building a membership app. My main tables are a members table and a subscriptions table. Members can purchase subscriptions, which expire after one year.
I have two main reports: Current Members and Expired Members.
Current Members is based on Members list and linked to Subscriptions list; it has a prefilter to display only those records where Subscriptions.Expiry Date is greater than today.
Expired Members is the same but prefilter says:
WHERE Subscriptions.Purchase Date IN SELECT MAX(subscriptions.date_time) FROM subscriptions WHERE 1 GROUP BY member AND Subscriptions.Expiry Date is less than today.
(NOTE: Subscriptions.PurchaseDate (label) is subscriptions.date_time element)
Both these reports have successfully shown the correct information for dummy test data so far. However, this morning I added some more test data, setting up some new members and a current subscription for each. Both reports were correct. Then I created some "past" subscriptions for previous years, just to have some subscription histories. The Expired Members report then incorrectly showed two of the members on the Expired Members report even though they also have a current subscription (and are correctly shown on the Current Members report as well).
All the other members are on the correct reports, even those with subscription histories. I can see no difference between these two members and the others. I have checked the database and all the data (expiry date, etc) is correct, so I can only conclude there is something wrong with my prefilter condition.
I tried deleting the two members from Joomla, then reinstating them and their subscription histories. At first all was correct, then ONE of them showed up again on the Expired report with an old subscription, even though they were still showing correctly on the Current report.
To summarise:
Member A has 3 subscriptions: 2019, 2020 and 2021. He correctly shows on the Current Members report;
Member B has 3 subscriptions: 2019, 2020 and 2021. He correctly shows on the Current Members report AND his 2019 subscription shows on the Expired Members report... incorrectly.
Can anyone spot any errors with my prefilter conditions which might be causing these apparently random inconsistencies?
Thanks,
Steve