Please can someone tell me how to JOIN "SimpleNews" data with "User Profile Data" in a view....bearing in mind the SimpleNews module might have ONE or MORE newsletters? (looping with SQL?)
Here is a detailed description of issue..
------------------------------
I am recommending the Drupal Module “Simple News Register” to my client. However we have hit a snag
Do you have a SQL query that can join SimpleNews Subscriptions –TO – User Profiles? (bearing in mind there may be one or many newsletters?)
Why? Because we are creating a custom Drupal view that needs to include User Profile fields, plus any newsletters they are subscribed to (i.e. Boolean field for each news letter. Like this:
Here is my developers reasoning?....
So we’re back to the snag:
SimpleNews cannot easily be used in Views. There is no default exposure of SimpleNews as a View with the exception of sent emails. Because it can’t link up to the profiles natively, and because you have as many ‘newsletters’ as you like, it’s not going to be easy to implement the link for the User Registration View or its export. As I have it set up now, there is an email list checkbox in profiles but it’s hidden on the login form. The SimpleNews registration is on and visible on the registration form but it is only linked to its own subscription set.
There are really four solutions I can think of:
1) Leave newsletter registration out of the registration view for *specific* newsletters. This means there will be a separate, ‘email list’ checkbox and one or more newsletter checkboxes on the registration form. The View/Export will only show the email list checkbox value. Individual subscriptions can be managed, and exported to a comma separated list, from the Subscription page. This essentially means there’s a separate registration for an email list and for newsletters, and newsletters don’t show up on the View/Export.
2) Force the registration of the default SimpleNews newsletter to flag the profile email list checkbox when people subscribe/unsubscribe. This requires piggybacking (modifying) the SimpleNews module so that subscribing to an event updates the profile settting as well. This will then appear in the View. This will only work for the one, default newsletter – not additional ones. This also means that you’re modifying a 3rd party module which means it won’t upgrade without modifications.
3) Dig deeper into the View’s query than the GUI allows to create a pivot joint so that the variable number of newsletters will show up in the view. This is complicated and outside the scope of this particular task.
4) Create a custom page with the same result set as the User Registration View with the Newsletter subscription stuff added to it. This is less work than getting the View to show registrations but would still take a while. We could maintain the Excel export but we’d probably lose the filters unless we took more time. But, if you added more fields to the profile, this would probably break.
Any suggestions?
