How to prevent Database Tables in LO CALC from merging

English
#1

I’ve been experimenting with database tables MENU>Data>Define Range and discovered that if data is entered into a cell that “bumps into” the next table then both tables are merged.
It’s quite a nightmare trying to separate them again as the procedure seems to involve deleting the offending data and modifying the damaged table. I haven’t yet been able to identify why it sometimes didn’t repair the table but I think it was probably just me experimenting to discover the correct procedure. The table just kept reverting to its merged state - it seemed to “remember” to where the table was extended and continued utilising those boundaries.
Is there a mechanism by which CALC can be instructed “do NOT merge” overlapping tables?

Version: 7.2.2.2 (x64) / LibreOffice Community
Build ID: 02b2acce88a210515b4a5bb2e46cbfb63fe97d56
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win

#2

menu:Data>Define…

Options
[X] Insert or delete cells
[X] Keep formatting (the formatting of the spreadsheet)

Both options should be on by default. OpenOffice does this right. I’ve never seen a use case where any of these options should be off.

#3

image

It defaults to “on” and I never even opened the options tab until you identified it.

#4

image
What I created
image
The Definition
image
Enter data beyond the range and bumping into the next array

image
image1071×608 30.2 KB

Sort column C and the LO adjustment to the Definition
Is it supposed to do it like that?

#5

I use this with linked database queries dragged from the data source window into the sheet.
When I drag another query over the existing range, I’m prompted if I want to replace the contents and get a new row set under the same name without overlapping.
Unfortunately, the 2 mentioned options are off and need to be set.
The 2 options do not make any sense with manually defined database ranges. I think, this is one of the many issues where some developer implemented something because of popular demand without actually understanding what he/she manipulates. The 2 options and “contains totals row” should be dropped altogether.
Import ranges (database ranges linked to data source) should always insert and remove cells. Any “totals row” moves up and down with the changing size of the imported row set. And nobody wants the spreadsheet formatting overridden by the database settings.
All this does not apply to manually defined db-ranges anyway, and I have no idea how to prevent overlapping.

#6

As I first mentioned, I was experimenting - because I could use the ability to have more than one sorted array in the sheet. I was hoping to investigate the 3D prospects related to the INDEX() function when I inadvertently typed into the area between the arrays and the moment I sorted it to see how the INDEX() responded - I groaned.
I think I’ve also “discovered” that if I clone a sheet with multiple filtered ranges to another .ods file then none of it works because the internal “embedding” seems to relate everything to the original sheet number so if it was sheet 3 in the source and it goes to sheet 4 in the destination then again it goes pear-shaped.
Perhaps everybody else (experienced) is aware of that but it is taking a lifetime to find and iron out the wrinkles.
Manually preventing the overlapping should be fairly simple - Just don’t do it. :innocent:
I didn’t quite understand whether you were saying the inclusion of a “totals” row shouldn’t or couldn’t happen with a manually defined db range.
Thanks for taking the time to help

#7

The totals row is just another header row at the bottom. It is excluded from sorting and filtering. That’s it. At least I can not find more info about this new feature. You could also not include it in the db-range. An import range would not include this row anyway and with insert/remove option it would stick to the end of the import range.

Indeed, this is the major benefit of “pure” database ranges without database connection. They allow multiple rectangles of cells, each one faking a database table, with some “memory” about sorting and filtering. This does not come close to the capabilities of a simple relational database.

#8

I can’t quite remember whether I first started experimenting with this at the same time I experienced the glitch with SMALL() function. I suspect part of the problem is related to my suspicion that some internal memory didn’t get cleared when I forced LO to stop. Are you aware of whatever the LO procedure is that enables it to open and process the second and subsequent files far more rapidly than whatever is the first file of the session?
I¨ve had a little more success with this current issue in that I simply re-wrote the ideas in a new sheet rather than any copying or repairing and it’s far more stable.
In the original, I created some sum() cells which when I double-clicked some of them to verify their target cells, identified ranges that overlapped more than one db range. ∴ it appears the autoindex cells persisted in including the rows in their “sort memory”. It was weird, I could edit the range to only include the cells I wanted, I could select the range and it would only select the defined range but the moment I activated any autofilter in the offending range, it simply extended the activity range to include the “overlapping” rows and their headers. I simply can’t get it to break the links. That’s why I suspect it was one of the items I first worked with during the failure of SMALL() - then again, it could be a LO procedure that gets corrupted by a user inadvertently writing a “range” function to overlap more than one db range. Obviously, it would be human error but the problem is - there’s no way to recover from it. Would that be a bug report?

#9

Perhaps I should clarify that I also remedied the overlapping sum() expression

#10

If you sort the entire range on column C from values A to A12 then yes. It does what you told it to do, you just told it to do the wrong thing.

#11

My point exactly - I never told it to sort on the overlapping range. I defined the db range as stopping short of the next array by 2 rows and then inadvertently populated the two empty “NON- dbrange” empty cells in one column - I never “inserted” rows into the range. LO then decided the db range should be extended because I entered something outside my defined db range. Subsequently, merged it all into one “sort”. LO doesn’t extend any other defined ranges unless cells are inserted - why do it now?

#12

I’m not familiar with any of the concepts @Villeroy has mentioned about inserting data from a real database. Is it that a sheet would be created with an insertion point for the data but not an end point and that LO automatically dimensions the array - shifting everything else down in the process?

#13

So how did you tell it to sort?

#14

I defined the range stopping short of the next array by two rows and simply set the autofilter.
I comfortably sorted any filter without affecting the next array.
EDIT: Ignore the next para. it is demonstrated further below as irrelevant.
I can’t remember whether I drag filled or simply typed the next two items outside the array limits and then activated the autofilter on the first column to discover it was arbitrarily extended to include the next array.

#15

I can conclusively identify that it is incorrect.
Create a matrix B2:E9 and call it anything you like
Set the top row as autofilter - you’ll need some “header” labels
insert data into B4:B9
Autofill C4:E9 with random numbers
Verify sorting functionality
Create a matrix B12:E18 and call it anything you like
Set the top row as autofilter - you’ll need some “header” labels
insert data into B13:B18
Autofill C13:E18 with random numbers
Verify sorting functionality

Define data ranges and verify the boundaries
Enter data into B10
Verify the boundaries
Enter data into B11
Verify the boundaries
Select tha autofilter button in B3
Observe the list
Verify the boundaries of the first array
Observe the arrray now encompasses both areas
Delete the data from B10:B11
Observe that the autofilter button still ascribes the entire column to the sort array and the array definition still encompasses both arrays.
Save it
Close it
Reload it
Observe the sheet is now completely pear-shaped

#16

Maybe related to the option Menu/Tools/LibreOffice Calc/General - Expand reference when new columns/rows are inserted.

#17

Already set

#20

I have now established that whilst the definitions for both ranges co-exist and the second range can indeed be selected, if the Second range is manually selected and moved out of the boundaries of the “extended” first range then the db range definition for that second range disappears.
The definition for the first range can then be corrected by deleting the “surplus” rows and a replacement definition for the second range created in the normal manner.
Unfortunately, even though the first range maintains its autofilter buttons, they are inoperative. This can be remedied by deleting the autofilter range and re-creating it

#21

bug 145995

#22

Workarounds:
1 Data Validation of the cells immediately above the header - say minimum of 5000 characters or perhaps a 256bit hash of something
2 Cell protection of the cells immediately above the header - ensure this doesn’t interfere with any regular sheet operations.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.