[SOLVED] Drop Down Menu on a Member Tab that shows all site members

Hello...

Im trying to add a member sponsor field and I believe this can be accomplished by a query maybe?

What I am wanting to do is create a dropdown field in a tab that lists the site members (formattedname) where you can select that site member to show on a particular members profile.

I saw someone asked about groups, but how do you have it look up all site members?

Basically, im trying to create a dropdown field that shows who recruited the member to the site, based on its existing site members.

How are you keeping track of who recruited who? You need that information in the database otherwise there's no way to establish a relationship between the two users. Also, why does it need to be a dropdown? I would assume the recruiter would be a singular person. Please clarify further what you're wanting. It is possible to have a dropdown of users on your site using CB Query Field and its Query Dropdown fieldtype, but that doesn't quite sound like what you're needing.

Yes, it will be stored in the database on that field. A dropdown is easier for us to administer. Dropdown would be alphabetical order based on the [formatname] field. It would store the name (dropdown would read the formatname from the entire site users list from the database.

Basically, I want it to show on their profile the name of the member who recruited them to the organization. All members of the organization are users on the site.

Hopefully that explains what I am asking. I was thinking a query would do it, im just trying to think exactly how to write it. I'm not that good at this and it took me a while to figure out the CustomHTML field that I asked about on here recently. LOL

I suggest using CB Query Field and creating a field of the type "Query Drop Down (Single Select)". Next you'll need the below configuration for it.

Query: Value Column: id
Label Column: username

This will give you a dropdown of all active users ordered by their username. It'll store in database the user id, but it'll display username on profile and in the dropdown. Adjust the query as needed depending on what information you need.

Ok, if I copy it exact like you have it here, it works, but the drop-down shows the profile ID number.

I would like for it to show First Name and Last Name in the dropdown (like the "formatname" field), but sorted by Last Name then First name. I would also like for it to store the information in the database with the First and Last Name (like the "formatname") so that when I pull a report from the database, it will show the name and not the ID or username.

I tried editing the formula however, I keep getting a error message when I try to access the profile so apparently I did something wrong. This is one of what I tried:

SELECT u.`formatname`, u.`username`
FROM `#__comprofiler` AS c
INNER JOIN `#__users` AS u
ON u.`id` = c.`id`
WHERE c.`confirmed` = 1
AND c.`approved` = 1
AND u.`block` = 0
ORDER BY u.`username`


What am I doing wrong?

I apologize I am such a novice on this, but I am trying to learn.