[Solved] Auto filter interferes with the advanced filter

[eeigor]

First of all, I studied this topic.
Everything was already working, and suddenly...

The Calc sheet has a database range with a criteria range that has an advanced filter associated with it. The advanced filter works through the menu commands as expected. Also worked with a macro. But something happened...
After opening the file, the advanced filter does not work, and if you display the auto filter buttons and expand one (which field does not matter), the auto filter window will be blank (here is the problem, see screenshot). After toggling the auto filter using the menu command, the advanced filter starts working. Accordingly, the auto filter also works when redisplayed by the menu command. But after reopening the file, everything is repeated.
Tried changing the name of the database range, copied the contents of the sheet to another sheet with a special paste ... did not help.
I am under the impression that this is an old problem that has not been resolved, but maybe someone can suggest something so that I don't recreate the sheet from scratch? After all, it worked before...

The screenshot below (the language is not important) shows one unnamed checkbox against the background of the data range behind it.
Moreover, when such a buggy auto filter is displayed, the DatabaseRange.AutoFilter property returns False.

UPD:
I have no any anonymous zombie range names (see the topic in the link above).

UPD2:
Applied a patch.

Code: Select all

Sub SpreadsheetDocument_OnLoad(oEvent)
'	Xray oEvent
	' Toggle auto filter twice.
	Call ToggleAutoFilter
	Call ToggleAutoFilter
End Sub

Sub ToggleAutoFilter()
	Dim document As Object
	Dim dispatcher As Object

	document = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	
	Dim args(0) As New com.sun.star.beans.PropertyValue

	args(0).Name = "ToPoint"
	args(0).Value = "$A$9"  'the top left corner of my database range

	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args())
	dispatcher.executeDispatch(document, ".uno:DataFilterAutoFilter", "", 0, Array())
End Sub

In this case, if after two switching of the auto filter it turns on, then when the database range is refreshed, the auto filter will be removed. It will work in macro now. I mentioned that with the described auto filter bug, the DatabaseRange.AutoFilter property returned False always.

I will prepare an example if necessary. It will take some time. Everything needs to be translated into English.

[Villeroy]

Stop waisting your time. Set up a database and all this shit falls off.

[eeigor]

And yet, let's try to solve this problem. Here's an example. Everything is exactly as described above. Let's leave BASE for larger tasks. The advanced filter is a very handy tool.

Experiment yourself (see example).

Code: Select all

Call ApplyAdvancedFilter  'this way works correctly
Call RefreshDBRange  'and this one is buggy and works with a patch; however, this also worked once

@Villeroy, I'm counting on your help. As well as other participants. Since the problem is solved in principle, the question is more debatable. Any code improvements are welcome. This is very important to me.

UPD:
For example, enter the "Jones" criterion without quotes in the Rep field of the criteria range.
All data below under the database header row will be hidden. Why?

1. Place the cursor in one of the cells in the header row of the database range and display the auto filter via menu command. Then enter the same criterion again. Everything is working. Magic. But the auto filter buttons will be hidden in the macro.

2. Or uncomment the call to the ApplyPatch procedure, save and open the file again.
Now it works but does not satisfy the way to solve the problem.

[Villeroy]

And yet, let's try to solve this problem.

Done

[eeigor]

It does not work for me due to the inability to establish a connection with the data source.
And to be honest, I don't know the Base. Although I once knew MS Access well... I switched to Linux and LibreOffice at the same time recently.
Still, the LO sheet is more convenient when you need to process raw data using formulas before inserting it into a database range.

UPD:
@Villeroy, I used your file (download) as there are no examples of using an advanced filter with a database object through the built-in filter descriptor (refresh method).
This filter works

Code: Select all

Sub ApplyAdvancedFilter()
	Dim oSheet As Object
	Dim oDataRange As Object, oCriteriaRange As Object  'data to filter & criteria
	Dim oDescriptor As Object  'filter descriptor

	On Error GoTo HandleErrors
	With ThisComponent
	'	oSheet = .Sheets.getByName("Данные")
		oSheet = .CurrentController.ActiveSheet
		oDataRange = oSheet.getCellRangeByName(DATABASE)
		oCriteriaRange = oSheet.getCellRangeByName(CRITERIA)
	'	oCriteriaRange = .NamedRanges.getByName(CRITERIA).ReferredCells
	End with

	oDescriptor = oCriteriaRange.createFilterDescriptorByObject(oDataRange)
	oDescriptor.UseRegularExpressions = True

	oDataRange.filter(oDescriptor)
	Exit Sub	

HandleErrors:
	Msgbox "#" & Err & ": " & Error _
	 , MB_ICONSTOP, "macro:ApplyAdvancedFilter"
End Sub

And such a filter does not work after opening the file, but then it works after performing the manipulations described above.

Code: Select all

Sub RefreshDBRange()
	Dim oDBRange As Object, oCriteriaRange As Object  'data to filter & criteria
	Dim oDescriptor As Object, oFields As Object  'filter descriptor & fields

	On Error GoTo HandleErrors
	With ThisComponent
		oDBRange = .DatabaseRanges.getByName(DATABASE)
		oCriteriaRange = .NamedRanges.getByName(CRITERIA).ReferredCells
	End With

	' Update filter fields.
	oDescriptor = oDBRange.FilterDescriptor
	oFields = oCriteriaRange _
	 .createFilterDescriptorByObject(oDBRange.ReferredCells).FilterFields
	oDescriptor.FilterFields = oFields

	With oDBRange
		.FilterDescriptor.UseRegularExpressions = True
	'	.UseFilterCriteriaSource = True
	'	.FilterCriteriaSource = oCriteriaRange.RangeAddress  'csa
REM	Xray oDBRange
		' When using the DatabaseRange object, an auto filter must be disabled,
		' otherwise the advanced filter will not work.
		If .AutoFilter Then .AutoFilter = False  '⁇
	End With

	oDBRange.refresh
	Call EnsureVisible
	Exit Sub	

HandleErrors:
	Msgbox "#" & Err & ": " & Error _
	 , MB_ICONSTOP, "macro:RefreshDBRange"
End Sub

See sample file. What is redundant here? What is missing? Or maybe that's not the point ...

[Villeroy]

Ubuntu Linux? Install some Java runtime and libreoffice-sdbc-hsqldb. Java might be installed already.

[eeigor]

@Villeroy, thank you. I will come to this one way or another. But it is desirable to bring this topic to the end.

[Villeroy]

I'm not in the right mood to struggle with this. All I can tell is that the standard filter, the advanced filter and the auto-filter are 3 different methods to apply the same filter criteria to a cell range. The 4th method is oRange.filter(oFilterDescriptor). Of course they do interfere. And I have no idea what happens if you apply one set of criteria to A1:F99 and another set of criteria to A1:G100.

[eeigor]

Yes, I understand. I have already exhausted my resources. And my experience with LO Calc is only about a year. I have prepared an example and I hope someone is interested in it. @Villeroy, thank you for your feedback.
However, I cannot guarantee that this error will inevitably repeat itself in other environments.
But even in the form that it turned out to be achieved, this example is interesting in itself and typical for solving a certain set of tasks.

[eeigor]

The meaning of the error is as follows:
After opening the file display the Standard Filter dialog, and you will see that the criterion value is set (garbage from the last filter), but the field name is not set. Therefore, all data is hidden. If you set an additional criterion in the criteria range on the sheet, its value can be seen in the same Standard Filter dialog, but without the field name. This is an error, and you need to somehow correct the code of the RefreshDBRange() procedure so that the filter fields are cleared at startup. Help.

UPD:
Double toggling the auto filter, resetting the standard filter, setting the advanced filter in the corresponding dialog windows resuscitates the advanced filter.

UPD:
The screenshot shows that the field name (on the left) is not set (no), but the value is set.

[eeigor]

@Villeroy, I only changed the data in your file without adding a single line of code. Alas, it doesn't work for me. All the same problems described above. All hope for you.
Your file works well.

[Villeroy]

Still works with OpenOffice. I have no idea why this fails with LO since the advanced filter works fine when applied manually. It is all too complex. Just use a database program for database tasks or learn to live with the limitations of a spreadsheet. Pimping sheets with macros is not a solution.

[Villeroy]

It is possible to create one or more FilterField structs, add them to a well prepared FilterDescriptor (array of structs) and then apply that filter to a given range.
My 13 year old solution assumed that it should be possible to define all the gory details in the GUI and then let the macro refresh the right range with the right criteria based on user-defined options. I am not inclinded to investigate why this stopped working with LO. May be a bug, may be a feature. I don't care anymore.

[RPG]

I have no idea why this fails with LO since the advanced filter works fine when applied manually.

As far I understand the filter proces in LibreOffice for database ranges.
The auto-filter extends the standardfilter and the advanced filter. It is nearly about version 5. I cannot find the exact version.

[Villeroy]

Whatever they messed up, I don't care anymore.
Another issue with that filter, apart from the auto-filter precedence, is that regular expressions become a bad idea when your criteria contain special regex characters ^$.?+* and all kinds of braces. My old filter document becomes more fool proof when you turn off all check boxes in the calculation options and replace the regex formula =".*"&linked_cell&".*" with formula =T(linked_cell). This will match all substrings.

[eeigor]

Well, that works better. The database range must be removed and added again every time before first use on load event.

Code: Select all

Const DATABASE = "Database"  'the name of the database range

	Dim oAddr As Object

	With ThisComponent.DatabaseRanges
		oAddr = .getByName(DATABASE).ReferredCells.RangeAddress
		.removeByName(DATABASE)
		.addNewByName(DATABASE, oAddr)
	End With

[Villeroy]

What a horrible mess. How much time did you waste on this?