Macro to Sort Listbox dimension and expression

[VivenReddy]

I need assistance in creating macros that will sort a listbox. One macro needs to sort the dimension (asc) and one macro should sort the expression (desc). I need to be able to switch between the 2 sorts using buttons.

A sample qvw has been attached.

[Frank_Hartmann]

attached sample might be helpful although its not using any macro.

hope this helps

[QFabian]

Hi @VivenReddy  please try this macro, i used 'LB01'  as Id, becasue is the id from your example qvw

Dont forget to add a 'external\ run macro' action, using the macro name

Let my know if it works

sub sort
set LB = ActiveDocument.GetSheetObject("LB01")
set box=LB.GetProperties
if box.SortCriteria.SortByState = true then
box.SortCriteria.SortByState = false
else
box.SortCriteria.SortByState = true
end if
LB.SetProperties box
end sub

[QFabian]

Dear @VivenReddy , can you just create a straight table with youProduct as dimension, and one expression, with the same expression that you use in the list box?

With that, you just to double click over the Sales expression title to change the order of Products and use the Product colum to filter.

You can even add aselector for the Product column

[VivenReddy]

Thank you @Frank_Hartmann and @QFabian for your great suggestions. Unfortunately the example I posted did not match my use case. I've attached another example. See screenshot below. My listbox actually has 2 expressions and I need buttons to sort the expr 1 in ascending order and expr 2 in descending order. Is this possible?

[QFabian]

Hi @VivenReddy , please check your updated qvw here attached, it has two macros associated in the  buttons.

Here the little macros, that can be found in APIGuide.qvw:

sub order1

set fld=ActiveDocument.GetField("Product")
set fldsrt = fld.GetDefaultSortCriteria
fldsrt.SortByExpression = 1
fldsrt.Expression.v = "only({<Product>}Description)"
fld.SetDefaultSortCriteria fldsrt

end sub

sub order2

set fld=ActiveDocument.GetField("Product")
set fldsrt = fld.GetDefaultSortCriteria
fldsrt.SortByExpression = 1
fldsrt.Expression.v = "sum({<Product>}Sales)"
fld.SetDefaultSortCriteria fldsrt

end sub

let me know if this is an acceptable solution

[VivenReddy]

Thanks @QFabian. I did see this in the APIGuide however I didnt think it would work for me for the following reasons:

1. The macro does not reference an object - so I was not sure how it would affect that field in the rest of my app.

2. What if the field in my listbox is an expression? 

I also noticed that your macro to sort the description is not working correctly. Is there an issue somewhere? 

[Frank_Hartmann · Accepted solution]

see attached file

[VivenReddy]

Thanks Frank - I did eventually implement your solution in my app.

If there is no other solution by next week I'll mark this as the solution.