Description: hi @all, i have a little issue with a function? method? in a macro, i asked other users to check it out before filing a bug at https://ask.libreoffice.org/en/question/218463/macro-script-getfilterdescriptor-broken-on-fresh-loaded-files/, in short: "getFilterDescriptor()" gets wrong values for 'field' as property of 'filterfields(x)' in the 'filterfields' part of the struct, once two conditions are met: - in a fresh loaded file where filters are set, - if the 'defined range' it works in has an 'offset' from cell A1, in long: i have a macro that 'steps through filtering values' of the autofilter for a column in order to check subsets of the table quite fast :-) - see attached code, it produces erroneous results with the abovementioned conditions, to check let it run on the different sheets of the attached file - see next comment, "getFilterDescriptor()", in this case called as "oFilterDesc = oRange.getFilterDescriptor()" gets the number of the column as value for "fields" on sheets 1,2,3, and the number of the column reduced by the 'vertical offset' (start row) of the 'defined range' it's working in on sheets 4 and 5. but "referredcells.filter(oFilterDesc)" does other calculations for the column, thus applying it to wrong columns in sheet 2,3,4, sheet 5 looks healthy, but i'm guessing only because two mistakes cancel each other out, after 'restart' of the autofilter - autofilter off - autofilter on - the value for 'field' is always calculated relative to the 'defined range', and everything works as intended, file with macro to play with it: - see next comment - the macro code: *********** sub PreviousFilterShortcut ' this macro - steps back - by one value in the range of possible autofilter ' values for the actual column. useful to cycle through subranges of datasets ' and e.g. check their results, e.g. in calculated subtotals of columns, ' (subtotal(9,(´range´)). ' ' the only requirements are that you have a 'database range' defined ' in the table under <data -="" define="" range="">, that you have selected a single cell, ' and that this cell, the 'focus', is within the defined range, oDoc = ThisComponent oControl = oDoc.CurrentController oSheet = oControl.getActiveSheet oCell = oDoc.getCurrentSelection Column = oCell.CellAddress.Column ' search for database range the focus is in oDBRanges = oDoc.DatabaseRanges for i = 0 to oDBRanges.Count-1 oDBrange = oDBRanges.getByIndex(i) oCellrange = oDbrange.ReferredCells if oCellrange.queryIntersection(oCell.RangeAddress).Count > 0 then oRange = oDBrange End If next ' add a messagebox for errors, if isempty(oRange) then msgbox "actual cell not within a database range",0,"" if isempty(oRange) then exit sub ' define new filter-description Dim oNeu As New com.sun.star.sheet.TableFilterField With oNeu .Field = Column - oRange.ReferredCells.RangeAddress.StartColumn ' filter-column (other position in code than in datasurfer?) .IsNumeric = False .StringValue = "" .Operator = com.sun.star.sheet.FilterOperator.NOT_EQUAL End With 'analyse existing filter oFilterDesc = oRange.getFilterDescriptor() aFields()=oFilterDesc.getFilterFields n=-1 sAlt="" Do while sAlt="" and n<ubound(aFields()) n=n+1 if aFields(n).Field=Column - oRange.ReferredCells.RangeAddress.StartColumn then 'column is actually filtered sAlt=aFields(n).StringValue aFields(n)=oNeu 'filter "off" end if loop ' filter without filtering actual column oRange.AutoFilter = True oFilterDesc.setFilterFields(aFields()) oFilterDesc.ContainsHeader=true oRange.referredcells.filter(oFilterDesc) ' some presets if sAlt="" then n=n+1 Redim Preserve aFields(n) aFields(n)=oNeu end if ' get all possible values for the actual column, respecting filtering done in other columns, zellen= oRange.ReferredCells.queryVisiblecells.queryintersection(oCell.columns(0).RangeAddress).cells.createenumeration ' search for max of smaller values sNeu="" sMax=sAlt zellen.nextelement do while zellen.hasmoreelements sTemp=zellen.nextelement.string if sTemp>sMax then sMax=sTemp ' find max if sNeu="" then if sTemp<sAlt then sNeu=sTemp ' else if sTemp<sAlt and sTemp>sNeu then sNeu=sTemp end if loop if sNeu="" then sNeu=sMax ' set new filter criteria aFields(n).StringValue=sNeu aFields(n).Operator = com.sun.star.sheet.FilterOperator.EQUAL ' apply filtering oFilterDesc.setFilterFields(aFields()) oRange.referredcells.filter(oFilterDesc) End Sub 'PreviousFilterShortcut *********** the macro is one of four which do quite handy things for me, (toggle autofilter on/off, modify autofilter according value of selected cell (datasurfer), step forward through the filtering values in one column, step backwards) which i'd like to give to others and 'the community' once they run well, the last three suffer from mentioned problem, i'd provide 'backwards' as example reg. it's 'short' and 'in english', 'forward' is partly german, credits to https://www.libreoffice-forum.de/ and esp. 'mikele' who helped coming so far ... reg. b. Steps to Reproduce: see description above and test file in next comment, Actual Results: 'field' in filterfields() of filterfields is calculated relative to column 1 of the sheet in fresh loaded files, affects database ranges with an offset to A1, Expected Results: the value for 'field' being calculated relative to the database range the filter is defined in, Reproducible: Always User Profile Reset: No Additional Info: different flav's from4.1.6.2 to 6.5.0.0 show identical behaviour,
Created attachment 156188 [details] macro_wrong_column_3 file with macro applying autofilter to wrong columns, when they are loaded with autofilter set, see initial bug report for details,
(In reply to b. from comment #1) > Created attachment 156188 [details] > macro_wrong_column_3 I opened the file, went to Tools - Macros - Run Macro. I chose PreviousFilterShortcut from the file. Result is that all of the data was hidden. Is this good or bad? I have no idea after reading your description and comments. Note: please try to separate the reproduction steps from any analytical writing. Arch Linux 64-bit Version: 7.0.0.0.alpha0+ Build ID: 623d6cf06ccba392c1993a3b0ad271d508205e73 CPU threads: 8; OS: Linux 5.6; UI render: default; VCL: kf5; Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US Calc: threaded Built on 29 April 2020
hello @Buovjaga, thanks for taking care, and sorry for having been imprecise ... it's been some time since i wrote this, may be - i hope so - this: https://bugs.documentfoundation.org/show_bug.cgi?id=132488 is the source of evil, i'd put it in another bug because it's not 'getfilterdescriptor' being wrong but the .field-values saved in the xml file. for the case it's not, or just 'for the records' a step-by-step workthrough for this sample: > I opened the file, went to Tools - Macros - Run Macro. ok, to see the problem evolving take 'edit macro' instead, > I chose PreviousFilterShortcut from the file. correct > Result is that all of the data was hidden. that's not the intended result of the macro, it should step-back the filter to the 'next-less' value in that column, if none is found to the largest, but it's intended from the sample / bug report to show the fault, > Is this good or bad? I have no idea after reading your description and comments. 'bad', let me explain the details: on load of the file 'as downloaded', sheet2 is displayed with the cursor in E2 (hidden), if you have another display change to sheet2, if the cursor is off from E1:E15 place it on E3, in the macro editor set 'afields' as watch variable (bottom left), step through the macro with F8, in line 49: 'aFields()=oFilterDesc.getFilterFields' afield get's assigned the actual filtering condition, check afields(0).field (unfold afields by clicking the v-hook, within that unfold afields(0) by clicking the v-hook), see Field = '4'. Field should be the value 'filtered column relative to database area zero-based', in this sample column C should be 0, D be 1, and E - the filtered one - should be 2, not 4! from this point on titanic will sink. in line 54: 'if aFields(n).Field=Column - oRange.ReferredCells.RangeAddress.StartColumn then' a decision is made if the filtered field is valid for the actual column (the loop is a sort out which field is valid), the check is made against the correct calculated value for .field for column E (4-th column zero based) minus start column of the database area (2-nd col (C, zero-based)) that fails as 2 (calculated) is compared to 4 (fetched from the file), thus the 'unfiltering' of the actual column doesn't work, after that and based on the fault instead of changing the actual filter for col E to 'not ""' that condition is applied to col '2', the area is re-filtered, based on that filtering the 'previous value' is evaluated (to '3'), and put in the filtering condition afields(1), and then instead of assigning one changed filter to the area two conditions are applied, one for .field being 2 requiring '3' in the column and one for .field being 4 requiring '3' in the column, not met in any row, thus 'all off', you can see that both columns 'absolute 2 - relative 0 - C' and 'absolute 4 relative 2 - E' have filtering conditions applied at the end of the macro , check the dropdown fields in the header row, both blue, both 'dotted', and both showing irrational conditions on click, that's how it's 'going wrong', do the same on sheet1, everything fine as no start-column-value has to be respected, absolute and relative column numbers are identical, filter condition for col. E is changed to '6', do the same on sheet3, it tries to filter columns '2' and '6', only one of them is visible in the header, do the same on sheet4, it tries to filter columns '2' and '4', the value fetched from the file is 4 for col. 6, imho caused by the 'row-offset' of the database area, do the same on sheet5, everything looks correct, col E is re-filtered to 6, imho the result of two errors - wrong evaluation of col-offset and wrong evaluation of row offset - canceling each other out ... > Note: please try to separate the reproduction steps from any analytical writing. i'll try, it is not easy (for me) to present difficult mistakes and complex correlations that I have not yet understood myself, i sometimes see 'there is something wrong' without being able to explain the reason. since most of the 'simple' errors in calc are solved in the meantime it will happen more and more often that you have to describe strange things with long explanations, i know that this is unpleasant for the developers ... i try my best to help instead of annoying. If you want to help me, give me an example how i should have presented this tricky bug better, and keep your fingers crossed that the analysis in #132488 is correct, and someone will soon be found who can correct it, if you can spend time pls. chek it and - if bug - set to 'new', Without anticipation, it could face a difficult situation of how to deal with all the 'old' files stored with wrong values at users ... may be they'll have new problems after a fix ... reg. b.
it boils down to something being unclear about the use of the flag/tag 'table:orientation="column"', described there: https://bugs.documentfoundation.org/show_bug.cgi?id=133529 as of now that's the main problem to be solved, thus setting this duplicate, *** This bug has been marked as a duplicate of bug 133529 ***