unsure about trigger actions/conditional or both ? SOLVED

I am updating a Joomla/Community Builder site from Joomla 1 to Joomla 3.x

I am using Joomla 3.x and Community Builder 2

There are aprox 1700 members that are successfully imported.

When a new member goes to register, they must enter a 7 digit or 1 letter followed by 6 digits member # in the first step of the registration.The member number is unique number assigned by the group (but this does not need to be confirmed at this stage)

I have accomplished this through using the Perl Regular Expression validation of /^[0-9]{7}|[a-zA-Z]{1}[1-9]{6}$/

Ideally we would like to check the database to ensure this number is not already being used by a registered user, and if it is, we would like a message displayed such as "the email address whatever@whatever.com is using that #" and for the registration process to cease.

I have been reading all about conditional fields and action queries for the last 2 days and just can't seem to get my head around what would be the best way, or how to accomplish this. Help ?

For what you're wanting you need CB Query Field. It provides a number of Query fieldtypes, but more specifically for your usage is it allows Query Validation for fields. You'd add query validation for it to compare the value the user supplied against the database to see if it's already in use.

ok, thanks.

Can I assume this would be in the Integrations/CB Query Field/Query validation of my current text field, as opposed to deleting my current text field and creating a new "query field" ?

In either case, can you give me a start as to what Query I want to make there. (I have been searching for mysql validation query examples for awhile now)

I assume it starts like
SELECT FROM `#__comprofiler_cb_acblno`
or
SELECT `cb_acblno` FROM `#__comprofiler`

and that is about as far as I get....

Can I assume this would be in the Integrations/CB Query Field/Query validation of my current text field, as opposed to deleting my current text field and creating a new "query field" ?

Yup, you add the query validation to your existing fields. No need to make a new field. The below should work.

Query: Validate On: Empty Results

Thank you for explaining more clearly, it seems like it should work but it doesn't.

I have this running on a local installation,if it would be helpful, I can upload it and provide login details.

Was able to find a bug with the query validation when there is no user object. Fixing for a new release for later today.