Dynamically Populate Simple Data Lists

The Individual, Spouse, Business Add Form is an Add Data Form that utilizes a UI model. This data form adds an individual constituent into the database. Within this data form, you will see a Country form field (Form Field ID: ADDRESS_COUNTRYID) that is based on a simple data list named Country List (see below). You will also see State form field (Form Field ID: ADDRESS_STATEID) that is based on a simple data list named State Abbreviation List (see below). Note the parameters in green within the State Abbreviation List that filter the contents of the simple data list based on the country that is selected.

COUNTRY LIST (SIMPLE DATA LIST)

<?xml version="1.0" encoding="utf-16"?> 
<SimpleDataListSpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ID="c9649672-353d-42e8-8c25-4d34bbabfbba" Name="Country List" Description="Returns a list of all countries.  Optionally can exclude inactive and/or return abbreviations." Author="Blackbaud Product Development" TranslationFunctionID="2035aec3-daa1-4ac6-b3e9-87eacea0edbe" NoSecurityRequired="true" xmlns="bb_appfx_simpledatalist"> 
  <DependencyList xmlns="bb_appfx_commontypes"> 
    <Dependency CatalogAssembly="Blackbaud.AppFx.Platform.Catalog.dll" CatalogItem="Blackbaud.AppFx.Platform.Catalog.UFN_COUNTRY_GETDESCRIPTION.xml" /> 
  </DependencyList> 
  <SPSimpleList SPName="USP_SIMPLEDATALIST_COUNTRY"> 
    <CreateProcedureSQL xmlns="bb_appfx_commontypes"> 
            
create procedure dbo.USP_SIMPLEDATALIST_COUNTRY(@INCLUDEINACTIVE bit = 0) AS 
    if dbo.UFN_GETLISTSORTMETHOD('COUNTRY') = 0 
        begin 
            if @INCLUDEINACTIVE = 0 
                select ID as VALUE, DESCRIPTION as LABEL from dbo.COUNTRY where ACTIVE = 1 order by DESCRIPTION 
            else 
                select ID as VALUE, DESCRIPTION as LABEL from dbo.COUNTRY order by DESCRIPTION 
        end 
    else 
        begin 
            if @INCLUDEINACTIVE = 0 
                select ID as VALUE, DESCRIPTION as LABEL from dbo.COUNTRY where ACTIVE = 1 order by SEQUENCE, DESCRIPTION 
            else 
                select ID as VALUE, DESCRIPTION as LABEL from dbo.COUNTRY order by SEQUENCE, DESCRIPTION 
        end
 
        
        </CreateProcedureSQL> 
  </SPSimpleList> 
  <LookupView ViewName="V_SIMPLEDATALIST_COUNTRY"> 
    <ViewSQL> 
            
select DESCRIPTION [LABEL] from dbo.COUNTRY where ACTIVE = 1; 
            
        </ViewSQL> 
  </LookupView> 
  <Parameters> 
    <FormMetaData xmlns="bb_appfx_commontypes"> 
      <FormFields> 
        <FormField FieldID="INCLUDEINACTIVE" DataType="Boolean" Caption="Include inactive" DefaultValueText="0" /> 
      </FormFields> 
    </FormMetaData> 
  </Parameters> 
</SimpleDataListSpec>

STATE ABBREVIATION LIST (SIMPLE DATA LIST)

<?xml version="1.0" encoding="utf-16"?> 
<SimpleDataListSpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" ID="7fa91401-596c-4f7c-936d-6e41683121d7" Name="State Abbreviation List" Description="Returns abbreviations for all states for the given country (or all countries if no country supplied)." Author="Blackbaud Product Development" TranslationFunctionID="12040dba-4ff1-47d0-a1b0-fc40b7627d0f" NoSecurityRequired="true" xmlns="bb_appfx_simpledatalist"> 
  <DependencyList xmlns="bb_appfx_commontypes"> 
    <Dependency CatalogAssembly="Blackbaud.AppFx.Platform.Catalog.dll" CatalogItem="Blackbaud.AppFx.Platform.Catalog.UFN_STATE_GETABBREVIATION.xml" /> 
  </DependencyList> 
  <SPSimpleList SPName="USP_SIMPLEDATALIST_STATE_ABBREVIATION"> 
    <CreateProcedureSQL xmlns="bb_appfx_commontypes"> 
            
create procedure dbo.USP_SIMPLEDATALIST_STATE_ABBREVIATION 
( 
    @INCLUDEINACTIVE bit = 0, 
    @COUNTRYID uniqueidentifier = null, 
  @USEDEFAULTCOUNTRY bit = 1 
) as 
    set nocount on; 
    
    if @COUNTRYID is null and @USEDEFAULTCOUNTRY = 1 
        exec @COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT; 
    
    select 
        ID as VALUE, 
        ABBREVIATION as LABEL 
    from dbo.STATE 
    where (ACTIVE = 1 or @INCLUDEINACTIVE = 1) and (COUNTRYID = @COUNTRYID or @COUNTRYID is null) 
    order by ABBREVIATION 
            
        </CreateProcedureSQL> 
  </SPSimpleList> 
  <LookupView ViewName="V_SIMPLEDATALIST_STATE_ABBREVIATION"> 
    <ViewSQL> 
            
select ABBREVIATION [LABEL] from dbo.STATE where ACTIVE = 1; 
            
        </ViewSQL> 
  </LookupView> 
  <Parameters> 
    <FormMetaData xmlns="bb_appfx_commontypes"> 
      <FormFields> 
        <FormField FieldID="INCLUDEINACTIVE" DataType="Boolean" Caption="Include inactive" DefaultValueText="0" /> 
        <FormField FieldID="COUNTRYID" DataType="Guid" Caption="Country" /> 
        <FormField FieldID="USEDEFAULTCOUNTRY" DataType="Boolean" Caption="Use default country" DefaultValueText="1" /> 
      </FormFields> 
    </FormMetaData> 
  </Parameters> 
</SimpleDataListSpec>

We can see the behavior of these two simple data lists by playing around with the Individual, Spouse, Business Add Form. As the value in the Country simple data list changes, the data in the State simple data list changes:

If we change the country from United States to Australia, the state values change as well:

By handling the valuechanged event from the country simple data list, we can call upon the ResetDataSource () on the state's simple data list to change which states are displayed based on the country.

Private Sub _countryId_ValueChanged(ByVal sender As Object, ByVal e As UIModeling.Core.ValueChangedEventArgs) Handles _countryId.ValueChanged
 
       'update the state when the country changes
 
       'in some specs, the STATEID field is defined before COUNTRYID, we need to avoid overwriting the value when loading the form 
       If Not _countryId.Model.RootUIModel.Loading Then 
           _stateid.Value = Guid.Empty 
       End If 
       _stateid.ResetDataSource()
 
End Sub