Forums

I'm trying to retrieve a unique list of dates from an event registration table so I can populate it in a dropdown to use for a csv export. The goal is that I can go to a form, select the event date, and it will give me a CSV export of all of the records of people registered for that event.

What I did (so far) is created a DB Record Loader before the Show HTML that will query the table and return the dates. Then I use the dynamic data for the dropdown to populate it with the dates. It works great - except they're not in a specific order and they duplicate. So if I have 10 people register, 10 options for that date appear in the dropdown.

Is there a way (without custom code) to add basically a group by and an order by? Maybe it's an option I'm just missing? Any help is appreciated.

Hi gotpowr,

I think that you can add an ORDER BY to the Where clause e.g. 1 ORDER BY `col_name` and possibly GROUP BY will work there too.

Bob

Thanks it worked perfectly!

Follow-up question, now that it worked, I'm trying to do a CSV export, how do I get the dropdown value to reflect in the SQL where clause? I tried encompassing it in {}, however it keeps giving me an error when I try and export.

Hi gotpowr,

What are you adding to the WHERE box? the {} format should work OK? What error do you get?

Bob

I thought it should but it's acting very weird (maybe I'm missing an option somewhere?). I named the dropdown box on the form "ddmeeting_date" so I added to the where clause: where meeting_date = {ddmeeting_date}.

The error I get is:" 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 'FROM `joom_chronoforms_data_registration_racing` WHERE meeting_date = {ddm' at line 2 SQL=SELECT FROM `joom_chronoforms_data_registration_racing` WHERE meeting_date = {ddmeeting_date} ORDER BY member_name;"

If I remove the where clause, it works fine. I tried even removing the order by when using the where and I get the same issue.

I'm not sure if this helps, but I'm also getting a similar error when I try to include/exclude columns from the export. I made sure the column names were right and even tried using only a single column to include/exclude. I added the columns in a csv list: cf_id,member_name
(for example).

Hi gotpowr,

The date result is a string so needs to be quoted. Please try

where `meeting_date` = '{ddmeeting_date}'

Bob