How attribute personalized filters to objects/buttons?

Hi everybody… and sorry for my bad english.
I’m trying to understand, if it is possible in Calc, to assigning a filter to an object/button.
I have a Spreadsheet, where i have some columns with names, containing data in rows.
I don’t be able to find a way, to filter for example a determinated data of the column “G” , and then
assigning this filter to an object/button, and calling THIS filtration by “pressing” the assigned button.
Is there any way to obtain this result already using macros maybe?
I hope that anyone understands my request and can help me.
Thank You!

This iis very easy to do with a database where you define queries that return any subset of columns and rows in any order of columns and rows.
Calc macros performing filter actions are rather difficult. See Macro in Basic - how to turn on Regular expression in Macro that uses an advance filter in one named range to filter another named range - #2 by Villeroy

1 Like

If you upload an example file and describe the required autofilter, then probably someone will write the necessary macro.

Alright, I did it again.
Attached spreadsheet has a documentation sheet, a source table in datbase range “Data” and a filtered output table in database range “Import1”.
All the filters and sort orders are defined in the attached database document “QBL” (Query By Listbox). The database is connected to the spreadsheet. It treats the spreadsheet as if it were a database.
How to make the sample work:
Download both files, call Tools>Options>Base>Databases and register the downloaded database file as “QBL”. Open the spreadsheet, choose the “Filter” sheet and use the listbox which contains query names.
This would be much better with a true database. Database data stored in spreadsheets are a mess.
QBL.odb (3.8 KB)
query_by_listbox.ods (81.2 KB)

1 Like

GesMag_Example1.ods (378.2 KB)
Hi sokol92, hi everybody. Thanks for support.
The attached example, is very similar to my real Spreadsheet… only the Graphic Look, and very many other records are absent. I’m sure that it don’t needs presentations for the work it does.
It contains macros, for locking the pages, and page change… and functions/conditions too.
The work is in italian, but I translated the interested fields, and think the best way to obtain a working result is to not hide anything. If you lock the page the unlock psw is 123456.
This protection is just to protect the sheet against accidentally changes… nothing else.
Ok let’s go…
Like you can see, the “Magazzino” page has named columns, and data is inserted in rows.
So it would be fantastic, if I can filter for example the “Category” column, to obtain and show only the
rows containing the “PMMA” data… and assign this “filtration event” to the shape in the header (or to any other “thing” you can suggest me.
Then, it would be very super, if i can filter another column, the “In Stock” for example, to show me only the RED values (or the Yellow, or only 0 values) of the “PMMA”… and assing this “filtration event” to another shape in the header.
And so on… in addictive mode, I’m tempted to say.
The intent of all this, is to make it easy to anyone to filter this principal records in easy way.
Would be for me, I can use the automatic filter… but I’m not the only user, unfortunately…
If anyone can show me, how to implement this “macros?” in general way, after I will adapt them
to my special needs(or I try), whenever possible for you.
Thanks for all who can help me.

I agree. Nevertheless, let’s try.

@GioC79 , please, read the description of the AddFilterField function, which sets (removes) a filter on an autofilter field and try the TestAddFilterField macro with various parameters.

Option Explicit

Sub TestAddFilterField()
  Dim oSheet
  oSheet=ThisComponent.Sheets.getByName("Magazzino")
  
  AddFilterField oSheet, "Category", 2, "PMMA"           ' 2: EQUAL
  AddFilterField oSheet, "L. mm", 2, Array(2030, 3050)   ' 2: EQUAL
  
  ' Also try this:
  ' AddFilterField oSheet, "Category", 2, Array("ACCIAIO-AISI304", "ALLUMINIO-FRE")
  ' AddFilterField oSheet, "L. mm", -1     ' Remove filter from "L. mm" field
End Sub

' -------------------------------------------------------------------------------------------------
' Adds a field to an existing autofilter.
' Params:
' obj        DataBaseRange object or spreadsheet.
' field      field number in the autofilter range or field name.
' operator   is specified according to the com.sun.star.sheet.FilterOperator enumeration:
'            https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet.html#af9e5fd8fd26fc252748d97ebd68ea6b1 
'            To remove a field from the filter, specify -1.
' value      value (scalar value or array of values).
Function AddFilterField(ByVal obj As Object, ByVal field, ByVal operator, Optional ByVal value) As Boolean
  Dim oDBRange As Object, oRange As Object, oFilterDesc As Object
  Dim s As String, ind as Long, i as Long, j As Long, lb as Long, filterFields, values, v
  Dim tableFilterField As New com.sun.star.sheet.TableFilterField3
  Dim filterFieldValues(0) As New com.sun.star.sheet.FilterFieldValue
  Dim filterFieldValue As New com.sun.star.sheet.FilterFieldValue
  Dim filterFieldsN(0) As New com.sun.star.sheet.TableFilterField3
  
  AddFilterField=False
  
  If HasUnoInterfaces(obj, "com.sun.star.sheet.XDatabaseRange") Then
    oDBRange=obj
  ElseIf HasUnoInterfaces(obj, "com.sun.star.sheet.XSpreadsheet") Then
    oDBRange=GetSheetFilterDBRange(obj)
  EndIf  
 
  If oDBRange Is Nothing Then Exit Function
  If Not oDBRange.AutoFilter Then Exit Function
  oRange=oDBRange.ReferredCells
  
  If VarType(field)=V_STRING Then   ' field name - search in the first row of oRangе
    s=Lcase(field)
    field=-1
    For j=0 To oRange.Columns.Count - 1
      If lCase(oRange.getCellByPosition(j, 0).string)=s Then 
        field=j
        Exit For 
      End If
    Next j
  End If         
  
  If field<0 Then Exit Function 
  
  oFilterDesc=oDBRange.getFilterDescriptor()
  filterFields=oFilterDesc.filterFields3

  ' The Values.FilterType property must be set.
  ' See: https://forum.openoffice.org/en/forum/viewtopic.php?t=109513&hilit=setFilterFields3 by @wrighch  
  For j=0 To UBound(filterFields)
    tableFilterField=filterFields(j)
    values=tableFilterField.values
    For i=0 To Ubound(values)
       filterFieldValue=values(i)
       If filterFieldValue.StringValue<>"" And filterFieldValue.NumericValue=0 Then
         filterFieldValue.FilterType=1     ' com.sun.star.sheet.FilterFieldType.STRING
       Else
         filterFieldValue.FilterType=0     ' com.sun.star.sheet.FilterFieldType.NUMERIC
       End If  
       values(i)= filterFieldValue
    Next i
    tableFilterField.values=values
    filterFields(j)=tableFilterField
  Next j    
     
  ' Define ind - the index of field in filterFields array
  ind=-1
  For j=0 To UBound(filterFields)
    If filterFields(j).Field=field Then
      ind=j
      Exit For
    End If
  Next j    
  
  If operator=-1 Then ' remove filter field
    If ind=-1 Then Exit Function
    If UBound(filterFields)=0 Then
      oFilterDesc.setFilterFields Array()
    Else
      For j=ind To UBound(filterFields)-1
        filterFields(j)=filterFields(j+1)
      Next j
      ReDim Preserve filterFields(UBound(filterFields)-1) 
      oFilterDesc.setFilterFields3 filterFields
    End If  
    
    oDBRange.refresh()
    AddFilterField=True    
    Exit Function 
  End If  ' operator=-1
  
  tableFilterField.Operator=operator
  tableFilterField.Field=field
  If Not IsArray(value) Then value=Array(value)
  lb=LBound(value)
  ReDim filterFieldValues(Ubound(value) - lb)
  For j=lb To Ubound(value)
    If varType(value(j))=V_STRING Then
      filterFieldValue.FilterType=1
      filterFieldValue.StringValue=value(j)
    Else
      filterFieldValue.NumericValue=value(j)
      filterFieldValue.FilterType=0
    End If  
    
    filterFieldValues(j-lb)=filterFieldValue
  Next j
  
  tableFilterField.Values=filterFieldValues
  
  If ind=-1 Then
    If Ubound(filterFields)=-1 Then
      filterFields=filterFieldsN
    Else  
      Redim Preserve filterFields(0 To Ubound(filterFields)+1)
    End If  
    ind=Ubound(filterFields)
  End If
  
  filterFields(ind)=tableFilterField  
  oFilterDesc.FilterFields3=filterFields
 
  oDBRange.refresh()
  AddFilterField=True
End Function

' -------------------------------------------------------------------------------------------------
' lang:en
' Returns the DatabaseRange object for sheet autofilter. 
Function GetSheetFilterDBRange(oSheet) as Object
  Dim i as Long, oDoc
  GetSheetFilterDBRange=Nothing
  oDoc=oSheet.Drawpage.Forms.Parent
  i=oSheet.RangeAddress.Sheet
  With oDoc.getPropertyValue("UnnamedDatabaseRanges")
    If .hasByTable(i) Then GetSheetFilterDBRange=.getByTable(i)
  End With
End Function
1 Like

Calc’s filtering and sorting is limited. I ge better result more easily from a database connection.
Something like the following is impossible or very hard to do in Calc, with or without macros:
OR before AND: SELECT * FROM TBL WHERE (A = B OR A=C) AND (B=D OR B=E)
Returnn 3 columns filtered and sorted by other columns: SELECT E,C,A FROM TBL WHERE B<1 ORDER BY F
There are no limitations on how many columns can be sorted and filtered, even when the database is a spreadsheet actually.
WIth a true relational database we have many more options because we can filter and sort one table by values in a related table.
We get much better performance after saving spreadsheet lists as *.dbf files, connecting a Base document to the dbf directory and adding some indices. Such a dBase connection works fluently with millions of records.

1 Like

Yes, that’s all true.
Nevertheless, millions of people use spreadsheets and we, to the best of our ability, try to help them.

1 Like

Well, no reasoning is strong enough to pull people away from silly spreadsheets. However, Base can add some advanced filtering and sorting to spreadsheets. Database links between different spreadsheet documents have some advantages over DDE and file:// links if the source data are consistatnt lists (one header row with consistant field types below).

1 Like

A different context is more familiar to me.
We, in particular, deal with corporate reporting. The data source is SQL databases (Oracle, PostgreSQL), reports are generated in Calc or Excel.

1 Like

This way you never encounter any difficulties with complex sorting and filtering. The spreadsheet is just a formattable and printable output medium with arithmetic extra features. You rarely have to write any bullshit like IF(A2="";"";B2/A2) either because there can not be any null value or because any operation with a null value gives null. And you never need to test if some value is consistent or not because the database engine takes care of it.

2 Likes

Yes, forum participants introduce me to these kinds of problems.
I even learned to use a compass and ruler to compose non-trivial formulas. :slightly_smiling_face:

1 Like

Ciao sokol92, ciao Villeroy… ciao everybody.
Thanks for spending time for my problem.
I say wow… :woozy_face:… I never think that this could be a problem of this entity.
So, I will make some trys… let me some time for this and even it works or not, I will let know.
Because i say it sincerly, for me it is not so simple to understand what you wrote (I have very basic knowledge of programming in basic, that’s why poeple like me uses macrorecorder :pleading_face:). But I will commit and try to do my best.
Thank you again.

1 Like

I have the same problem. I found out that MS 365 (Office) has a function called “Slicer”, that does exactly this type of filter. It creates buttons on top of the spreadsheet, based on column titles, and by clicking that button it shows only the rows with that specific choice.
Problem is, I can`t find an equivalent function on LibreOffice. Seems like it’s not available at all.
Update: I found out that WPS Office (free…) has same functions as MS 365 Excel. Slicers works perfectly.