Recent posts on the forum have enquired about how to send an email to an address drawn from the database based on the selection from a drop down list.

The problem with data controls is that there is only ever one value that can be obtained from the data source for your email template. For drop down lists this value is the one stored in the DataValueField when your drop down is using a <ControlDataSource> or the DataField when not. If your drop down data source contains two values such as ID and Email then you can only put one field in the DataValueField property and it is this field that is available on form submit.

So imagine you have a contact form for a company and this form requires various details from the user. It has a drop down list of countries. When the user chooses a country the form is primed to send an email to the support department for that country. Now imagine also that the email address is required to be hidden from the user. The simplest way I could conceive of making this happen is to use dependent drop down lists. The first lists countries and the second lists an email address looked up for the chosen country. Once this is set and and working correctly the email address can be passed into the <Email></Email> section of the form from the DataValueField property. Then, when the email has been tested and is working, the email drop down can simply be hidden with visible=”false” or disabled with readonly=”true”.

This example is based on the dependent drop down lists example from the help file. It uses the included DotNetNuke table for Countries. However, I have created a second table which maps the country ID to an email address. You can run in the SQL to create the table and test data here.

/****** Object: Table [dbo].[CD_EmailAdresses] Script Date: 11/09/2010 19:14:20 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CD_EmailAdresses]') AND type in (N'U'))
DROP TABLE [dbo].[CD_EmailAdresses]
GO

/****** Object: Table [dbo].[CD_EmailAdresses] Script Date: 11/09/2010 19:14:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CD_EmailAdresses](
[CountryID] [int] NOT NULL
[EmailAddress] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO
delete from CD_EmailAdresses
go

insert into CD_EmailAdresses(countryID,emailaddress)
values (219,'[email protected]')
insert into CD_EmailAdresses(countryid,emailaddress)
values (221,'[email protected]')
insert into CD_EmailAdresses(countryID,emailaddress)
values (205,'[email protected]')
insert into CD_EmailAdresses(countryID,emailaddress)
values (204,'[email protected]')
insert into CD_EmailAdresses(countryID,emailaddress)
values (212,'[email protected]')

Note, I have only added test data for the UK, US, Thailand and Turkey. So you must choose one of those countries from the drop down to get an email address.

The XMod Pro form is fairly simple:

<AddForm>

  <ControlDataSource Id="dsCountries" CommandText="SELECT Text, EntryID FROM  Lists WHERE ListName='Country' ORDER BY Text" />
  
  <ControlDataSource Id="dsEmailAddresses" CommandText="SELECT CountryID, EmailAddress FROM CD_EmailAdresses WHERE countryID=@CountryID">
  	<Parameter Name="CountryID" />
  </ControlDataSource>
  
  	<Label For="ddlCountries" text="Country" />
  	<DropDownList DataField="EntryID" DataType="string" Id="ddlCountries" DataTextField="Text" DataValueField="EntryID" DataSourceID="dsCountries" TargetDataSourceId="dsEmailAddresses">
  		<Parameter Name="CountryID" TargetControlId="ddlEmailAddresses" />
  	</DropdownList>

  	<Label For="ddlEmailAddresses" text="Support Email" /> 
  	<DropDownList DataField="EmailAddress" DataType="string" Id="ddlEmailAddresses" DataTextField="EmailAddress" DataValueField="EmailAddress" DataSourceId="dsEmailAddresses" />
  
  	<AddButton Text="Add" Style="color: #0000ff;" /> 
  
  <Email To="[email protected]" From="[email protected]" Subject="The email address based on your country choice is:" Format="html">
  		Email address: [[EmailAddress]]
  </Email>

</AddForm>

To test this form, just configure an XMod Pro FormView module to use the above form and edit the <Email></Email> section to use your email address or put the one provided by the drop down into the To: section. e.g. To=”[[EmailAddress]]”. Then, when it’s working, you can hide the second drop down.

The only snag I can find with this method is that when you choose the country name, the form refreshes. I don’t think there is any way around this problem but if you can suggest something please let me know.

I hope you find it useful.

Thanks for reading,

Gus