Skip to main content
Solved

Searching email address via Cases


Forum|alt.badge.img

Hello, not sure if this a setup or a feature to be implemented.

Is there a way to search for email addresses through cases screen?? (CR3060PL)

 

Best answer by Robert Sternberg

Another option would be to rebuild the original GI as a SQL View, then use something like this to aggregate the contact emails into a single searchable field using STRING_AGG()

 

Here is an example of how to use STRING_AGG, in this example I use the function to list all display names in a single, searchable, and comma separated column.

 

SELECT 
	CompanyID,
    ProjectID,
    STRING_AGG(DisplayName, ', ') WITHIN GROUP (ORDER BY MostRecentInteractionDate DESC) AS OwnersByRecency
FROM 
    (SELECT DISTINCT
		pmta.CompanyID,
         pmta.ProjectID, 
         c.DisplayName,
         MAX(cra.StartDate) as MostRecentInteractionDate
     FROM 
         CRActivity cra
     LEFT JOIN 
         PMTimeActivity pmta ON cra.NoteID = pmta.RefNoteID AND cra.CompanyID = pmta.CompanyID
     LEFT JOIN 
         Contact c ON cra.OwnerID = c.ContactID AND cra.CompanyID = c.CompanyID
     WHERE 
         pmta.ProjectID IS NOT NULL AND
         pmta.ProjectID <> 0
     GROUP BY
		 pmta.CompanyID,
         pmta.ProjectID,
         c.DisplayName) AS Subquery
GROUP BY 
	CompanyID,
	ProjectID;

 

View original
Did this topic help you find an answer to your question?

8 replies

darylbowman
Captain II
Forum|alt.badge.img+13

Are you wanting to search for an email address tied to the Case’s contact?

 

The Generic Inquiry will need to be modified, but yes, that is possible.


Connor Morse
Jr Varsity III
Forum|alt.badge.img
  • Jr Varsity III
  • 50 replies
  • November 14, 2023

Hi @ikarisg ,

The Contact Email field should already by added as a result grid to the Cases (CR3060PL), but it may be hidden in the column configurator. 

 

By default, it appears this field is turned off for Quick Search functionality. To turn it on, edit the generic inquiry, locate the field on the results grid, and toggle on the “Use in Quick Search” button (this column also may be hidden in the column configurator).

 

Now when you search on the GI, the email from the contact will be considered. See the steps below and hope this helps!

 

Make Contact Email a visible column and select Edit Generic Inquiry
Find the Contact Email field on the Results Grid and toggle on the Use in Quick Search function.

 


Forum|alt.badge.img
  • Author
  • 14 replies
  • November 15, 2023

Hi @darylbowman more like activities, or is the only way is to search via “All Emails” screen?

Hi @cmorse26 thank you so much for the guidance with screenshots! Really appreciate it. What about activities within the case, let’s say if business account/contact is not yet selected?


darylbowman
Captain II
Forum|alt.badge.img+13

You could join the Activities table, but each Activity would display as its own row, meaning there would be a lot of duplicate Case info. Depending upon your needs, perhaps this is acceptable.


Connor Morse
Jr Varsity III
Forum|alt.badge.img
  • Jr Varsity III
  • 50 replies
  • November 15, 2023

@ikarisg  - I agree with @darylbowman above. You can bring in the table, just will get a line for each Activity so Cases may have multiple lines.


Robert Sternberg
Captain II
Forum|alt.badge.img+8

Another option would be to rebuild the original GI as a SQL View, then use something like this to aggregate the contact emails into a single searchable field using STRING_AGG()

 

Here is an example of how to use STRING_AGG, in this example I use the function to list all display names in a single, searchable, and comma separated column.

 

SELECT 
	CompanyID,
    ProjectID,
    STRING_AGG(DisplayName, ', ') WITHIN GROUP (ORDER BY MostRecentInteractionDate DESC) AS OwnersByRecency
FROM 
    (SELECT DISTINCT
		pmta.CompanyID,
         pmta.ProjectID, 
         c.DisplayName,
         MAX(cra.StartDate) as MostRecentInteractionDate
     FROM 
         CRActivity cra
     LEFT JOIN 
         PMTimeActivity pmta ON cra.NoteID = pmta.RefNoteID AND cra.CompanyID = pmta.CompanyID
     LEFT JOIN 
         Contact c ON cra.OwnerID = c.ContactID AND cra.CompanyID = c.CompanyID
     WHERE 
         pmta.ProjectID IS NOT NULL AND
         pmta.ProjectID <> 0
     GROUP BY
		 pmta.CompanyID,
         pmta.ProjectID,
         c.DisplayName) AS Subquery
GROUP BY 
	CompanyID,
	ProjectID;

 


Forum|alt.badge.img
  • Author
  • 14 replies
  • November 20, 2023

Robert Sternberg
Captain II
Forum|alt.badge.img+8
ikarisg wrote:

Thanks @Robert Sternberg !

Happy to help!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings