Form field data source based on another field

Gerard Young asked on July 18, 2019 13:00

I have a form with 2 dropdown fields. The value of the first needs to be taken into account when populating the data source of the second. I have the dependant field checkboxes correctly set.

I've tried SQL and macro solutions for this but the value is ignored in the macro version and nothing is returned in the sql version

Macro:

GlobalObjects.Journals.Filter(JournalGuid != Fields["JournalSelection"].Value)#
GlobalObjects.Journals.Filter(JournalGuid != JournalSelection.Value)#

I have tried both of these but all values are returned including the one selected in "JournalSelection".

SQL:

Select JournalGuid, JournalName from RSS_Journal 
WHERE JournalGuid != {%Fields["JournalSelection"].Value#%}
ORDER BY JournalName

Select JournalGuid, JournalName from RSS_Journal 
WHERE JournalGuid != {%JournalSelection.Value#%}
ORDER BY JournalName

Nothing returns for wither of these.

Is there a way to get this value to work in the data source?

Recent Answers

Dragoljub Ilic answered on July 18, 2019 13:30 (last edited on July 18, 2019 13:31)

Hi Gerard,

Your code for macros looks okay. This peace of code should work for you: GlobalObjects.Journals.Filter(JournalGuid != JournalSelection.Value)#

Thing that could be cause of the issue can be in JournalSelection field. Can you check if that form field (if it's dropdown) has value: {% JournalGuid %} in Value column property? It could be that field is not saving correct value and because of that it's affect on second dropdown filter. You can easily check it on Recorded data tab (forms application) after you successfully submit form.

Best regards, Dragoljub

0 votesVote for this answer Mark as a Correct answer

Gerard Young answered on July 18, 2019 14:24

Hi, thanks for the answer.

The JournalSelection.Value definitely holds the correct data. I've tested this using the visibility condition. The Value field is set on the first drop down so I'm still in the dark here.

0 votesVote for this answer Mark as a Correct answer

Dragoljub Ilic answered on July 18, 2019 15:03

Hi,

I just tested the same by using 'GlobalObjects.Categories' and it's working as expected in Kentico 11. Which version of Kentico you are using? You said that dependent fields are checked correctly, did you configured them in the same way like here?

0 votesVote for this answer Mark as a Correct answer

Gerard Young answered on July 18, 2019 15:15

v12.0.7

Yes first dropdown has depending fields

Second depends on

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on July 18, 2019 15:40 (last edited on July 18, 2019 15:54)

I did a test with a country and state as example, just the field name should work fine:

you should use :

Select JournalGuid, JournalName from RSS_Journal
 WHERE JournalGuid != {%JournalSelection%} ORDER BY JournalName

if JournalSelection is the field name and it is int. May be it is string and you need quotes?

0 votesVote for this answer Mark as a Correct answer

Gerard Young answered on July 19, 2019 10:04

At this point I'm not really sure what's happening. I've fired up sql profiler and it's never sending the value in the sql. I've also tried using a macro to populate a "List of options" and it's never seeing the value. Yet I'm using the value to show the visibility. I'm very confused.

0 votesVote for this answer Mark as a Correct answer

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