Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need to do a macro which loop on a list on field value in a certain order as example below:
MyField | MySortField |
---|---|
A1gA | 1 |
fefz | 2 |
adfgh | 3 |
1sdz | 4 |
I want to select in the order of MySortField the value in the field MyField.
Is it possible to do in macro with getPossibleValues with others functions without use a list box?
If not, how can I do the same thing in looping on a list box object containing field Value of MyField order by MysortField ? What macro function use to do this in using directly this graphic object.
In the second case, I have seen a little problem : sort by expression in my list box don't work on "gray" value (exclude of possible values list). they are all at the end of the available values which are correctly sorted.
Could you help me?
Thanks.
You have to loop by the MySortField, but use MyField as "output". Something like this:
sub macro
set sf=ActiveDocument.Fields("MySortField").GetPossibleValues
set mf=ActiveDocument.Fields("MyField")
for i=0 to sf.Count-1
msgbox (mf.GetPossibleValues.item(i).text)
next
end sub
Thanks.
I solve it in adding a select for each of my SortFieldValue before select first possible value of MyField (only one value by sortFieldValue selection).
I suppose the sort is automaticaly done on the field type (numeric or text) ? Or it's the line order in the QlikView table?
Regards.
I think the sort is taken from the field sort order on the document properties. Which is, by default, numeric for numbers and text for others.
user5674,
I am having the same issue where I need to load an alpha numeric field (Field #1) and have it load/sorted by a numeric sort field (Field #2). The sort order of (Field #1) is unique to each person's ID number (Field #3). I have tried what I believe is your solution of using a .Select, but can't get the sort order to change. Can you share a little code.
Hello,
Sure:
MySortField : the field use for sorting
MySelectField : the field sorted
A QlikView inline table like this
MySortField,MySelectField
1, aaaa
2, d466+
3, arfegg
..., ...
Set MySortField = ActiveDocument.Fields("MySortField")
Set MySortFieldOrder = MySortField.GetPossibleValues
nbSelect = MySortFieldOrder.Count
If nbSelect>0 Then
for valIter = 0 to nbSelect-1
vSortFieldValue=MySortFieldOrder.Item(valIter).text
IF vSortFieldValue<>"" THEN
MySortField.Select vSortFieldValue
ActiveDocument.GetApplication.WaitForIdle
set selectionFieldValues = ActiveDocument.Fields(MySelectField).GetPossibleValues
vMySelectFieldValue=selectionFieldValues.Item(0).Text
ActiveDocument.Fields(MySelectField).Select vMySelectFieldValue
End IF
Next
End IF
A simpler approach may be to leverage the sortorder of the QV concat() function.
x = ActiveDocument.Evaluate("concat(chr(34) & MySelectField & chr(34), ',', MySortField)")
list = Eval("Array(" + x + ")")
For Each item in list
msgbox item
Next
-Rob