LO Base - How to move columns?

[jtm4890]

How can I move the columns’ location? For example, move the ID column to the end?

[RobertG]

You have created the table in Firebird? Then you could change the sorting by

ALTER TABLE "Table1" ALTER "ID" POSITION 9;

but sometimes the position has been changed but the GUI for the table won’t show it.
With internal HSQLDB you could only set the position while inserting a new field, not after a field has been inserted.
And as @Villeroy commented earlier: You could position the fields in queries, forms and views. So if you need a special position create a query and input values directly in a query.

[Villeroy]

We still don’t know the software we are discussing here. Is it embedded HSQL?
If yes, this sample moves column “Y” before “Z”:

1. First of all, call the relations window and remove any lines connected to column “Y”.
2. ALTER TABLE "T" ALTER COLUMN "Y" RENAME TO "EXY" renames the old column.
3. ALTER TABLE "T" ADD COLUMN "Y" VARCHAR(20) BEFORE "Z" inserts a new column “Y” before “Z”. Without BEFORE "Z", the column is appended as last column. The 20 in VARCHAR(20) is guesswork.
4. UPDATE "T" SET "Y" = "EXY" fills the new column with values from the old column.
   4.1. IF the old column was complete (mandatory, not null), the new column is filled completely now and should be flagged as mandatatory: ALTER TABLE "T" ALTER COLUMN "Y" SET NOT NULL
5. ALTER TABLE "T" DROP COLUMN "EXY"
6. Use the relations window to restore any relations to the new column “Y”.

Having column “Y” before “Z” has no technical advantage.

[fpy]

How do I change the order of fields when I edit a table? base 5.0.3.2

The order of rows and columns in a database table is insignificant. You can easily create queries that return the exact same data in any order of rows and columns you want. Then you can use a query instead of the table. Open the query designer, add one table and add the columns in any order you want. Optionally you can use alias names to change the column names on the fly. This query with modified column order works in the exact same ways as the original table.

[skyandrews]

Opposing opinion… The order of columns in a database table IS very significant!
Consider: When designing Forms and subforms with listbox controls, the default bound column index is “1” (2nd column). The bound column is “usually” the indexed primary key of a table, and so it is most efficient to create the primary key in the second column (index 1). For instance Table1 has two columns: custName (text), custID (integerPrimaryKey). If in the listbox I wish to view the custName in reference to the custID of the underlying table, then after placing a listbox on a form, one only needs to select type of list: “table” in the data - control properties menu… Done! Otherwise, if table columns are not so ordered, it is necessary to write SQL or queries to “virtually change” the order of table columns. Conclusion: unless one prefers to write lines of SQL or queries, a huge amount of time will be saved by consistently ordering at least the first two columns of all tables as: reference(1st), primaryKey(2nd). The current topic lends plenty of evidence for this!

[Villeroy]

Developing a good form takes hours. Arranging form controls is a matter of seconds. The order of columns in a table has zero influence on a record set. The order of columns and the tab order on a form is freely eligible. It may change between forms and reports. From an end user’s perspective, there is not even any table.

[skyandrews]

As you said, the end user doesn’t even know tables exist. Therefore, from designer’s standpoint, a standard column order of reference and primaryKey column in underlying tables will save a huge amount of time when creating form list box controls. Even when writing SQL and queries, one does not have to go Looking for an “oddly” named reference column or key column in the table since pre-ordered columns may be queried by index. (0 , 1). Yes, when it comes to designing and creating the frontend, the backend column order is important!

[UnklDonald]

Skyandrews, the approach you propose might work for customers with a single name but for instance most people have two or more names, How would you handle that?.

Using your approach the names in the ListBox appear in the order they were entered. In a few cases that may be desirable but more frequently having the list in alphabetical order makes more sense. Not easily done using a table as a data source for the ListBox.

Another common need is for the list of names be limited to some subset of the entire list, i.e the customer resides in a specific city or postal code. Once more, good luck using a table as the data source.

Of course all those things can be done using queries for the data source. Yes, it might take a few minutes extra to write the query but the the results will usually be worth the effort.

[skyandrews]

Yes you are right, there are a lot of user variants when it comes to workflow! However, even when it is necessary to use SQL or queries to get the preferred list box output, one does not have to remember or go looking for those goofy column names since the reference and primaryKey can be queried by their pre-ordered column indexes (0, 1). As far as multi-names, usually name tables have columns for each name type (lastName, middleName, firstName, fullName, nickName, refName, etc…). Just create the desired name type (fullName, refName) to be used as the reference in the first column of the table. (index=0). I am not trying to “force” anyone into anything, but for me, having at least the first two columns of the backend tables ordered, is by far more efficient for creating list boxes, AND SQL or queries! There is always more than one way to skin a cat!