Forums

I have placed the following lines in the Sortables box in the table settings in CC5:

carList.year:Year
carList.bodyStyle:Body Style
carList.vin:VIN Number

When I view the form the columns are linked and I can click on them. Example is on this page but I currently have the sorting turned off because of the error:

http://www.wmachineregistry.com/index.php/registry-list/car-list?act=index&orderfld=Year&orderdrc=desc

The first column (YEAR) sorts fine but the others throw an error screen when you click them:

1064
YOU HAVE AN ERROR IN YOUR SQL SYNTAX; CHECK THE MANUAL THAT CORRESPONDS TO YOUR MYSQL SERVER
VERSION FOR THE RIGHT SYNTAX TO USE NEAR '`CARLIST`.`STYLE` ASC LIMIT 30' AT LINE 1 SQL=SELECT 
`CARLIST`.`ID` AS `CARLIST.ID`, `CARLIST`.`UNIQ_ID` AS `CARLIST.UNIQ_ID`, `CARLIST`.`USER_ID` AS 
`CARLIST.USER_ID`, `CARLIST`.`CREATED` AS `CARLIST.CREATED`, `CARLIST`.`MODIFIED` AS `CARLIST.MODIFIED`, 
`CARLIST`.`USERID` AS `CARLIST.USERID`, `CARLIST`.`FIRSTNAME` AS `CARLIST.FIRSTNAME`, `CARLIST`.`LASTNAME` 
AS `CARLIST.LASTNAME`, `CARLIST`.`CITY` AS `CARLIST.CITY`, `CARLIST`.`STATE` AS `CARLIST.STATE`, `CARLIST`.`COUNTRY` AS `CARLIST.COUNTRY`, `CARLIST`.`EMAIL` AS `CARLIST.EMAIL`, `CARLIST`.`PHONE` AS `CARLIST.PHONE`, `CARLIST`.`SHAREINFO` AS `CARLIST.SHAREINFO`, `CARLIST`.`YEAR` AS `CARLIST.YEAR`, `CARLIST`.`BODYSTYLE` AS `CARLIST.BODYSTYLE`, `CARLIST`.`VIN` AS `CARLIST.VIN`, `CARLIST`.`CHECKBOX_GROUP15` AS `CARLIST.CHECKBOX_GROUP15`, `CARLIST`.`ODOMETER` AS `CARLIST.ODOMETER`, `CARLIST`.`CURRENTLYOWNED` AS `CARLIST.CURRENTLYOWNED`, `CARLIST`.`YEARSOWNED` AS `CARLIST.YEARSOWNED`, `CARLIST`.`DEALERSHIP` AS `CARLIST.DEALERSHIP`, `CARLIST`.`BUILDDATE` AS `CARLIST.BUILDDATE`, `CARLIST`.`TRANSMISSION` AS `CARLIST.TRANSMISSION`, `CARLIST`.`AXELRATIO` AS `CARLIST.AXELRATIO`, `CARLIST`.`WHEELS` AS `CARLIST.WHEELS`, `CARLIST`.`COLOR` AS `CARLIST.COLOR`, `CARLIST`.`STRIPECOLOR` AS `CARLIST.STRIPECOLOR`, `CARLIST`.`INTERIORCOLOR` AS `CARLIST.INTERIORCOLOR`, `CARLIST`.`CURRENTEXTERIORCOLOR` AS `CARLIST.CURRENTEXTERIORCOLOR`, `CARLIST`.`CURRENTSTRIPE` AS `CARLIST.CURRENTSTRIPE`, `CARLIST`.`CURRENTINTERIOR` AS `CARLIST.CURRENTINTERIOR`, `CARLIST`.`CHECKBOX_GROUP29` AS `CARLIST.CHECKBOX_GROUP29`, `CARLIST`.`MODELYEAR` AS `CARLIST.MODELYEAR`, `CARLIST`.`DIVISION` AS `CARLIST.DIVISION`, `CARLIST`.`BODYSTYLE2` AS `CARLIST.BODYSTYLE2`, `CARLIST`.`ASSEMBLYPLANT` AS `CARLIST.ASSEMBLYPLANT`, `CARLIST`.`BODYUNIT` AS `CARLIST.BODYUNIT`, `CARLIST`.`TR` AS `CARLIST.TR`, `CARLIST`.`PNT` AS `CARLIST.PNT`, `CARLIST`.`PNTUPPER` AS `CARLIST.PNTUPPER`, `CARLIST`.`TIMEBUILTMONTH` AS `CARLIST.TIMEBUILTMONTH`, `CARLIST`.`TIMEBUILTWEEK` AS `CARLIST.TIMEBUILTWEEK`, `CARLIST`.`COMMENTS` AS `CARLIST.COMMENTS`, `CARLIST`.`PHOTO` AS `CARLIST.PHOTO`, `CARLIST`.`DOCPHOTO2` AS `CARLIST.DOCPHOTO2`, `CARLIST`.`DOCPHOTO` AS `CARLIST.DOCPHOTO`, `CARLIST`.`BUTTON43` AS `CARLIST.BUTTON43` FROM `UZT8U_CHRONOENGINE_CHRONOFORMS_DATATABLE_CARREGISTRATIONFORM2` AS `CARLIST` ORDER BY `CARLIST`.`BODY` `CARLIST`.`STYLE` ASC LIMIT 30

The other two columns are just varchar text fields. Is there something else I need to do to get the sorting to work correctly here? Example is on this page:

Hi MaestroC,

It looks as though there is a comma needed between the two columns to be sorted:

ORDER BY `CARLIST`.`BODY` `CARLIST`.`STYLE` ASC LIMIT 30

Bob

Not sure what to do there as it is Chronoforms creating that query and I can't control it. Adding a comma to the end of the lines in the sortables box like this does not fix it:

carList.year:Year,
carList.bodyStyle:Body Style,
carList.vin:VIN Number

Honestly now that I see where the error is it looks more like it is a problem with the fact that my fields have spaces in their names. It is splitting the Body and Style into two different parts of the query. Is that the reason for the problem and if so how can I fix it?

Hi MaestroC,

Ah OK - the Sortables box takes a different syntax "Multi line list of fields to be sortable, e.g:Model.field:Model.field" (rather than Model.field:Title). So try it with

carList.year:carList.year
carList.bodyStyle:carList.bodyStyle
carList.vin:carList.vin

Note: Spaces should be OK in Titles - though not in column names.

Bob

Bingo! That was it. I should have read the fine print under the sortables box more closely. Thank you!