Win 11 Libre Office 7.3 Base "Insert Null into non-nullable column

[ocgirl]

I created a basic spreadsheet to compare various cover crop seed prices and it just became so ungainly that I am attempting to create a database instead. It is only one table, there are no relationships, but I have been unable to make it work. I have spent this whole weekend on the database because searching for seeds and all the pests and diseases is like going down a rabbit hole. I am so far behind in planting; I am one terrified farmer. I gave up doing it on paper, hoping the spreadsheet would help. I’ve spent the last 2 days and nights hoping the database would help. Instead of using the original spreadsheet, I took just the first seed supplier on my list and added a Sheet with just his information and seeds. I attempted to copy and paste just that 1 supplier’s information to see if it would work and it doesn’t. Most of the sheet is empty. I thought if I could complete it & I can get the database working, I could finish the rest, so I was only copying cells A1 to EQ6. Can someone please tell me if it is the table that I set up wrong or if it’s the spreadsheet. Can the format of the spreadsheet columns interfere with the Table “Field Type” values. For example, the spreadsheet has dollar signs in the price columns, but the field type is number. I have attached the files I am working with. Thank you.

Cover Crop Seed Cost Database Worksheet.ods

Working Copy of Organic Seed Price and Vendors.odb

This is the very first time that I am attempting to plant a cover crop, so I would like to be able to pull information from this database in the future. This is also the first time I’ve attempted to create a database, so I really appreciate your assistance. I do have a screenshot of the error message, if you need it, but I can only add 2 links.

[RobertG]

Both tables contain a primary key. If you won’t write any value in this field you couldn’t save a new row. There is no duplicate value allowed in this field, also no empty entry.

Most people will do this automatically:
Open the tables for editing.
Mark the primary key.
Set in FieldProperties “AutoValue” → ‘Yes’

… but you could also set this value directly in Calc: Field “ID” in your Calc table should start to count with ‘1’.

[ocgirl]

Thank you for your reply, but I’m not sure I understand what you mean. First the other table should not have been there - my mistake, I have deleted it. In the Table I have a Field Name = ID set as the primary key. The Field Type = Integer, Auto Value = Yes, Field Length = 10, Format = General. In the Spreadsheet, I also have a column labeled ID, but it is blank since I have Auto Value on in the Table. I was just following the online tutorial in setting up the primary key and leaving the spreadsheet ID empty. Is this wrong?

When I paste the spreadsheet into the Table I still get "Attempt to insert Null into a non-nullable column. I have attached a copy of the corrected Table and the error message.

Working Copy of Organic Seed Price and Vendors.odb

Thanks

[ocgirl]

Sorry, I’m not sure why that posted as a big fat picture. It was supposed to be a link. I made some minor changes to the spreadsheet. Is there a way that I can edit that link to post the new version?

[RobertG]

Have downloaded your Calc file also. I couldn’t see which table from Calc should contain the values for table in the Base file.

The error you reported in title for this thread is “Insert Null into non-nullable column”. This error appears if you try to save data in a table without having a value for the primary key. See first table in the Calc file. Column for ID is totally empty. So if you try to copy this to Base “ID” could never be the column for the primary key.

Copy the content of the first table of Calc without “ID” and insert this to Base. Wizard will ask you if it should add a field for primary key. Let it create this field an all will work.

[ocgirl]

I am using the Sheet titled “Adams Brisco”. Column A is the ID column which is blank. I highlighted B1 -ER6. Copied and then pasted into the Table for Base. Now error is “Wrong data type:java.lang.NumberFormatException: For input string “Adams Brisco” Continue anyway?” I clicked yes and get a blank table.
I went back to the spreadsheet, deleted Column A, highlighted the range A1-EQ6 (same exact data just one less column), got the same message about a string error and blank table.
I did check the Table and Vendor Field is set as Text [VARCHAR]. Entry is set to yes required, Length is 30 and Format Field is set to @ character.

[ocgirl]

Sorry, I should also mention that when I was getting the null error. I would unclick the ID box from the source file before hitting create.

After making the changes you suggested, the first field in the source file is Vendor. I no longer have to uncheck anything.

[RobertG]

Tried it with the sheet you mentioned:

… but got an error, because you have set "Vendor Street" → Entry required → Yes in properties of the table. Field doesn’t contain any value in the Calc file.
Here you could see the error with the main information:

[ocgirl]

I went thru all of the field types and changed most to required entry no. I did leave Vendor name, Common Name and a couple of others to yes. I did that so that later when I create the form a new record could not be created without certain identifying information to protect the integrity of the data. But that is in the future when entries will be input using the form. I guess I don’t understand how base reads a spreadsheet. Is there a tutorial on just that subject somewhere?

[Wanderer]

@ocgirl

create the form a new record could not be created without certain identifying information

and if you put this as constraint of the TABLE not the FORM the database will not accept a single row, where your condition is not fulfilled.

@ocgirl

don’t understand how base reads a spreadsheet.

row by row and all rules of the givven type or by yourself (not null) are enforced. The problem is: Spreadsheets neither know nor follow this rules, so the spreadsheet don’t “help” you making checks…

[RobertG]

@ocgirl

Is there a tutorial on just that subject somewhere?

You could try this: Base Guide - Import of data to Base

[ocgirl]

Thank you for the link. I made sure that the spreadsheet had a column labeled ID and all Field Type Properties in the Tables are set to NO for required and it actually worked!!! So excited Thank you!