Recently, we have gotten quite some questions regarding the implementation of custom Dynamic Option Lists using C# Code References. In this blog post, we will walk you through the process of writing such a completely custom Dynamic Option List, what is expected and what are the caveats to look out for.

How do Dynamic Option Lists work?

The Dynamic Option List code reference is supposed to fetch and return the available options, taking into account optional Key and Value filters. These filter values can be accessed through the Global Variables collection (i.e. "adamKey" and "adamValue").

The following snippet is the skeleton of your Dynamic Option List code reference, including all of the appropriate Global Variables at your disposal.

1
2
3
4
5
6
7
8
9
10
11
12
13

object ICodeReference.Resolve(ReferenceData data)
{
   DynamicOptionCollection adamOptions = data.Manager.GetGlobalVariable<DynamicOptionCollection>("adamOptions");
   int adamPageNumber = data.Manager.GetGlobalVariable<int>("adamPageNumber");
   int adamRecordsPerPage = data.Manager.GetGlobalVariable<int>("adamRecordsPerPage");
   string adamKey = data.Manager.GetGlobalVariable<string>("adamKey");
   string adamValue = data.Manager.GetGlobalVariable<string>("adamValue");

   // Fetch and filter options
   // ...

   return adamOptions;
}

The actual choice on how to populate the adamOptions collection is completely free. In the ADAM Help an example is given using built in ADAM functionality such as search expressions. Search expressions have built in support for things like filtering (using wildcards) and paging, making it very easy to use within your Dynamic Option List code reference. But off course, search expressions can only be used on ADAM objects.

Below is a copy of the code sample for a Dynamic Option List code reference, using ADAM functionality.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

object ICodeReference.Resolve(ReferenceData data)
{
   DynamicOptionCollection adamOptions = data.Manager.GetGlobalVariable<DynamicOptionCollection>("adamOptions");
   int adamPageNumber = data.Manager.GetGlobalVariable<int>("adamPageNumber");
   int adamRecordsPerPage = data.Manager.GetGlobalVariable<int>("adamRecordsPerPage");
   string adamKey = data.Manager.GetGlobalVariable<string>("adamKey");
   string adamValue = data.Manager.GetGlobalVariable<string>("adamValue");

   // Create a search expression for the user groups 
   SearchExpression oExpression = adamOptions.CreateSearchExpression("Id", "Name");
   // Create and load the collection of user groups filtered on adamKey and adamValue. 
   // We also use the adamPageNumber and adamRecordsPerPage variables to implement paging. 
   UserGroupCollection userGroups = new UserGroupCollection(data.App);
   userGroups.Load(oExpression, "Name", adamPageNumber, adamRecordsPerPage, out isLastPage);
   // Add all user groups to the list of options 
   foreach (UserGroup userGroup in userGroups)
   {
      adamOptions.Add(new DynamicOption(userGroup.Id.ToString(), userGroup.Name));
   }
 
   // Indicate whether or not the last page was reached. 
   adamOptions.IsLastPage = isLastPage;
   return adamOptions;
}

Notice the creation of the search expression on line 10. That's where the filtering based on the value of adamKey and adamValue is set up. We'll come back to that later in this post.

When is the code reference called?

It is very important to understand when and where this code reference will be called. Firstly and most obviously, it is called each time the options are requested.

However, one consideration is often being overlooked: the code reference is only supposed to return the options that are compliant with the filters as defined by the adamKey and adamValue Global Variables! Even if you don't want to enable filtering, you're still required to implement it because of the second place where your code reference is called i.e. during validation.

When your object is being validated (e.g. during saving), the dynamic option list field will validate all the selected options. It does so by passing the key of each of the selected options to the code reference. If your code reference returns an option list with exactly one match, then the selected option will be regarded as valid. If the code reference returns an empty option list, or a list with multiple options, the field will be invalid. That is why it is crucial to correctly implement filtering using the value of adamKey (and adamValue) on your custom Dynamic Option List code reference.

Writing a non-ADAM code reference

Now that we know where, when and how the Dynamic Option List's code reference is being called, we will write a completely custom code reference without using any of the built in ADAM functionality. For example, you could be querying data from an external source (e.g. another database, web service, file, ...).
Up next, we'll write a custom code reference that queries an external database.

Querying an external database

Let's assume we want to populate our Dynamic Option List with some data coming out of an external database. For the sake of simplicity, let's pop out Ye Olde DataTable once more.

1
2
3
4
5
6
7
8
9
10
11
12

private DataTable GetData()
{
   string connectionString = "somevalidconnectionstring";
   DataSet ds = new DataSet();
   using (SqlConnection conn = new SqlConnection(connectionString))
   {
      string sql = "SELECT ID, Name FROM tblSOMETABLE";
      SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
      adapter.Fill(ds);
   }
   return ds.Tables[0];
}

However, we're missing one key component here: the filtering! Remember how I referred to the CreateSearchExpression call on the DynamicOptionListCollection to generate a search expression that takes into account the values of adamKey and adamValue to filter and return the appropriate options. Since we're querying an external non-ADAM database here, we'll have to implement this crucial functionality ourselves. So let's do so and refactor our method a bit so that it can handle filters for key and value.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

private DataView GetData(string keyFilter, string valueFilter)
{
   string connectionString = "somevalidconnectionstring";
   DataSet ds = new DataSet();
   using (SqlConnection conn = new SqlConnection(connectionString))
   {
      string sql = "SELECT ID, Name FROM tblSOMETABLE";
      SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
      adapter.Fill(ds);
   }
   
   if (string.IsNullOrEmpty(keyFilter))
      keyFilter = "%";
   if (string.IsNullOrEmpty(valueFilter))
      valueFilter = "%";
   ds.Tables[0].DefaultView.RowFilter = string.Format("ID like '{0}' and Name like '{1}'", keyFilter, valueFilter);
   return ds.Tables[0].DefaultView;
}

In this sample, I have implemented a rudimentary contains filter supporting wildcards on both the ID and Name column.

Populating the options collection

Next, we simply need to populate the DynamicOptionCollection using the data from our external datasource. It is just a matter of mapping each row to a new DynamicOption object.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

object ICodeReference.Resolve(ReferenceData data)
{
    DynamicOptionCollection adamOptions = data.Manager.GetGlobalVariable<DynamicOptionCollection>("adamOptions");
    int adamPageNumber = data.Manager.GetGlobalVariable<int>("adamPageNumber");
    int adamRecordsPerPage = data.Manager.GetGlobalVariable<int>("adamRecordsPerPage");
    string adamKey = data.Manager.GetGlobalVariable<string>("adamKey");
    string adamValue = data.Manager.GetGlobalVariable<string>("adamValue");

    // Fetch and filter options
    DataView results = GetData(adamKey, adamValue);
    foreach (DataRowView row in results)
    {
        adamOptions.Add(new DynamicOption(row["ID"].ToString(), row["Name"].ToString()));
    }

    adamOptions.IsLastPage = true;
    return adamOptions;
}

That's all there's to it. Your custom Dynamic Option List is ready to use. Note that in this example we haven't implemented paging yet. But using the appropriate Global Variables (i.e. adamPageNumber and adamRecordsPerPage), that's also easy to achieve, if you would require this functionality.