Anyone know of a Report Tag or Report EXP to get Custom Product Tag tagValue using tagName and productId ??
Here is a way to do it in SQL 2016, but thought there might be a simpler method …
SELECT
p.[Id]
,p.[GroupCode]
,p.[Name]
,p.[Tag]
,p.[CustomTags]
,ct.[tagName]
,ct.[tagValue]
FROM [MenuItems] p
CROSS APPLY OPENJSON(p.[CustomTags]) WITH (tagName varchar(255) '$.TN',tagValue varchar(2048) '$.TV') ct
where 1=1
and ct.[tagName]='productType'
--and p.[Id]=46
A Function wrapper …
function getCustomProductTagValue(productId,tagName) {
var qry = "SELECT ct.[tagValue] FROM [MenuItems] p CROSS APPLY OPENJSON(p.[CustomTags]) WITH (tagName varchar(255) '$.TN',tagValue varchar(2048) '$.TV') ct where 1=1 and ct.[tagName]='"+tagName+"' and p.[Id]='"+productId+"'";
var res = sql.Query(qry).First;
return res;
}