Selecting elements in the Subset Editor quickly

  • Apr. 16, 2020

Introduction

You could read earlier how I successfully leverage AutoHotKey when I am using TM1. Let's do some more automation ! Anyone who is often working in TM1 in the Subset Editor (Architect / Perspectives) knows that selecting multiple elements at a time can be a pain.

  • Yes: you can use subsets.
  • Yes: you can use the wildcard search.
  • Yes: you can "Show all" and either keep what you need or delete what you don't.
  • Yes: you can enter your element names in Excel or Notepad and copy/paste to the Subset Editor

This is not very convenient and also not fast enough. So what is the alternative ? You can:

  1. show the "Expression Window" if it's not already visible
  2. enter your elements there (aliases are allowed)
  3. clear the main area of the Subset Editor (choose "Keep" twice, or Ctrl + k twice)
  4. copy (or cut) the information from the "Expression Window"
  5. and paste the clipboard into the main area above
  6. hit OK in the Subset Editor

I find that too much of a hassle to be honest. If you do this tens and tens of times a day it's just brain-damaging. The remainder of the article shows a few simple steps in AutoHotKey code to do the hard work for us. What about typing your elements in the "Expression Window" and then have only 1 keystroke to do all the rest for us ? We eliminate steps 3, 4, 5, 6 above.

For example, here's a an excerpt from a simple P&L statement:

And we want to select the countries Denmark, Spain, Mexico.

So you type the 3 country names in the 'Expression Window":

Then you replace the existing selection of countries with the 3 new names. The easiest way (I find) is pressing twice the green checkmark, then copy/paste (or cut) the 3 countries as the new selection:

You have your new selection. Finally !

So here is AutoHotKey code to do it for you. I bind the routine to the key of my liking, which is a very useful key because of its location on the keyboard. In fact, on my keyboard, I have this key right above the Tab key - it's very well accessible with your left hand. We will remap the key to something more useful, such as to assist us in Subset Editor automation. However, if you don't remap the key, its native function is to give you the superscript 2 (power 2). If you press Shift in combination with the key you will get superscript 3 (power 3). Frankly, I don't need that key. I am doing finance and IT, not mathematics ! But I like the interesting location of the key.

Here's the code. You can read it and we will discuss below. 

SC029::
{
   IfWinActive, ahk_class AfxFrameOrView100u
   {
      ControlClick, ListBox1, A,,,NA
      
      Loop, 2
      {
         Send ^k
         KeyWait Control
      }
      
      ControlGetText, Selection_Subset, ListBox1, A
      While (Selection_Subset != "")
      {
         Sleep 100
         ControlGetText, Selection_Subset, ListBox1, A
      }
      
      ControlGetText, Clipboard, RichEdit20W1, A
      
      ControlClick, ListBox1, A,,,NA
      WinWaitActive
      Send ^v
      ControlClick, Button1, A,,,NA
      Return
   }
}

SC029 stands for the power 2 / 3 key on the keyboard. If you don't have that square key on your keyboard or you prefer a different key, please change the SC029 part in the code. Function keys such as F2 and F3, ... are good candidates too.

First, we send Ctrl + k twice to get rid of the current selection for the subset. Ctrl + k can also be done as WinMenuSelectItem, , , Edit, Keep, that's equivalent, but watch out if the user interface language is not set to English: then your routine can break down. We also wait until the selection is really empty - for larger selections it can take some time to finish.

Next, we grab the contents of RichEdit20W1, which happens to the "Expression Window" control. No need to do Ctrl + Home and Shift + Ctrl + End and lastly Ctrl + c. Then we move the cursor to the main selection window and we paste the clipboard contents. The last step involves clicking the OK button of the Subset Editor.

As a final note, another small automation. ITif you prefer to press F5 to run a TI process rather than taking the mouse, use this little snippet of code in an AutoHotKey script and press F5: 

#IfWinActive ahk_class AfxFrameOrView100u
{
   F5::WinMenuSelectItem, , , File, Run
}
#IfWinActive

Same caveat about user interface language and English menu names. Yay, less RSI and we work in a more efficient way ! :-) Enjoy !




Homepage

Section contents

About Wim

Wim Gielis is a Business Intelligence consultant and Excel expert

Other links