Sorting a MySQL Database

users
#1

Hi All

I now have my new MySQL/LO Base Database up, running and fully edited from the old one. And what a pleasure it is to work with!! This IS the way to go,

One question - It seems to have a strange way of sorting the Data as displayed in the Form I designed and I have to select 'Sort' in the bottom Bar and then pick 'Last Name', Ascending and 'First Name' Ascending to get it the way I want it.

Unfortunately it does not seem to keep this Sort!! Is there a way to "lock' this sort or to make it automatically do it when you start the program??

PClinuxOS 2013
LO Version 3.6.5.2 (Build ID: 5b93205)

Thanks for any help.

IanW
Pretoria RSA

#2

Ian,

Is there an id key field? If there is the default sorting is by the id key (usually numerical order).

In SQL you can add the line:

ORDER BY Last-Name, First-Name

The default order is ascending (ASC) if descending is needed

ORDER BY Last-Name DESC, First-Name DESC

#3

Thanks Jay

Ian,

Is there an id key field? If there is the default sorting is by the id key (usually numerical order).
In SQL you can add the line:
ORDER BY Last-Name, First-Name
The default order is ascending (ASC) if descending is needed
ORDER BY Last-Name DESC, First-Name DESC

Yes - I do have an ID Field ("Record_No").
I found the correct SQL 5.1 syntax for me was -

     Select * from {Table Name}
     ORDER BY LastName, FirstName;

This worked fine and does the same as the 'Sort' button BUT it is still not "fixed" or remembered!!
You have to run this each time you start.

Is there no way to make this automatic when you start the Database??

With thanks

IanW
Pretoria RSA

#4

Ian

Unless the query contains ORDER BY clause in general the return set will be whatever default order of the dataset. Usually what I have seen, is to have a saved query that has the all the relevant options built-in and run it. I am not sure how to do this best in Base; I tend to use MySQL/MariaDB with other tools because my results will be used on a production database.

Your SQL code below will run on any SQL database I am aware of. SQL is a query language that is independent of the database so plain vanilla SQL should always work.

     Select * from {Table Name}
     ORDER BY LastName, FirstName;

Jay

#5

Hi Ian,

With a Form, I have found the following is the way to lock in the Form's
sort order -

1. Open the Form in Edit mode
2. Right click on the form and from the dropdown choose Form...
3. Click on the Data Tab
4. The contents field should be pointing to your Table
5. Click on the box to the right of the Sort field
6. Then you can choose your sort fields, (LastName and FirstName)
7. Click OK
8. Close Form Properties
9. Save the Form

This works with my H2 database. Hopefully it is the same for your MySQL.

Noel

#6

Hi Noel

Hi Ian,

With a Form, I have found the following is the way to lock in the Form's
sort order -

1. Open the Form in Edit mode
2. Right click on the form and from the dropdown choose Form...
3. Click on the Data Tab
4. The contents field should be pointing to your Table
5. Click on the box to the right of the Sort field
6. Then you can choose your sort fields, (LastName and FirstName)
7. Click OK
8. Close Form Properties
9. Save the Form

This works with my H2 database. Hopefully it is the same for your MySQL.

Thanks for your reply - This is exactly what I was looking for!!

BUT

I open the Form in Edit Mode and Right-Click on it and there is NO "Form" in the options that come up!!

My LO is version 3.6.5.2 (Build ID: 5b93205) and MySQL is 5.1

thanks anyway

IanW
Pretoria RSA

#7

Hi Noel

Hi Ian,

With a Form, I have found the following is the way to lock in the Form's
sort order -

1. Open the Form in Edit mode
2. Right click on the form and from the dropdown choose Form...
3. Click on the Data Tab
4. The contents field should be pointing to your Table
5. Click on the box to the right of the Sort field
6. Then you can choose your sort fields, (LastName and FirstName)
7. Click OK
8. Close Form Properties
9. Save the Form

This works with my H2 database. Hopefully it is the same for your MySQL.

Just checked on my OLD Base / HSQL Database and there is NO "Form" option there either - so I guess this is an option only with your H2 engine (??)

IanW
Pretoria RSA

#8

Hi Ian,

There is another way -

1. After opening your Form in design mode, click on View | Toolbars | and
make sure Form Design is ticked
2. On that toolbar, click on Form Navigator (5th from left on my toolbar -
it's the one with the 4 point star on it)
3. Right click on your Form name (probably the 2nd item from the top)
4. Choose Properties
5. Continue from point 3 on my first reply

If that doesn't work, then someone who uses Forms with MySQL may be able to
help you.

Noel

#9

It seems where you right-click on the form is important. If I click anywhere but the top of the form, then I do not get the "Form" option. However, if I click near the top edge, I do get the "Form" option. Don't know why this is, unless it has something to do with the beginning of data. There was something I read about the form being made up of "paragraphs", so maybe you need to click near the beginning of the first "paragraph" to get outside it. Otherwise, you get the paragraph context menu for the right-click. If you don't have the form broken up into paragraphs (I don't), then you have one paragraph.
Hope this helps.
Girvin Herr