Database Design Field Order Change

Hi,
sorry to just pointing you to openoffice.org for specifics on this.
http://documentation.openoffice.org/

As I bit of a relational database purist I have to invoke the rule that the order of columns in a base table is not significant because it is the column name that identifies the data not the position of the column in the table.

That said, I know that MS Access allows you to move columns around and reference them with an ordinal value. Also, OOo Base looks as if it can move columns (right click the table > Edit, then right click to the left of a row and do Cut, then paste at the end) but it doesn't work. A bug maybe?

So what can you do to get what you want. The answer is to create a View (under Tasks > Create view...). You can place the columns where you like in the View, and you can Edit and reorganise the View whenever you want (as long as you are using the default HSQL database). The View will also appear as a table in whatever application you use.

If this answers your question, please mark the problem as Solved

Tony

Sam thanks for that info, I will definitely go there first.

Tony, I understand your purity (at least here), and you are correct, but visually it is a pain as you are trying to logically go through your mind (or check list) to see if everything is there -> Name, Address, City and so on. If they happen to be scrambled it is a harder job to do.

I tried what you said and not only did it not work, but it moved one of my fields to the end, and therefore scrambled it even more. So, maybe that is a sign that things can be moved. I will keep trying, but in the meantime I just wanted to thank you for your assistance.

If I resolve it, I will post it.

One more thing ... when I try to insert a row, it always do so at the end. Well, that is not inserting a row where I want it. I know that I can do it at the end. So, maybe there is a bug.

I know that if you use the Table > Edit option, it will only ever insert (with Insert Field or Paste) at the end. This may be a bug, or it may be a restriction that HSQLDB has. That is why I suggest using a View to organise the data in the order you want it in.

Tony

I tried a few things and it worked just as we thought. Everything ends up at the end. I guess I can live with that, and I tried the View as you suggested. I created ID, School, City, State, Country, and a bunch of other things that ended up scrambled.

So, in the View, I put just ID, School, City, State, and Country, and guess what? I got ID, City, State, Country, and Country (again) with the name of the School under City, and nothing on both Country. Really weird. I have been a programmer for a long time, and worked a lot on Access, so you have to trust me when I say this, and that I recheck my work.

I deleted the View and redid it and it worked. I do not know what happened before, but that will work for me. I need to move on and finish a big project. But, I think your suggestion is perfect, so I will live with it until they can find a way to move rows around. Thanks Tony for all your help. Raul

Thanks

Hi,

I know you marked this as solved - but I want to comment here anyway.

Using a view has one big caveat - all views in Base are read only. Not a problem when used for Reports then but not suitable fo data entry.

In Base the data view compoenet - what you get if you double click a table name from the main Base window - is as you have seen tied directly to the table layout.

However - if you create a form and place a grid control on the form then this grid view of the table can be changed to list the columns in any order your like, display or hide certain columns, set the display format for individual columns, pre-set the column widths, etc.

Base as you may have noticed opens up to the Forms view by default (with one exception and that is the very first time you open a Base file, before any tables exist it opens to the tables area). What I do as a rule is - when I fnish a table I just automatically use the Form wizard to build a simple form with the table displayed as a gric control - and name it to match the table name. This is my default view to the table, it works quite well for me.

HTH

Drew

Thanks Drew for this extra piece of info. I agree with you, and probably I will use all suggestions as I will need the Forms for some things, and the View for others. And, even though I did mark the issue as solved, my main concern is that I cannot change the fields in the original table. Oh well. Maybe, if that is not a bug, and was intended to be that way, then we can make an enhancement to add functionality. I still think it is a bug because you cannot even insert a row.

Again, thanks, and I will use your suggestion.