Showing posts with label items. Show all posts
Showing posts with label items. Show all posts

Saturday, March 22, 2025

Using Sitecore Indexes in PowerShell-Driven Multilist Datasources


If you didn't know, you can point a Sitecore field's datasource to a PowerShell script. It's a super clean way to make dynamic picklists, filtering based on the current item, tags, templates, relationships, you name it.

But if you're pulling a large set of items, you really want to use the search index.

That's where things get weird.

The Find-Item command gives you fast results… but they aren't real Sitecore items. They're search result objects; great for speed, not so great for populating a multilist. Sitecore expects actual items, and when it doesn't get them, your field ends up looking empty.

Luckly, you don't have to abandon the approach completely. The fix is actually a pretty straightforward.


Some Context

For context, you can set the datasource for a multilist to be driven via a script in SPE like this:

In the script definition itself, you can write PowerShell to obtain some set of items from the tree. The resulting list is what shows as applicable for selection on the field.

In theory, you should be able to also utilize the Find-Item commandlet to obtain a list of items from the index. In my case was necessary due the performance implications of running a Get-ChildItem against a massive subtree.

First attempt looked something like this:

So far, so good. You get back a list of items. Or do you?


The Catch

The objects in the $list variable returned by Find-Item are not Sitecore items. They're dynamic search result objects that look like items, walk like items, but won't work in your multilist unless they quack like items.

If you try to return them as-is from your script, you'll find that, even if items were found in the index, the multilist fails to render the items for selection.

The fix? Pretty simple actuallu: Transform the search results back into legit Sitecore items.

Put it all together and you're golden

Now your multilist knows what to do. The search is lightning fast thanks to the index, and authors can pick from relevant matches without sifting through the entire tree.


Why This Matters

This pattern shines when you're working with large content trees or complex tagging structures where traditional item traversal would be painfully slow. By using the index, you're offloading the heavy lifting to Solr, gaining serious performance without sacrificing editor experience.

But more importantly, it calls out a subtle, easy-to-miss SPE gotcha: not all objects returned from PowerShell helpers are Sitecore items. If you're using Find-Item, you'll almost always need a second pass to resolve those results into actual items before they'll work in a field context.

Fail to do that, and you might spend hours wondering why your multilist is coming up empty, despite the index finding exactly what you wanted.

Happy datasourcing! 🚀

Monday, August 28, 2017

Sitecore Text Searcher - The Ultimate SQL Script

"Where can I edit this text in Sitecore?"
"Can you find out where this content can be changed in Sitecore? 
"I tried changing this content in Sitecore here, but it's not reflecting on the front-end after I publish." 

I hear that a lot.  

When maintaining multiple Sitecore solutions - knowing every template, rendering, and how the data ties in to what you see on the front-end - is nearly impossible.   Prior to Helix, developers were free to develop using various patterns. Some are built in MVC, others are WebForms.  Some components rely on datasource items, others rely on data from the items template. Regardless of architecture, we need to be able to help Content Authors change content if they can't find what they're looking to change.

There are typically a few options to obtain the information for the Content Author's request:

     - If you have access to the solution - inspect the page elements, grab a common class or id, do a Entire Solution search, find some files and follow the code to determine how it populates the component.

     - Start with the page item in Sitecore and determine whether the content is located on the item's template, or in within a data source item.

Those options are both fine - and eventually effective - but there is a faster way - IF YOU HAVE ACCESS TO SQL (which is normally the case if you have a recent DB locally or access to the DB server).

Simply start a new query in SQL MS on the Sitecore DB you want to search in (Web or Master),  modify the @SEARCHTERM variable (include the percent symbols eg. '%content goes here%'), and execute this handy script:



DECLARE @SITECOREROOTID NVARCHAR(MAX), @FULLPATHSEARCH NVARCHAR(MAX), @SEARCHTERM NVARCHAR(MAX)

SET @SITECOREROOTID = '11111111-1111-1111-1111-111111111111';
SET @FULLPATHSEARCH = 'sitecore/content/%';
SET @SEARCHTERM = '%search term%';

WITH FullPathItems (ID, NAME, ITEMPATH, TEMPLATEID)
    
AS (SELECT ID,
                
NAME,
                
CAST(NAME AS NVARCHAR(MAX)) AS itempath,
                
TEMPLATEID
        
FROM   [dbo].ITEMS
        
WHERE  ID = @SITECOREROOTID
        
UNION ALL
        
SELECT i.ID,
                
i.NAME,
                
CAST(( ITEMPATH + '/' + i.NAME ) AS NVARCHAR(MAX)) AS itempath,
                
i.TEMPLATEID
        
FROM   [dbo].ITEMS i
                
INNER JOIN FullPathItems a
                        
ON i.PARENTID = a.ID)
                        
SELECT fieldsX.ITEMID,
      
fpi.NAME AS ITEMNAME,
      
fieldsX.VALUE,
      
fpi.TEMPLATEID AS TEMPLATEID,
      
templatesX.NAME AS TEMPLATENAME,
      
ITEMPATH,
      
fieldsX.FIELDID,
      
itemsX.NAME AS FIELDNAME,
      
fieldsX.LANGUAGE  AS ITEMLANGUAGE 

       FROM   [dbo].[FIELDS] fieldsX
      
INNER JOIN FullPathItems fpi
              
ON fpi.ID = fieldsX.ITEMID
      
INNER JOIN [dbo].ITEMS itemsX
              
ON itemsX.ID = fieldsX.FIELDID
      
INNER JOIN [dbo].ITEMS templatesX
              
ON templatesX.ID = fpi.TEMPLATEID

WHERE fieldsX.VALUE LIKE @SEARCHTERM 
AND fpi.ITEMPATH LIKE @FULLPATHSEARCH

/*INCLUDE  THE FOLLOWING LINE TO INCLUDE ITEM ID CONDITION */
-- AND fieldsX.ItemId = 'B3F15B8C-BAE5-40F4-A139-BA7B3EC6E1ED'

/*INCLUDE THE FOLLOWING LINE TO INCLUDE ITEM NAME CONDITION */
-- AND fpi.Name LIKE '%My Item%'

/* INCLUDE THE FOLLOWING LINE TO INCLUDE TEMPLATE NAME CONDITION */
--AND templatesX.Name = 'My Template'

/* INCLUDE THE FOLLOWING LINE TO ADD LANGUAGE CONDITION */
-- AND fieldsX.Language = 'en-US'


ORDER  BY fpi.ITEMPATH



The result includes the following columns:

Sometimes, you'll be presented with several instances of a specific keyword, in which case you can uncomment various lines near the bottom of the script to drill down and filter the results even further.

I find myself using this script on a daily basis and hope that sharing this script helps others track down content quickly and effectively, too.