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, and 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, the 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.
Step 1
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.
Step 2
Put the Parent Tree on 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, 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:
BMCServiceDesk__FKCategory__r.BMCServiceDesk__parentTree__c
I put it on my Remedyforce Incident form, so I could see the results:
Step 3
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 a 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. |
Step 4
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!