BY ANNE BROCK, RIGHTSTAR
First, I want to define the problem we need to solve.
We had a customer who had a multi-level category tree- more than two levels. Some of her categories were three levels deep, some four, some five. Something like this:
Now of course, this isn’t a problem to set up in Remedyforce; you can have many levels, although we recommend not going too deep.
But the problem came with reporting: The customer wanted to run a report from the top level – in the example above, regardless of whether they picked Level 1, 2, 3 or 4, she wanted to run a report of all “Level 1” tickets. Since the ultimate parent category isn’t stored on the ticket, this took a little bit of work.
Now you can put the whole parent tree on the ticket – In the case of the above example, if I choose Level 4, that gives me a field with this in it:
If I pick Level 3, the field looks like this:
Which is a great start; but HOW do you pull the top category off of that? It’s on the right-hand side of the field – but of course, number of characters may vary. And you can’t just search for “>” because that may be in the string multiple times. Here’s what I did.
Put a special character that the beginning of your top level categories. The sharp-eyed among you will have noticed I’m using an exclamation point.
Put the Parent Tree on to your form. You can show it or not in the Field Sets; but create a field that looks up the full Parent Tree in your Incident form. So go to Setup/Create/Objects/Incident Object; and go to Custom Fields and Relationships, and create a new field. I called mine AB_Category_Tree. Create it as a formula field, and in the Formula, enter:
I put it on my Remedyforce Incident form, so I could see the results:
Create a field that will extract everything after the “!” and put it into a field that can be used for reporting. The important thing here is that IF someone chooses the very top level category – i.e. !Level 1 – the field we created in Step 2 will be blank, so we need to account for that.
So again, go to Setup/Create/Object/Incident and go to Custom Fields and Relationships. Create a formula field; I called my field Parent Category. This time, the Formula is:
IF( ISBLANK(AB_Category_Tree__c), BMCServiceDesk__Category_ID__c , RIGHT(AB_Category_Tree__c, LEN(AB_Category_Tree__c)- FIND(“!”, AB_Category_Tree__c) + 1) )
To break that down:
|IF( ISBLANK(AB_Category_Tree__c), BMCServiceDesk__Category_ID__c ,||This is for those cases where someone picks the top level category; since the tree will be blank, we’ll just assign the category|
|RIGHT(AB_Category_Tree__c||We want to start from the right of the parent tree field, not the left and take a certain number of characters|
|FIND(“!”, AB_Category_Tree__c)||This gives us the place in the string where the special character is – I used !, but replace that with your special character|
|LEN(AB_Category_Tree__c)- FIND(“!”, AB_Category_Tree__c) + 1)||If I take the full length of the string (let’s say 25) and remove where I found the special character (let’s say position 17) I’ll have the number of characters remaining after the special character – 8. I add 1 back on to get the special character. (I’ll need this to match what happens if I just select the top level category.)|
|RIGHT(AB_Category_Tree__c, LEN(AB_Category_Tree__c)- FIND(“!”, AB_Category_Tree__c) + 1)||Using the example right above, if the string is 25 characters long and the special character is in position 17, I am taking the right-most 9 characters to fill in the Parent Category field.|
Create a report showing Parent Category and Category to see the results! No matter which category they pick under !Level 1, I can still report on all tickets that came in for that parent category.
And here’s that same report in Lightning:
Let us know if you have any questions or comments!