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

formatname doesn't exist in the database. It's calculated in PHP. You need to use username, name, etc.. Please see my above configuration; you need to set the what column is used for label and value of the options. You also should not store name, username, etc.. for the value and should use their id; it will always display the label on profile. For name you'd make the following change.

FROM: Label Column: username

TO: Label Column: name

I actually made it kinda work this way, I had to reverse the name and id to make the names populate and not the id numbers:

SELECT u.`name`, u.`id`
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.`name`

This works, but the only thing is that it sorts by first name (because I have to use the "name" field to order by) instead of last name then first name. I tried to correct the ORDER BY by using ORDER BY u.`lastname` but I got a error message.

This works, so I can use it this way if I have to.

I have 2 more related questions, does the part: WHERE c.`confirmed` = 1, does it (the 1) mean it does or does not show confirmed users or approved users or blocked users?

Also, is there something, possibly another query or CustomHTML field I can create that will show on the user/members tab a running count of how many members they themselves have recruited/sponsored, based on the above formula? I guess it would read how many times their "name" field was used?

This works, but the only thing is that it sorts by first name (because I have to use the "name" field to order by) instead of last name then first name. I tried to correct the ORDER BY by using ORDER BY u.`lastname` but I got a error message.

First, Middle, and Last do not exist in Joomla. You need to make sure you use the "c" alias instead of "u" alias (e.g. c.`firstname`).

I have 2 more related questions, does the part: WHERE c.`confirmed` = 1, does it (the 1) mean it does or does not show confirmed users or approved users or blocked users?

The query is designed to only show users that are approved, confirmed, and not disabled.

Also, is there something, possibly another query or CustomHTML field I can create that will show on the user/members tab a running count of how many members they themselves have recruited/sponsored, based on the above formula? I guess it would read how many times their "name" field was used?

Yes, you'd need to use CB Query Field and its Query fieldtype. You'd then need to do a count query for every user that has that field set to the users ID. Example as follows.


The above assumes your Query Select field has a name of "cb_sponsor"; replace as needed.

OK Great! This works!:

SELECT u.`name`, u.`id`
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 c.`lastname` , c. `firstname`

So I am assuming that "c" = CB fields and "u" = Joomla fields and "1" means yeas and "0" means no


And, I created this formula below and it works great!

SELECT COUNT(*)
FROM `#__comprofiler`
WHERE `cb_recruitedby` = '[name]'

Thanks for your professional insight and help. I am not that good at coding and trying to learn. This is valuable information and I appreciate it. Thanks for your patience in helping me get it working correctly

So I am assuming that "c" = CB fields and "u" = Joomla fields and "1" means yeas and "0" means no

Correct.

Thanks for your professional insight and help. I am not that good at coding and trying to learn. This is valuable information and I appreciate it. Thanks for your patience in helping me get it working correctly

Glad I could be of help.