Single Table sorting not working

Hi @websgt,

Can you please change your default order by from:
1,desc
to:
0,desc
The column index is zero based. It looks like this is the first column…

Let me know,
Peter

Peter,
Thank you for your quick reply.

1. I should have known that..
2. I tried it both way, 0,desc and 0,asc. The table results are unchanged.

Thanks,
Steve

Hi Steve,

Your order by looks like this:
0,asc;

Please remove the ; character like this:
0,asc

Thanks,
Peter

Peter,

I had tried it both asc and desc with and without the “;”, as I read somewhere that might be required. Just to be sure, I just tried it again, without the “;” and the results are unchanged. I also tried it asc and desc. One of them should have changed the results.

I’m open for suggestions.

Thanks.
Steve

Can you just sort using just the first four characters of the TERM field? This will definitely work.
My suspicion is that the presence of the minus sign in the term of office is somehow causing MySQL to convert the field into a numeric calculation whereupon the result of each row is -1. This would explain no change when sorting ASC or DESC. Just a thought.

@rvwill48
How do I set that up to use the first 4 Characters? The field is set to Char (9).

• This reply was modified 3 years, 8 months ago by websgt.

try
ORDER BY LEFT(TERM,4) DESC

@rvwill48,
Thank you for your reply.

hmm.. I don’t seem to have access to the underlaying SQL in this plugin. I would think that would need to be entered in the Data Publisher section, but it requires Column, asc|desc. I don’t see where I can use the LEFT clause for TERM.

Regards,
Steve

Hi Steve and Richard,

There is no way to use SQL functions in the order by of a publication. But there are alternatives:
(1) Use a view and add your order by to your view
(2) Use Data Projects to create a read-only Data Form (requires a premium license)

Does this help?

Best regards,
Peter

Ok Peter, thanks for clarifying.
Could Steve’s issue have anything to do with webpage caching then, since he’s changing the sort order in the publication but sees no effect?
How is the ‘order by’ setting applied in your plugin?
Richard

Good point Richard!

Current order by = 0,desc
But the table is not ordered on that column.

@websgt Is this a view which has its own order by?

Thanks,
Peter

Peter,

It is one table with one view.

Here is the SQL for the create table:

CREATE TABLE MCL_Officers
(Term char(10) NOT NULL
,Commandant char(100) NULL
,Sr_Vice_Commandant char(100) NULL
,Jr_Vice_Commandant char(100) NULL
,Judge_Advocate char(100) NULL
,Adjutant char(100) NULL
,Paymaster char(100) NULL
,Sergeant_At_Arms char(100) NULL
,Chaplain char(100) NULL
,Aide_de_Camp char(100) NULL
,Jr_PastCommandant char(100) NULL
,WebSergeant char(100) NULL
,Historian char(100) NULL
,Public_Affairs char(100) NULL
,PRIMARY KEY (Term)
) ENGINE MyISAM DEFAULT CHARACTER SET utf8 COLLATE=utf8_general_ci;

CREATE UNIQUE INDEX MCL_INDX ON MCL_Officers (Term);

On the data publisher I have the columns set for:
Term,Commandant,Sr_Vice_Commandant,Jr_Vice_Commandant,Judge_Advocate,Adjutant,Paymaster,Sergeant_At_Arms,Chaplain,Aide_de_Camp,Jr_PastCommandant,WebSergeant,Historian

(it is essentially everything except the last field which is public affairs (not currently needed.)

Output: responsive
number of columns: 1

In all its one table with 1 view.
Default Order/By: 0,desc

Term is the first column, hence 0 for the order by, and I have tried both asc and desc to see if it make a difference.

The page is live and I put the link in the initial post to see the out put (second table down, the first table is static)

I have gone through the data designer, project templates, data projects and data publisher and reviewed all the options. Did I miss something?

Thanks for your help.

Looking at your create table statement I notice that:
1. Your database engine is specified as ISAM, why is this?
2. You specify a Primary Key on the TERM column and
3. Then go on to specify a unique index on the same column.

A primary key column is by definition an unique index, so it appears you are creating a second unique index on a primary key column. I’ve no idea what effect this will have on the behaviour of the database table when sorting.

To reorder your table rows you’ll need administrative access to the database. I would suggest using the ALTER TABLE sql command to reorder the rows as follows:

ALTER TABLE ‘MCL_Officers’ ORDER BY ‘TERM’ DESC;

This will physically rearrange the records into the order you want and they’ll stay that way until you add a new record or delete an existing one, then you’ll need to rerun the above ALTER TABLE command again if by then you haven’t fixed the sorting issue.

Best wishes,
Richard

@rvwill48,

Well I changed the table type to InnoDB and dropped the index. To do this I had to drop the table. I exported the records as a CSV , of course I did not realize that import only supports SQL, and it only exported 10 records :(. Luckily I had an older list of the officers (up to 2014), and and inserted the appropriate SQL statements and got most of it imported.

Regardless, this change did not change the order of the records.

@peterschulznl
I am adding a id column (rec_ID), I have it as mandatory, and key. How do I set it to auto-increment?