Get a list of all pages that use a block on live system(without code)

Vote:
 

We changed our Link-Block, it's Reference property is now localizable:

[CultureSpecific]
public virtual Url Reference { get; set; }

Until now these links were defined only in the master, they are not translated everywere. So the customer noticed that it's missing on some pages.

Now he needs a way to identify all pages which use the block and where this property is not yet translated. Is it possible without code?

I have just found out how to determine all pages where this property is already translated, so the opposite. Therefore i needed to go to Admin/Content Types/Link Block/Reference

If i uncheck the "unique value per language" the CMS will list me all pages(with links to them which is quite convenient) where the property is translated, as a warning that this content will be deleted if i proceed. But i don't have an idea how to get all pages where the property is not translated or just all pages that use this block.

#208009
Edited, Oct 10, 2019 16:28
Vote:
 

Hi Tim,

I don't think you can achieve this without the code but you can easily get the block reference and then check whether the block has that particular language(using the ExistingLanguage property of that block) or not.

https://stackoverflow.com/questions/49454291/episerver-how-can-i-find-out-if-a-block-is-being-used-on-any-published-page

#208015
Oct 10, 2019 17:31
Tim Schmelter - Oct 10, 2019 17:38
It doesn't help because we can't use code, it's just missing live content. I don't want to develop a plugin to help the customer to find specific untranslated content. But he needs little help to find it in CMS right now. I'll post an answer to show my workaround.
Vote:
 

I didn't find a way to list missing translations in CMS, even if that would be a great help for CMS editors. I also didn't find a way to list all content that uses a specific block, although this would be an even greater help. The only workaround i've found was to use this query to list the content-id's which used the block:

select c.pkID  from tblContentType ct
inner join tblContent c ON c.fkContentTypeID = ct.pkID
where ct.Name = 'LinkBlock'
order by c.pkID

Now you can use these ID's to find the content in CMS, just append it to the URL, for example(649 is the pkID)

https://Domain/Somename/CMS/#context=epi.cms.contentdata:///649

The query listed 129 contents that used the block, so there is a lot of work for the customer now. Would be great if there was more help from CMS. 

#208017
Edited, Oct 10, 2019 17:50
Vote:
 

Tim,
Please submit a feature request: https://world.episerver.com/forum/developer-forum/Feature-requests/.  If this would help you, it would help other developers too.

#208019
Oct 10, 2019 18:10
Tim Schmelter - Oct 16, 2019 9:37
I did: https://world.episerver.com/forum/developer-forum/Feature-requests/Thread-Container/2019/10/list-all-content-that-use-a-block-in-cms/
Bob Bolt - Oct 16, 2019 18:23
Thanks!
Vote:
 

Sometime I use episerver find index view for these type of queries but mostly it's a lot of work for editors

#208160
Oct 15, 2019 19:25
Vote:
 

Maybe sticking to SQL could be faster? If you run this query for each of your 129 blocks, it should list what language versions that lack your Reference property. Simply update the ContentId on line 2.

DECLARE @ContentId int;
SET @ContentId = 4; --<--< CHANGE THIS

SELECT lb.LanguageID FROM tblLanguageBranch lb, tblContentLanguage cl
WHERE 
	lb.pkID = cl.fkLanguageBranchID 
		AND lb.pkID = cl.fkLanguageBranchID
		AND cl.fkContentID = @ContentId 
		AND lb.pkID NOT IN
		(
			SELECT cp.fkLanguageBranchID FROM tblContentProperty cp, tblPropertyDefinition pd
			WHERE pd.Name = 'Reference' 
				AND pd.pkID = cp.fkPropertyDefinitionID 
				AND fkContentID = @ContentId
		)

The query will first locate all language versions for the specific block, and will then hide those that include your Reference-property.

#208162
Edited, Oct 15, 2019 22:32
Tim Schmelter - Oct 17, 2019 15:14
Thanks for the idea. But it didn't work yet so i have modified it. I will post it
Vote:
 

This sql query seems to be what i'm looking for. It returns the language and the content which is not yet translated. You can make the hard coded values LinkBlock and Reference variables. The pkId can be used to find the content in CMS, therefore just append it to the url(...context=epi.cms.contentdata:///16820):

DECLARE @nameOfBlock VARCHAR(MAX) = 'LinkBlock'
DECLARE @nameOfBlockProperty  VARCHAR(MAX) = 'Reference'
 
SELECT LanguageID, c.pkID
FROM tblLanguageBranch lb
INNER JOIN tblContentLanguage cl ON lb.pkID = cl.fkLanguageBranchID
INNER JOIN tblContent c ON cl.fkContentID = c.pkID
INNER JOIN tblContentType ct ON c.fkContentTypeID = ct.pkID
WHERE ct.Name = @nameOfBlock
AND NOT EXISTS(
    SELECT 1
    FROM tblContentProperty cp
    INNER JOIN tblPropertyDefinition pd ON cp.fkPropertyDefinitionID = pd.pkID
    WHERE pd.Name = @nameOfBlockProperty
    AND pd.fkContentTypeID = ct.pkID
    AND cp.fkLanguageBranchID = lb.pkID
    AND cp.fkContentID = c.pkID
)
ORDER BY pkID, LanguageID
#208210
Edited, Oct 18, 2019 10:45
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.