How to use the parent field value in the sql query data source of dependent field

Just a quick statement, with a Like query you usually is something like '%your string%'. You don't have the percent signs in there.

You mean for the NodeAliasPath? The /PDF/% isn't an object or a variable; it's the actual path for the pdf directory. It is working properly.

If you mean the fields.Campus.Value, I've tried formatting it in several different ways, including just '%fields.Campus.Value%' and I get a failed result. The reason I have it in the code above the way it is now is because I confirmed (via an error message) that it was pulling the value. For example, if the campus chosen was "Los Angeles" then {%fields.Campus.Value|(identity)GlobalAdministrator%} became 'Los Angeles' in the result. Mechanically it looks correct but the result still isn't working. I get nothing populating the Program field. The sql query seems to be ignoring it.

I usually prefer charindex(search_criteria, name) > 0 i.e. charindex('{%fields.Campus.Value|(identity)GlobalAdministrator%}', DocumentPageKeyWords ) > 0. charindex has s slightly better performance, but Brenden's like will work as well.

Thank you all for the responses.

Brenden,

Using DocumentPageKeyWords like '%{%fields.Campus.Value|(identity)GlobalAdministrator%}', DocumentPageKeyWords ) > 0 did not work for me. The Program dropdown result is empty.

The DocumentPageKeyWords field is a string field. There are no restrictions as to how the content is added, it's free text, so you'd need to ensure the format of that string is consistent. Then you'd need a complex where statement like so (assuming they are comma separated values:

DocumentPageKeyWords LIKE '%{%fields.Campus.Value|(identity)GlobalAdministrator%},' OR -- catch value in between commas

Brenden,

You're right. I hadn't considered that. DocumentPageKeyWords isn't well suited for what I need. Do you think it's possible for me to use categories instead?

Categories is a much better option. You can get a dynamic list of categories to choose from and use either their category code name or ID to store and look up. I'd suggest code name.

So having set up categories matching the Campus field values, I now have questions about how to use that for the Program field query. Assigning categories to the pdf documents was no problem, but there's no column under View_CMS_Tree_Joined to pull for category name. Some help documentation I've been reading suggests that I have to add a custom query to the cms.file page type if I want to use it in form queries. Is that necessary?

After a lot of tooling around, I'm still having issues using categories. I think I'll have to match against category display name instead of code name because the format can match the Campus field I'm already using; code name doesn't allow spaces.

Unfortunately, I'm having a hard time finding information about how to use categories in a query. My latest attempts at the code is below.

select '' as name1, 'Select Program' as name
union all
Select RTRIM(LTRIM(DocumentTags)) AS name1,RTRIM(LTRIM(DocumentName)) AS name from dbo.View_CMS_Tree_Joined
WHERE NodeAliasPath like '/PDF/%' AND DocumentTagGroupID = 9 AND SiteObjects.Categories.DisplayNames = '%{%fields.Campus.Value|(identity)GlobalAdministrator%}%'
order by name1

Of course, this gives me the error "The multi-part identifier 'SiteObjects.Categories.DisplayNames' could not be bound." I was expecting this, but because I've never worked with categories before I don't know how to resolve it. Any suggestions?

I think you have a mishmash of script SQL and Macro. You probably looking for this:

SELECT'' as name1, 'Select Program' as name
UNION ALL
SELECT  RTRIM(LTRIM(DocumentTags)) AS name1,RTRIM(LTRIM(DocumentName)) AS name
FROM cms_category c (NOLOCK)
JOIN cms_documentcategory dc(NOLOCK) on c.categoryid =  dc.categoryid
JOIN View_CMS_Tree_Joined vw on dc.DocumentID = vw.DocumentID
WHERE CategoryDisplayName like '%{%fields.Campus.Value|(identity)GlobalAdministrator%}%' 

Thank you very much, Peter, for the suggestion. Unfortunately, your solution seems to share the same problem other solutions have had: pulling the Campus field value isn't being taken into account when the query is resolved. Instead of getting programs for what was selected in the Campus field, I'm getting everything that has a category assigned to it.

I know the rest of the query works fine. For example, if I replace what you've contributed with the following...

ELECT'' as name1, 'Select Program' as name
UNION ALL
SELECT  RTRIM(LTRIM(DocumentTags)) AS name1,RTRIM(LTRIM(DocumentName)) AS name
FROM cms_category c (NOLOCK)
JOIN cms_documentcategory dc(NOLOCK) on c.categoryid =  dc.categoryid
JOIN View_CMS_Tree_Joined vw on dc.DocumentID = vw.DocumentID
WHERE CategoryDisplayName like 'Chicago'

... then everything works perfectly. However, I know through various tests that '%{%fields.Campus.Value|(identity)GlobalAdministrator%}%' is pulling the correct information from the Campus dropdown - the query just doesn't seem to care. After all of these attempts, I'm back to my original way of thinking: that the Campus value is being pulled too late in the process for the sql query to take it into account. I don't know what else would explain it, and I can't seem to find a solution for it.