I'm designing a form to be used internally by portal subcribers.

I'd like to retrieve a list of all subscribers names and their corresponding email addresses from DNN user profiles and populate a drop down box on the form. Once a name is selected in drop down box, an email address is populated automatically in another form field. This is a small membership group (max 30 people) so I do not worry about having too many names in a drop down box.  

Can anyone recommend the cleanest way of accomplishing this and what module (DNN or custom) would work the best for this purpose? After a form is submitted, an email will be auto generated for the user who's name was selected on the form.

Thanks!

Jake

The social networking modules from ActiveModules and Smart-Thinker will let you do this. Be aware that the Smart-Thinker modules aren't being developed actively, but they will suit your purpose.

Joseph, thanks for the pointers. I own a copy of Active Social from Active Module, seems a bit overkill to implement Facebook-like functionality to get a list of registered users filled in a drop down box. I was hoping someone could provide an example of SQL query that returns a list of portal subscribers. That would be all is needed.

Ah ...

You don't have to include the entire functionality.  I was thinking of using the Members display.

But, if all you want is something that you can download, you can use this query:  select firstname, lastname, email from users

You can use that in the reports module, or any other module that lets you use a SQL statement.  Mitchel Sellers has just released a module that will do that and, with a single mouse click, download the results of the query as a CSV or XML file.

Sorry, I read your message wrong again.

The select query will work.  But, if you want to fill a dropdown box on a form, I suggest that you use Dynamic Forms from DataSprings.  You will find some tutorials about that module here:

• DataSprings Dynamic Forms Quick Start Guide
• DataSprings Dynamic Forms Question Events
• DataSprings Dynamic Forms Data Management
• DataSprings Dynamic Forms Completion Events
• How to Create an Email Opt-In Form with a Third Party Email Service (AWeber) using DataSprings Dynamic Forms
• DataSprings Opt In Email Module

Sure!  I neglected to mention that Version 5 of DotNetNuke has a "soft delete" for users.  This is done via a field called IsDeleted.

So ...

where IsDelete=0

will return users who have not been deleted.  Using IsDeleted=1 will return a list of "deleted" users.

I'm using the following statement: select firstname, lastname, username, displayname, email from users where IsDeleted=0 and it returns the list of deleted and non-deleted users. Basically it returns the same information as it does without using he WHERE clause. I'm running DNN 5.2

Include IsDeleted in the select  list and verify the values for IsDeleted. 

All records shows FALSE for IsDeleted column. I added a new user, then deleted it. IsDeleted = FALSE. A bug in 5.2?

No, a bug in Joe! 

Looking deeper, it appears that the IsDeleted field that is important is in the userportals table.  I suppose that this is so that the same user can be a member of multiple portals.

Try this:

 select * from users U inner join userportals P on U.userrid=P.userid where P.isdeleted=0

That worked! I added CreatedByUserID=1 to filter out admin account. Here is the final statmenet I used:

select username, email from users U inner join userportals P on U.userid=P.userid where (P.isdeleted=0 and CreatedByUserID=1)