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.
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
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.
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
What are you adding to the WHERE box? the {} format should work OK? What error do you get?
Bob
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).
This topic is locked and no more replies can be posted.
