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

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. 

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.

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

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.

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