Base won't save field type change

[Peregrine]

I’m creating a database to match an existing Filemaker database. I followed the manual instructions: FileMaker exported a table to an XLS file, with the field names in the first row, which Base opened. I copied all the data. Pasting into the Tables area brought up the dialogue box for creating a new table, and I selected ‘Definition and data’, told LO to use the first line as column names, and allowed it to generate a new Primary Key field. So far it seemed fine.

Problem: I needed to change the field type of some of the fields – they were all showing as Text[VARCHAR] fields. I could use the drop-down menu against a field to change it to Integer, or something else, but LO would not let me save that change. Gave the error: ‘The column “Institution ID” could not be changed. Should the column instead be deleted and the new format appended?’ This problem occurred with all tables and fields.

In my previous forays into Base, I’ve been able to change the field type of fields I’ve created from scratch, and I don’t know why LO won’t let me change these.

[keme]

I have seen this problem when connecting to external database service. Can’t test right now, but IIRC it works only with embedded databases. For external DB server you have to use the DB administration tool for the server.

[Peregrine]

Thanks, Keme. But this is an embedded database. I’m not using an external DB server. I’m just importing an XLS file as the basis for a new table.

[Ratslinger]

Hello,

You do not specify but it appears you are using a Firebird embedded DB.

Firebird does not allow changing a type if it will potentially cause a loss of data such as going from VARCHAR to INTEGER.

It appears your import may be part of the problem. First you should use a native Calc file. Open the XLS file with Calc. Save as .ods file. Now insure all column types are correct. Copy and paste data into table. If table definition is not pre-defined, use the Next button in the dialog to insure the field types are set to the correct types. You can also set an existing column to be the primary key if this is desired.

If you do not want to import again, create new columns in the table with the correct type and then use SQL to update one column from another. This may be a more meticulous process.

[Peregrine]

Thank you for the reply. Creating a native Calc file from which to copy made the Wizard go through the proper steps. I didn’t find anywhere in Calc to set the data-type of a column, so I assume that by ‘insure all column types are correct’ you meant I should make sure the actual data in each cell was of the correct type. I was able to choose the type for each field and create the table.

But: I’d told Base to create its own Primary Key field, which it did. But in the table editing view, that field was set to AutoValue:No. The Primary Key should auto–increment, so I changed that to AutoValue:Yes. Base would not let me save that change. Back in the data view, I created a new record, and Base did not automatically fill in the next value in the Primary Key field. So:

1. I still can’t see how to set the Primary Key to auto-increment.
2. I can see no way of identifying an existing field as a Primary Key, in spite of the Wizard saying I’d be able to do that ‘on the type formatting step’.

[Ratslinger]

@Peregrine,

On the Type Formatting step, in the left column are the columns you are creating. Right mouse click on the name you want to be the primary key and then the pop up will allow you to mark it as such.

Please note. LO Base does not currently allow a change in the auto increment setting using Firebird. You are better off to first define the table with your fields (including the auto increment) then copy & paste using the Append data selection.

[Ratslinger]

@Peregrine,

As there are still a number of open issues using Firebird in Base, if you wish to avoid these you can use HSQLDB embedded.

[Peregrine]

@Ratsilnger,
I note your comment about Firebird – disappointing as my initial research suggested LO had moved towards Firebird because it was a stronger option. Perhaps its security is better. I will probably go back and repeat the whole process with HSQLDB to see what transpires.
Meanwhile, as you suggested, I created my table from scratch and then appended the data. Worked fine except for a final Notes column which seemed to see it as Data data and failed to import most of what. But that’s another question.
It’s been frustrating, coming from a FileMaker background, to find this LO environment to be so difficult and inflexible. I’ll go back and see whether HSQLDB is more amenable. Meanwhile, thanks for your input.