Forums

I swore I saw this capability somewhere in the chronoform component but now I can't seem to find it - maybe it's my imagination??

Is it possible to alter the order (move up/down) the database fields?? I had a date field; I later decided to split it up into three separate dropdown boxes and store them separately. But now they are appearing at the end of the CSV/Excel/web output and I'd like them to be near the middle where they really belong.

I swore I moved something up and down by selecting a circle and dragging it to the right order - like the plugins page; maybe that's all it was. But it would be very helpful if this capability were also in place for the form database fields!!

Jenny

I discovered where I saw this functionality. When first creating the table, be checking a form and going to "Create Table", and I can move the form fields around. But once the table is created and I try to add new fields in the Table Manager, they only appear at the bottom and I can't rearrange anything.

The appearance seems similar enough - it's the same green and red boxes with pluses and checks - that editing the table should be able to have the same "move the field order around" functionality as when creating the table. Especially since, regardless of the order, the form connection code is altered when a new form field is added - the insert code is regenerated to allow for the new field, it should be able to regenerate itself regardless of the order.

Just a thought...

Hi Jenny,
This is an issue with MySQL, as it's engine does not permit re-ordering existing columns. You'd have to remove any column that needs re-ordering, or drop the table altogether - loosing any stored data in the table.

A personal thought; you should never write your software or other implementation to be dependant on the column order of your database table. Either select the columns in a known order ("SELECT col1, col2, col3"), or use some API that provides the column names (when using "SELECT *").

/Fredrik

Hi Jenny,

You an alter the order of the columns in a table using PHPMyAdmin or something similar - though thi smay have no effect on the output!!

There may be a workaround in ChronoForms of you edit the order of the column in the dbclasses column in the jos_chrono_contact table. Not guaranteed to work and may be re-set if you edit the form again :-(

If you really need to manage the output order then you'd be better off writing your own Excel export code. You can use the ChronoForms code as a base and it's fairly simple to add your own column_name arrays.

Bob

Hi Bob,
Do you mind if I ask which tools you've been using to re-order columns in the database table? Using PhpMyAdmin, I've only been able to rename the columns, and possibly add new columns at specific positions.

Or nevermind, I suppose you could create a new table with the proper column order, then "INSERT INTO newtable SELECT col2, col1, col3 FROM oldtable", and then drop oldtable, and finally rename newtable into oldtable...

/Fredrik

Frederik,

Yes, MySQL does support this, by exactly what you said - the "alter table" syntax adds columns "after" or "before" an existing column. That is what I'd ideally do, but like Bob said, I'm not sure if chronoform will read the changes and I do need the changes to show up in chronoform.. :/

Yes, I will eventually be making a custom table/Excel output script to order the data as we need it. (Especially because I'll be copying the data to another table eventually anyway.) I was just hoping there was an intermediate step!

Jenny

Hi fredrik,

As Jenny says you can do it with ALTER COLUMN - though you have to include the column create clause too.

ALTER TABLE `database`.`#__test` MODIFY COLUMN `column_a` INTEGER NOT NULL DEFAULT 0 AFTER `column_d`;

Bob

Thank you Bob,
I'll keep that one in mind for the future, must've had some very old docs in my head.

Jenny,
I just dug through the source of the BackupExcel function responsible for generating the excel-output. It solely depends on the order of the columns as provided by the SQL server from a "SELECT *" query. As such, altering the order of the columns in the database should sort the issue for now.
The DB Connection code uses associative code, and thus is not affected by re-ordering the table.

/Fredrik

Here's a related question...

I need to add a field to the form, after I connected it to the DB table for the form. I added the field to the table, then added the field to the form with the same name and ID. But when I submitted the form, the value didn't get stored in the table.

THIS should be doable.. Perhaps there's a "refresh" button so the query can be appropriately rewritten to match up with the new fields.

Hi Jenny,

To recreate the ChronoForms table list disconnect the DB Connection, save the form, re-open the form and remake the DB Connection. I think that should do the trick.

Bob

You're right!! Thanks Bob, that did the trick....🙂