3 Replies Latest reply on Mar 10, 2016 3:39 PM by knightk

    Processbook: Table with dynamic sorting

    Doddabasappa

      Hi,

       

      We have a requirement to create table in Processbook. Each row of the table belongs to one element in AF and each columns get data from attributes of corresponding element.

       

      Based on range of values background color of cell has to change to different color like red, yellow and green.  And sorting of rows has to be done based on color each time cell color changes in the order of red, yellow followed green.

       

      For example, if row which is in good condition with green color is changes to red, then it has to moved up dynamically above yellow color rows, vice-versa.

       

      Untitled113.png

       

      Is this can be achieved in processbook?  if possible, please help me out.

        • Re: Processbook: Table with dynamic sorting
          Eugene Lee

          Hi Doddabasappa,

           

          I found a threadhere about creating tables in Processbook using VBA. Please have a look at it to see if it helps.

           

          Theoretically speaking, the dynamic part should be possible if you use multistate symbols in Processbook. You can add multistate functionality to shapes like rectangles etc. However, do take note that this will only change the color. Regarding sorting, in my opinion, this is not so trivial to do. You might need to redraw the table multiple times and this might affect performance. Furthermore, to sort based on color, you might need to assign values to the colors in order for the sort to have logic of what is big and small. We can see what others think about this.

          2 of 2 people found this helpful
          • Re: Processbook: Table with dynamic sorting
            Rhys Kirk

            Does it HAVE to live in ProcessBook? This is common way to display this in Excel and host it in Office 365 or equivalent.

            • Re: Processbook: Table with dynamic sorting
              knightk

              I agree with Rhys, wrong tool for the job.... but, I was able to set up something quickly enough which may work as expected, although it is a little slow to update.

               

              1.png

              I've made some elements for testing. Status is an enumeration set (Red/Yellow/Green) and is using a formula to check whether "Value" is > HIHI (red) > HI (yellow) else (Green)

               

              I created the table with all Elements in ascending order. Whats important is that for each Value and the multistate rectangle for the row, they are suffixed with a number starting at 1, this way when I copy and pasted them to create the table, I got a sequential numbering scheme. ie. Rectangle is MS_1, MS_2 etc. Value1 is V1_1, V1_2 etc.

               

              2.png

               

              I then wrote some code which loops through each value and stores it in dictionary along with the number that corresponds with it's original row location. I then create an array list and sort that by the PI value (descending so highest values/red appears on top). Then by iterating through item in the array list, I can get the original row ID from the dictionary by passing the PI value, and I simply change the top property of each tag to equal the top property of the item number (NUM_1, NUM_2 etc) which doesn't change so the location of the tags are changing. There is a bit of lag but it seems to work. To improve this I would maybe have a hidden text field which contains the order in a string, and check this each time so if the order hasn't changed don't bother moving the items. If you wanted to see this in Coresight, you would need a computer to have this PDI open all the time and add in some additional code so that if the order is changed it saves the PDI, a bit of a messy work around but I like that PDI's update dynamically once saved!!

               

              3.png

              See if the attached helps!

              3 of 3 people found this helpful