Constrained Dynamic Linked Items (DLI) & Picklists

How to use a Constrained DLI to select a Finance Contact and how you can combine the use of a Dynamic Picklist and Constrained DLI to Create a Product Picker.

Basic Dynamic Linked Items and picklists make every row in the Report that they are based on, available in the picklist. However, in many situations you may find that the picklist will return results which are not relevant to the record you are working on. Adding constraint mappings to the Dynamic Linked Item will limit the picklist results by applying a filter to the underlying Report based on values found on the form fields of the record you are working on. 

When using constraint mappings on Dynamic Linked Items, you will need to be conscious of the mapping between form field and Report column when setting up the Report. For the constraint to work, your report will need to include a column which will contain the values you would expect to see on the form field. 

Below are two working examples using Constrained Dynamic Linked Items & Picklists.

Using a Constrained Dynamic Linked Item to select a Finance Contact

So for example, on Invoices we would like to add a Finance Contact field as a Dynamic Linked Item. Without setting a constraint mapping, when we use the picklist to select the Finance Contact, we will get a list of all contacts found on our database, irrespective of their relevance to the invoice.

Setting a constraint mapping will make it possible to only show people in the picklist where their employer is the customer of the invoice. Thus our list of available contacts in the picklist will contain fewer options which are directly relevant to the invoice, making it much easier and quicker to select the right contact. 

Creating the Report

Our Finance Contact will be a Person record in Workbooks and we would like to constrain our results to only display employees of the Organisation selected as the Customer of the Invoice. Therefore it would be sensible to create our Report on People, then add columns for Person Name (the column we wish to display), the Employer and the Employer Id (Id is necessary here as this is what the Customer field is stored as on the database).

Setting up the Dynamic Linked Item Custom Field

As mentioned previously, the Customer form field is actually using the object Id on the database, therefore we need to map this form field to the Employer Id Report column. You can of course add further auto-populate mappings if required. 

Now if we go to the Invoice record and select Atlantic Computer Services as our customer, the Finance Contact field will only display results for People whose Employer is Atlantic Computer Services. 

To confirm this is correct, we can compare the rows on our Report used in the Dynamic Linked Item where the Employer is Atlantic Computer Services to those given in the picklist and see that they correspond. 

Using a Dynamic Picklist and Constrained Dynamic Linked Item to Create a Product Picker

In this example we will create a product picker on Opportunity line items. The product picker consists of two custom fields on the line item; the first is called Select Category, which will offer you a list of product categories once selected, using a constrained Dynamic Linked Item, the second Select Product field will list just the products available which reflect that of the chosen product category. 

Using an auto-populate field mapping from the Product Id of the Report to the Product field on the line item, the remaining fields on the line item will be automatically populated as if we had selected the product normally. 

Creating the Report

The report which will form basis of the Dynamic Picklist and Dynamic Linked Item will need to include columns for Product Code, Category and Id at a minimum. 

To build the Dynamic Picklist for Product Category as part of our picker, we add a summary view and group by Category.

Adding the ‘Select Category’ Dynamic Picklist

We create our Select Category custom field on the Opportunity line item. This field is of type Dynamic Picklist, the Report and view we select correspond to the Product Categories view in the Report just created. 

This will create a picklist listing our product categories on the Opportunity line item. 

Adding the Select Product Constrained Dynamic Linked Item

To complete our product picker we add another Opportunity line item custom field, this time of type Dynamic Linked Item. We choose the same Report as before but select the Details view which contains all our Products. We add a constraint mapping where the Form Field is Select Category (Line items). The field we have just created as the first stage in the product picker is mapped to the Category Report Column. 

To ensure that fields on the line item are populated in the same way as if we had selected the Product normally, we must also add an auto-populate field mapping. The required mapping is Id to Product (Line items). 

With the Select Category and Select Product columns in place we now have our functioning product picker. So if we select the category Expenses, the Select Product column only gives us the option for Products which are of the category Expenses, rather than the entire list. Likewise, when we select the category as Merchandise, the products available correspond to our Products of Category Merchandise.

We can correlate this to our Product Report.

Lastly, when we select a Product, the auto-populate field mapping populates the underlying Product field with the Product Id, which then populates other line item fields.