Forms Table dropdown option selected, to run separate query

Tom Wisneski asked on May 29, 2020 18:31

Question. Using a Kentico forms table which has a dropdownlist with options. Depending on a given option selected, we would like to run a separate query. How would this be done? Some background: using a form table for the built email notification functionality. We have another table, a Custom Table with values that are used (Model IDs, Model Names, Liters, Voltage, etc.). Depending on what option is selected in the Kentico form, then we would query the Custom table for those values. Hope the question and info makes sense. Just trying to figure the easiest way to make this happen. Thanks.

Correct Answer

Dmitry Bastron answered on May 29, 2020 23:27

Tom, this can be done like this:

First form field, let's call it Frequency, is configured as:
- Data type: Text
- Form control: Drop-down list
- Data source: list of options
  - 50Hz
  - 60Hz
- Has depending fields: true
Second form field, GeneratorModel is configured as:
- Data type: Text
- Form control: Drop-down list
- Data source: SQL Query (will be below)
- Depends on another field: true

Query:

select GeneratorCode,  --this is drop-down item value
       GeneratorName   --this is drop-down item display name
from customtable_Generators
where GeneratorFrequency = '{% Fields["Frequency"].Value #%}'

When you configure form fields, you can use this macro to get the value of another field on the same form: {% Fields["Frequency"].Value #%}

0 votesVote for this answer Unmark Correct answer

Recent Answers

Dmitry Bastron answered on May 29, 2020 19:00

Hi Tom,

Fortunately Forms autoresponder and notification emails support Kentico macro methods. You can read more about using it here. So you can try using this macro in your email:

{% GlobalObjects.CustomTables["customtable.Test"].Items.Where("YourCustomTableFieldName = '" + YourFormFieldName + "'").FirstItem.YourCustomTableOtherFieldName %}

Also have a look at macro methods syntax as it can be useful.