# Can't link 'Labels' to database

I wish to use my LibreOffice address book (.ods), derived from a Windows Database (.xlsx) on an earlier machine, to produce sheets of address labels. I have followed File-New-Labels to open the Labels dialogue, but cannot choose the Database. All I am offered is 'Bibliography' which leads nowhere. The database appears to behave correctly in all other respects. I am on Windows 10, LibreOffice version is 5.4.1.2 (x64). Any help would be gratefully received.

edit retag close merge delete

Sort by » oldest newest most voted

Hello,

In order to see your data in 'Data Sources' it must be registered. For a Calc DB (most likely what you are discussing) see answer in this post for detailed info.

Edit 11/15/2017:

In response to your secondary question, you can create a Query (SQL selection of information from table) which will then become available in the Data Sources under Queries instead of Tables. Information on creating queries can be found in the LO Base documentation, Chapter 5 - click here. Databases bases upon spreadsheets can only use SQL in a limited fashion. However yours is simple enough for this. Your statement will be something like:

SELECT * FROM "YOUR_TABLE_NAME" WHERE "CHRISTMAS LABEL" = 'Y'


Now this is direct SQL code but the documentation also shows how to use the IDE to generate. It's not as hard as it may sound.

more

Many thanks, and apologies for breaching the 'reply' protocol. Regrettably, after many attempts, I am still doing something wrong in my efforts to apply a 'Query'. Firstly, I am unsure as to the filename to be inserted as "YOUR_TABLE_NAME" in the SQL - is this the .ods file, the .odb file or something altogether different? Secondly, as to the stage at which the SQL is to be applied to the document to be printed. I have tried to make sense of the LO Base documentation Chapter 5. Any further help?

( 2017-11-18 20:53:54 +0200 )edit

When you first open the Base file (the .odb) it will be on the "Forms' section. Click on the 'Tables' section in left column. Once done, in lower center is your table name and replaces YOUR_TABLE_NAME in the statement. If you are certain that your field to check is Christmas label (make sure spelling & case are exact) then the statement is ready for entry. CONTINUED ->

( 2017-11-18 21:16:31 +0200 )edit

Now click on "Queries" in Base left column. At center toward top click on Create Query in SQL View.... Now enter the completed statement on the blank area. Then press F5 to run the query (as a test). If you get results, check that it is what is wanted. If you get an error dialog there is something wrong in the statement. If all OK save & give it a name. The name given is what you will access it with in writer.

( 2017-11-18 21:22:41 +0200 )edit

Many thanks for your patience in dealing with my tedious questions. After several false starts I have now got the basic process to work, and even to produce a document using the construct . . . WHERE "Field" IS NOT NULL . I hope I will not need to trouble you again, and presume it is now in order for me mark this as 'answered'.

( 2017-11-23 22:44:50 +0200 )edit

Many thanks for this reply, and my apologies for confusing the tags for databases and spreadsheets - being new to LibreOffice I was under the impression that I was working with a database.

The information in the reference that you provided was excellent as far as it went, and I can now print sheets of address labels. However, I require to select certain entries - in the previous Windows Database I could select only those with a 'Y' in a certain field e.g. 'Christmas label'. Is there any way that this can be done in LibreOffice other than by going through a copy of the .ods file and deleting the unwanted entries before converting it to .odb ? I have looked at various 'Help' pages, but cannot find any guidance on this.

more

@JKeswick Please do not use answers for a reply as they are to be used for response to the original question. Instead use a comment for the answer you are responding to (can be multiple). Will answer your other question shortly.

( 2017-11-15 22:40:30 +0200 )edit

( 2017-11-15 22:58:11 +0200 )edit

BTW - You stated '...deleting the unwanted entries before converting it to .odb' above. You didn't really "convert" it but rather created a link to it. It isn't a relational database as Base usually is used for but rather a flat file accessed by Base from the Calc document.

( 2017-11-16 01:31:48 +0200 )edit