This is not a question, but an observation - and I hope it is the right place to post it.
I have been working on a database that has passed through various transformations, including being exported as .csv files and then imported to another platform. In its current incarnation the data is stored in an SQLite database, with LibreOffice Base as the front-end. As I have progressively replicated what it used to do on another platform, all sorts of glitches have had to be ironed out. One of the more puzzling I have resolved only recently, and the discovery may be of use to others.
The problem presented itself in certain reports, in which, inexplicably, calculated fields for certain records came out blank when they should not have done. I discovered in due course - using SQLite Studio to examine the data files - that in the “old” data, “empty” text fields were represented by zero-length strings, while in the new configuration, such fields in new records were stored as NULL values. (I suspect that different databases may behave differently, allowing only one or the other but not both.) Visually, this distinction was completely hidden in the LO forms and tables. Having detected this inconsistency, I was able to pin it down in the calculated fields, by use of two different conditional expressions:
IF ISBLANK(this field) returns TRUE to fields with null values and FALSE to any other content (including a field with a zero-length string);
IF (thisfield = "") returns TRUE to a zero-length string and FALSE to any concrete value, but returns NULL to a null value in the field.
The former, used on its own, appears to produce inconsistent results, with blanks appearing in calculated expressions in which they should have been suppressed, while the latter, by returning NULL in one part of a calculated expression, resulted in the entire expression being null - and therefore showing blanks in a report where values should have appeared.
There are two work-arounds for this problem. The best is obviously to remove the inconsistency by updating each table to put zero-length strings in fields which have null values (or vice-versa). It is not too laborious, and this is what I have now done. The other alternative is specifically to allow for both conditions when constructing calculated expressions.
Let us suppose that you have a table containing names of individuals, with two fields for forenames and one for the surname. Some people will have two forenames, but not all, so the second forename may have a null value or string of zero length. In a report based on this table, you want full names to print out, according to what the records contain. First name and Surname are required fields and do not contribute to the problem. However, the calculated field =Name1 & " " & Name2 & " " & Surname will generate a complete blank if the middle name is null. The answer is a nested IF-statement:
= Name1 & (IF(ISBLANK(Name2);"";IF(Name2="";"";" "&Name2)) & " " & Surname
This tests whether Name2 is null, in which case nothing is added to the string to be printed; if it is not null, it is tested again to see if it is empty; if so, again nothing is added between the first and last names, but if there is a name, it is inserted appropriately and all conditions have been satisfied.