Solved

Searching email address via Cases

  • 14 November 2023
  • 8 replies
  • 62 views

Userlevel 2
Badge

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)

 

icon

Best answer by Robert Sternberg 15 November 2023, 16:09

View original

8 replies

Badge +11

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.

Userlevel 5
Badge

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.

 

Userlevel 2
Badge

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?

Badge +11

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.

Userlevel 5
Badge

@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.

Userlevel 7
Badge +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;

 

Userlevel 2
Badge

Thanks @Robert Sternberg !

Userlevel 7
Badge +8

Thanks @Robert Sternberg !

Happy to help!

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved