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

Chris Wunder asked on March 28, 2018 03:59

Okay, here's the setup: I am building a form to retrieve a list of pdf documents from a location based on the Campus and Program selected.

I have a form with two dropdown fields: Campus and Program. The data source for both fields are sql queries. The visibility of Program is dependent on Campus, so when a campus is selected the Program field appears. Then, once a program is selected, the form can be submitted and a webpart is displayed with links to the appropriate pdfs.

The problem I'm having involves parameters for the list of programs in the Program dropdown field. It has three requirements: document location (the folder where all the pdfs are located), the DocumentTagGroupID and the campus (as selected in the parent field). The location and DocumentTagGroupID work perfectly, but I need to refine the list by the campus selected and it's not working.

Below is the code I currently have.

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 DocumentPageKeyWords like '{%fields.Campus.Value#%}'
order by name1

When I use this as the data source, a blank Program dropdown appears. I've confirmed that the {%fields.Campus.Value#%} is pulling the correct value from the Campus dropdown. My theory is that the Campus value is being pulled too late in the process for the sql query to take it into account. However, I don't understand why that's the case when this field is dependent on the Campus field in the first place - shouldn't the Campus field value already be available before the Program field is even active?

Does anybody have an idea as to what I am forgetting here?

Correct Answer

Brenden Kehren answered on March 28, 2018 05:41

The format of your LIKE condition should be:

DocumentPageKeyWords like '%{%fields.Campus.Value%}%'

1 votesVote for this answer Unmark Correct answer

Recent Answers


Brenden Kehren answered on March 28, 2018 04:15

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

0 votesVote for this answer Mark as a Correct answer

Chris Wunder answered on March 28, 2018 04:51

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#%} 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.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on March 28, 2018 12:09

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

0 votesVote for this answer Mark as a Correct answer

Chris Wunder answered on March 28, 2018 20:06

Thank you all for the responses.

Brenden,

Using DocumentPageKeyWords like '%{%fields.Campus.Value%}%' I now get results in the Program dropdown, but it's listing every document that has keywords, not just the selected keyword from the Campus field. i.e. It's behaving like DocumentPageKeyWords != ''


Peter,

charindex('{%fields.Campus.Value#%}', DocumentPageKeyWords ) > 0 did not work for me. The Program dropdown result is empty.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on March 28, 2018 20:16

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%}%' OR -- catch any value (prob not a good idea)
DocumentPageKeyWords LIKE '{%fields.Campus.Value%},%' OR -- catch a value at the beginning of a string
DocumentPageKeyWords LIKE ',{%fields.Campus.Value}%' OR -- catch a value at the end of a string
DocumentPageKeyWords LIKE ',{%fields.Campus.Value%},' OR -- catch value in between commas

1 votesVote for this answer Mark as a Correct answer

Chris Wunder answered on March 29, 2018 02:08

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?

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on March 29, 2018 02:38

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.

0 votesVote for this answer Mark as a Correct answer

Chris Wunder answered on March 29, 2018 19:46

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?

0 votesVote for this answer Mark as a Correct answer

Chris Wunder answered on March 30, 2018 21:17 (last edited on April 2, 2018 19:58)

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#%}%'
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?

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on April 2, 2018 23:05 (last edited on April 2, 2018 23:06)

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#%}%' 
0 votesVote for this answer Mark as a Correct answer

Chris Wunder answered on April 4, 2018 00:01

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#%}%' 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.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.