Filtering on results of XPySort in Calc

[FrogFan]

Hello,

I use XPySort to sort a sheet automatically for my user. A link to XPySort is available in the last post in this thread. It has worked flawlessly for me since @mikekaganski patched it to work with Python 3, and hence LibreOffice, in June 2019.

I run LO in the Windows 10 environment. My user needs to filter the results of XPySort on values of a particular column. Until recently, such filtering has been no problem.

I recently switched to version 7.3.4.2 (x64) of LO and now the results of XPySort can’t be filtered. For example, if I add an AutoFilter to the appropriate column, then attempt to filter, I see all of the values in that column; i.e., the filter recognizes the data in the column to be filtered. However, when I select the value I’d like to filter on, the filter returns no rows. Indeed, regardless of which value I choose from the AutoFilter, I get no rows back. Then, if I choose “All” values, I get all the rows back. Interestingly, perhaps, I’m able to “hide” rows in the result of XPySort.

XPySort is an array function, so I thought maybe there had been a change in LO that prohibited filtering on the results of an array formula. But then I constructed a simple array formula and demonstrated to myself that that was not the case. LO was perfectly happy to allow me to filter on one of the columns of the result of an array formula.

Can XPySort be “fixed” so that I can filter on resulting array? I understand that XPySort is old and might be obsolete, and I might need to abandon its use. That would be a shame, but in that case, I would appreciate any suggestions that would help satisfy my user’s requirement.

Thanks for any help.

[mikekaganski]

It would be nice if you attached a sample document: I don’t feel like creating some imagined data set, and some formula using the extension, just to learn later than it’s not the data set that causes the error

[FrogFan]

Of course. I was actually afraid someone might come along and dismiss my question out-of-hand because the extension is so old! Thanks for helping.

I’ve included a workbook with three included sheets, unsorted data, sorted data, and field specs for the XPySort. If you try to filter on any of the sorted data columns, you’ll get no rows back.

For fun, I downloaded a old version of LO (6.4.7.2) and confirmed that I could filter on the sorted data successfully. Something happened between that version and the one I’m currently using.

Thanks again for your help.
AskLibreOffice.ods (38.6 KB)

[karolus]

With me (LO7.0.4.2) no problem with Autofilter|Standardfilter on your py_sorted Data

[FrogFan]

Thanks for the feedback. 7.3.4.2 doesn’t work with the standard or auto filter.

[mikekaganski]

tdf#152334

[FrogFan]

Thank you for filing a bug report. It looks like the work around for me is to roll back to any version before 7.3.0.0. I just tested the last release before 7.3.0.0 (7.2.7.2) and found it supported filtering correctly. I will assist my user in rolling back to that version.

I will await a release that fixes the bug in versions 7.3.0.0 and later.

Thanks again for your help.

[FrogFan]

Hello,

I realize this is an old thread, but the topic is still relevant to me, and I thought it might be helpful to preserve all the background information as I try to figure out how to proceed.

I received an email a few months back regarding the bug report @mikekaganski filed on my behalf. The bug was never addressed and the LibreOffice team requested that I (or someone) test the newest version of Calc to see if the problem still existed. I spent some time loading up the most recent version and found that I could not even load Xpysort, let alone use it and filter on its results. I left my comments in the bug report form (see the above link).

I’ve noticed that there is now an “onboard” function in Calc that performs a sort much like Xpysort. If I use this function instead of Xpysort, I can filter the results. So, if I upgrade to the newest LibreOffice version, I can replace Xpysort with the onboard function.

The problem with upgrading LibreOffice and replacing Xpysort is that my user has more than 5 years of historical spreadsheets compiled using older versions of LO and Xpysort. If my user tries to open one of these historical spreadsheets using the new version, it won’t load correctly.

I’m trying to understand how to support my user short of installing two versions of LO on her computer and telling her to open files created before a certain date with an old version and after that date with the new version. My user isn’t computer savvy and this approach would be very error prone for her. (I’m not even sure how to have two versions of LO running on the same computer at the same time.)

One thing I could do is go back 5 years and re-write the spreadsheets applicable to those years to be compatible with the new version and onboard sort function. There is separate spreadsheet for every month in each of the 5 historical years, so proceeding in this way like a fair bit of work. I’d like to avoid it if there is a more clever approach available.

Another approach would be to simply tell my user that any historical spreadsheet before a specified date can no longer be accessed. She doesn’t often need to check historical data, but she does from time to time, so this approach seems unworkable.

I look forward to any assistance from the experts here.

[mikekaganski]

@FrogFan

I spent some time loading up the most recent version and found that I could not even load Xpysort, let alone use it and filter on its results.

Works (and of course, initially installs) fine for me using Version: 25.2.3.1 (X86_64) / LibreOffice Community
Build ID: d8d1af5f77df955194e52baabe19324532ac8e8b
CPU threads: 24; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Vulkan; VCL: win
Locale: de-DE (en_US); UI: en-GB
Calc: CL threaded

Try installing with a clean profile.