CB Query - need push in right direction please

Hello Support,

CB Query add-on has changed hugely since I first used it several years ago. That is a good thing.

My knowledge of mySQL and CB Query hasn't changed much. That is a bad thing!

I have GeoFactory 5 map and CB gateway and we have a sort issue. Cedric has no immediate solution other than to use a different field as the first field in the template for the map bubble. So here is my dilemma...

I want to create a field in each profile that self populates based on concatenation so that each record updates the cb_mapsort field based on the cb_discpline field. I have watched the videos, trawled the forums and am completely lost, more confused than when I started.

For example if a record has cb_lodgenumber = 39 and cb_discipline = 'craft' then I want cb_mapsort to populate itself with cb_lodgenumber. However, if the details are cb_lodgenumber = 39 and cb_discipline = 'chapter' then I want cb_mapsort to populate itself with 'C'+'cb_lodgenumber'

This way we can add cb_mapsort as the main field to drive the sort on the fly but use css to hide display.

We need to display each record based on all craft lodgenumbers first (in sequence lowest to highest) and then all chapter lodgenumbers (lowest to highest). The lodgenumber field is not unique and there are often duplicates, but not always.

I hope this makes sense.

Regards

David

Does your field have to be stored in the database? A CB Query field is not stored so it won't help you in this regard. You'd have to use CB Auto Actions and a Field action acting after profile update and likely registration if you need the value stored. If you don't need it stored then your conditions are pretty basic and wouldn't waste SQL time to compile that, but instead a Custom HTML field with substitutions will work as follows.


It sounds like you need it stored, but for sorting where? If a userlist then it could just be a part of the advanced sorting usage.

Hi Krileon,

Thanks for the info that CB Query doesn't store anything. It is stuff like that I need to get my head around.

As a test I did a quick query directly on database and filled the new field to test that it would actually work on the GeoFactory map via the CB Gateway.

The new field content does now sort in correct order by setting map to use cb_mapsort as main sort field.

I like the idea of substitution but I don't think that would work as we need the field to exist. Fortunately it appears we do not add many new Lodges, perhaps one every 5 years or so, therefore it won't be an issue going forward, as we can manually populate the field.

Thanks for the prompt reply and the alternatives. Much appreciated.

David

I've no idea how GeoFactory is doing all of that so I don't know what to suggest. My guess is they're just querying _comprofiler directly so you'll indeed need a stored value, which will require using CB Auto Actions to update the stored field as needed. It would be better if GeoFactory let you sort by more than 1 field as that likely would solve your issue so sort by cb_discipline then by cb_lodgenumber or if it let you specify more customized sorting.