Range filter (on front-end) to produce monthly report of Active and Expired memberships

[reidwalley]

Desired result: To produce a monthly report that shows both Active and Expired memberships.

Setup: I have 3 LEFT JOINS. One table contains "subscription_date" column; another table contains "expiration_date" column. And I have a Range Filter on each column's date element.

Test: (On the front-end) using the Range Filter on the "subscription_date" column, I pick [between] Aug 1, 2016 [and] Aug 31, 2016, and think this will display all Active and Expired memberships for the month of Aug 2016.

But this is not what happens. The "subscription_date" Range Filter only displays memberships that are Active during the month of Aug 2016 (which makes sense) - but it doesn't show any of the Expired memberships during the month of Aug 2016.

How can I use Range Filter to display Active and Expired memberships for a monthly report? Or am I way off base?

 

[cheesegrits]

You haven't explained how you differentiate between active and expired in your data.

-- hugh

 

[reidwalley]

Active and Expired is stored in a "status" field: payplans_subscription.status
Active=1601
Expired=1603

• Ultimately, we want to generate a monthly report, of Active and Expired memberships, for 8 different chapter locations throughout California.
• The dropdown Filter for the "Chapters" on jsn_users.aep_chapter works great.
• But getting a monthly snapshot (using the Range Filter) of Active and Expired memberships, I can't figure out. It shows either the Active (status code: 1601) or Expired (status code: 1603). But I can't both to show at the same time when using the Range Filter.

Here's my schema:

SELECT
users.name,
users.id,
users.email,
users.registerDate,
jsn_users.firstname,
jsn_users.lastname,
jsn_users.aep_chapter,
payplans_subscription.status,
payplans_subscription.subscription_date,
payplans_subscription.experation_date,
payplans_plan.plan_id

FROM Users

LEFT JOIN users.id = jsn_users.id
LEFT JOIN jsn_users.id = payplans_subscription.user_id
LEFT JOIN payplans_subscription.plan_id = payplans_plan.plan_id

WHERE payplans_plan.plan_id IN 1,2,3,4,5,6 (type: text)

(Display Mode: Merge rows and reduce data)

 

[cheesegrits]

I'll have to see you site. Provide the backend URL and an admin login, in http://fabrikar.com/you/my-sites

-- hugh

 

[reidwalley]

I added Super User Admin username and password access for "CalifAEP - Stage" to My Sites.

To recap: We have a subscription membership site, with 8 different Chapters. We want to generate a separate monthly CSV report, for 8 different Chapter member subscriptions.

Generate Reports: The main focus of each Chapter's monthly report is to show the "Subscription Date" and "Expiration Date" for each subscriber. Other info for each subscriber is also part of each report (Chapter, name, id, email, etc).

Tables used so far: users, jsn_users, payplans

So far: We have dropdown filters for "Chapters" and "Date Picker." But it's not enough to get what we want.

 

[cheesegrits]

I can't get at the site as you seem to have an htaccess login for it.

-- hugh

 

[reidwalley]

I can't get at the site as you seem to have an htaccess login for it.

-- hugh

Sorry about that. I added the Password-Protected Admin Access UN/PW to My Sites's Notes section.

 

[cheesegrits]

If I search on 2014-12-01 through 2014-12-31, I see 1601 and 1603 entries.

There's nothing in the filtering which would be excluding the status.

However ... I think there's a problem with the joins, and i'm not sure we can hammer Fabrik to fit.

You have the payplans_subscriptions joined on user_id, and repeat "No". But ... it looks like you have more than one subscription for some users. So Fabrik is finding more than one subscription for some users, where it only expects to find one (repeat is No). Then you are joining payplans_plan to payplans_subscriptions, again with repeat No ... using an FK to PK join (so the FK is on the "from" table).

This just isn't going to work. And even if you turn "repeat" on for the subscriptions (and the plans), I don't think it's going to work. Although we allow the "daisy chained" joins, so main table joins to child table, child table joins to another table ... it won't work in a "many" join. We can handle a main table one-to-many join to child table, which then has any kind of "FK to PK" join.

The basic problem is that Fabrik really only handles joins form the main table, to children. Those "daisy chain" joins kind of work in very simple situations. But as soon as the table you are joining to is repeated, the daisy chaining won't work.

So if you only had one sub per user, it might work.

-- hugh