Sort by alphabetical order and not by id

Scriptcase 9 General
#1

Hello everyone,

In a DB I created an table “Authors” with the fields “id” and “name”, which gives:
author_id: 1 - author_name: “George Orwell”
author_id: 2 - author_name: “William Shakespeare”
author_id: 3 - author_name: “Arthur Conan Doyle”

In my table “Books” the name of the author is simply linked with author_id to the table “Authors” (“1” for “George Orwell”, etc.) So I have fields like this:
book_title: “1984” - author_id: 1
book_title: “Hamlet” - author_id: 2
etc.

Then, when I want to display the “Books” table in a form or grid, I can not find a solution that allows me to display the “Name” field properly. If I go through a “Text” data type, the author_name appears below the field to be completed. If it passes by a field “Selection”, the sorting of the author column will be done by “author_id” and will display something like :
George Orwell
William Shakespeare
Arthur Conan Doyle

and not in alphabetical order:

Arthur Conan Doyle
George Orwell
William Shakespeare

Has anyone an idea about it?
Sincerely.

#2

Could this not been done with an "order by author_name"in your SQL-query or am I mistaken? Or set the order on top of the fields concerned in your Grid.

#3

Thank you ctmservice for your answer.

This is a problem I have been experiencing for a long time and I now understand my mistake.

When creating an application, I usually make a simple SQL query based on a single table. Then I add informations from other tables by manually creating new fields. Unfortunately, in doing so, the column that will appear in the grid does not have all the properties of a column created directly by the main SQL query. For example, you can not sort by clicking the small triangle at the top of the column.

To remedy this it is necessary, in the main query, to include all the tables that will be needed by a JOIN instruction. The fields will be created from the start and the sort function of the column will now be accessible to the user.

Best regards