Labels generated pdf does not show cell units

[holy-harp]

I have a Labels document that uses an .ods spread sheet (wizarded to a database). Two columns in the sheet have numerical values with the cells formatted to display the units. In the printed off document, the units from the cells in one column show while the units from the cells in the other column do not. An example format code is, #" mcg", the units appear on the spread sheet but not in the pdf. Another example format is, #" per day", the units appear both in the spread sheet and pdf. I tried copying formatting from one column to the other. I tried copying cells from one column to the other. Neither had an affect on the display of units in the pdf. Can a column or cell have a property that is not reflected in the cell dialog box? I think I’ve checked all the properties between a cell in the unit-visble column and the unit-invisible column.

The Dosage column units do not appear in the merge printed off but the Frequency column units do.

As @Ratslinger has pointed out, the wizard transferred some cell units and not others to the database. You can see this clearly in the print filter dialog box:

Here is the relevant part of the .ods file MedsPartial.ods

[Ratslinger]

Hello,

Can’t even get “Frequency” to appear with " per day". Neither is transferred to the database which is where the labels are getting the data. What “Wizard” did you use? Also what is your OS and specific LO version?

[holy-harp]

I used, Address Data Source Wizard. I use LibreOffice 6.1.1.2(x64) Win10
I’m not sure I understand your comment. My best guess is that you tried to replicate what I’ve done. I just tried to replicate with a new spreadsheet (.xls) and neither column cell units transferred.

[Ratslinger]

The database is only picking up the data entered into the cell and not the formatted text. Have tried to duplicate “per day” working as you state did with the .ods file. I can copy/paste the Calc data into an actual database and the text does show but this is a different process.

Would need you to post a sample (of course no personal or confidential information) of the .ods file to try and determine how one field worked & another did not. Can’t get either to work.

[holy-harp]

@Ratslinger. Thanks for looking at this. I’ve edited my post to include a partial file. I checked, it has the same characteristic of the original file. I copied the original file and cut out most of it. I’ve exported spreadsheets to databases before. I don’t know what code or export function I’d have to use to make sure the unit is taken too. This is mysterious. Does the database see this as text or number? I can’t copy either as text using paste>special, only as a number. How does the Wizard handle the fields I wonder.

[Ratslinger]

Hello,

The problem lies in the data within the column. The database fields are created based upon what it sees in a column of the spreadsheet. If all data is numeric it will assign it as a Decimal type field. If mixed (text & numbers) it is a Varchar field - contains text.

Since the data entered is say 5 this is a numeric field. Just because you format the cell to add " mcg" doesn’t change the fact the entered value is still only 5. Just the display has changed. So if the entire column is like this it ends up being a Decimal type field. A cell without data is still acceptable for a numeric field. So the Dosage column is all numbers and transfers only the numeric data.

Now in the Frequency column there is one field which is text - PRN. Now since the column is mixed type it is established as a Varchar field in the database and the displayed values are taken as is.

Can’t say this is right or wrong. Only can offer a fix - a kludge if you will allow me. In cell C11 ( next available line ) enter text only - say xxx. Now save and close all open LO files.

Test your labels again. You should find all fields on labels print as wanted. The exception is the rogue label with xxx on it.

Hope this works for you.

[holy-harp]

Very well thought out and articulated @Ratslilnger. I tweaked your kludge: I filter on null End Dates, so I put PRN in the next line down and 1/1/19 in the End Date field. The dummy record gets filtered out in the printing but the units are saved in the database. In the spreadsheet, In a spreadsheet, each cell can be formatted (have a numeric or text type) but in a database, all the “cells” in a “column” must have the same data type. Thank you very much. May you sleep well.