Rearrange columns in table view?

[riverty]

I'm looking for a way to rearrange columns in table view. Can't seem to get this done. Can anyone shed some light on how this is accomplished?

Thanks!

[Villeroy]

Create a query with the fields you need in any order you like

Code: Select all

SELECT "Field3","Field2","Field1" FROM "MyTable"

[riverty]

Good idea and will try this but would really like to keep the table view to be able to see all data. Anyway to rearrange the columns in this view?

[Villeroy]

As far as I know, order has no meaning in a relational database. Neither columns nor rows are stored in a particular order (there is no concept of "preceeding value" as in a spreadsheet). Using the "command line" in menu:Tools>SQL... you can insert a field before another one, but I don't know how to change the position of an existing field.

UPDATE TABLE "Table" ADD COLUMN "Foo" INTEGER BEFORE "Bla"

Code: Select all

ALTER TABLE "Table" ADD COLUMN "Foo" INTEGER BEFORE "Bla"

Queries can extract data from one or more tables in any order of rows and columns.

[Sliderule]

Just for clarity . . . in a post above, Villeroy, I am sure, inadvertently gave an SQL command to 'add' a Column to a table . . . with a BEFORE clause.

But, . . . the SQL statement should have read:

Code: Select all

ALTER TABLE "Table" ADD COLUMN "Foo" INTEGER BEFORE "Bla"

The difference . . . UPDATE is used to change the DATA in a table . . . whereas, ALTER TABLE is used to change the definition of a table.

And, I agree with everything Villeroy was saying about the 'order / layout' of the field ( column ) order is unimportant.

Sliderule

[riverty]

OK. Let me try better.

I have a database with say, 25 records. When I look at the records in 'table" view (spreadsheet view), the data I want to see on the left-hand side of the (fairly long) table view, is currently on the right-hand side. I realize that the order of the data means nothing to the database. I would like to simply sort data that would bring certain records to the top of the list, and be able to see the 'important' stuff in the default view, without having to side-scroll to the right-most side of the spreadsheet. With that, I would think that one could simply grab a column heading and drag the column over towards the left. Doesn't seem to work that way in OO.

[Sliderule]

riverty:

You are using terms . . . TABLE VIEW, SPREADSHEET VIEW . . . etc . . . and . . . while I think ( yes, I know, Sliderule thinking . . . that means . . . we are all in trouble now ) . . . if it were me . . . this is what I would do.

I would CREATE A VIEW ( that is the correct term ), with the 'desired' field / column order I wanted. Think of a VIEW as a PSEUDO TABLE. That is, it tells the database, how to PRESENT the data . . . BUT . . . it ( the VIEW ) does not actually contain any data. It merely contains the INSTRUCTIONS ( rules ) on how to present the data.

Now, if you asked, what in the name of the third rock from the sun is Sliderule talking about . . . and . . . what do I do about this . . .

Sliderule would respond . . . Create a VIEW this way . . . FROM YOUR CURRENT TABLE:

1. Open your database in OpenOffice
2. On the Left . . . under Database . . . click on the Tables icon
3. Under Task . . . click on Create View...
4. When the Add Tables pop-up appears, click on YOUR TABLE NAME followed by the Add button
5. Click on the Close button ( since this view is only from one table )
6. In the Box that his presented with your Table name . . . and . . . the Fields in your table, click on EACH APPROPRIATE ONE, in the Order ( Sequence ) you want to display your data -- the idea is YOU can determine the SEQUENCE of which fields will be presented
7. You can TEST the order . . . press the Run Query Icon . . . or . . . F5
8. You can ALTER the order of the columns . . . if needed . . . by pressing on a column above the Field Name . . . and . . . 'drag it' to the the left or right . . . until you are satisfied.
9. Once satisfied with the ORDER ( sequence ) of displayed columns . . . press the Save icon . . . OR . . . from the Menu: File -> Save
10. You will be prompted to give a NAME . . . a NAME to save this VIEW . . . enter a name . . . press the OK button

Now . . . ( it sure seems like Sliderule is writing a novel . . . but . . . thanks Sliderule . . . those are clear instructions ) . . . when you see your TABLES . . . you will also see . . . the VIEW you just created with the fields - columns presented in the SEQUENCE you determined. By OPENING the VIEW . . . you will have your data displayed, in your NEWLY PREDETERMINED FIELD ORDER.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.

[Emmaneul]

Creating an extra view is not the solution, it's a workaround.

If you really want the table to be in the right order you could try to copy all the data to CALC, reorder the columns and paste the rearranged table to BASE:

1. Open the table you want to change.
2. Select all the rows and copy the selection to the clipboard.
3. Open CALC and paste the content you copied.
4. Reorder the columns by inserting empty columns, cutting & pasting other columns, etc.
5. Select and copy the newly ordered data.
6. Open BASE and paste data to a new table. You might need to edit your relationships in Tools>Relationships...
7. Delete old table.
8. Rename new table.

[Villeroy]

Without Calc, you can easily copy the existing table to a new table and modify the column order. This will even preserve the field types.
You can also create a view with the desired order and then
Tools>SQL... SELECT INTO "NewTable" (SELECT * FROM "View")

As already stated, all this is completely useless since the order of columns in a database can be modified at any time by means of queries without losing the ability to edit the row set and without any data loss. Just create a query in the designer and add all columns in the column order you need. The result will be just as good as a table with that order of columns.

[RPG]

Hello

I would think that one could simply grab a column heading and drag the column over towards the left. Doesn't seem to work that way in OO.

This is working for me with OOo 3.2.1. So maybe you can update to a higher version. But I think the best version on this moment is OOo3.1.1, don't go higher when you really want work with a database

Romke

[Villeroy]

Hello

I would think that one could simply grab a column heading and drag the column over towards the left. Doesn't seem to work that way in OO.

This is working for me with OOo 3.2.1. So maybe you can update to a higher version. But I think the best version on this moment is OOo3.1.1, don't go higher when you really want work with a database

Romke

Works with a table control on a form.
All the table grids and query grids are not meant to be a fully functional user interface. Forms provide much more tools to edit, filter and sort table contents. List boxes and subforms let you edit the relations between tables.
Right-click table>form wizzard... <keep defaults and finish> creates a simple form with a table control where you can drag around the fields.

[SoundWave]

Hey, I know I'm rezzing a dead topic, but I thought it might be helpful to some if I shared my experience.

I was having a similar problem with rearranging columns in 3.2.1 on Vista. The ID field was the furthest-right column, and I wanted it to be the first column in the table. None of the methods mentioned worked for me (probably didn't interpret the instructions right), except for the Calc method which I thought was kind of a tedious way of doing things. As I'm a complete novice to DB creation and management, I was also too lazy to put in that much effort. But I found a way to do it in under a minute:

1. Open the table you want to rearrange ("OLDTABLE")
2. Create a new table ("NEWTABLE")
3. Edit> Copy the columns from OLDTABLE to NEWTABLE in the order you want them to be in
4. Rename OLDTABLE to OLDTABLE_2
5. Save NEWTABLE as OLDTABLE
6. Delete OLDTABLE_2

Worked like a charm. Hope it helps someone else!

[Villeroy]

1. Open the table you want to rearrange ("OLDTABLE")
2. Create a new table ("NEWTABLE")
3. Edit> Copy the columns from OLDTABLE to NEWTABLE in the order you want them to be in

3.1 remove all relations from the table

4. Rename OLDTABLE to OLDTABLE_2
5. Save NEWTABLE as OLDTABLE
6. Delete OLDTABLE_2

6.1 rebuild all relations

Worked like a charm. Hope it helps someone else!

This is not how one should torture a database. Simply use queries to show the very same data in any order of rows and columns. The order of rows and columns in a relational database is completely meaningless.

[SoundWave]

This is not how one should torture a database. Simply use queries to show the very same data in any order of rows and columns. The order of rows and columns in a relational database is completely meaningless.

This is probably very true. Though the order of rows and columns in the a relational database may be completely meaningless, it wasn't a relational database and it certainly wasn't the database that was consistently annoyed by the order of the columns in the table.

[DACM]

QUESTION:
When you use a Query or View as a workaround to "rearrange columns" for a single Table, are there any limitations such as SELECT only?
CASE 1: single Table, single View or Query, no IDENTITY column (SELECT only ?)
CASE 2: single Table, single View or Query, with IDENTITY column (UPDATE ?)
CASE 3: multiple Tables, single View or Query, with or without IDENTITY columns and Table relations (SELECT only?)

Just wondering if this would affect the decision matrix.

[Villeroy]

it wasn't a relational database

If you can edit the table structure it was most likely a HSQLDB. Other people reading this topic should be aware of their table relations.
You can rearrange the columns by means of queries and you can create input forms with arbitrary order of columns. Input forms can work on a record-by-record basis or you can draw a table control. For editing unrelated lists in the data source window from any office document I use queries which show a certain amount of recent data in geological order (latest on top) and optional columns behind mandatory ones.