0

Display cases as per user

sohail sarwar

December 13, 2018 02:01

None

Follow

Hi All, I am create a table where I can see the following based on the case owner from a report in SFDC.  Basically I want it to show the Case Owner, and display all the tickets  and case origin.

Support Engineer	P1 & Phone Queue Ticket	Case Origin

Person1                        122345                                          portal
Person2                        128345                                          phone
Person1                        123451                                          web

 

Sales force report has Case Owner, Case Number, Case Origin. 

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Janice Janczyn December 13, 2018 14:42

  Hi Sohail,

  I'm not sure I understand your question. Have you already created your Salesforce datasource (based on the Salesforce report listing Case Owner, Number and Origin) in Klipfolio? If so, you can select this datasource from your Data Sources library and click New Klip in the upper right and pull in a table component. If this is your first klip, please see Start Building Klips section of our Knowledge Base. If you haven't yet created your Salesforce datasource in Klipfolio, refer to How to connect Klipfolio and Salesforce.

   

  Thank you,
               Janice

• 1
  
  sohail sarwar December 15, 2018 15:12

  Hi Janice and All,

  Let me clarify.  I have my first report in Salesforce that has the following columns, "Full Name", "User Manager", "Username", "First Name", "Last Name".  Now I have a second report in Salesforce, where I have "Case Owner", "Priority of Ticket", "Case Numbers", "Case Origin"

  I would like to do the following:

  1. Klipfolio to look up the employee "Full Name" from report 1, compare it to the "Case Owner" of report two and enter it in Klipfolio Colum1 as Employee Name

  2. Klipfolio to look up the "Full Name" from report 1, compare it to the "Case Owner" of report two and enter the corresponding "User Manager" in column 2 as Manager

  3. Last I would want lookup the "Full Name from report 1, compare it to the "Case Owner" of report two and list all the "Case Numbers" that "Case Owner" has in Column 3. Basically if "Case Owner" has 5 tickets, then it should list all 5 tickets.

   

   

• 0
  
  Janice Janczyn December 17, 2018 18:30

  Hi Sohail.

  Use the LOOKUP function in each column to look up the employee Full Name from report 1 and find a match with Case Owner in report 2 and return the corresponding data. Your first 2 columns will be very straightforward as I assume Employee Name and User Manager are the same per Case Owner. Note that LOOKUP returns the first match it finds.

       LOOKUP( employeeFullName, CaseOwner, EmployeeName)
       LOOKUP( employeeFullName, CaseOwner, UserManager)

  For the 3rd column where you want to show a  list of tickets, you'll need to group the Case Owners and the Case Numbers by Case Owners because you don't want just the first match returned, you want all matches combined somehow. In this scenario, use Join to combine the tickets into 1 comma-separated item. Refer to the Use LOOKUP to group values together section in the LOOKUP function article. 

       LOOKUP( employeeFullName, GROUP(CaseOwner), GROUPBY(CaseOwner, CaseNumbers, Join) )

   

  Thank you,
                Janice

• 0
  
  sohail sarwar December 17, 2018 19:34

  Janice,

  Thank you for your help.  I think I have it almost.  I used the following:

  LOOKUP(slice(d20b7c1ff4c9dfb1a90db48efc02aba6@A:A;),GROUP(2bfff9200ecdd245b69d453d411b7d84@C:C;),GROUPBY(slice(2bfff9200ecdd245b69d453d411b7d84@C:C;), slice(2bfff9200ecdd245b69d453d411b7d84@D:D;),"join(values)"))

  How do I get it to spit out in a list, so instead of seeing it in one line as "00538231,00538099,00537943,00535485,00537359,00535485" I see it as

  00538231,
  00538099,
  00537943,
  00535485,
  00537359,
  00535485 

   

  Thank you again.

• 0
  
  Janice Janczyn December 18, 2018 13:31

  Hi Sohail,

  First I want to point out that in the LOOKUP function, the 2nd & 3rd parameters must align exactly. This means they must return the same number of items in the same order. If you evaluate the 2nd & 3rd parameters in your LOOKUP (the GROUP & GROUPBY), you'll see they do not align and this is because you've wrapped the SLICE function around around the 2 parameters in your GROUPBY, but not in the GROUP. You do not need the SLICE, your LOOKUP should be something like:
  
       LOOKUP( SLICE( @A:A ),
                        GROUP( @C:C ),
                        GROUPBY( @C:C, @D:D ), "join(values)" ) )

  If you list your ticket numbers vertically, how do you want them aligned with the Employee Name & User Manager columns? For example

       empName1        userMgr1     ticket1
       empName1        userMgr1     ticket2

  In the above case, you could simply have each column point to the relevant columns from datasource2 and use hidden data to check that each employee name (column C in datasource2) is contained in datasource1 (for the following example, assume this is column A).

       IN( @C:C, @A:A )

  Then set the filter on the hidden data to include only True results.

   

  Thank you,
              Janice

   

   

  Thanks,
           Janice