500 Error SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT on list when trying to order

[ontarget]

FABRIK 3.3.2
JOOMLA3.4.3
Hi I have created a list - there is an order option on some fields e.g Date
The backend list orders perfectly when clicking the ordering header title but on the frontend the list generates the following error:

Error: 500
Fabrik has generated an incorrect query for the list :

Unknown column '7' in 'order clause' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `atecicpdcourses`.`edcentre` AS `atecicpdcourses___edcentre`,
`atecicpdcourses`.`edcentre` AS `atecicpdcourses___edcentre_raw`,
`atecicpdcourses`.`title` AS `atecicpdcourses___title`,
`atecicpdcourses`.`title` AS `atecicpdcourses___title_raw`,
`atecicpdcourses`.`startdate` AS `atecicpdcourses___startdate`,
`atecicpdcourses`.`startdate` AS `atecicpdcourses___startdate_raw`,
`atecicpdcourses`.`starttime` AS `atecicpdcourses___starttime`,
`atecicpdcourses`.`starttime` AS `atecicpdcourses___starttime_raw`,
`atecicpdcourses`.`address` AS `atecicpdcourses___address`,
`atecicpdcourses`.`address` AS `atecicpdcourses___address_raw`,
`atecicpdcourses`.`price` AS `atecicpdcourses___price`,
`atecicpdcourses`.`price` AS `atecicpdcourses___price_raw`,
`atecicpdcourses`.`booknow` AS `atecicpdcourses___booknow`,
`atecicpdcourses`.`booknow` AS `atecicpdcourses___booknow_raw`,
`atecicpdcourses`.`id` AS slug
, `atecicpdcourses`.`id` AS `__pk_val`

FROM `atecicpdcourses`
ORDER BY `7` desc LIMIT 6, 1

I have tried truncating the table but the error persists in the frontend.
Thanks for any suggestions

 

[troester]

Did you select an "Order by" in list settings (Data tab)?
Is element with id 7 in this list?

 

[ontarget]

Hi Troester - thanks for your reply.
Not initially
Date element id = 7
I have tried setting the Data >order by > "Date" = "Ascending"
However the error remains.

Backend list order by function works perfectly - its only on the frontend when i get the error message.

I have tried trashing the date element and recreating it however still the same problem.
I thought it may be an ACL issue so i tried loggin in to the frontend with admin access but same error!

 

[troester]

Strange...

I have tried trashing the date element

Did you empty the trash?

 

[ontarget]

Ok getting somwhere -
I try to remove the original start date element but get
1091 Can't DROP 'startdate'; check that column/key exists SQL=ALTER TABLE `atecicpdcourses` DROP `startdate`
The element was being shared across 2 lists

Error: 500
Fabrik has generated an incorrect query for the list :

Unknown column '17' in 'order clause' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `atecicpdcourses`.`id` AS `atecicpdcourses___id`,
`atecicpdcourses`.`id` AS `atecicpdcourses___id_raw`,
`atecicpdcourses`.`date_time` AS `atecicpdcourses___date_time`,
`atecicpdcourses`.`date_time` AS `atecicpdcourses___date_time_raw`,
`atecicpdcourses`.`edcentre` AS `atecicpdcourses___edcentre`,
`atecicpdcourses`.`edcentre` AS `atecicpdcourses___edcentre_raw`,
`atecicpdcourses`.`title` AS `atecicpdcourses___title`,
`atecicpdcourses`.`title` AS `atecicpdcourses___title_raw`,
`atecicpdcourses`.`description` AS `atecicpdcourses___description`,
`atecicpdcourses`.`description` AS `atecicpdcourses___description_raw`,
`atecicpdcourses`.`date` AS `atecicpdcourses___date`,
`atecicpdcourses`.`date` AS `atecicpdcourses___date_raw`,
`atecicpdcourses`.`starttime` AS `atecicpdcourses___starttime`,
`atecicpdcourses`.`starttime` AS `atecicpdcourses___starttime_raw`,
`atecicpdcourses`.`tutor` AS `atecicpdcourses___tutor`,
`atecicpdcourses`.`tutor` AS `atecicpdcourses___tutor_raw`,
`atecicpdcourses`.`location` AS `atecicpdcourses___location`,
`atecicpdcourses`.`location` AS `atecicpdcourses___location_raw`,
`atecicpdcourses`.`address` AS `atecicpdcourses___address`,
`atecicpdcourses`.`address` AS `atecicpdcourses___address_raw`,
`atecicpdcourses`.`price` AS `atecicpdcourses___price`,
`atecicpdcourses`.`price` AS `atecicpdcourses___price_raw`,
`atecicpdcourses`.`booknow` AS `atecicpdcourses___booknow`,
`atecicpdcourses`.`booknow` AS `atecicpdcourses___booknow_raw`,
`atecicpdcourses`.`id` AS slug
, `atecicpdcourses`.`id` AS `__pk_val`

FROM `atecicpdcourses`
ORDER BY `17` ASC LIMIT 0, 1

 

[ontarget]

Ok Solved!
1. First reinstate the trashed element (id 7) then delete it from the joined list. (id 17) - this sorts the error, 1091 Can't DROP 'startdate'; check that column/key exists SQL=ALTER TABLE `atecicpdcourses` DROP `startdate`
2. There was a fabrik list module referencing the old element - re-save this
3. In the fabrik list mod set >ordering>elements =" -" > Asc
4. Also remove any list module that is on the same menu as your list