Dynamic field selection on trigger button

[#1]

Hi,

I want to write a macro, which selects by pushing a trigger button in the field "Customers" all customers with "sales > 10".
I know how to select via bookmark in a macro, but is it possible to do this dynamic?

regards,
corleone

[blaise]

Customers field: Customer

Sales field: Sales

sub Sel10SalesCustomer
 ActiveDocument.ClearAll
 set objCust = ActiveDocument.Fields("Customer")
 set objSales = ActiveDocument.Fields("Sales")
 objSales.Select ">10"
 objCust.SelectPossible
 objSales.Clear
end sub

[#3]

thx 4 answer! short question about this. for my app "sales" is not a field, it's an calculated number from 2 fields.....so objSales.Select ">10" will not work! any idea?

[blaise]

move the calculation to the script.

Cant really come up with an alternative solution. Maybee it can be solved by creating a straight table with customer and your sum calculation, when having the macro loop over the sum field and check if the sum if >10, then fetch the customer dim in the table and ToggleSelect the custom din value to the Customer field. This sounds like a horrible solution though 🙂

[#5]

ok thx i will try!

[blaise]

I got a bit curious on the loop cell values in straight table, so I developed the code for it. My straight table has one dimension, "customer" and one expression. If your table has more dimensions and/or expressions you should change the colIter to match your table (for my example the colIter is 0 for the dimension and 1 for the expression, as seen in the table.GetCell() function).

 sub Sel10SalesCustomer
 ActiveDocument.ClearAll
 set objCust = ActiveDocument.Fields("customer")
 set table = ActiveDocument.GetSheetObject( "CH01" ) 'change to your ID
 Customer = ""
 cellCustCount = 1
 for rowIter = 1 to table.GetRowCount-1
 set cell = table.GetCell(rowIter,1)
 if cell.Text > 10 then
 set cellCust = table.GetCell(rowIter,0)
 if CellCustCount = 1 then
 Customer = cellCust.Text
 cellCustCount = 2
 else
 Customer = Customer &"|"& cellCust.Text
 end if
 else
 'do nothing
 end if
 next
 custString = split(Customer,"|")
 for i= lbound(custString) to ubound(custString)
 objCust.ToggleSelect custString(i)
 next
end sub

[Miguel_Angel_Baeyens]

Hello,

You may try creating a button as new object and adding the built-in action (right click, "Properties" -> Actions tab) and "Select" -> "Select in Field" and use "> 10" (without quotes). I haven't tested that with more complex expressions but it works, and you need not to create a macro.

Regards

[wizardo]

hmmm

just an idea

what if you make a listbox with this expression:

=aggr(sum( [Field_A] )+sum( [Field_B] ),[Customer])

then you could either have your user type >10 in the listbox's search,

or you could wrap the above expr inside an IF statement and make a basket of selections

for example

IF ( aggr(sum( [Field_A] )+sum( [Field_B] ),[Customer]) > 10 , '> 10' , '< 10')

i think you can take it from here

i try to avoid macros when ever i can 🙂

Mansyno

[blaise]

but macros are so fun, but of course your solution is much better (i always forgets expression-made list boxes)

[wizardo]

hmmm yes somtimes they can be

but figuring out how to do things bypassing macros is even more fun 🙂

i thought using a macro too at first but i tried using Evaluate method, which eventually led me to a macroless solution.