Hey guys,
Is there any ways to share the search fields you created in “Retrieve” tab and made it available to other users, so they can use the same search columns??
Thanks…
Not that I have ever found, but you can send in a betterizer.
I don’t think there is a way in the UI, but if you are daring enough, there may be a way via the SavedSearch Table - but you’d have to recreate the rows with new ID GUID’s and find the UserID GUID as well… but this is at your own risk of course.
I would also suggest to submit it via the Betterizer and in the “Epicor Ideas”. Actually all of us should submit the betterizer requesting the Saved Searches have the option of being global, or to be copied to a group of users.
EDIT - I added a Betterizer, here’s the text for you to use if you want.
I’m requesting the Saved Searches have the option of being global, or to be copied to a group of users so that an administrator can create ‘canned’ searches available to others in the company without having to recreate it from scratch.
2 Likes
As @MikeGross mentioned, if you are daring and assume the risks, you can copy a Saved Search from one user to another via SQL scripts against the SQL Server database.
I have done this for the past 2 years against my on premises ECM database. Of course, to do this, you will need read / write access to the SQL Server database tables. If you have are a SQL novice, do not do this on your own!
I should probably have written a stored procedure that takes in such inputs as Name of Source Saved Search to copy from, Source ECM User whose saved search you’re copying from, and Target ECM User whom you’re copying the Saved Search to. But I never got around to creating such a stored proc.
Here are the SQL scripts I used to copy a Saved Search from ECM user sam [Source] to another user tammy [Target] – Remember: Use at your own risk!:
---- STEP 1-A: IDENTIFY THE ID OF THE SOURCE USER WHOSE SAVED SEARCH TO COPY FROM
SELECT u.Username AS Username, u.Id, u.UserPrincipalName, u.NetBiosName, u.*
FROM [Astria_Host].[dbo].[User] u
WHERE u.Username NOT LIKE '%internal'
AND LOWER(u.Username) LIKE '%sam%'
ORDER BY u.Username;
/* For this example, the Id of user 'sam' = '7ECE299E-8785-EA11-B7F2-00155D024D2F' */
--- STEP 1-B: IDENTIFY THE SOURCE SAVED SEARCH ID TO COPY FROM
SELECT ss.Id, ss.*
FROM [AAAA0001].dbo.SavedSearch ss
WHERE ss.UserId = '7ECE299E-8785-EA11-B7F2-00155D024D2F' /* Id of Source User */
ORDER BY ss.Name;
/* For this example, I've looked at the result set and selected a saved search with Name =
'My AP Invoices' and Saved Search Id = '0E1E94F6-EA7F-ED11-B84D-00155D656B1A' */
------------------------------------------------------------------
--- Note: Steps 1-A & 1-B could be combined through this script:
SELECT u.Username, ss.UserId, ss.Id AS SavedSearchId, ss.*
FROM [AAAA0001].dbo.SavedSearch ss
LEFT OUTER JOIN [Astria_Host].[dbo].[User] u ON u.Id = ss.UserId
WHERE LOWER(u.Username) LIKE '%sam%'
ORDER BY u.Username, ss.Name;
------------------------------------------------------------------
---- STEP 1-C: IDENTIFY THE ID OF THE TARGET USER TO COPY THE SAVED SEARCH TO
SELECT u.Username AS Username, u.Id, u.UserPrincipalName, u.NetBiosName, u.*
FROM [Astria_Host].[dbo].[User] u
WHERE u.Username NOT LIKE '%internal'
AND LOWER(u.Username) LIKE '%tammy%'
ORDER BY u.Username;
/* For this example, the Id of user 'tammy' = '257C4D4D-8A7D-ED11-B84C-00155D656B1A' */
---- STEP 2-A: INSERT THE TARGET SAVED SEARCH RECORD
---- Note: For the value after NEWID, paste in Id of the Target user 'tammy'.
---- Note: For the WHERE condition, paste in Id of the Source saved search 'My AP Invoices'.
INSERT INTO [AAAA0001].dbo.SavedSearch ([Id],[UserId],[Name],[IncludeFolders],[IncludeInboxes],[IncludeDocuments],[IncludePackages],[ContentTypeId],[InboxId],[FolderId],[MaxRows],[TextCriteria],[SortBy],[SortOrder],[PredefinedSearch],[Columns])
SELECT NEWID(),'257C4D4D-8A7D-ED11-B84C-00155D656B1A',
---- Id of Target User
ss.Name, ss.IncludeFolders, ss.IncludeInboxes, ss.IncludeDocuments, ss.IncludePackages,
ss.ContentTypeId, ss.InboxId, ss.FolderId, ss.MaxRows, ss.TextCriteria, ss.SortBy, ss.SortOrder,
ss.PredefinedSearch, ss.Columns
FROM [AAAA0001].dbo.SavedSearch ss
WHERE ss.Id = '0E1E94F6-EA7F-ED11-B84D-00155D656B1A'; /* Id of Source Saved Search */
---- STEP 2-B: SPOTCHECK THE NEWLY-INSERTED SAVED SEARCH RECORD & OBTAIN ITS ID VALUE
SELECT ss.Id, ss.UserId,
(SELECT u.Username FROM [Astria_Host].[dbo].[User] u WHERE u.Id = ss.UserId) AS Username,
ss.Name, ss.*
FROM [AAAA0001].dbo.SavedSearch ss
WHERE ss.UserId = '257C4D4D-8A7D-ED11-B84C-00155D656B1A' /* Id of Target User */
AND ss.Name LIKE '%My AP Invoices%' /* Saved Search Name */
ORDER BY ss.Name;
/* In this example, the new Target Saved Search for tammy has a saved search Id = '5551033E-52CC-4808-8376-E4C8C83297E2'. */
---- STEP 3-A: INSERT THE SAVED SEARCH FIELD CRITERIA RECORD(S)
---- Note: For the value after NEWID, paste in the Id of the newly-created Target saved search 'My AP Invoices' for user 'tammy'.
---- Note: For the WHERE condition, paste in Id of the Source saved search 'My AP Invoices' for user 'sam'.
---- Note: This may insert multiple records. It should insert a record for each criteria on the Source saved search.
---- In this example, the Source saved search has 3 Criteria conditions (rows), so the script should insert 3 new records for the Target saved search.
INSERT INTO [AAAA0001].dbo.SavedSearchFieldSearchCriteria ([Id], [SavedSearchId], [DatabaseField], [DatabaseFieldOperator], [DatabaseFieldValue], [Type], [Concatenation], [GroupConcatenation], [GroupId], [CultureCode], [SearchOperator])
SELECT NEWID(),'5551033E-52CC-4808-8376-E4C8C83297E2', /* Id of Target Saved Search */
fsc.DatabaseField, fsc.DatabaseFieldOperator, fsc.DatabaseFieldValue, fsc.Type,
fsc.Concatenation, fsc.GroupConcatenation, fsc.GroupId, fsc.CultureCode, fsc.SearchOperator
FROM [AAAA0001].dbo.SavedSearchFieldSearchCriteria fsc
WHERE fsc.SavedSearchId = '0E1E94F6-EA7F-ED11-B84D-00155D656B1A'; /* Id of Source Saved Search */
--- STEP 3-B: SPOTCHECK THE NEWLY-INSERTED SAVED SEARCH CRITERIA RECORD(S)
--- In this example, 3 Criteria records were returned as expected for the Target saved search.
SELECT * FROM [AAAA0001].dbo.SavedSearchFieldSearchCriteria
WHERE SavedSearchId = '5551033E-52CC-4808-8376-E4C8C83297E2'; /* Id of Target Saved Search */
Version Note: I most recently used these scripts against an on premises instance of ECM version 22.1.
2 Likes
Thanks for sharing!!
Thanks for sharing this, will keep that in mind… 
Thanks!!