How to set SortDescriptor for a DatabaseRange

[wrighch]

I have a workbook in which I have used Calc’s menu to define a DatabaseRange using Data > Define Range. The workbook has a macro that builds a SortDescriptor which includes several values and invokes the sort using:
Range.Sort(SortDescriptor()). The Sort works just fine.

Further along, the macro retrieves the Database’s FilterDescriptor using DatabaseRange.getFilterDescriptor. It goes on to modify that FilterDescriptor and imposes that modified descriptor on the same range using DatabaseRange.refresh(). That filtering operation, too, works just fine. Except …

The SortDescriptor that is a Property of the DatabaseRange is not the same as the SortDescriptor which the macro built and applied earlier. And when the Refresh is executed, the SortDescriptor that is a Property of the DatabaseRange overrides the one that was built and executed earlier and re-does the sort based on whatever manual sort selections were last manually applied using the AutoFilter pull-downs.

This page indicates that the refresh method “executes the stored import, filter, sorting, and subtotals descriptors again.” But the SortDescriptor Property of the DatabaseRange is Read-Only.

How does one go about modifying or replacing the SortDescriptor that is a Property of the DatabaseRange?

[newbie-02]

hello @wrighch,

in short: difficult,

in long: without a sample of file and / or macro i might fail in ideas …

might be an option to use ‘xxx.filter’ instaed of ‘xxx.refresh’,

may be other questions as question on stackoverflow, click to open will help,

be aware of some problems:

1. a database range has a property ‘table:orientation=column/row’ defining how the records are oriented in the area, it has only one! such tag, but ‘sort’ uses it as well as ‘filter’, for sort it’s meaningful as horizontal sort is defined and can be used, horizontal filtering isn’t yet implemented, but the filterdescriptor reacts on it and it’s set to ‘column’ instead of the default row on save,

2. ‘filter’ is using that tag with a wrong preset, and changes that over the first save-load cycle, which messes up the filter definition for macro access to the filter descriptor, while the problem is canceled out for the UI by a wrong interpretation for the area and the filterfield values, it also messes up the sort descriptor,

3. all that happens often unnoticed as the UI works, and the impact is different if the row offset and the column offset of the range from top and left border are symmetric or different,

have fun …

[wrighch]

Thanks for your input @newbie-02. You are right: It’s difficult. If any relevant documentation exists, I haven’t found it. Meanwhile, I have identified (by brute force) what I’m using as a workaround. FIRST: I reversed my code sequence use refresh to invoke the FilterDescriptor first, THEN invoke the Sort. SECOND: Rather than build a SortDescriptor from scratch as previously, I use DatabaseRange.getSortDescriptor to obtain the existing SortDescriptor from the DatabaseRange and then replace SortDescriptor(3).Value with the Array of PropertyValue I construct to provide the SortFields. SortDescriptor(3).Name is “SortFields” while the other 8 items in the SortDescriptor array appear to have nothing to do with sorting!? Not sure if I needed to swap the sequence of the Filter and Sort operations. But it works now, so I’m not going to try to “fix it.”

For now, I will leave this question unanswered awaiting a possible suggestion from others.