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

Using queries is probably the preferred way of accessing MS-Access tables because you can format the columns (like Base), even calculate new columns, and then reopen the formatted query. It works so well you don’t need reports for tables if you format the title, header and footer. In Base the formatting is lost when the query is reopened, so it’s not a viable option.

Create an input form with a table grid from that query with 3 or 4 clicks in the forms wizard.

…which is like putting a car on a train to travel to a destination instead of driving it.

1 Like

You have to do this only one time and then you can use the car on rails “for ever”. You have raw data stored in tables. You have queries to return table contents in any order of rows and columns and you have input forms as a more convenient user interface. Even the most simple input form with a table grid has specialized input boxes for dates, times and numbers. A form has a toolbar with big push buttons. You may also add a navigation control which is a fixed toolbar attached to the form’s canvas.

Storage, Processing, Representation. This is what professional applications do separately.

Just my personal recommendation: Do not mess with the structures in the database backend! Use the Base frontend to get any subset of columns and rows in any order of columns and rows.

This is neither car nor train. It’s a helicopter. You can edit the same table data in every dimension along client names, articles, orders, invoices, costs, revenues, … The order of rows and columns has zero influence on the functionality.

@Villeroy thanks for your comments.
.
I strongly agree with your comment about transforming a copy of data rather than changing the original (my words). Calcs like units, date format, density from vol/weight etc. That and layout are the key things I have been able to do easily in MS-Access query.
.
I tried your suggestion and I feel you have over-sold it. If MS-Access query is helicopter the Base functionality of a query in a table grid in a form is an old train at best. Seems like forever juggling query/grid/form properties. There is really no need for it to be this primitive since a query is a grid having most of the display properties of a table, except most are lost when the query is closed. Saving Base query display properties would allow it to fly.

SQL. I don’t know of a way to do this within Base easily. I typically use SQL Workbench or MySQL Workbench depending on what database I’m using. If the DB is empty, it is fairly simple. Take the SQL source displayed, re-arrange (cut & paste) what you want, Drop the old & create the new. If, however, you have data in the DB, it can be tedious. Create a new table with what you want then with SQL, copy the data from one table to another field by field. As a side note, there is no logical reason that fields have any sequence within a database.

1 Like

Confirmed on LO 5.1.0.3, you can try to drag-drop the columns but the cursor changed to the You can't do this one, and doesn’t matter how you drag or drop. However, I checked the HSQLDB 1.8.0 syntax and it confirms you can change the order through SQL: http://www.hsqldb.org/doc/guide/ch09.html#alter_table-section. Although I can understand the reason for lack of such feature to be harder to figure out which column was moved where, it’s not impossible, just keep copies of the column names.

1 Like

I also want to note that it is as easy as drag & drop to move columns using MySQL Workbench.

I came across this (old) question trying to do the same thing. Not as professional but I found a work around that I found easier than using SQL. (Although I guess I should look at MySQL Workbench…)
Probably easier/safer If you haven’t got lots of data in your table and I would make sure I had a backup first and know the ‘field type’ /settings of each field.

I ‘imported’ (dragged the table into) a blank calc sheet. Added a new column and the new field name I wanted.
Deleted the table in Base, went back to Calc and exported (dragged back) the table to Base. Gave it the same name, used the column headings as field names etc. I did have to go through and reset the field type of each field.
I might have to edit a couple of queries and a form… but quicker than having to make a new table, typing in 15 field names etc.

I guess I should say that I had no data in the table - I was using a copy of a pre-existing database as a template and needed to add and change a couple of field names in one table. And also I import and export tables between Base and Calc quite often - I get data sent to me as an Excel file (don’t have a choice about it) that I want to add to a database.

Even easier. Drag the database table into a blank calc sheet, move columns as required and save as original file name. Open Base with the database table and the columns have been changed. Do backup first though.

Are you able to record yourself doing this? (I am not easily able to believe that merely saving an ODS file as an ODB file shall allow me to retain all its information.)

Hi. I have just tried again using the .dbf file. I had Calc open, a smaller window so I can see the .dbf, and dragged the .dbf file into Calc. I inserted a new column, copied a column into it and deleted the original column and the column has been moved. I then saved it back as a .dbf file. On opening it again the columns are as they were after the column move. I hope this helps.

Your way is possible for dbf and csv/tsv, as this files are actually tables and now you also state “save back as .dbf” instead of save under original file-name.
.
If you would do the same with an .odb-embedded database, wich can contain several tables, all queries, forms you will loose data!
.
Even for .dbf/csv etc be very careful. The Auto-detect “features” of calc can silently change data, for example if misread as Date (1234.05.06 is just text, but 12.05.06 is converted to an integer representing a date) and depending on your settings you may alter data.
.
I would guess nobody would try this with really big databases, but I should mention it anyway: A lot of people lost databat the end of their tables saving in .xls as the number of lines is limited. Calc files can be much bigger, but always check if all your data is back in the database.
.
Better: do as @lucky67 and modify an empty table. Data can be moved by SQL:

INSERT INTO new SELECT ... FROM old;

But as you’d have to type and fill the dots it is the same task as before …

1 Like

Hi. You are correct but I was only suggesting a simple solution for simple database set ups. I retired 14 years ago and so am not keeping up to date any more and only use Base for simple card address lists etc. Simple compared to the potential.

1 Like

@Wanderer, are .ODS files actually able to support more information than .XLSX files?

Actually my example mentioned xls, wich supports less lines and columns.
.
xlsx and ods are both comparable, but different. Problems can arise using advanced Features with both standards. Imhof the bigger issue ist MS advertising Standard but silently using non-standard Elements (you can prevent this, but most xlsx out there are not set to obey standard only.

2 Likes

Apologies, @Wanderer. I had not realized that XLS existed as a format, so I expected that you had mistyped XLSX.

Indeed, Office supports some non-standard elements, but the user is forced to choose whether they prefer compatibility or functionality when the suite is first installed. I do not believe that adding optional functionality is necessarily bad: standardization necessitates some previous experimentation.

1 Like

File format does not matter. When you have something in a Calc window then it is an Open Document Spreadsheet. Any xls(x), csv, dbf, whatever has been converted into an Open Document Spreadsheet. And when you save it back to disk, it is converted back into xls, csv, dbf, whatever.
A Calc spreadsheet has no more than 3 data types: text, floating point numbers and error values. There are no dates, times, booleans, fixed decimals. Anything looking like that is either a text or a formatted number.
A database has a dozend data types. When you drag a record set into a sheet, is comparatively simple to convert any database type into either text or number and every single sheet cell accepts any of them.
The other way round is way more difficult. If the text or the number can’t be converted into the right column type, you get a load of errors messages.
Never mess with the structure of a working database.
Never edit a dBase file in Calc. Always use Base to edit dBase. Otherwise the dBase file may become unusable for the original database application. MS Excel opens dBase files read-only.
Before trying to paste sheet data into a database, you have to make sure that the database structure accepts every single row.
– Every column value must match the column type of the database. This affects numeric scales, text lengths, valid or invalid dates, times, booleans etc.
– Every record must be complete. No missing values in mandatory database columns.
– Every foreign key must have a matching entry in the referenced table in order to keep referencial integrity. A well made database rejects any client ID in a table of invoices where that client ID does not exist in the referenced table of clients.
A spreadsheet does not care about vaidity and integrity. A useful and fully functional sheet may not even contain any structure that could be identified as a “table”.

A tutorial about database data in Calc: [Tutorial] Using registered datasources in Calc

How to analyse a table on a sheet before trying a copy/paste into Calc: Unable to change Key ID - #4 by Villeroy

1 Like